SHARE
TWEET

Untitled

a guest Oct 14th, 2019 105 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. set serveroutput on;
  3. declare
  4.     idDept integer;
  5.     contor integer := 7000;
  6.     numeAngajat emp.ename%type;
  7.     comision emp.comm%type;
  8.     dataAngajare emp.hiredate%type;
  9.     lipsaComision exception;
  10. begin
  11.     idDept := 30;
  12.     dbms_output.put_line(rpad('Nume', 15, ' ') ||
  13.         rpad('Data angajare', 20, ' ') || lpad('Comision', 15, ' '));
  14.     dbms_output.put_line(rpad('=', 15, '=') ||
  15.         rpad('Data =', 20, '=') || lpad('=', 15, '='));
  16.     loop
  17.         contor := contor + 1;
  18.         begin
  19.             select ename, hiredate, comm
  20.                 into numeAngajat, dataAngajare, comision
  21.                 from emp where deptno = idDept and empno = contor;
  22.             if comision is null then
  23.                 raise lipsaComision;
  24.             end if;
  25.             dbms_output.put_line(rpad(numeAngajat, 15, ' ') ||
  26.                 rpad(dataAngajare, 20, ' ') || lpad(comision, 15, ' '));
  27.             exception
  28.                 when lipsaComision then
  29.                     dbms_output.put_line(rpad(numeAngajat, 15, ' ') ||
  30.                         rpad(dataAngajare, 20, ' ')
  31.                         || lpad('lipsa comision', 15, ' '));
  32.                 when no_data_found then null;
  33.                 when others then null;
  34.         end;
  35.         exit when contor = 8000;
  36.     end loop;
  37. end; */
  38.  
  39. /*
  40. type cevaTabel is table if emp%rowtype;
  41. tabAng cevaTabel;
  42.  
  43. select *
  44. bulk collect into tabAng
  45. from emp
  46.  
  47. for index in tabAng.first .. tabbAng.last
  48.     tabAng(index).ename
  49. */
  50.  
  51. -- sa se scrie un bloc PL SQL care pt angajatii dintr-un departament
  52. -- a caruui ID se introduce de la tastatura, care au salariul peste media salariilor
  53. -- pt functia pe care o detin
  54. -- sa se genereze o exceptie definita si sa se afiseze un afisez (Angajatul din functia
  55. -- are salariul prea mare
  56. -- daca nu e excpetie, sa se angajeze angajatul
  57.  
  58. SET serveroutput ON;
  59. DECLARE
  60.     idDept INTEGER;
  61.     contor INTEGER := 7000;
  62.     idx INTEGER;
  63.     numeAngajat emp.ename%TYPE;
  64.     comision emp.comm%TYPE;
  65.     dataAngajare emp.hiredate%TYPE;
  66.     lipsaComision EXCEPTION;
  67.     TYPE cevaTabel IS TABLE OF emp%ROWTYPE;
  68.     tabAng cevaTabel;
  69.     average INTEGER;
  70. BEGIN
  71.     idDept := 30;
  72.     DBMS_OUTPUT.put_line(RPAD('Nume', 15, ' ') ||
  73.         RPAD('Salariu', 20, ' '));
  74.  
  75.     SELECT * BULK COLLECT INTO tabAng FROM emp WHERE deptno = idDept;
  76.     FOR idx IN tabAng.FIRST..tabAng.LAST
  77.     LOOP
  78.         BEGIN
  79.             SELECT AVG(sal) INTO average FROM emp WHERE job = tabAng(idx).job;
  80.             IF tabAng(idx).sal > average THEN
  81.                 RAISE lipsaComision;
  82.             END IF;
  83.             DBMS_OUTPUT.put_line(RPAD(tabAng(idx).ename, 15, ' ') || LPAD(tabAng(idx).sal, 15, ' '));
  84.             EXCEPTION
  85.                 WHEN lipsaComision THEN
  86.                     DBMS_OUTPUT.put_line(RPAD(tabAng(idx).ename, 15, ' ')
  87.                         || LPAD('salariu prea mare', 20, ' '));
  88.                 WHEN NO_DATA_FOUND THEN NULL;
  89.                 WHEN OTHERS THEN NULL;
  90.             END;
  91.         END LOOP;
  92.         EXCEPTION
  93.             WHEN OTHERS THEN NULL;
  94. END;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top