· 6 years ago · May 23, 2019, 09:30 AM
1--new command window
2--loop through the first 100 numbers and display which are odd and which are even
3--you can't increase the step; it doesn't exist
4-- i can write -> for i in reverse 1..100 loop
5--you can't change the i inside of the loop (e.g.: you cand write i:=i+1; this is a standard syntax for
6--don't put a comment after '/' and no spaces before it
7-- i is declared and allocated automatically by pl (and also automatically deallocated - it only exists in the loop)
8-- cu f8 rulez
9set serveroutput on
10declare
11 i number:=500;
12 j number;
13begin
14 for i in 1..100 loop
15 -- i:=i+2 --it doesn't work
16 if i mod 2 = 0 then
17 dbms_output.put_line(i||' is even');
18 else
19 dbms_output.put_line(i||' is odd');
20 end if;
21 j:=i;
22 end loop;
23 i:=i+1;
24 dbms_output.put_line(i||' is a number'); --this is a different i
25 dbms_output.put_line(j);
26end;
27/
28
29
30-- daca scriu else if tre sa inchid inca un if
31-- conditiile sunt evaluate in ordine
32set serveroutput on
33begin
34 for i in 1..100 loop
35 if i mod 2 = 0 then
36 dbms_output.put_line(i||' is even');
37 elsif i in (90,94,96,98) then
38 dbms_output.put_line(i||' is a special even number');
39 elsif i in (91,93,95,97) then
40 dbms_output.put_line(i||' is a special odd number');
41 else
42 dbms_output.put_line(i||' is odd');
43 end if;
44 end loop;
45end;
46/
47
48--sum of odds and sum of evens
49set serveroutput on size 10000
50declare
51 s1 number:=0;
52 s2 number:=0;
53begin
54 for i in 1..100 loop
55 if i mod 2 = 0 then
56 s1:=s1+i;
57 else
58 s2:=s2+i;
59 end if;
60 end loop;
61 dbms_output.put_line('The sum of even numbers is: '|| s1);
62 dbms_output.put_line('The sum of odd numbers is: '|| s2);
63end;
64/
65
66--sum of odds and sum of evens
67--implicit conversion from a string to a number
68set serveroutput on size 10000
69declare
70 s1 number:=0;
71 s2 number:=0;
72 x varchar2(20):='914272';
73begin
74 for i in 1..length(x) loop
75 if substr(x,i,1) mod 2 = 0 then
76 s1:=s1+substr(x,i,1);
77 elsif substr(x,i,1) mod 2 <> 0 then
78 s2:=s2+substr(x,i,1);
79 end if;
80 end loop;
81 dbms_output.put_line('The sum of even numbers is: '|| s1);
82 dbms_output.put_line('The sum of odd numbers is: '|| s2);
83end;
84/
85
86--sum of odds and sum of evens
87--implicit conversion from a string to a number
88set serveroutput on size 10000
89declare
90 s1 number:=0;
91 s2 number:=0;
92 x varchar2(20):='&n'; --substitution variable
93begin
94 for i in 1..length(x) loop
95 if substr(x,i,1) mod 2 = 0 then
96 s1:=s1+substr(x,i,1);
97 elsif substr(x,i,1) mod 2 <> 0 then
98 s2:=s2+substr(x,i,1);
99 end if;
100 end loop;
101 dbms_output.put_line('The sum of even numbers is: '|| s1);
102 dbms_output.put_line('The sum of odd numbers is: '|| s2);
103end;
104/
105--sum of odds and sum of evens
106--implicit conversion from a string to a number
107set serveroutput on size 10000
108declare
109 s1 number:=0;
110 s2 number:=0;
111 x varchar2(20):='789a4*75';
112begin
113 for i in 1..length(x) loop
114 if substr(x,i,1) between '0' and '9' then
115 if substr(x,i,1) mod 2 = 0 then
116 s1:=s1+substr(x,i,1);
117 elsif substr(x,i,1) mod 2 <> 0 then
118 s2:=s2+substr(x,i,1);
119 end if;
120 end if;
121 end loop;
122 dbms_output.put_line('The sum of even numbers is: '|| s1);
123 dbms_output.put_line('The sum of odd numbers is: '|| s2);
124end;
125/
126
127--sum of odds and sum of evens
128--implicit conversion from a string to a number
129set serveroutput on size 10000
130declare
131 s1 number:=0;
132 s2 number:=0;
133 x varchar2(20):='789a4*75';
134begin
135 for i in 1..length(x) loop
136 if substr(x,i,1) not between '0' and '9' then
137 null;
138 elsif substr(x,i,1) mod 2 = 0 then
139 s1:=s1+substr(x,i,1);
140 elsif substr(x,i,1) mod 2 <> 0 then
141 s2:=s2+substr(x,i,1);
142 end if;
143 end loop;
144 dbms_output.put_line('The sum of even numbers is: '|| s1);
145 dbms_output.put_line('The sum of odd numbers is: '|| s2);
146end;
147/
148
149--sum of odds and sum of evens
150--implicit conversion from a string to a number
151--this way it stops when it arrives to 'a'
152set serveroutput on size 10000
153declare
154 s1 number:=0;
155 s2 number:=0;
156 x varchar2(20):='789a4*75';
157begin
158 for i in 1..length(x) loop
159 if substr(x,i,1) mod 2 = 0 then
160 s1:=s1+substr(x,i,1);
161 elsif substr(x,i,1) mod 2 <> 0 then
162 s2:=s2+substr(x,i,1);
163 end if;
164 end loop;
165 exception
166 when others then
167 dbms_output.put_line('The sum of even numbers is: '|| s1);
168 dbms_output.put_line('The sum of odd numbers is: '|| s2);
169end;
170/
171
172--sum of odds and sum of evens
173--implicit conversion from a string to a number
174--this way it stops when it arrives to 'a'
175-- the slash is only for the outer block
176set serveroutput on size 10000
177declare
178 s1 number:=0;
179 s2 number:=0;
180 x varchar2(20):='789a4*75';
181begin
182 for i in 1..length(x) loop
183 begin
184 if substr(x,i,1) mod 2 = 0 then
185 s1:=s1+substr(x,i,1);
186 elsif substr(x,i,1) mod 2 <> 0 then
187 s2:=s2+substr(x,i,1);
188 end if;
189 exception --when the exception is not raised it goes on with the sum
190 when others then null;
191 end;
192 end loop;
193 dbms_output.put_line('The sum of even numbers is: '|| s1);
194 dbms_output.put_line('The sum of odd numbers is: '|| s2);
195end;
196/
197
198--sum of odds and sum of evens
199--implicit conversion from a string to a number
200--this way it stops when it arrives to 'a'
201-- the slash is only for the outer block
202set serveroutput on size 10000
203 variable s1 number
204 --they are like global variables
205 variable s2 number
206 --i can't initialize them here
207declare
208 x varchar2(20):='789a4*75';
209begin
210 :s1:=0;
211 :s1:=0;
212 -- i initialize them here
213 for i in 1..length(x) loop
214 begin
215 if substr(x,i,1) mod 2 = 0 then
216 :s1:=:s1+substr(x,i,1);
217 elsif substr(x,i,1) mod 2 <> 0 then
218 :s2:=:s2+substr(x,i,1);
219 end if;
220 exception --when the exception is not raised it goes on with the sum
221 when others then null;
222 end;
223 end loop;
224end;
225/
226print s1
227begin
228 dbms_output.put_line('The sum of even numbers is: '|| :s1);
229 dbms_output.put_line('The sum of odd numbers is: '|| :s2);
230end;
231/
232
233Homework:
234String e.g.: A E e D R 1 G
235Display the substring of vowels, of consonants and the substring of other characters
236set serveroutput on
237declare
238 x varchar2(20):='Aa*b9DeE+7';
239 s1 varchar2(20);
240 s2 varchar2(20);
241 s3 varchar2(20);
242begin
243
244 for i in 1..length(x) loop
245 if ((substr(x, i, 1) between 'a' and 'z') OR (substr(x, i, 1) between 'A' and 'Z')) then
246 if substr(x, i, 1) IN ('A', 'a', 'E', 'e', 'i', 'I', 'o', 'O', 'u', 'U') then
247 s1:=s1|| substr(x, i, 1);
248 else s2:=s2||substr(x, i, 1);
249 end if;
250 else s3:=s3||substr(x, i, 1);
251 end if;
252
253 end loop;
254dbms_output.put_line('The substring of vowels is: '|| s1);
255dbms_output.put_line('The substring of consonants is: '|| s2);
256dbms_output.put_line('The substring of other characters is: '|| s3);
257end;
258/
259
260
261
262
263
264
265------------------
266set serveroutput on
267declare
268 x varchar2(20):='Aa*7bD*cE';
269 s1 varchar2(20);
270 s2 varchar2(20);
271 s3 varchar2(20);
272
273begin
274 for i in 1..length(x) loop
275 if UPPER(substr(x,i,1)) BETWEEN 'A' AND 'Z' then
276 if UPPER(substr(x,i,1)) IN ('A', 'E', 'I', 'O', 'U') then
277 s1:=s1||substr(x,i,1);
278 else s2:=s2||substr(x,i,1);
279 end if;
280 else s3:=s3||substr(x,i,1);
281 end if;
282 end loop;
283 dbms_output.put_line('The substring of vowels is: '||s1);
284 dbms_output.put_line('The substring of consonants is: '||s2);
285 dbms_output.put_line('The substring of other characters is: '||s3);
286end;
287/
288
289
290--in a case we need an else if the when conditions don't cover all the cases or you treat it as an exception
291set serveroutput on
292declare
293 x varchar2(20):='Aa*7bD*cE';
294 s1 varchar2(20);
295 s2 varchar2(20);
296 s3 varchar2(20);
297
298begin
299 for i in 1..length(x) loop
300 case
301 when UPPER(substr(x,i,1)) IN ('A', 'E', 'I', 'O', 'U') then
302 s1:=s1||substr(x,i,1);
303 when UPPER(substr(x,i,1)) NOT IN ('A', 'E', 'I', 'O', 'U') AND UPPER(substr(x,i,1)) BETWEEN 'A' AND 'Z' then
304 s2:=s2||substr(x,i,1);
305 else s3:=s3||substr(x,i,1);
306 end case;
307 end loop;
308 dbms_output.put_line('The substring of vowels is: '||s1);
309 dbms_output.put_line('The substring of consonants is: '||s2);
310 dbms_output.put_line('The substring of other characters is: '||s3);
311end;
312/
313 
314set serveroutput on
315declare
316 x varchar2(20):='Aa*7bD*cE';
317 s1 varchar2(20);
318 s2 varchar2(20);
319 s3 varchar2(20);
320 i number:=0;
321
322begin
323 while i<=length(x) loop
324 i:=i+1;
325 case
326 when UPPER(substr(x,i,1)) IN ('A', 'E', 'I', 'O', 'U') then
327 s1:=s1||substr(x,i,1);
328 when UPPER(substr(x,i,1)) NOT IN ('A', 'E', 'I', 'O', 'U') AND UPPER(substr(x,i,1)) BETWEEN 'A' AND 'Z' then
329 s2:=s2||substr(x,i,1);
330 else s3:=s3||substr(x,i,1);
331 end case;
332 end loop;
333 dbms_output.put_line('The substring of vowels is: '||s1);
334 dbms_output.put_line('The substring of consonants is: '||s2);
335 dbms_output.put_line('The substring of other characters is: '||s3);
336end;
337/
338
339set serveroutput on
340declare
341 x varchar2(20):='Aa*7bD*cE';
342 s1 varchar2(20);
343 s2 varchar2(20);
344 s3 varchar2(20);
345 i number:=0;
346
347begin
348 loop
349 i:=i+1;
350 exit when i>length(x); --i can put this condition anywhere
351 --exit also works with for and while
352 case
353 when UPPER(substr(x,i,1)) IN ('A', 'E', 'I', 'O', 'U') then
354 s1:=s1||substr(x,i,1);
355 when UPPER(substr(x,i,1)) NOT IN ('A', 'E', 'I', 'O', 'U') AND UPPER(substr(x,i,1)) BETWEEN 'A' AND 'Z' then
356 s2:=s2||substr(x,i,1);
357 else s3:=s3||substr(x,i,1);
358 end case;
359 end loop;
360 dbms_output.put_line('The substring of vowels is: '||s1);
361 dbms_output.put_line('The substring of consonants is: '||s2);
362 dbms_output.put_line('The substring of other characters is: '||s3);
363end;
364/
365 
366--Display the names of the employees with IDs between 10 and 500 that have salaries between 2000 and 10000;
367set serveroutput on
368declare
369nume_emp varchar2(20);
370begin
371 for i in 10..500 loop
372 begin
373 select nume into nume_emp
374 from ANGAJATI
375 where ((salariul between 2000 and 10000) AND (id_angajat=i));
376
377 if nume_emp is not null then
378 dbms_output.put_line('Name: '||nume_emp);
379 end if;
380
381 exception
382 when others then null;
383 end;
384 end loop;
385end;
386/
387
388
389--Display the names of the employees with IDs between 10 and 500 that have salaries between 2000 and 10000;
390set serveroutput on
391declare
392nume_emp angajati.nume%type; --has the same data type as the colomn nume frm table angajati
393prenume_emp angajati.prenume%type;
394begin
395 for i in 10..500 loop
396 begin
397 select nume, prenume into nume_emp, prenume_emp
398 from ANGAJATI
399 where ((salariul between 2000 and 10000) AND (id_angajat=i));
400
401 if nume_emp is not null then
402 dbms_output.put_line('Name: '||nume_emp || ' ' || prenume_emp);
403 end if;
404
405 exception
406 when others then null;
407 end;
408 end loop;
409end;
410/
411
412 
413set serveroutput on
414declare
415type t_ang is record(
416nume_emp angajati.nume%type,
417prenume_emp angajati.prenume%type,
418salary angajati.salariul%type);
419v_ang t_ang;
420begin
421 for i in 10..500 loop
422 begin
423 select nume, prenume, salariul into v_ang
424 from ANGAJATI
425 where ((salariul between 2000 and 10000) AND (id_angajat=i));
426
427 if v_ang.nume_emp is not null then
428 dbms_output.put_line('Name: '||v_ang.nume_emp || ' ' || v_ang.prenume_emp|| ' ' || v_ang.salary);
429 end if;
430
431 exception
432 when others then null;
433 end;
434 end loop;
435end;
436/
437
438set serveroutput on size 10000
439declare
440type t_ang is record(
441nume_emp angajati.nume%type,
442prenume_emp angajati.prenume%type,
443salary angajati.salariul%type);
444v_ang t_ang;
445n number:=0;
446begin
447 for i in 10..500 loop
448 select count (*) into n
449 from ANGAJATI
450 where ((salariul between 2000 and 10000) AND (id_angajat=i));
451
452 if (n=1) then --pentru ca sunt in for si la fiecare id eu am un singur angajat egal cu id-ul i
453 select nume, prenume, salariul into v_ang
454 from ANGAJATI
455 where ((salariul between 2000 and 10000) AND (id_angajat=i));
456
457 if v_ang.nume_emp is not null then
458 dbms_output.put_line('Name: '||v_ang.nume_emp || ' ' || v_ang.prenume_emp|| ' ' || v_ang.salary);
459 end if;
460 end if;
461 end loop;
462end;
463/
464
465set serveroutput on size 10000
466declare
467v_ang angajati%rowtype; --are coloane ca in angajati
468n number:=0;
469begin
470 for i in 10..500 loop
471 select count (*) into n
472 from ANGAJATI
473 where ((salariul between 2000 and 10000) AND (id_angajat=i));
474
475 if (n=1) then
476 select * into v_ang -- because you can't put 3 values in 10 or the other way around, you must put *
477 from ANGAJATI
478 where ((salariul between 2000 and 10000) AND (id_angajat=i));
479
480 if v_ang.nume is not null then
481 dbms_output.put_line('Name: '||v_ang.nume || ' ' || v_ang.prenume|| ' ' || v_ang.salariul); --au numele din tabele
482 end if;
483 end if;
484 end loop;
485end;
486/
487
488Homework: Display the product names that have IDs between 1000 and 20000 and a list_price between 10 and 50
489
490set serveroutput on
491declare
492 v_denumire produse.denumire_produs%type;
493begin
494 for i in 1000..2000 loop
495 begin
496 select denumire_produs into v_denumire
497 from produse
498 where id_produs=i and pret_lista between 10 and 50;
499 dbms_output.put_line('Denumire: '||v_denumire);
500 exception
501 when others then null;
502 end;
503 end loop;
504end;
505/
506
507----------------------
508
509--SQL(IMPLICIT) CURSORS
510
511begin
512 dbms_output.put_line(SQL%ROWCOUNT||' rows were updated');
513 update angajati set salariul=salariul*1.1
514 where id_departament=500;
515 if SQL%FOUND then
516 dbms_output.put_line(SQL%ROWCOUNT||' rows were updated');
517 else
518 dbms_output.put_line('No rows were updated');
519 end if;
520 rollback;
521 end;
522/
523
524begin
525 dbms_output.put_line(SQL%ROWCOUNT||' rows were updated');
526 update angajati set salariul=salariul*1.1
527 where id_departament=500;
528 if SQL%FOUND then
529 dbms_output.put_line(SQL%ROWCOUNT||' rows were updated');
530 else
531 dbms_output.put_line('No rows were updated');
532 end if;
533
534 delete from departamente where id_manager is null;
535 if SQL%FOUND then
536 dbms_output.put_line(SQL%ROWCOUNT||' rows were deleted');
537 else
538 dbms_output.put_line('No rows were deleted');
539 end if;
540 rollback;
541 end;
542/
543
544
545set serverouput on
546declare
547 v_name varchar2(25);
548begin
549 select nume into v_name from angajati where id_angajat=200;
550 if SQL%FOUND then
551 dbms_output.put_line(SQL%ROWCOUNT||' rows were found');
552 else
553 dbms_output.put_line('No rows were found');
554 end if;
555 end;
556/
557
558 
559--this makes little sense
560--if it runs successfully it always displays 1 row, if it doesn't it return an exception
561set serverouput on
562declare
563 v_name varchar2(25);
564begin
565 select nume into v_name from angajati where id_angajat=2000; --this select return an exception because this exmployee doesn't exist
566 if SQL%FOUND then
567 dbms_output.put_line(SQL%ROWCOUNT||' rows were found');
568 else
569 dbms_output.put_line('No rows were found');
570 end if;
571 end;
572/
573
574set serverouput on
575declare
576 v_name varchar2(25);
577begin
578 select nume into v_name from angajati where id_angajat>200; --it returns more than one row -> exception
579 if SQL%FOUND then
580 dbms_output.put_line(SQL%ROWCOUNT||' rows were found');
581 else
582 dbms_output.put_line('No rows were found');
583 end if;
584 end;
585/
586
587
588set serverouput on
589declare
590 v_name varchar2(25);
591begin
592 select nume into v_name from angajati where id_angajat>=200;
593 dbms_output.put_line('1 row was found');
594 exception
595 when others then
596 dbms_output.put_line('res: '||SQL%ROWCOUNT);
597 end;
598/
599 
600--EXPLICIT CURSORS
601
602set serveroutput on size 10000
603declare
604 cursor c is select nume, prenume, salariul
605 from angajati
606 order by salariul desc;
607
608 v_nume varchar2(25);
609 v_prenume varchar2(25);
610 v_salariul number;
611begin
612 if not c%isopen then
613 open c;
614 end if;
615
616 loop
617 fetch c into v_nume, v_prenume, v_salariul;
618 exit when c%NOTFOUND;
619 dbms_output.put_line(c%ROWCOUNT||' Emp: '||v_nume||' '||v_prenume||' '||v_salariul);
620 end loop;
621 close c;
622end;
623/
624
625
626set serveroutput on size 10000
627declare
628 cursor c is select nume, prenume, salariul
629 from angajati
630 order by salariul desc;
631
632 v_nume varchar2(25);
633 v_prenume varchar2(25);
634 v_salariul number;
635begin
636 if not c%isopen then
637 open c;
638 end if;
639
640 loop
641 fetch c into v_nume, v_prenume, v_salariul;
642 dbms_output.put_line(c%ROWCOUNT||' Emp: '||v_nume||' '||v_prenume||' '||v_salariul);
643 exit when c%NOTFOUND; --the last row is displayed twice
644 end loop;
645 close c;
646end;
647/
648 
649set serveroutput on size 10000
650declare
651 cursor c is select nume, prenume, salariul
652 from angajati
653 order by salariul desc;
654
655 v_nume varchar2(25);
656 v_prenume varchar2(25);
657 v_salariul number;
658begin
659 if not c%isopen then
660 open c;
661 end if;
662
663 loop
664 fetch c into v_nume, v_prenume, v_salariul;
665 exit when c%NOTFOUND;
666 dbms_output.put_line(c%ROWCOUNT||' Emp: '||v_nume||' '||v_prenume||' '||v_salariul);
667 end loop;
668 dbms_output.put_line('No of rows: ' || c%ROWCOUNT);
669 close c;
670end;
671/
672
673
674set serveroutput on size 10000
675declare
676 cursor c is select nume, prenume, salariul
677 from angajati
678 order by salariul desc;
679
680 r c%rowtype;
681
682begin
683 if not c%isopen then
684 open c;
685 end if;
686
687 loop
688 fetch c into r;
689 exit when c%NOTFOUND;
690 dbms_output.put_line(c%ROWCOUNT||' Emp: '||r.nume||' '||r.prenume||' '||r.salariul);
691 end loop;
692 dbms_output.put_line('No of rows: ' || c%ROWCOUNT);
693 close c;
694end;
695/
696 
697
698set serveroutput on size 10000
699declare
700 cursor c is select nume, prenume, salariul
701 from angajati
702 order by salariul desc;
703
704 r c%rowtype;
705
706begin
707 if not c%isopen then
708 open c;
709 end if;
710
711 loop
712 fetch c into r;
713 exit when c%NOTFOUND or c%ROWCOUNT>5; --afiseaza fix 5 randuri daca pui >
714 dbms_output.put_line(c%ROWCOUNT||' Emp: '||r.nume||' '||r.prenume||' '||r.salariul);
715 end loop;
716 dbms_output.put_line('No of rows: ' || c%ROWCOUNT);
717 close c;
718end;
719/
720
721
722/*Display the top 5 clients (first and last name) according to their total ordered value*/
723set serveroutput on;
724declare
725cursor c is select prenume_client, nume_client, sum(rc.pret*rc.cantitate) suma
726from clienti c, comenzi cz, rand_comenzi rc
727where c.id_client=cz.id_client and cz.nr_comanda=rc.nr_comanda
728group by prenume_client, nume_client
729order by sum(rc.pret*rc.cantitate) desc;
730
731r c%rowtype;
732
733begin
734 if not c%isopen then
735 open c;
736 end if;
737
738 loop
739 fetch c into r;
740 exit when c%NOTFOUND or c%ROWCOUNT>5;
741 dbms_output.put_line(c%ROWCOUNT||' Emp: '||r.prenume_client||' '||r.nume_client||' '||r.suma);
742 end loop;
743 dbms_output.put_line('No of rows: ' || c%ROWCOUNT);
744 close c;
745end;
746/
747
748
749
750--homework: top 5 products according to the total ordered value
751
752set serveroutput on;
753declare
754cursor c is select p.id_produs, p.denumire_produs, sum(rc.pret*rc.cantitate) suma
755from produse p, rand_comenzi rc
756where p.id_produs=rc.id_produs
757group by p.id_produs, p.denumire_produs
758order by sum(rc.pret*rc.cantitate) desc;
759
760r c%rowtype;
761
762begin
763 if not c%isopen then
764 open c;
765 end if;
766
767 loop
768 fetch c into r;
769 exit when c%NOTFOUND or c%ROWCOUNT>5;
770 dbms_output.put_line(c%ROWCOUNT||' Produs: '||r.id_produs||' '||r.denumire_produs||' '||r.suma);
771 end loop;
772 dbms_output.put_line('No of rows: ' || c%ROWCOUNT);
773 close c;
774end;
775/
776
777
778------------------------
779set serveroutput on
780declare
781 cursor c is
782 select denumire_produs, sum(pret*cantitate) total
783 from produse p, rand_comenzi rc
784 where p.id_produs = rc.id_produs
785 group by denumire_produs
786 order by total desc;
787begin
788 for r in c loop
789 dbms_output.put_line(c%ROWCOUNT||' denumire_produs'||' '||r.total);
790 exit when c%rowcount = 5;
791 dbms_output.put_line('No of rows: ' || c%ROWCOUNT);
792 end loop;
793end;
794/
795
796--inline cursor
797set serveroutput on size 20000
798begin
799 for r in (select denumire_produs, sum(pret*cantitate) total
800 from produse p, rand_comenzi rc
801 where p.id_produs = rc.id_produs
802 group by denumire_produs
803 order by total desc) loop
804 dbms_output.put_line(r.denumire_produs||' '||r.total);
805 end loop;
806end;
807/
808
809
810--display the departments that have at least one employee
811set serveroutput on
812declare
813 cursor c is
814 select denumire_departament, count(a.id_angajat)
815 from departamente d, angajati a
816 where d.id_departament=a.id_departament
817 having count(a.id_angajat)>=1
818 group by denumire_departament;
819begin
820 for r in c loop
821 dbms_output.put_line(c%ROWCOUNT||'' || r.denumire_departament);
822 end loop;
823end;
824/
825 
826--display the departments that have at least one employee
827set serveroutput on
828declare
829 cursor c is
830 select denumire_departament
831 from departamente d, angajati a
832 where d.id_departament=a.id_departament
833 group by denumire_departament;
834
835 r c%rowtype;
836begin
837 if not c%isopen then
838 open c;
839 end if;
840
841 loop
842 fetch c into r;
843 exit when c%notfound;
844 dbms_output.put_line(r.denumire_departament);
845 end loop;
846 close c;
847
848end;
849/
850
851--display the departments that have at least one employee and their employees
852set serveroutput on size 10000
853declare
854
855cursor c is
856 select d.id_departament, d.denumire_departament
857 from departamente d, angajati a
858 where d.id_departament=a.id_departament
859 group by d.id_departament, d.denumire_departament;
860
861cursor d is select * from angajati;
862
863begin
864 for r in c loop
865 dbms_output.put_line(c%ROWCOUNT||' '|| r.denumire_departament);
866 for s in d loop
867 if (s.id_departament = r.id_departament) then
868 dbms_output.put_line(' ' || s.nume || ' ' || s.prenume);
869 end if;
870 end loop;
871 end loop;
872end;
873/
874 
875--display the departments that have at least one employee
876set serveroutput on size 20000
877declare
878 cursor c is
879 select d.id_departament, d.denumire_departament from departamente d, angajati a
880 where d.id_departament=a.id_departament
881 --having count(a.id_angajat) >0
882 group by d.id_departament, d.denumire_departament;
883 cursor d(p_id_dep number) is select * from angajati where id_departament=p_id_dep ;
884begin
885 for r in c loop
886 dbms_output.put_line(c%ROWCOUNT||' '||r.denumire_departament);
887 for s in d(20) loop
888
889 dbms_output.put_line(' '||s.nume||' '||s.prenume);
890
891 end loop;
892 end loop;
893
894end;
895/
896
897--display the departments that have at least one employee
898set serveroutput on size 20000
899
900declare
901
902 cursor c is
903 select d.id_departament, d.denumire_departament from departamente d, angajati a
904 where d.id_departament=a.id_departament
905 group by d.id_departament, d.denumire_departament;
906
907 cursor d(p_id_dep number) is
908 select * from angajati
909 where id_departament=p_id_dep;
910
911begin
912 for r in c loop
913 dbms_output.put_line(c%ROWCOUNT||' '||r.denumire_departament);
914 for s in d(r.id_departament) loop
915 dbms_output.put_line(' '||s.nume||' '||s.prenume);
916 end loop;
917 end loop;
918end;
919/
920 
921--display the departments that have at least one employee, their employees, and the number of employees
922set serveroutput on size 20000
923
924declare
925 angajati number;
926 cursor c is
927 select d.id_departament, d.denumire_departament
928 from departamente d, angajati a
929 where d.id_departament=a.id_departament
930 group by d.id_departament, d.denumire_departament;
931
932 cursor d(p_id_dep number) is
933 select * from angajati
934 where id_departament=p_id_dep;
935
936begin
937 for r in c loop
938 angajati :=0;
939 dbms_output.put_line(c%ROWCOUNT||' '||r.denumire_departament);
940 for s in d(r.id_departament) loop
941 dbms_output.put_line(' '||s.nume||' '||s.prenume);
942 angajati:=angajati+1;
943 end loop;
944 dbms_output.put_line('nr angajati: ' || angajati);
945 end loop;
946end;
947/
948
949
950--version 2
951set serveroutput on size 20000
952declare
953 angajati number;
954 cursor c is
955 select d.id_departament, d.denumire_departament from departamente d, angajati a
956 where d.id_departament=a.id_departament
957 group by d.id_departament, d.denumire_departament;
958 cursor d(p_id_dep number) is select * from angajati where id_departament=p_id_dep ;
959begin
960
961 for r in c loop
962 angajati := 0;
963 dbms_output.put_line(c%ROWCOUNT||' '||r.denumire_departament);
964 for s in d(r.id_departament) loop
965 dbms_output.put_line(' '||s.nume||' '||s.prenume);
966 angajati := d%rowcount;
967 end loop;
968 dbms_output.put_line('nr nagajati: '||angajati);
969 end loop;
970end;
971/
972 
973--version 3
974set serveroutput on size 20000
975declare
976
977 cursor c is
978 select d.id_departament, d.denumire_departament, count(a.id_angajat) nr
979 from departamente d, angajati a
980 where d.id_departament=a.id_departament
981 --having count(a.id_angajat) >0
982 group by d.id_departament, d.denumire_departament;
983 cursor d(p_id_dep number) is select * from angajati where id_departament=p_id_dep ;
984
985begin
986
987 for r in c loop
988
989 dbms_output.put_line(c%ROWCOUNT||' '||r.denumire_departament);
990 for s in d(r.id_departament) loop
991 dbms_output.put_line(' '||s.nume||' '||s.prenume);
992
993 end loop;
994 dbms_output.put_line('nr nagajati: '||r.nr);
995 end loop;
996
997end;
998/
999
1000--version 4
1001--he also displays the departments with no employees (left outer join)
1002set serveroutput on size 20000
1003declare
1004
1005 cursor c is
1006 select d.id_departament, d.denumire_departament, count(a.id_angajat) nr
1007 from departamente d, angajati a
1008 where d.id_departament=a.id_departament(+)
1009 group by d.id_departament, d.denumire_departament;
1010 cursor d(p_id_dep number) is select * from angajati where id_departament=p_id_dep ;
1011
1012begin
1013
1014 for r in c loop
1015
1016 dbms_output.put_line(c%ROWCOUNT||' '||r.denumire_departament);
1017 for s in d(r.id_departament) loop
1018 dbms_output.put_line(' '||s.nume||' '||s.prenume);
1019
1020 end loop;
1021 dbms_output.put_line('nr angajati: '||r.nr);
1022 end loop;
1023end;
1024/ 
1025--schimbarea mesajului
1026set serveroutput on size 20000
1027declare
1028
1029 cursor c is
1030 select d.id_departament, d.denumire_departament, count(a.id_angajat) nr
1031 from departamente d, angajati a
1032 where d.id_departament=a.id_departament
1033 group by d.id_departament, d.denumire_departament;
1034 cursor d(p_id_dep number) is select * from angajati where id_departament=p_id_dep ;
1035
1036begin
1037
1038 for r in c loop
1039
1040 dbms_output.put_line(c%ROWCOUNT||' '||r.denumire_departament);
1041 for s in d(r.id_departament) loop
1042 dbms_output.put_line(' '||s.nume||' '||s.prenume);
1043
1044 end loop;
1045 if r.nr = 1 then
1046 dbms_output.put_line('there is '||r.nr||' employee');
1047 else
1048 dbms_output.put_line('there are '||r.nr||' employees');
1049 end if;
1050 end loop;
1051end;
1052/
1053
1054--change the message
1055set serveroutput on size 20000
1056declare
1057
1058 cursor c is
1059 select d.id_departament, d.denumire_departament, count(a.id_angajat) nr
1060 from departamente d, angajati a
1061 where d.id_departament=a.id_departament
1062 group by d.id_departament, d.denumire_departament;
1063 cursor d(p_id_dep number) is select * from angajati where id_departament=p_id_dep ;
1064
1065begin
1066
1067 for r in c loop
1068
1069 dbms_output.put_line(c%ROWCOUNT||' '||r.denumire_departament);
1070 for s in d(r.id_departament) loop
1071 dbms_output.put_line(' '||s.nume||' '||s.prenume);
1072
1073 end loop;
1074 if r.nr = 1 then
1075 dbms_output.put_line('there is '||r.nr||' employee');
1076 elsif r.nr=0 then
1077 dbms_output.put_line('there are no employees');
1078 else
1079 dbms_output.put_line('there are '||r.nr||' employees');
1080 end if;
1081 end loop;
1082
1083end;
1084/
1085
1086--change the second cursor to inline cursor
1087set serveroutput on size 20000
1088declare
1089
1090 cursor c is
1091 select d.id_departament, d.denumire_departament, count(a.id_angajat) nr
1092 from departamente d, angajati a
1093 where d.id_departament=a.id_departament
1094 group by d.id_departament, d.denumire_departament;
1095begin
1096
1097 for r in c loop
1098
1099 dbms_output.put_line(c%ROWCOUNT||' '||r.denumire_departament);
1100 for s in (select * from angajati where id_departament=r.id_departament ) loop
1101 dbms_output.put_line(' '||s.nume||' '||s.prenume);
1102
1103 end loop;
1104 if r.nr = 1 then
1105 dbms_output.put_line('there is '||r.nr||' employee');
1106 elsif r.nr=0 then
1107 dbms_output.put_line('there are no employees');
1108 else
1109 dbms_output.put_line('there are '||r.nr||' employees');
1110 end if;
1111 end loop;
1112end;
1113/
1114
1115--homework: display the products from the category with the most products
1116 
1117--homework: display the products from the category with the most products
1118set serveroutput on size 200000
1119
1120declare
1121 cursor c is select id_produs, denumire_produs, categorie from produse;
1122 v_categorie produse.categorie%type;
1123
1124begin
1125 select categorie into v_categorie
1126 from produse
1127 group by categorie
1128 having count(*)=(select max(count(*)) from produse group by categorie);
1129 dbms_output.put_line('The category with the most products is ' || v_categorie || ' and the products from this category are: ');
1130 for r in c loop
1131 if r.categorie = v_categorie then
1132 dbms_output.put_line(r.id_produs||' '||r.denumire_produs);
1133 end if;
1134 end loop;
1135end;
1136/
1137---------------
1138
1139
1140--homework: display the products from the category with the most products
1141
1142set serveroutput on size 200000
1143
1144declare
1145 cursor c is select id_produs, denumire_produs, categorie from produse;
1146 v_categorie produse.categorie%type;
1147
1148begin
1149 select categorie into v_categorie
1150 from produse
1151 group by categorie
1152 having count(*)=(select max(count(*)) from produse group by categorie);
1153 dbms_output.put_line('The category with the most products is ' || v_categorie || ' and the products from this category are: ');
1154 for r in c loop
1155 if r.categorie = v_categorie then
1156 dbms_output.put_line(r.id_produs||' '||r.denumire_produs);
1157 end if;
1158 end loop;
1159end;
1160/
1161
1162set serveroutput on size 20000
1163
1164declare
1165max_c number:=0;
1166max_categorie varchar(20);
1167cursor c is select count(*) counter, categorie from produse
1168group by categorie;
1169
1170begin
1171 for line in c loop
1172 if max_c <line.counter then
1173 max_c := line.counter;
1174 max_categorie := line.categorie;
1175 end if;
1176 end loop;
1177 for r in (select* from produse where categorie = max_categorie) loop
1178 dbms_output.put_line(r.denumire_produs);
1179 end loop;
1180end;
1181/
1182 
1183set serveroutput on size 20000
1184
1185declare
1186max_c number:=0;
1187max_categorie varchar(20);
1188cursor c is select categorie from produse
1189group by categorie order by count(*) desc;
1190
1191begin
1192 open c;
1193 fetch c into max_categorie;
1194 close c;
1195
1196 for r in (select* from produse where categorie = max_categorie) loop
1197 dbms_output.put_line(r.denumire_produs);
1198 end loop;
1199end;
1200/
1201
1202set serveroutput on size 20000
1203
1204declare
1205max_c number:=0;
1206max_categorie varchar(20);
1207d sys_refcursor;
1208v_den_produs varchar2(100);
1209
1210cursor c is select categorie from produse
1211group by categorie order by count(*) desc;
1212
1213begin
1214 open c;
1215 fetch c into max_categorie;
1216 close c;
1217
1218 open d for 'select denumire_produs from produse where categorie = :1' using max_categorie;
1219 loop
1220 fetch d into v_den_produs;
1221 exit when d%notfound;
1222 dbms_output.put_line(v_den_produs);
1223 end loop;
1224 close d;
1225end;
1226/
1227
1228 
1229set serveroutput on
1230
1231declare
1232 cursor c is select * from angajati where id_departament=50 for update;
1233 r c%rowtype;
1234begin
1235 open c;
1236 loop
1237 fetch c into r;
1238 exit when c%notfound;
1239 dbms_output.put_line('Raising the salary for ' ||r.nume|| ' ' || r.prenume);
1240 update angajati set salariul=salariul*1.1 where current of c;
1241 end loop;
1242 close c;
1243end;
1244/
1245
1246
1247set serveroutput on size 2000
1248
1249declare
1250 v_full_name varchar2(100);
1251begin
1252 select nume||' '|| prenume into v_full_name from angajati where id_angajat=1900;
1253 dbms_output.put_line('Employee: '||v_full_name);
1254 exception
1255 when NO_DATA_FOUND then
1256 dbms_output.put_line('Employee does not exist ');
1257end;
1258/
1259
1260set serveroutput on size 2000
1261
1262declare
1263 v_full_name varchar2(5);
1264begin
1265 select nume||' '|| prenume into v_full_name from angajati where id_angajat=190;
1266 dbms_output.put_line('Employee: '||v_full_name);
1267 exception
1268 when NO_DATA_FOUND then
1269 dbms_output.put_line('Employee does not exist ');
1270 when VALUE_ERROR then
1271 dbms_output.put_line('Variable dimension error ');
1272end;
1273/
1274
1275
1276
1277set serveroutput on size 2000
1278
1279declare
1280 v_full_name varchar2(5);
1281begin
1282 select nume||' '|| prenume into v_full_name from angajati where id_angajat>=190;
1283 dbms_output.put_line('Employee: '||v_full_name);
1284 exception
1285 when NO_DATA_FOUND then
1286 dbms_output.put_line('Employee does not exist ');
1287 when VALUE_ERROR then
1288 dbms_output.put_line('Variable dimension error '); --am declarat varchar(5), dar numele e mai mare de 5
1289 when others then --ALWAYS GOES LAST
1290 dbms_output.put_line('Another exception was raised');
1291 dbms_output.put_line(SQLERRM);
1292 dbms_output.put_line(SQLCODE);
1293end;
1294/
1295
1296set serveroutput on size 2000
1297
1298declare
1299 v_full_name varchar2(5);
1300begin
1301 select nume||' '|| prenume into v_full_name from angajati where id_angajat>=190;
1302 dbms_output.put_line('Employee: '||v_full_name);
1303 exception
1304 when NO_DATA_FOUND then
1305 dbms_output.put_line('Employee does not exist ');
1306 when VALUE_ERROR then
1307 dbms_output.put_line('Variable dimension error ');
1308 when TOO_MANY_ROWS then
1309 dbms_output.put_line('More than one row was returned by the select');
1310 when others then
1311 dbms_output.put_line('Another exception was raised');
1312 dbms_output.put_line(SQLERRM);
1313 dbms_output.put_line(SQLCODE);
1314end;
1315/
1316
1317 
1318--Add the region 4 - Atlantida. Treat the exception that is raised!
1319set serveroutput on size 2000
1320
1321declare
1322 v_denumire_regiune varchar2(100):='Atlantida';
1323 v_id_regiune number := 4;
1324begin
1325 insert into regiuni values (v_id_regiune, v_denumire_regiune);
1326exception
1327 when DUP_VAL_ON_INDEX then
1328 dbms_output.put_line ('There is already a record with the primary key ' || v_id_regiune);
1329 end;
1330/
1331
1332
1333--create the table then run into
1334
1335create table raised_exceptions(
1336c_user varchar2(50),
1337c_date date,
1338c_message varchar2(500),
1339c_code number);
1340
1341
1342
1343--Add the region 4 - Atlandida, |Treat the exception that is raised; create a table with exceptions
1344-- predefined exceptions 7
1345set serveroutput on size 20000
1346declare
1347 v_full_name varchar2(100):='Atlantida';
1348 id_region number:=4;
1349 v_message varchar2(500);
1350 v_code number;
1351begin
1352 insert into regiuni values(id_region,v_full_name);
1353 exception
1354 when DUP_VAL_ON_INDEX then
1355 v_message:=sqlerrm;
1356 v_code:=sqlcode;
1357 insert into raised_exceptions values(user,sysdate,v_message,v_code);
1358 dbms_output.put_line('There is already a region with id = '||id_region);
1359
1360end;
1361/
1362
1363select * from raised_exceptions;
1364
1365 
1366--raising a user-defined exception
1367set serveroutput on
1368
1369declare
1370no_emp exception;
1371pragma exception_init (no_emp, -20000);
1372begin
1373 update angajati set salariul = salariul*1.1 where id_angajat=2000;
1374 if sql%notfound then
1375 raise no_emp;
1376 end if;
1377 exception when no_emp then
1378 dbms_output.put_line('no salary updated');
1379 dbms_output.put_line(SQLERRM);
1380end;
1381/
1382
1383--Homework: create table X(n number). If the table exists, drop it! 
1384set serveroutput on size 2000
1385
1386declare
1387 v_full_name varchar2(5);
1388 has_employees exception;
1389 pragma exception_init (has_employees, -02292);
1390begin
1391 delete from departamente where id_departament = 50;
1392 select nume||' '|| prenume into v_full_name from angajati where id_angajat>=190;
1393 dbms_output.put_line('Employee: '||v_full_name);
1394 exception
1395 when NO_DATA_FOUND then
1396 dbms_output.put_line('Employee does not exist ');
1397 when VALUE_ERROR then
1398 dbms_output.put_line('Variable dimension error ');
1399 when TOO_MANY_ROWS then
1400 dbms_output.put_line('More than one row was returned by the select');
1401 when has_employees then
1402 dbms_output.put_line('There are employees in this department');
1403 when others then
1404 dbms_output.put_line('Another exception was raised');
1405 dbms_output.put_line(SQLERRM);
1406 dbms_output.put_line(SQLCODE);
1407end;
1408/
1409
1410-----------------------------
1411
1412--create an exception (Homework)
1413set serveroutput on 20000
1414declare
1415table_exists exception;
1416pragma exception_init(table_exists,-00955);
1417begin
1418 execute immediate 'create table X(n number)';
1419 dbms_output.put_line('Table X has been created');
1420 exception
1421 when table_exists then
1422 execute immediate 'drop table X';
1423 dbms_output.put_line('Table X has been dropped');
1424end;
1425/
1426
1427
1428set serveroutput on
1429declare
1430 no_emp exception;
1431 pragma exception_init (no_emp, -20500);
1432begin
1433 update angajati set salariul=salariul*1.1 where id_departament = 500;
1434 if SQL%NOTFOUND then
1435 raise no_emp;
1436 end if;
1437 --update does not raise no_data_found
1438 exception
1439 when no_emp then
1440 dbms_output.put_line('An exception was raised!');
1441 dbms_output.put_line(SQLERRM);
1442 dbms_output.put_line(SQLCODE);
1443end;
1444/
1445
1446set serveroutput on
1447declare
1448 no_emp exception;
1449 pragma exception_init (no_emp, -20500);
1450begin
1451 update angajati set salariul=salariul*1.1 where id_departament = 500;
1452 if SQL%NOTFOUND then
1453 --raise no_emp;
1454 raise_application_error(-20594,'We couldn''t update any salary');
1455 end if;
1456 --update does not raise no_data_found
1457 exception
1458 when no_emp then
1459 dbms_output.put_line('An exception was raised!');
1460 dbms_output.put_line(SQLERRM);
1461 dbms_output.put_line(SQLCODE);
1462end;
1463/
1464set serveroutput on
1465declare
1466 no_emp exception;
1467 pragma exception_init (no_emp, -20500);
1468 v_message varchar2(500);
1469 v_code number;
1470begin
1471 update angajati set salariul=salariul*1.1 where id_departament = 500;
1472 if SQL%NOTFOUND then
1473 --raise no_emp;
1474 raise_application_error(-20500,'We couldn''t update any salary');
1475 end if;
1476 --update does not raise no_data_found
1477 exception
1478 when no_emp then
1479 dbms_output.put_line('An exception was raised!');
1480 --dbms_output.put_line(SQLERRM);
1481 --dbms_output.put_line(SQLCODE);
1482 v_message:=SQLERRM;
1483 v_code:=SQLCODE;
1484 --insert into occured_exceptions values(user, sysdate, SQLERRM, SQLCODE);
1485 --SQLERRM, SQLCODE can't be used in SQL statements; I need 2 variables
1486 insert into occured_exceptions values(user, sysdate, v_message, v_code);
1487end;
1488/
1489/*drop table occured_exceptions; --I drop the table in case I had it;
1490--If I have written the code inside the block I should have used execute immediate
1491create table occured_exceptions (
1492c_user varchar2(50),
1493c_date date,
1494c_message varchar2(500),
1495c_code number);
1496
1497
1498set serveroutput on
1499declare
1500 table_exists exception;
1501 pragma exception_init(table_exists, -00955);
1502 n number;
1503begin
1504 select count(*) into n from user_tables where table_name = 'X';
1505 if n=0 then
1506 execute immediate 'create table X(n number)';
1507 dbms_output.put_line('table X has been created');
1508 else
1509 execute immediate 'drop table X';
1510 dbms_output.put_line('table X has been droped');
1511 end if;
1512end;
1513/
1514
1515set serveroutput on
1516declare
1517
1518 n number;
1519begin
1520 select count(*) into n from user_tables where table_name = 'X';
1521 if n=0 then
1522 execute immediate 'create table X(n number)';
1523 dbms_output.put_line('table X has been created');
1524 else
1525 execute immediate 'drop table X';
1526 dbms_output.put_line('table X has been droped');
1527 end if;
1528end;
1529/
1530
1531
1532set serveroutput on
1533-- mai jos pot pune si IS si AS
1534create or replace procedure construct_table(p_table_name varchar2) is
1535 n number;
1536begin
1537 select count(*) into n from user_tables where table_name = upper(p_table_name);
1538 if n=0 then
1539 execute immediate 'create table '||p_table_name||'(n number)';
1540 dbms_output.put_line('table X has been created');
1541 else
1542 execute immediate 'drop table '||p_table_name;
1543 dbms_output.put_line('table X has been droped');
1544 end if;
1545end;
1546/
1547
1548set serveroutput on
1549-- mai jos pot pune si IS si AS
1550--create procedure create a table receiving the name as parameter
1551create or replace procedure construct_table(p_table_name varchar2)
1552authid current_user --imi trebuie asta ca sa am privilegii pt creare
1553is --declare we dont have declare in procedures..between is and begin we have the declarations
1554 n number;
1555begin
1556 select count(*) into n from user_tables where table_name = upper(p_table_name);
1557 if n=0 then
1558 execute immediate 'create table '||p_table_name||'(n number)';
1559 dbms_output.put_line('table '||p_table_name||' has been created');
1560 else
1561 execute immediate 'drop table '||p_table_name;
1562 dbms_output.put_line('table '||p_table_name||' has been droped');
1563 end if;
1564end;
1565/
1566
1567--this is the call of the procedure
1568set serveroutput on
1569begin
1570 construct_table('ABC');
1571end;
1572/
1573
1574
1575set serveroutput on
1576-- mai jos pot pune si IS si AS
1577create or replace procedure construct_table(p_table_name varchar2, p_message OUT varchar2)
1578authid current_user --imi trebuie asta ca sa am privilegii pt creare
1579is
1580 n number;
1581begin
1582 select count(*) into n from user_tables where table_name = upper(p_table_name);
1583
1584 if n=0 then
1585 execute immediate 'create table '||p_table_name||'(n number)';
1586 p_message:='table '||p_table_name||' has been created';
1587 else
1588 execute immediate 'drop table '||p_table_name;
1589 p_message:='table '||p_table_name||' has been droped';
1590 end if;
1591end;
1592/
1593
1594--this is the call of the procedure
1595set serveroutput on
1596declare
1597v_message varchar2(500);
1598begin
1599 construct_table('ABC', v_message);
1600 dbms_output.put_line(v_message);
1601end;
1602/
1603
1604
1605---------------------
1606--Build a function that takes a product id and returns the number of orders on which it appears
1607
1608create or replace function get_no_orders(p_product_id number)
1609return number is
1610v_n number; -- In functions and procedures I don't have the "declare" keyword, it's implied (between is and begin)
1611begin
1612select count(*) into v_n from RAND_COMENZI t where t.id_produs=p_product_id;
1613return v_n;
1614end;
1615/
1616
1617--call of the function
1618set serveroutput on
1619declare
1620n number;
1621begin
1622 n:=get_no_orders(2311);
1623 dbms_output.put_line('Number of orders: ' || n);
1624end;
1625/
1626
1627--call of the function
1628set serveroutput on
1629declare
1630n number:=get_no_orders(2311); -- i can call it in the declaritive part
1631begin
1632 dbms_output.put_line('Number of orders: ' || n);
1633end;
1634/
1635
1636--sql window
1637
1638select t.*, get_no_orders(t.id_produs) no_of_orders from PRODUSE t
1639where get_no_orders(t.id_produs)>10
1640order by no_of_orders desc;
1641
1642--Display the categories for which there are no orders (for no product in that category)
1643--SQL statement
1644
1645SELECT categorie
1646FROM produse p
1647MINUS
1648SELECT categorie
1649FROM produse
1650WHERE get_no_orders(id_produs) > 0;
1651
1652--version 2, without the function
1653SELECT categorie
1654FROM produse p
1655MINUS
1656SELECT categorie
1657FROM produse p JOIN rand_comenzi r ON
1658p.id_produs=r.id_produs;
1659
1660--version 3
1661SELECT categorie
1662FROM produse
1663GROUP BY categorie
1664HAVING sum(get_no_orders(id_produs))=0;
1665
1666--version 4
1667SELECT categorie
1668FROM produse
1669GROUP BY categorie
1670HAVING max(get_no_orders(id_produs))=0;
1671
1672
1673--Construct a function that takes a client id and returns the total ordered value for that client (pl/sql)
1674create or replace function get_val_orders(c_client_id number)
1675 return number is
1676 c_n number;
1677begin
1678 select sum(rc.pret*rc.cantitate) total_value into c_n
1679 from comenzi co, rand_comenzi rc
1680 where co.nr_comanda=rc.nr_comanda and co.id_client=103;
1681return c_n;
1682end;
1683/
1684
1685--call in sql
1686select t.*, get_val_orders(t.id_client) total_ordered_value from clienti t;
1687
1688--total ordered value by gender (all orders for males and all orderes for females)
1689select sex, sum(get_val_orders(t.id_client)) total_ordered_value
1690from clienti t
1691group by sex;
1692
1693--group by marital status
1694select starea_civila, sum(get_val_orders(t.id_client)) total_ordered_value
1695from clienti t
1696group by starea_civila;
1697
1698--group by marital status and genders
1699select sex, starea_civila, sum(get_val_orders(t.id_client)) total_ordered_value
1700from clienti t
1701group by starea_civila, sex
1702order by 1;
1703
1704--group by marital status and genders
1705select starea_civila, sex, sum(get_val_orders(t.id_client)) total_ordered_value
1706from clienti t
1707group by rollup(starea_civila, sex) --also subtotals
1708order by 1;
1709
1710select starea_civila, sex, sum(get_val_orders(t.id_client)) total_ordered_value
1711from clienti t
1712group by cube(starea_civila, sex)
1713order by 1;
1714
1715--I want to transpose the matrix, so I have Males, Females on the first row
1716select sum(decode(sex,'M',get_val_orders(id_client))) Males,
1717sum(decode(sex,'F',get_val_orders(id_client))) Females
1718from clienti;
1719
1720
1721----------------------------
1722Command window:
1723
1724create or replace function get_value_client_year
1725(p_client_id number, p_year number)
1726return number is
1727p_val number;
1728begin
1729 select sum(pret*cantitate) into p_val
1730 from comenzi t join rand_comenzi r on
1731 t.nr_comanda=r.nr_comanda where id_client = p_client_id
1732 and extract (year from t.data) = p_year;
1733 return nvl(p_val,0);
1734 end;
1735/
1736
1737
1738SQL window:
1739select t.*, get_value_client_year (t.id_client,1998) val_1998 ,
1740get_value_client_year (t.id_client,1999) val_1999
1741from clienti t
1742order by val_1998 desc
1743
1744
1745create or replace function get_value_client_year
1746(p_client_id number, p_year number default null) -- pun valoare default ca sa imi mearga urmatorul select
1747return number is
1748p_val number;
1749begin
1750 select sum(pret*cantitate) into p_val
1751 from comenzi t join rand_comenzi r on
1752 t.nr_comanda=r.nr_comanda where id_client = p_client_id
1753 and extract (year from t.data) = p_year;
1754 return nvl(p_val,0);
1755 end;
1756/
1757SQL window:
1758select t.*, get_value_client_year (t.id_client,1998) val_1998 ,
1759get_value_client_year (t.id_client,1999) val_1999,
1760get_value_client_year (t.id_client) val_total
1761from clienti t
1762order by val_1998 desc
1763
1764
1765
1766
1767
1768
1769
1770
1771--if you put null, the entire select is null => we need the if!
1772
1773create or replace function get_value_client_year
1774(p_client_id number, p_year number default null) -- pun valoare default ca sa imi mearga urmatorul select
1775return number is
1776p_val number;
1777begin
1778if p_year is not null then
1779 select sum(pret*cantitate) into p_val
1780 from comenzi t join rand_comenzi r on
1781 t.nr_comanda=r.nr_comanda where id_client = p_client_id
1782 and extract (year from t.data) = p_year;
1783else
1784 select sum(pret*cantitate) into p_val
1785 from comenzi t join rand_comenzi r on
1786 t.nr_comanda=r.nr_comanda where id_client = p_client_id;
1787end if;
1788return nvl(p_val,0);
1789end;
1790/
1791
1792
1793
1794--package -> specification : public part
1795 -- -> body: private part
1796--package specification
1797create or replace package group_1052 is
1798 vat_rate constant number := 19;
1799 function get_value_with_vat(p_val number) return number;
1800end group_1052;
1801/
1802
1803
1804
1805--package specification
1806create or replace package group_1052 is
1807 vat_rate constant number := 19;
1808 function get_value_with_vat(p_val number) return number;
1809end group_1052;
1810/
1811set serveroutput on
1812begin
1813 dbms_output.put_line('Standart VAT rate is: '||group_1052.vat_rate);
1814end;
1815/
1816
1817 --we can acess it directly because it is delared in the public part, not in the body
1818set serveroutput on
1819--package specification
1820create or replace package group_1052 is
1821 vat_rate constant number := 19;
1822 function get_val_with_vat(p_val number) return number;
1823end group_1052;
1824/
1825create or replace package body group_1052 is
1826
1827 function get_val_with_vat(p_val number) return number is
1828 begin
1829 return p_val*(1+vat_rate/100);
1830 end get_val_with_vat;
1831end group_1052;
1832/
1833
1834begin
1835 dbms_output.put_line('Standart VAT rate is: '||group_1052.vat_rate);
1836 --we can acess it directly because it is delared in the public part, not in the body
1837 dbms_output.put_line('Value with standart VAT rate is: '||group_1052.get_val_with_vat(200));
1838end;
1839/
1840
1841
1842Command window:
1843CREATE OR REPLACE PACKAGE group_1052 is
1844vat_rate constant number:=19;
1845FUNCTION get_val_with_vat(p_val number) return number;
1846FUNCTION get_val_with_vat(p_val number,p_rate number) return number;
1847Function get_value_client_year(p_client_id number, p_year number default null) return number;
1848end group_1052;
1849/
1850Apoi:
1851CREATE OR REPLACE PACKAGE BODY group_1052 is
1852
1853FUNCTION get_val_with_vat(p_val number) return number is
1854 begin
1855 return p_val*(1+vat_rate)/100;
1856 end get_val_with_vat;
1857
1858
1859FUNCTION get_val_with_vat(p_val number,p_rate number) return number is
1860 begin
1861 return p_val*(1+vat_rate)/100;
1862 end get_val_with_vat;
1863
1864
1865Function get_value_client_year
1866(p_client_id number, p_year number default null)
1867return number is
1868p_val number;
1869begin
1870if p_year is not null then
1871 select sum(pret*cantitate) into p_val
1872 from comenzi t join rand_comenzi r on
1873 t.nr_comanda=r.nr_comanda where id_client = p_client_id
1874 and extract (year from t.data) = p_year;
1875else
1876 select sum(pret*cantitate) into p_val
1877 from comenzi t join rand_comenzi r on
1878 t.nr_comanda=r.nr_comanda where id_client = p_client_id;
1879end if;
1880return nvl(p_val,0);
1881end get_value_client_year;
1882--if you put null, the entire select is null => we need the if!
1883
1884
1885end group_1052;
1886/
1887
1888
1889--construct a package that has one fct and one procedure (1 in and 1 out paramater)
1890--call the fct and the procedure
1891--start with the specification and then build the body
1892CREATE OR REPLACE PACKAGE my_package is
1893FUNCTION get_sum(n1 number, n2 number) return number;
1894procedure happy_easter(p_name varchar2, p_message OUT varchar2);
1895end my_package;
1896/
1897
1898CREATE OR REPLACE PACKAGE BODY my_package is
1899
1900FUNCTION get_sum(n1 number, n2 number) return number is
1901 begin
1902 return n1+n2;
1903 end;
1904procedure happy_easter(p_name varchar2, p_message OUT varchar2) is
1905 begin
1906 p_message := 'Happy easter '||p_name||'!';
1907 end;
1908end my_package;
1909/
1910--calling block
1911set serveroutput on
1912-- i need a variable for the OUT param of the procedure
1913declare
1914 m varchar2(50);
1915begin
1916 dbms_output.put_line('The sum is: '||my_package.get_sum(2,3));
1917 my_package.happy_easter('John', m);
1918 dbms_output.put_line(m);
1919end;
1920/
1921---------------------------
1922
1923--Construct a trigger that doesn't allow inserting new orders between 20:00 and 23:00
1924--and doesn't allow updating od order status outside de 9:00-20:00 interval
1925
1926--tables: only before and after (no instead of)
1927--INSERTING, UPDATING, DELETING -> PREDICATES
1928
1929--hh24:mi (ora si minutul)
1930--22:00->22:59
1931--the column name as parameter has to be in caps (THE COLUMN NAME IS ONLY FOR UPDATE) ; more columns – separate by comma
1932
1933
1934--version 1 – the condition is in the header
1935
1936create or replace trigger check_order_hour
1937before insert or update on comenzi
1938begin
1939 if inserting and to_char(sysdate, 'hh24') between '20' and '22' then
1940 raise_application_error(-20000, 'We dont''t allow new orders at this hour');
1941 elsif UPDATING('STARE_COMANDA') and to_char(sysdate, 'hh24') not between '08' and '19' then
1942 raise_application_error(-20001, 'We don''t allow updated at this hour');
1943 end if;
1944end;
1945/
1946
1947--version 2 – the condition is in the predicate
1948
1949create or replace trigger check_order_hour
1950before insert or update of stare_comanda on comenzi
1951begin
1952 if inserting and to_char(sysdate, 'hh24') between '20' and '22' then
1953 raise_application_error(-20000, 'We dont''t allow new orders at this hour');
1954 elsif UPDATING and to_char(sysdate, 'hh24') not between '08' and '19' then
1955 raise_application_error(-20001, 'We don''t allow updated at this hour');
1956 end if;
1957end;
1958/
1959
1960create or replace trigger check_order_hour
1961before insert or delete or update of stare_comanda on comenzi
1962begin
1963 if inserting and to_char(sysdate, 'hh24') between '20' and '22' then
1964 raise_application_error(-20000, 'We dont''t allow new orders at this hour');
1965 elsif UPDATING and to_char(sysdate, 'hh24') not between '08' and '19' then
1966 raise_application_error(-20001, 'We don''t allow updated at this hour');
1967 --don't allow deletes between 20:00 and 20:30
1968 elsif DELETING and to_char(sysdate, 'hh24:mi') between '20:00' and '20:29' then
1969 raise_application_error(-20002, 'We don''t allow deleting at this hour');
1970 end if;
1971end;
1972/
1973
1974--the difference between before and after: after->the constraints fire after the trigger
1975--before -> the constraints don't get checked out before the trigger
1976--check:
1977insert into comenzi values(2441,sysdate,'direct',150,0,180);
1978update comenzi c set c.modalitate='online' where c.nr_comanda=2441;
1979delete from comenzi where nr_comanda>2440;
1980
1981--Construct a trigger that doesn't allow order with status>5 to be deleted
1982
1983create or replace trigger check_order_status
1984before delete on comenzi for each row
1985begin
1986 if :OLD.stare_comanda > 5 then
1987 raise_application_error(-20005, 'We don''t allow deleting the order'||:OLD.nr_comanda||' because it has the status '||:OLD.stare_comanda);
1988 end if;
1989end;
1990/
1991
1992delete from comenzi where nr_comanda>2440;
1993
1994--version 2
1995create or replace trigger check_order_status
1996before delete on comenzi for each row
1997begin
1998 if :OLD.stare_comanda > 5 then
1999 raise_application_error(-20005, 'We don''t allow deleting the order'||:OLD.nr_comanda||' because it has the status '||:OLD.stare_comanda);
2000 end if;
2001end;
2002/
2003
2004delete from comenzi where nr_comanda>2440;
2005----------------------------------