ZvcPewQA

1zadania z baz 20-11-2018
25-1: 1
35-2: 2,3
4
55.1: 1
6
7DECLARE
8v_dept_rec departments%ROWTYPE;
9BEGIN
10SELECT * INTO v_dept_rec
11FROM departments
12WHERE department_id = 80;
13DBMS_OUTPUT.PUT_LINE(v_dept_rec.department_id || ' ' || v_dept_rec.department_name || ' ' || v_dept_rec.manager_id || ' ' || v_dept_rec.location_id);
14EXCEPTION
15WHEN NO_DATA_FOUND THEN
16DBMS_OUTPUT.PUT_LINE('This department does not exists');
17END;
18
195-2: 2a
20
21DECLARE
22
23TYPE t_country_names IS TABLE OF countries.country_name%TYPE
24INDEX BY BINARY_INTEGER
25v_country_names t_country_names;
26CURSOR country_curs IS
27SELECT country_id, country_name
28FROM countries
29WHERE region_id = 5
30ORDER BY country_id;
31v_country_rec country_curs%ROWTYPE;
32BEGIN
33OPEN country_curs;
34LOOP
35FETCH country_curs INTO v_country_rec;
36EXIT WHEN country_curs%NOTFOUND;
37v_country_names(v_country_rec.country_id) := v_country_rec.country_name;
38END LOOP;
39CLOSE country_curs;
40END;
41
42
435-2 2b
44
45TYPE t_country_names IS TABLE OF countries.country_name%TYPE
46INDEX BY BINARY_INTEGER
47v_country_names t_country_names;
48CURSOR country_curs IS
49SELECT country_id, country_name
50FROM countries
51WHERE region_id = 5
52ORDER BY country_id;
53v_country_rec country_curs%ROWTYPE;
54BEGIN
55OPEN country_curs;
56LOOP
57FETCH country_curs INTO v_country_rec;
58EXIT WHEN country_curs%NOTFOUND;
59v_country_names(v_country_rec.country_id) := v_country_rec.country_name;
60END LOOP;
61CLOSE country_curs;
62FOR i IN v_country_names.FIRST .. v_country_names.LAST
63LOOP
64IF v_country_names.EXISTS(i) THEN
65DBMS_OUTPUT.PUT_LINE(i || ' ' || v_country_names(i));
66END IF;
67END LOOP;
68END;
69
705-2 2c
71
72TYPE t_country_names IS TABLE OF countries.country_name%TYPE
73INDEX BY BINARY_INTEGER
74v_country_names t_country_names;
75CURSOR country_curs IS
76SELECT country_id, country_name
77FROM countries
78WHERE region_id = 5
79ORDER BY country_id;
80v_country_rec country_curs%ROWTYPE;
81BEGIN
82OPEN country_curs;
83LOOP
84FETCH country_curs INTO v_country_rec;
85EXIT WHEN country_curs%NOTFOUND;
86v_country_names(v_country_rec.country_id) := v_country_rec.country_name;
87END LOOP;
88CLOSE country_curs;
89DBMS_OUTPUT.PUT_LINE(v_country_names.FIRST) || ' ' || v_country_names(v_country_names.FIRST));
90DBMS_OUTPUT.PUT_LINE(v_country_names.LAST) || ' ' || v_country_names(v_country_names.LAST));
91DBMS_OUTPUT.PUT_LINE(' ');
92DBMS_OUTPUT.PUT_LINE('Number of countries is: ' || v_country_names.COUNT);
93END;
94
955-2 3 A
96
97DECLARE
98CURSOR emp_curs IS
99SELECT employee_id, last_name, job_id , salary
100FROM employees
101ORDER BY employee_id;
102v_emp_rec emp_curs%ROWTYPE;
103TYPE t_emp_data IS TABLE OF emp_curs%ROWTYPE
104INDEX BY BINARY_INTEGER;
105v_emp_data t_emp_data;
106BEGIN
107OPEN emp_curs;
108LOOP
109FETCH emp_curs INTO v_emp_rec;
110EXIT WHEN emp_curs%NOTFOUND;
111v_emp_data(v_emp_rec.employee_id) := v_emp_rec;
112END LOOP;
113CLOSE emp_curs;
114END;
115
116
1175-2 3 B
118
119DECLARE
120CURSOR emp_curs IS
121SELECT employee_id, last_name, job_id , salary
122FROM employees
123ORDER BY employee_id;
124v_emp_rec emp_curs%ROWTYPE;
125TYPE t_emp_data IS TABLE OF emp_curs%ROWTYPE
126INDEX BY BINARY_INTEGER;
127v_emp_data t_emp_data;
128BEGIN
129OPEN emp_curs;
130LOOP
131FETCH emp_curs INTO v_emp_rec;
132EXIT WHEN emp_curs%NOTFOUND;
133v_emp_data(v_emp_rec.employee_id) := v_emp_rec;
134END LOOP;
135CLOSE emp_curs;
136FOR i IN v_emp_data.FIRST .. v_emp_data.LAST
137LOOP
138IF v_emp_data.EXISTS(i) THEN
139DBMS_OUTPUT.PUT_LINE(v_emp_data(i).employee_id || ' ' || v_emp_data(i).last_name || ' ' || v_emp_data(i).job_id || ' ' || v_emp_data(i).salary);
140END IF
141END LOOP;
142END;
143
1447-1: 4,6,7,9
1457-2: 3,4
1467-3: 1,2,3,4
147
148
149
150
151
152section 6-1: 4E
153
154DECLARE CURSOR
155currencies_cur IS
156SELECT currency_code, currency_name
157FROM currencies;
158v_code currencies.currency_code%TYPE;
159v_name currencies.currency_name%TYPE;
160BEGIN OPEN currencies_cur;
161FETCH currencies_cur INTO v_code, v_name;
162DBMS_OUTPUT.PUT_LINE(v_code || v_name);
163CLOSE currencies_cur;
164END;
165
166section 6-1: 4F
167
168DECLARE
169CURSOR currencies_cur IS
170SELECT currency_code, currency_name
171FROM currencies;
172v_code currencies.currency_code%TYPE;
173v_name currencies.currency_name%TYPE;
174BEGIN OPEN currencies_cur;
175LOOP FETCH currencies_cur INTO v_code, v_name;
176DBMS_OUTPUT.PUT_LINE(v_code || v_name);
177EXIT WHEN currencies_cur%NOTFOUND;
178END LOOP;
179CLOSE currencies_cur;
180END;
181
182
1834G
184
185DECLARE
186v_country_name countries.country_name%TYPE;
187v_national_holiday_name countries.national_holiday_name%TYPE;
188v_national_holiday_date countries.national_holiday_date%TYPE;
189CURSOR countries_cur IS
190SELECT country_name, national_holiday_name, national_holiday_date
191FROM countries
192WHERE region_id =5 AND national_holiday_date IS NOT NULL;
193BEGIN
194OPEN countries_cur;
195LOOP
196FETCH countries_cur INTO v_country_name,
197v_national_holiday_name, v_national_holiday_date;
198EXIT WHEN countries_cur%NOTFOUND;
199DBMS_OUTPUT.PUT_LINE('Country: ' || v_country_name || ' National holiday: ' || v_national_holiday_name || 'held on:' || v_national_holiday_date);
200END LOOP;
201CLOSE countries_cur;
202END
203
204
2056-1: 6
206DECLARE
207v_region_name regions.region_name%TYPE;
208v_no_of_countries NUMBER(6);
209CURSOR regions_cur IS
210SELECT region_name, COUNT(*)
211FROM regions r, countries c
212WHERE r.region_id = c.region_id
213GROUP BY region_name
214HAVING COUNT(*) >= 10
215ORDER BY region_name;
216BEGIN
217OPEN regions_cur;
218
219LOOP
220FETCH regions_cur INTO v_region_name, v_no_of_countries;
221EXIT WHEN regions_cur%NOTFOUND;
222DBMS_OUTPUT.PUT_LINE (v_region_name || ' contains ' || v_no_of_countries || ' countries.');
223END LOOP;
224CLOSE regions_cur;
225END;
226
227
2286-2: 3
229
230
231DECLARE
232CURSOR emp_cursor IS
233SELECT first_name, last_name, job_id, salary
234FROM employees
235ORDER BY salary DESC;
236emp_record emp_cursor%ROWTYPE;
237BEGIN
238OPEN emp_cursor;
239LOOP
240FETCH emp_cursor INTO emp_record;
241DBMS_OUTPUT.PUT_LINE(emp_record.first_name || ' ' || emp_record.last_name || ' ' || emp_record.job_id || ' ' || emp_record.salary);
242EXIT WHEN emp_cursor%ROWCOUNT >= 6;
243END LOOP;
244CLOSE emp_cursor;
245END;
246
247
2486-3: 2
249
250DECLARE
251CURSOR countries_cur IS
252SELECT country_name, national_holiday_name, national_holiday_date
253FROM countries
254WHERE region_id = 5;
255BEGIN
256
257FOR countries_rec IN countries_cur
258LOOP
259DBMS_OUTPUT.PUT_LINE ('Country: ' || countries_rec.country_name || ' National holiday: '|| countries_rec.national_holiday_name
260|| ', held on: '|| countries_rec.national_holiday_date);
261END LOOP;
262END;
263
2646-3: 3 
265
266BEGIN
267FOR countries_rec IN (SELECT country_name, national_holiday_name, national_holiday_date
268FROM countries
269WHERE region_id = 5)
270LOOP
271DBMS_OUTPUT.PUT_LINE ('Country: ' || countries_rec.country_name || ' National holiday: '|| countries_rec.national_holiday_name
272|| ', held on: '|| countries_rec.national_holiday_date);
273END LOOP;
274END;
275
2766-4: 2
277
278DECLARE
279CURSOR country_curs
280(p_region_id countries.region_id%TYPE) IS
281SELECT country_name, area FROM countries
282WHERE region_id = p_region_id;
283country_rec country_curs%ROWTYPE;
284BEGIN
285OPEN country_curs(5);
286LOOP
287FETCH country_curs INTO country_rec;
288EXIT WHEN country_curs%NOTFOUND;
289DBMS_OUTPUT.PUT_LINE('Name: ' || country_rec.country_name || 'Area: ' || country_rec.area);
290END LOOP;
291CLOSE country_curs;
292END;
293
2946-4: 3
295
296DECLARE
297CURSOR country_curs
298(p_region_id countries.region_id%TYPE) IS
299SELECT country_name, area FROM countries
300WHERE region_id = p_region_id;
301BEGIN
302FOR country_rec in country_curs(5)
303LOOP
304DBMS_OUTPUT.PUT_LINE('Name: ' || country_rec.country_name || 'Area: ' || country_rec.area);
305END LOOP;
306END;
307
308
3096-4: 4
310
311
312DECLARE
313CURSOR country_curs
314(p_region_id countries.region_id%TYPE, p_area countries.area%TYPE) IS
315SELECT country_name, area FROM countries
316WHERE region_id = p_region_id AND area>p_area;
317BEGIN
318FOR country_rec in country_curs(5,200000)
319LOOP
320DBMS_OUTPUT.PUT_LINE('Name: ' || country_rec.country_name || 'Area:' || country_rec.area);
321END LOOP;
322END;
323
324
325
326PD
3274-1: 5,6,9
3284-2: 2
3294-3: 4,5,6
3304-4: 1,2
331
332
333
3347-1: 4,6,9,7
3357-2: 3,4
3367,3: 1,2,3,4
337
3387-1: 4
339DECLARE
340v_job_id employees.job_id%TYPE;
341BEGIN
342SELECT job_ID into V_JOBID
343FROM employees
344WHERE department_id = 80;
345EXCEPTION
346WHEN TOO_MANY_ROWS THEN
347DBMS_output.PUT_LINE('Too many rows returned from the query');
348END:
349
3507-1: 7
351
352DECLARE
353v_employee_id emp_temp:employee_id%TYPE;
354v_last_name emp_temp.last_name%TYPE;
355BEGIN
356SELECT employee_id, last_name INTO v_employee_id, v_last_name
357FROM emp_temp
358WHERE department_id = 10; --run with values 10, 30
359DBMS_OUTPUT.PUT_LINE('The SELECT was successful')
360EXCEPTION
361WHEN NO_DATA_FOUND THEN
362DBMS_OUTPUT.PUT_LINE('No rows were selected');
363WHEN TOO_MANY_ROWS THEN
364DBMS_OUTPUT.PUT_LINE('More than one row as selected');
365WHEN OTHERS THEN
366DBMS_OUTPUT.PUT_LINE('An exception has occured');
367END;
368
369DBMS_OUTPUT.PUT_LINE('The SELECT was successful');
370EXCEPTION
371WHEN NO_DATA_FOUND THEN
372DBMS_OUTPUT.PUT_LINE('No rows were selected');
373WHEN TOO_MANY_ROWS THEN
374DBMS_OUTPUT.PUT_LINE('More than one row was selected');
375WHEN OTHERS THEN
376DBMS_OUTPUT.PUT_LINE('An exception has occured');
377END;
378
3797-2: 3A
380DECLARE
381v_number NUMBER(6,2) :=100;
382v_region_id regions.region_id%TYPE;
383v_region_name regions.region_name%TYPE;
384BEGIN
385SELECT region_id, region_name INTO v_region_id,
386v_region_name
387FROM regions
388WHERE region_id = 1;
389DBMS_OUTPUT.PUT_LINE('Region: ' || v_region_id || v_region_name);
390v_number := v_number / 0;
391END;
392
3937-1:9
394DECLARE
395v_number NUMBER(2);
396BEGIN
397v_number := 9999;
398EXCEPTION
399WHEN OTHERS THEN
400DBMS_OUTPUT.PUT_LINE('An exception has occurred');
401END;
402
4037-2:3
404A
405DECLARE
406v_number NUMBER(6, 2) := 100;
407v_region_id regions.region_id%TYPE;
408v_region_name regions.region_name%TYPE;
409BEGIN
410SELECT region_id, region_name INTO v_region_id, v_region_name
411FROM regions
412WHERE region_id = 1;
413DBMS_OUTPUT.PUT_LINE('Region: ' || v_region_id || ' is: ' || v_region_name);
414v_number := v_number / 0;
415END;
416
417B
418DECLARE
419v_number NUMBER(6, 2) := 100;
420v_region_id regions.region_id%TYPE;
421v_region_name regions.region_name%TYPE;
422BEGIN
423SELECT region_id, region_name INTO v_region_id, v_region_name
424FROM regions
425WHERE region_id = 1;
426DBMS_OUTPUT.PUT_LINE('Region: ' || v_region_id || ' is: ' || v_region_name);
427v_number := v_number / 0;
428EXCEPTION
429WHEN NO_DATA_FOUND THEN
430DBMS_OUTPUT.PUT_LINE('Select returned no rows');
431END;
432
433C
434DECLARE
435v_number NUMBER(6, 2) := 100;
436v_region_id regions.region_id%TYPE;
437v_region_name regions.region_name%TYPE;
438BEGIN
439SELECT region_id, region_name INTO v_region_id, v_region_name
440FROM regions
441WHERE region_id = 1;
442DBMS_OUTPUT.PUT_LINE('Region: ' || v_region_id || ' is: ' || v_region_name);
443v_number := v_number / 0;
444EXCEPTION
445WHEN NO_DATA_FOUND THEN
446DBMS_OUTPUT.PUT_LINE('Select returned no rows');
447WHEN ZERO_DIVIDE THEN
448DBMS_OUTPUT.PUT_LINE('Attempt to divide by zero');
449END;
450
451
4527-2:4
453A
454DECLARE
455CURSOR regions_curs IS
456SELECT * FROM regions
457WHERE region_id < 20
458ORDER BY region_id;
459regions_rec regions_curs%ROWTYPE;
460v_count NUMBER(6);
461BEGIN
462LOOP
463FETCH regions_curs INTO regions_rec;
464EXIT WHEN regions_curs%NOTFOUND;
465DBMS_OUTPUT.PUT_LINE('Region: ' || regions_rec.region_id
466|| ' Name: ' || regions_rec.region_name);
467END LOOP;
468CLOSE regions_curs;
469SELECT COUNT(*) INTO v_count
470FROM regions
471WHERE region_id = 1;
472DBMS_OUTPUT.PUT_LINE('The number of regions is: ' || v_count);
473END;
474
475pierwszy fetch niepowiedzie sie bo nie jest otwarty kursor, predefidniowana wyjatek is raised but is not trapped within the block
476
477
4787-2:4
479B
480DECLARE
481CURSOR regions_curs IS
482SELECT * FROM regions
483WHERE region_id < 20
484ORDER BY region_id;
485regions_rec regions_curs%ROWTYPE;
486v_count NUMBER(6);
487BEGIN
488LOOP
489FETCH regions_curs INTO regions_rec;
490EXIT WHEN regions_curs%NOTFOUND;
491DBMS_OUTPUT.PUT_LINE('Region: ' || regions_rec.region_id
492|| ' Name: ' || regions_rec.region_name);
493END LOOP;
494CLOSE regions_curs;
495SELECT COUNT(*) INTO v_count
496FROM regions
497WHERE region_id = 1;
498DBMS_OUTPUT.PUT_LINE('The number of regions is: ' || v_count);
499EXCEPTION
500WHEN INVALID_CURSOR THEN
501DBMS_OUTPUT.PUT_line('Attempt to fetch from an unopened cursor');
502END;
503
504C
505DECLARE
506CURSOR regions_curs IS
507SELECT * FROM regions
508WHERE region_id < 20
509ORDER BY region_id;
510regions_rec regions_curs%ROWTYPE;
511v_count NUMBER(6);
512BEGIN
513OPEN regions_curs;
514LOOP
515FETCH regions_curs INTO regions_rec;
516EXIT WHEN regions_curs%NOTFOUND;
517DBMS_OUTPUT.PUT_LINE('Region: ' || regions_rec.region_id
518|| ' Name: ' || regions_rec.region_name);
519END LOOP;
520CLOSE regions_curs;
521SELECT COUNT(*) INTO v_count
522FROM regions
523WHERE region_id = 1;
524DBMS_OUTPUT.PUT_LINE('');
525DBMS_OUTPUT.PUT_LINE('The number of regions is: ' || v_count);
526EXCEPTION
527WHEN INVALID_CURSOR THEN
528DBMS_OUTPUT.PUT_line('Attempt to fetch from an unopened cursor');
529END;
530
5317-3; 1
532
533DECLARE
534e_no_rows_updated EXCEPTION;
535BEGIN
536UPDATE excep_emps
537SET salary = 10000
538WHERE department_id=20; --also try 30 and 40
539IF SQL%NOTFOUND THEN --or we could say: IF SQL%ROWCOUNT=0
540RAISE e_no_rows_updated;
541END IF;
542EXCEPTION
543WHEN e_no_rows_updated THEN
544DBMS_OUTPUT.PUT_LINE('There are no employees in that department.');
545WHEN OTHERS THEN
546DBMS_OUTPUT.PUT_LINE('An error has occured:' || SQL_CODE || '-' || SQLERRM);
547END;
548
5497-3; 2
550
551DECLARE
552e_no_rows_updated EXCEPTION;
553BEGIN
554UPDATE excep_emps
555SET salary = 10000
556WHERE department_id=20; --also try 30 and 40
557IF SQL%NOTFOUND THEN --or we could say: IF SQL%ROWCOUNT=0
558RAISE e_no_rows_updated;
559END IF;
560EXCEPTION
561WHEN e_no_rows_updated THEN
562RAISE_APPLICATION_ERROR(-20202, 'There are no employees in that department');
563WHEN OTHERS THEN
564DBMS_OUTPUT.PUT_LINE('An error has occured:' || SQL_CODE || '-' || SQLERRM);
565END;
566
5677-3; 3
568DECLARE
569e_no_rows_updated EXCEPTION;
570BEGIN
571UPDATE excep_emps
572SET salary = 10000
573WHERE department_id=20; --also try 30 and 40
574IF SQL%NOTFOUND THEN --or we could say: IF SQL%ROWCOUNT=0
575RAISE e_no_rows_updated;
576END IF;
577BEGIN
578UPDATE except_emps
579SET salary = 10000
580WHERE department_id = 40;
581IF SQL%NOTFOUND THEN
582RAISE_APPLICATION_ERROR(-20202, 'There are no employees in that department');
583END IF;
584END;
585END;
586
5877-3; 4
588A
589DECLARE
590v_dept_id excep_emps.department_id%TYPE;
591v_count NUMBER;
592BEGIN
593v_dept_id := 40;
594SELECT COUNT(*) INTO v_count
595FROM excep_emps
596WHERE department_id = v_dept_id;
597DBMS_OUTPUT.PUT_LINE('There are ' || v_count || ' employees');
598DELETE FROM excep_emps
599WHERE department_id = v_dept_id;
600DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees were deleted');
601ROLLBACK;
602END;
603
604B
605
606DECLARE
607v_dept_id excep_emps.department_id%TYPE;
608v_count NUMBER;
609e_no_emps_in_dept EXCEPTION;
610e_no_rows_deleted EXCEPTION;
611BEGIN
612v_dept_id := 40;
613SELECT COUNT(*) INTO v_count
614FROM excep_emps
615WHERE department_id = v_dept_id;
616IF v_count = 0 THEN
617RAISE e_no_emps_in_dept
618END IF;
619
620DBMS_OUTPUT.PUT_LINE('There are ' || v_count || ' employees');
621DELETE FROM excep_emps
622WHERE department_id = v_dept_id;
623
624IF SQL%NOTFOUND THEN --or IF SQL%ROWCOUNT=0 THEN
625RAISE e_no_rows_deleted;
626END IF
627DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees were deleted');
628ROLLBACK;
629EXCEPTION
630WHEN e_no_emps_in_dept THEN
631DBMS_OUTPUT.PUT_LINE('This department has no employees');
632WHEN e_no_rows_deleted THEN
633DBMS_OUTPUT.PUT_LINE('No employees were deleted');
634END;
635
636C
637DECLARE
638v_dept_id excep_emps.department_id%TYPE;
639v_count NUMBER;
640e_no_emps_in_dept EXCEPTION;
641e_no_rows_deleted EXCEPTION;
642BEGIN
643v_dept_id := 40;
644SELECT COUNT(*) INTO v_count
645FROM excep_emps
646WHERE department_id = v_dept_id;
647IF v_count = 0 THEN
648RAISE_APPLICATION_ERROR(-20203, 'This department has no employees');
649END IF;
650
651DBMS_OUTPUT.PUT_LINE('There are ' || v_count || ' employees');
652DELETE FROM excep_emps
653WHERE department_id = v_dept_id;
654
655IF SQL%NOTFOUND THEN --or IF SQL%ROWCOUNT=0 THEN
656RAISE_APPLICATION_ERROR(-20204, 'No employees were deleted');
657END IF
658DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees were deleted');
659ROLLBACK;
660//nie wiem czy to potrzebne
661EXCEPTION
662WHEN e_no_emps_in_dept THEN
663DBMS_OUTPUT.PUT_LINE('This department has no employees');
664WHEN e_no_rows_deleted THEN
665DBMS_OUTPUT.PUT_LINE('No employees were deleted');
666//do tad
667END;
668
669
670CREATE OR REPLACE TRIGGER EMP_AUDIT_TRIGG 
671AFTER INSERT OR DELETE ON EMPLOYEES 
672BEGIN
673
674  if inserting then
675INSERT INTO audit_table (action) VALUES ('Inserting');
676  end if;
677  if deleting then
678INSERT INTO audit_table (action) VALUES ('Deleting');
679  end if;
680END;
681
682
683
684CREATE OR REPLACE TRIGGER EMP_AUDIT_TRIGG 
685AFTER INSERT OR DELETE ON EMPLOYEES 
686FOR EACH ROW
687BEGIN
688
689  if inserting then
690INSERT INTO audit_table (action) VALUES ('Inserting');
691  end if;
692  if deleting then
693INSERT INTO audit_table (action) VALUES ('Deleting');
694  end if;
695END;
696
697
698/\ jedno z poziomu wierszy a drugie z instrukcji
699
700
701
702CREATE OR REPLACE TRIGGER EMP_AUDIT_TRIGG 
703AFTER INSERT OR DELETE ON EMPLOYEES 
704FOR EACH ROW
705BEGIN
706
707  if inserting then
708INSERT INTO audit_table (action,emp_id) VALUES ('Inserting',:new.employee_id);
709  end if;
710  if deleting then
711INSERT INTO audit_table (action,emp_id) VALUES ('Deleting',:old.employee_id);
712  end if;
713END;
714
715
716pamietac o NEW i o OLD
717i o odswiezaniu
718nie ma szkieletu compound
719
720
721
72213-3
7235A
724
725CREATE OR REPLACE TRIGGER TRIGGER1
726FOR UPDATE OF SALARY ON EMPLOYEES 
727compound trigger
728
729before statement is
730begin
731insert into audit_table (action) values('Updating');
732end before statement;
733
734after each row is
735begin
736insert into audit_table (action) values('Update complete; old salary was ' || :old.salary || 'new salary is' || :new.salary);
737end after each row;
738
739END;
740
74113-3
742??
743
744CREATE OR REPLACE TRIGGER TRIGGER1
745BEFORE UPDATE OF SALARY ON EMPLOYEES
746FOR EACH ROW
747DECLARE
748v_max NUMBER;
749BEGIN
750	select max(salary) into v_max from EMPLOYEES;
751	DBMS_OUTPUT.put_line(v_max);
752END;
753
754
755
756dynamicznie
757
758
759CREATE OR REPLACE PROCEDURE HOW_MANY_ROWS 
760(
761  P_TABLE IN VARCHAR2 
762) AS 
763v_number NUMBER;
764BEGIN
765execute immediate 'SELECT count(*) from ' || P_TABLE  into v_number;
766dbms_output.put_line(P_TABLE || ' ma ' || v_number || 'wierszy');
767
768EXCEPTION
769when NO_DATA_FOUND then dbms_output.put_line('Brak danych');
770when TOO_MANY_ROWS then dbms_output.put_line('Za duzo');
771
772END HOW_MANY_ROWS;
773
774
775
776usuwanie
777
778CREATE OR REPLACE PROCEDURE HOW_MANY_ROWS 
779(
780  P_TABLE IN VARCHAR2 
781) AS 
782v_number NUMBER;
783BEGIN
784execute immediate 'delete from ' || P_TABLE;
785dbms_output.put_line(P_TABLE || ' ma ' || SQL%ROWCOUNT || 'wierszy');
786
787EXCEPTION
788when NO_DATA_FOUND then dbms_output.put_line('Brak danych');
789when TOO_MANY_ROWS then dbms_output.put_line('Za duzo');
790
791END HOW_MANY_ROWS;
792
793
794kolos
795
796
797
798
799To mamy rows
800No data found
801Zakazać to before
802
803do innej tablicy z trigerami mozna
804a tak to old i new bo bedzie mutacja jak nie bedzie new
805
8061.
807
808set serveroutput on;
809DECLARE
810CURSOR c_prac is select salary, department_id from employees;
811r_prac c_prac%rowtype;
812
813BEGIN
814for r_prac  in c_prac loop
815if r_prac.salary>1000 and r_prac.department_id in (10,30) then
816SYS.DBMS_OUTPUT.PUT_LINE('Prosze o podwyzke');
817else
818SYS.DBMS_OUTPUT.PUT_LINE('Podwyzka sie nie nalezy');
819end if;
820end loop;
821
822END;
823
824
8252.
826
827
828CREATE OR REPLACE PROCEDURE ZAD2 
829(
830  P_DEP_IN IN NUMBER 
831) AS 
832BEGIN
833  if P_DEP_IN<0 then
834  RAISE_APPLICATION_ERROR(-20101, 'Departament nie istnieje');
835  end if;
836  
837  delete job_history where end_date is not null and department_id=P_DEP_IN;
838  if SQL%ROWCOUNT=0 then
839  RAISE_APPLICATION_ERROR(-20102, 'Brak usunietych');
840  end if;
841  
842  
843END ZAD2;
844
845
846
8473.
848
849CREATE OR REPLACE TRIGGER ZAD3 
850BEFORE UPDATE OF EMAIL ON EMPLOYEES 
851FOR EACH ROW 
852BEGIN
853  if :old.email=:new.email then
854  RAISE_APPLICATION_ERROR(-20000, 'Niezmienione dane');
855  end if;
856END;