· 4 years ago · Dec 05, 2020, 11:44 AM
1-- 1
2DECLARE
3 V_NUME EMPLOYEES.LAST_NAME%TYPE := INITCAP('&p_nume');
4
5 FUNCTION F1 RETURN NUMBER IS
6 SALARIU EMPLOYEES.SALARY%TYPE;
7 BEGIN
8 SELECT
9 SALARY
10 INTO SALARIU
11 FROM
12 EMPLOYEES
13 WHERE
14 LAST_NAME = V_NUME;
15
16 RETURN SALARIU;
17 EXCEPTION
18 WHEN NO_DATA_FOUND THEN
19 DBMS_OUTPUT.PUT_LINE('Nu exista angajati cu numele dat');
20 RETURN -1;
21 WHEN TOO_MANY_ROWS THEN
22 DBMS_OUTPUT.PUT_LINE('Exista mai multi angajati ' || 'cu numele dat');
23 RETURN -2;
24 WHEN OTHERS THEN
25 DBMS_OUTPUT.PUT_LINE('Alta eroare!');
26 RETURN -3;
27 END F1;
28
29BEGIN
30 IF F1 > 0 THEN
31 DBMS_OUTPUT.PUT_LINE('Salariul este ' || F1);
32 END IF;
33--EXCEPTION
34-- WHEN OTHERS THEN
35-- DBMS_OUTPUT.PUT_LINE('Eroarea are codul = '
36-- || SQLCODE
37-- || ' si mesajul = '
38-- || SQLERRM);
39END;
40/
41
42-- 2
43
44CREATE OR REPLACE FUNCTION F2_DSO (
45 V_NUME EMPLOYEES.LAST_NAME%TYPE DEFAULT 'Bell'
46) RETURN NUMBER IS
47 SALARIU EMPLOYEES.SALARY%TYPE;
48BEGIN
49 SELECT
50 SALARY
51 INTO SALARIU
52 FROM
53 EMPLOYEES
54 WHERE
55 LAST_NAME = V_NUME;
56
57 RETURN SALARIU;
58EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 RAISE_APPLICATION_ERROR(-20000, 'Nu exista angajati cu numele dat');
61 WHEN TOO_MANY_ROWS THEN
62 RAISE_APPLICATION_ERROR(-20001, 'Exista mai multi angajati cu numele dat');
63 WHEN OTHERS THEN
64 RAISE_APPLICATION_ERROR(-20002, 'Alta eroare!');
65END F2_DSO;
66/
67
68-- metode de apelare
69-- 1 bloc plsql
70
71BEGIN
72 DBMS_OUTPUT.PUT_LINE('salariul esete ' || F2_DSO);
73END;
74/
75
76BEGIN
77 DBMS_OUTPUT.PUT_LINE('salariul esete ' || F2_DSO('King'));
78END;
79/
80
81-- 2. SQL
82
83SELECT
84 F2_DSO
85FROM
86 DUAL;
87
88SELECT
89 F2_DSO('King')
90FROM
91 DUAL;
92
93-- 3. SQL*PLUS CU VARIABILA HOST
94
95VARIABLE NR NUMBER
96
97EXECUTE :NR := F2_DSO('Bell');
98
99PRINT NR
100
101-- 3
102
103-- varianta 1
104
105DECLARE
106 V_NUME EMPLOYEES.LAST_NAME%TYPE := INITCAP('&p_nume');
107
108 PROCEDURE P3 IS
109 SALARIU EMPLOYEES.SALARY%TYPE;
110 BEGIN
111 SELECT
112 SALARY
113 INTO SALARIU
114 FROM
115 EMPLOYEES
116 WHERE
117 LAST_NAME = V_NUME;
118
119 DBMS_OUTPUT.PUT_LINE('Salariul este ' || SALARIU);
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 DBMS_OUTPUT.PUT_LINE('Nu exista angajati cu numele dat');
123 WHEN TOO_MANY_ROWS THEN
124 DBMS_OUTPUT.PUT_LINE('Exista mai multi angajati ' || 'cu numele dat');
125 WHEN OTHERS THEN
126 DBMS_OUTPUT.PUT_LINE('Alta eroare!');
127 END P3;
128
129BEGIN
130 P3;
131END;
132/
133
134-- varianta 2
135
136DECLARE
137 V_NUME EMPLOYEES.LAST_NAME%TYPE := INITCAP('&p_nume');
138 V_SALARIU EMPLOYEES.SALARY%TYPE;
139
140 PROCEDURE P3 (
141 SALARIU OUT EMPLOYEES.SALARY%TYPE
142 ) IS
143 BEGIN
144 SELECT
145 SALARY
146 INTO SALARIU
147 FROM
148 EMPLOYEES
149 WHERE
150 LAST_NAME = V_NUME;
151
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 RAISE_APPLICATION_ERROR(-20000, 'Nu exista angajati cu numele dat');
155 WHEN TOO_MANY_ROWS THEN
156 RAISE_APPLICATION_ERROR(-20001, 'Exista mai multi angajati cu numele dat');
157 WHEN OTHERS THEN
158 RAISE_APPLICATION_ERROR(-20002, 'Alta eroare!');
159 END P3;
160
161BEGIN
162 P3(V_SALARIU);
163 DBMS_OUTPUT.PUT_LINE('Salariul este ' || V_SALARIU);
164END;
165/
166
167-- 4
168-- varianta 1
169
170CREATE OR REPLACE PROCEDURE P4_DSO (
171 V_NUME EMPLOYEES.LAST_NAME%TYPE
172) IS
173 SALARIU EMPLOYEES.SALARY%TYPE;
174BEGIN
175 SELECT
176 SALARY
177 INTO SALARIU
178 FROM
179 EMPLOYEES
180 WHERE
181 LAST_NAME = V_NUME;
182
183 DBMS_OUTPUT.PUT_LINE('Salariul este ' || SALARIU);
184EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 RAISE_APPLICATION_ERROR(-20000, 'Nu exista angajati cu numele dat');
187 WHEN TOO_MANY_ROWS THEN
188 RAISE_APPLICATION_ERROR(-20001, 'Exista mai multi angajati cu numele dat');
189 WHEN OTHERS THEN
190 RAISE_APPLICATION_ERROR(-20002, 'Alta eroare!');
191END P4_DSO;
192/
193-- metode apelare
194-- 1. Bloc PLSQL
195
196BEGIN
197 P4_DSO('Bell');
198END;
199/
200-- 2. SQL*PLUS
201
202EXECUTE P4_DSO('Bell');
203
204EXECUTE P4_DSO('King');
205
206EXECUTE P4_DSO('Kimball');
207
208-- varianta 2
209
210CREATE OR REPLACE PROCEDURE P4_DSO (
211 V_NUME IN EMPLOYEES.LAST_NAME%TYPE,
212 SALARIU OUT EMPLOYEES.SALARY%TYPE
213) IS
214BEGIN
215 SELECT
216 SALARY
217 INTO SALARIU
218 FROM
219 EMPLOYEES
220 WHERE
221 LAST_NAME = V_NUME;
222
223EXCEPTION
224 WHEN NO_DATA_FOUND THEN
225 RAISE_APPLICATION_ERROR(-20000, 'Nu exista angajati cu numele dat');
226 WHEN TOO_MANY_ROWS THEN
227 RAISE_APPLICATION_ERROR(-20001, 'Exista mai multi angajati cu numele dat');
228 WHEN OTHERS THEN
229 RAISE_APPLICATION_ERROR(-20002, 'Alta eroare!');
230END P4_DSO;
231/
232-- metode apelare
233-- 1. Bloc PLSQL
234
235DECLARE
236 V_SALARIU EMPLOYEES.SALARY%TYPE;
237BEGIN
238 P4_DSO('Bell', V_SALARIU);
239 DBMS_OUTPUT.PUT_LINE('Salariul este ' || V_SALARIU);
240END;
241/
242-- 2. SQL*PLUS
243
244VARIABLE V_SAL NUMBER
245
246EXECUTE P4_DSO('Bell', :V_SAL)
247
248PRINT V_SAL
249
250-- 5
251
252VARIABLE ANG_MAN NUMBER
253
254BEGIN
255 :ANG_MAN := 200;
256END;
257/
258
259CREATE OR REPLACE PROCEDURE P5_DSO (
260 NR IN OUT NUMBER
261) IS
262BEGIN
263 SELECT
264 MANAGER_ID
265 INTO NR
266 FROM
267 EMPLOYEES
268 WHERE
269 EMPLOYEE_ID = NR;
270
271END P5_DSO;
272/
273
274EXECUTE P5_DSO(:ANG_MAN)
275
276PRINT ANG_MAN
277
278-- 6
279
280DECLARE
281 NUME EMPLOYEES.LAST_NAME%TYPE;
282
283 PROCEDURE P6 (
284 REZULTAT OUT EMPLOYEES.LAST_NAME%TYPE,
285 COMISION IN EMPLOYEES.COMMISSION_PCT%TYPE := NULL,
286 COD IN EMPLOYEES.EMPLOYEE_ID%TYPE := NULL
287 ) IS
288 BEGIN
289 IF ( COMISION IS NOT NULL ) THEN
290 SELECT
291 LAST_NAME
292 INTO REZULTAT
293 FROM
294 EMPLOYEES
295 WHERE
296 COMMISSION_PCT = COMISION;
297
298 DBMS_OUTPUT.PUT_LINE('numele salariatului care are
299comisionul '
300 || COMISION
301 || ' este '
302 || REZULTAT);
303 ELSE
304 SELECT
305 LAST_NAME
306 INTO REZULTAT
307 FROM
308 EMPLOYEES
309 WHERE
310 EMPLOYEE_ID = COD;
311
312 DBMS_OUTPUT.PUT_LINE('numele salariatului avand codul '
313 || COD
314 || ' este '
315 || REZULTAT);
316 END IF;
317 END P6;
318
319BEGIN
320 P6(NUME, 0.4);
321 P6(NUME, COD => 200);
322END;
323/
324
325-- 7
326
327DECLARE
328 MEDIE1 NUMBER(10, 2);
329 MEDIE2 NUMBER(10, 2);
330
331 FUNCTION MEDIE (
332 V_DEPT EMPLOYEES.DEPARTMENT_ID%TYPE
333 ) RETURN NUMBER IS
334 REZULTAT NUMBER(10, 2);
335 BEGIN
336 SELECT
337 AVG(SALARY)
338 INTO REZULTAT
339 FROM
340 EMPLOYEES
341 WHERE
342 DEPARTMENT_ID = V_DEPT;
343
344 RETURN REZULTAT;
345 END;
346
347 FUNCTION MEDIE (
348 V_DEPT EMPLOYEES.DEPARTMENT_ID%TYPE,
349 V_JOB EMPLOYEES.JOB_ID%TYPE
350 ) RETURN NUMBER IS
351 REZULTAT NUMBER(10, 2);
352 BEGIN
353 SELECT
354 AVG(SALARY)
355 INTO REZULTAT
356 FROM
357 EMPLOYEES
358 WHERE
359 DEPARTMENT_ID = V_DEPT
360 AND JOB_ID = V_JOB;
361
362 RETURN REZULTAT;
363 END;
364
365BEGIN
366 MEDIE1 := MEDIE(80);
367 DBMS_OUTPUT.PUT_LINE('Media salariilor din departamentul 80'
368 || ' este '
369 || MEDIE1);
370 MEDIE2 := MEDIE(80, 'SA_MAN');
371 DBMS_OUTPUT.PUT_LINE('Media salariilor managerilor din'
372 || ' departamentul 80 este '
373 || MEDIE2);
374END;
375/
376
377-- 8
378
379CREATE OR REPLACE FUNCTION FACTORIAL_DSO (
380 N NUMBER
381) RETURN INTEGER IS
382BEGIN
383 IF ( N = 0 ) THEN
384 RETURN 1;
385 ELSE
386 RETURN N * FACTORIAL_DSO(N - 1);
387 END IF;
388END FACTORIAL_DSO;
389/
390
391-- apelare
392
393BEGIN
394 DBMS_OUTPUT.PUT_LINE('Factorial ' || FACTORIAL_DSO(6));
395END;
396/
397
398-- 9
399
400CREATE OR REPLACE FUNCTION MEDIE_DSO RETURN NUMBER IS
401 REZULTAT NUMBER;
402BEGIN
403 SELECT
404 AVG(SALARY)
405 INTO REZULTAT
406 FROM
407 EMPLOYEES;
408
409 RETURN REZULTAT;
410END;
411/
412
413SELECT
414 LAST_NAME,
415 SALARY
416FROM
417 EMPLOYEES
418WHERE
419 SALARY >= MEDIE_DSO;
420
421-- Exercitii
422-- 1
423
424CREATE TABLE INFO_DSO (
425 ID NUMBER(3) PRIMARY KEY,
426 UTILIZATOR VARCHAR2(50),
427 DATA TIMESTAMP(3),
428 COMANDA VARCHAR2(50),
429 NR_LINII NUMBER,
430 EROARE VARCHAR2(200)
431);
432
433CREATE SEQUENCE S_INFO_DSO START WITH 1;
434
435-- 2
436
437CREATE OR REPLACE FUNCTION F2_DSO2 (
438 V_NUME EMPLOYEES.LAST_NAME%TYPE DEFAULT 'Bell'
439) RETURN NUMBER IS
440 SALARIU EMPLOYEES.SALARY%TYPE;
441 V_MESAJ VARCHAR2(50);
442BEGIN
443 SELECT
444 SALARY
445 INTO SALARIU
446 FROM
447 EMPLOYEES
448 WHERE
449 LAST_NAME = V_NUME;
450
451 INSERT INTO INFO_DSO VALUES (
452 S_INFO_DSO.NEXTVAL,
453 USER,
454 SYSTIMESTAMP,
455 'select',
456 1,
457 NULL
458 );
459
460 RETURN SALARIU;
461EXCEPTION
462 WHEN NO_DATA_FOUND THEN
463 INSERT INTO INFO_DSO VALUES (
464 S_INFO_DSO.NEXTVAL,
465 USER,
466 SYSTIMESTAMP,
467 'select',
468 0,
469 'Nu exista angajati cu numele specificat'
470 );
471
472 RETURN -1;
473 WHEN TOO_MANY_ROWS THEN
474 INSERT INTO INFO_DSO VALUES (
475 S_INFO_DSO.NEXTVAL,
476 USER,
477 SYSTIMESTAMP,
478 'select',
479 2,
480 'Exista mai multi angajati cu numele dat'
481 );
482
483 RETURN -2;
484 WHEN OTHERS THEN
485 V_MESAJ := SQLCODE
486 || ' '
487 || SQLERRM;
488 INSERT INTO INFO_DSO VALUES (
489 S_INFO_DSO.NEXTVAL,
490 USER,
491 SYSTIMESTAMP,
492 'select',
493 NULL,
494 V_MESAJ
495 );
496
497 RETURN -3;
498END F2_DSO2;
499/
500
501SELECT
502 *
503FROM
504 INFO_DSO;
505
506BEGIN
507 DBMS_OUTPUT.PUT_LINE('Salariu ' || F2_DSO2);
508 DBMS_OUTPUT.PUT_LINE('Salariu ' || F2_DSO2('King'));
509 DBMS_OUTPUT.PUT_LINE('Salariu ' || F2_DSO2('K'));
510END;
511/
512-- ex 2 procedura 4 tema
513-- 3
514
515SELECT
516 *
517FROM
518 LOCATIONS;
519
520SELECT
521 COUNT(*)
522FROM
523 EMPLOYEES E,
524 DEPARTMENTS D,
525 LOCATIONS L
526WHERE
527 L.LOCATION_ID = D.LOCATION_ID
528 AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
529 AND UPPER(CITY) = 'LONDON';
530
531SELECT
532 COUNT(*)
533FROM
534 (
535 SELECT
536 EMPLOYEE_ID
537 FROM
538 JOB_HISTORY JH
539 WHERE
540 EXISTS (
541 SELECT
542 EMPLOYEE_ID
543 FROM
544 EMPLOYEES E,
545 DEPARTMENTS D,
546 LOCATIONS L
547 WHERE
548 L.LOCATION_ID = D.LOCATION_ID
549 AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
550 AND JH.EMPLOYEE_ID = E.EMPLOYEE_ID
551 AND UPPER(CITY) = 'LONDON'
552 )
553 GROUP BY
554 EMPLOYEE_ID
555 HAVING
556 COUNT(DISTINCT(JOB_ID)) >= 2
557 );
558
559CREATE OR REPLACE FUNCTION EX3_DSO (
560 V_ORAS LOCATIONS.CITY%TYPE
561) RETURN NUMBER IS
562 NR NUMBER;
563 ORAS LOCATIONS.CITY%TYPE;
564BEGIN
565 SELECT
566 CITY
567 INTO ORAS
568 FROM
569 LOCATIONS
570 WHERE
571 UPPER(CITY) = UPPER(V_ORAS);
572
573 SELECT
574 COUNT(*)
575 INTO NR
576 FROM
577 EMPLOYEES E,
578 DEPARTMENTS D,
579 LOCATIONS L
580 WHERE
581 L.LOCATION_ID = D.LOCATION_ID
582 AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
583 AND UPPER(CITY) = UPPER(V_ORAS);
584
585 IF NR = 0 THEN
586 INSERT INTO INFO_DSO VALUES (
587 S_INFO_DSO.NEXTVAL,
588 USER,
589 SYSTIMESTAMP,
590 'select',
591 0,
592 'Nu exista niciun angajat in orasul dat'
593 );
594
595 RETURN -1;
596 END IF;
597
598 SELECT
599 COUNT(*)
600 INTO NR
601 FROM
602 (
603 SELECT
604 EMPLOYEE_ID
605 FROM
606 JOB_HISTORY JH
607 WHERE
608 EXISTS (
609 SELECT
610 EMPLOYEE_ID
611 FROM
612 EMPLOYEES E,
613 DEPARTMENTS D,
614 LOCATIONS L
615 WHERE
616 L.LOCATION_ID = D.LOCATION_ID
617 AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
618 AND JH.EMPLOYEE_ID = E.EMPLOYEE_ID
619 AND UPPER(CITY) = 'LONDON'
620 )
621 GROUP BY
622 EMPLOYEE_ID
623 HAVING
624 COUNT(DISTINCT(JOB_ID)) >= 2
625 );
626
627 INSERT INTO INFO_DSO VALUES (
628 S_INFO_DSO.NEXTVAL,
629 USER,
630 SYSTIMESTAMP,
631 'select',
632 1,
633 NULL
634 );
635
636 RETURN NR;
637EXCEPTION
638 WHEN NO_DATA_FOUND THEN
639 INSERT INTO INFO_DSO VALUES (
640 S_INFO_DSO.NEXTVAL,
641 USER,
642 SYSTIMESTAMP,
643 'select',
644 0,
645 'Nu exista orasul dat'
646 );
647
648 RETURN -1;
649END EX3_DSO;
650/
651
652BEGIN
653 DBMS_OUTPUT.PUT_LINE('Nr angajati ' || EX3_DSO(''));
654END;
655/
656
657-- tema ex 2 partea cu ex 4 tema
658-- si ex 4,5,6