itD3s5rN

1--Procedures
2SET SERVEROUTPUT ON;
3--0
4CREATE TABLE BEMP AS (SELECT * FROM EMP);
5CREATE TABLE BDEPT AS (SELECT * FROM DEPT);
6CREATE TABLE BSALGRADE AS (SELECT * FROM SALGRADE);
7--1
8CREATE OR REPLACE PROCEDURE NEW_PERSON
9(
10ENAME1 VARCHAR2,
11JOB1 VARCHAR2,
12MGR1 NUMBER,
13HIREDATE1 DATE,
14SAL1 NUMBER,
15COMM1 NUMBER,
16DEPTNO1 NUMBER
17)
18AS
19PERSON_EXISTS EXCEPTION;
20X NUMBER;
21Y INTEGER;
22BEGIN
23SELECT COUNT(ENAME) INTO Y FROM BEMP WHERE BEMP.ENAME = ENAME1 AND BEMP.JOB = JOB1 AND BEMP.HIREDATE = HIREDATE1;
24IF(Y>0)
25THEN RAISE PERSON_EXISTS;
26ELSE
27SELECT (MAX(EMPNO)+1) INTO X FROM BEMP;
28INSERT INTO BEMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(X, ENAME1, JOB1, MGR1, HIREDATE1, SAL1, COMM1, DEPTNO1);
29END IF;
30EXCEPTION 
31WHEN PERSON_EXISTS
32THEN
33DBMS_OUTPUT.put_line('This person already exists in the database');
34END;
35--Example for task 1
36EXEC NEW_PERSON('MICHAL','MANAGER', 7654, TO_DATE('07-03-1980','MM-DD-YYYY'), 3000, 20, 10);
37--2
38CREATE OR REPLACE PROCEDURE INCOMSUM 
39(
40DEPTNO1 INTEGER
41)
42AS
43X INTEGER;
44FINALSUM INTEGER;
45EMPTY_DEPARTMENT EXCEPTION;
46DEPARTMENT_DOES_NOT_EXIST EXCEPTION;
47BEGIN 
48SELECT COUNT(DEPTNO) INTO X FROM BDEPT WHERE DEPTNO = DEPTNO1;
49IF(X=0)
50THEN RAISE DEPARTMENT_DOES_NOT_EXIST;
51ELSE
52SELECT SUM(SAL) INTO FINALSUM FROM BEMP WHERE DEPTNO = DEPTNO1;
53IF(FINALSUM IS NULL)
54THEN RAISE EMPTY_DEPARTMENT;
55ELSE
56DBMS_OUTPUT.put_line('In department '||DEPTNO1|| ' sum of salaries is '|| FINALSUM);
57END IF;
58END IF;
59EXCEPTION 
60WHEN DEPARTMENT_DOES_NOT_EXIST
61THEN 
62DBMS_OUTPUT.put_line('The department '||DEPTNO1|| ' does not exist');
63WHEN EMPTY_DEPARTMENT
64THEN
65DBMS_OUTPUT.put_line('The department '|| DEPTNO1||' is empty');
66END;
67--Example for task 2
68EXEC INCOMSUM(50);
69EXEC INCOMSUM(10);
70EXEC INCOMSUM(40);
71--3(a)
72CREATE OR REPLACE PROCEDURE CON_TO_MAN
73AS
74NOT_EXISTS EXCEPTION;
75CURSOR A IS
76SELECT EMPNO FROM BEMP WHERE 
77JOB = 'SALESMAN';
78D NUMBER;
79O1C INTEGER;
80BEGIN 
81SELECT COUNT(EMPNO)INTO O1C FROM BEMP WHERE JOB = 'SALESMAN';
82IF(O1C = 0)
83THEN RAISE NOT_EXISTS;
84ELSE
85OPEN A;
86LOOP 
87FETCH A INTO D;
88EXIT WHEN A%NOTFOUND;
89UPDATE BEMP
90SET JOB = 'MANAGER' WHERE EMPNO = D;
91END LOOP;
92COMMIT;
93CLOSE A;
94END IF;
95EXCEPTION
96WHEN NOT_EXISTS THEN
97DBMS_OUTPUT.put_line('There are no salesmen');
98END;
99--Execution of task 3(a)
100EXEC CON_TO_MAN;
101--3(b)
102CREATE OR REPLACE PROCEDURE CHANGE_JOB
103(
104O VARCHAR2,
105N VARCHAR2
106)
107AS
108NOT_EXISTS EXCEPTION;
109CURSOR A IS
110SELECT EMPNO FROM BEMP WHERE 
111JOB = O;
112O1C INTEGER;
113D NUMBER;
114BEGIN 
115SELECT COUNT(EMPNO)INTO O1C FROM BEMP WHERE JOB = O;
116IF(O1C = 0)
117THEN RAISE NOT_EXISTS;
118ELSE
119OPEN A;
120LOOP 
121FETCH A INTO D;
122EXIT WHEN A%NOTFOUND;
123UPDATE BEMP
124SET JOB = N WHERE EMPNO = D;
125END LOOP;
126COMMIT;
127CLOSE A;
128END IF;
129EXCEPTION
130WHEN NOT_EXISTS THEN
131DBMS_OUTPUT.put_line('Job ' ||O||' does not exist in the table');
132END;
133--Execution of task 3(b)
134EXEC CHANGE_JOB('WRONG_NAME','WAITER');
135EXEC CHANGE_JOB('MANAGER','WAITER');
136--4
137CREATE OR REPLACE PROCEDURE AVCOM 
138(
139DNAME1 VARCHAR2 
140)
141AS
142NUMBERF NUMBER;
143X INTEGER;
144FINALAVG INTEGER;
145DEPARTMENT_DOES_NOT_EXIST EXCEPTION;
146EMPTY_DEPARTMENT EXCEPTION;
147BEGIN 
148SELECT COUNT(DEPTNO) INTO X FROM BDEPT WHERE DNAME = DNAME1;
149IF(X=0)
150THEN RAISE DEPARTMENT_DOES_NOT_EXIST;
151ELSE 
152SELECT DISTINCT DEPTNO INTO NUMBERF FROM BDEPT WHERE DNAME = DNAME1;
153SELECT AVG(NVL(COMM,0)) INTO FINALAVG FROM BEMP, BDEPT WHERE BEMP.DEPTNO = BDEPT.DEPTNO AND BDEPT.DNAME = DNAME1;
154IF(NVL(FINALAVG,0) = 0)
155THEN RAISE EMPTY_DEPARTMENT;
156ELSE
157DBMS_OUTPUT.put_line('In department '||DNAME1|| ' ('||NUMBERF||') average of commisions is '|| FINALAVG);
158END IF;
159END IF;
160EXCEPTION 
161WHEN DEPARTMENT_DOES_NOT_EXIST
162THEN 
163DBMS_OUTPUT.put_line('The department '||DNAME1|| ' does not exist');
164WHEN EMPTY_DEPARTMENT
165THEN
166DBMS_OUTPUT.put_line('The department '||DNAME1||' ('||NUMBERF||') is empty');
167END;
168--Execution of task 4
169EXEC AVCOM('WRONG_NAME');
170EXEC AVCOM('ACCOUNTING');
171EXEC AVCOM('RESEARCH');
172EXEC AVCOM('SALES');
173EXEC AVCOM('OPERATIONS');
174--5
175CREATE OR REPLACE PROCEDURE RAISESAL
176AS
177NOT_ANALYST_EXISTS EXCEPTION;
178NOT_MANAGER_EXISTS EXCEPTION;
179CURSOR A IS
180SELECT EMPNO FROM BEMP WHERE 
181JOB = 'ANALYST' OR JOB = 'MANAGER';
182D NUMBER;
183O1C INTEGER;
184O2C INTEGER;
185BEGIN 
186SELECT COUNT(EMPNO)INTO O1C FROM BEMP WHERE JOB = 'ANALYST';
187IF(O1C = 0)
188THEN RAISE NOT_ANALYST_EXISTS;
189ELSE
190SELECT COUNT(EMPNO)INTO O2C FROM BEMP WHERE JOB = 'MANAGER';
191IF(O2C = 0)
192THEN RAISE NOT_MANAGER_EXISTS;
193ELSE
194OPEN A;
195LOOP 
196FETCH A INTO D;
197EXIT WHEN A%NOTFOUND;
198UPDATE BEMP
199SET SAL = (SAL+SAL*0.1) WHERE EMPNO = D AND JOB = 'ANALYST';
200UPDATE BEMP
201SET SAL = (SAL+SAL*0.2) WHERE EMPNO = D AND JOB = 'MANAGER';
202END LOOP;
203IF( A%ROWCOUNT>2) THEN
204DBMS_OUTPUT.put_line('3 salaries have been changed');
205COMMIT;
206CLOSE A;
207END IF;
208END IF;
209END IF;
210EXCEPTION
211WHEN NOT_ANALYST_EXISTS THEN
212DBMS_OUTPUT.put_line('No ANALYST in the table');
213WHEN NOT_MANAGER_EXISTS THEN
214DBMS_OUTPUT.put_line('No MANAGER in the table');
215END;
216--Execution of task 5
217EXEC RAISESAL;