tema3SQL

A
Creati o noua tabela, SPION, cu o coloana numerica (pentru care definiti o secventa) si o
coloana text.
Creati diversi declansatori pentru cele patru posibilitati urmatoare: Declansator inainte
nivel comanda, declansator inainte nivel linie, declansator dupa nivel comanda, declansator
dupa nivel linie; ei vor inregistra IN tabela SPION numarul urmator generat de secventa,
precum si textul potrivit situatiei: " inainte nivel comanda ", " inainte nivel linie ", " dupa
nivel comanda ", " dupa nivel linie ".
Creati un script cu instrutiuni LMD care prodvoaca declansarea acestor declansatori si, IN
acelasi timp, inregistrati IN tabela SPION.
Analizati continutul tabelei SPION.
CREATE TABLE SPION (
id NUMBER,
comentariu VARCHAR2);


CREATE SEQUENCE spion_sq
MINVALUE 1
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER spion_trigger_before
before INSERT OR UPDATE OR DELETE ON emp
BEGIN
    INSERT INTO spion VALUES(spion_sq.NEXTVAL,'inainte nivel comanda');
END;

CREATE OR REPLACE TRIGGER spion_trigger_before_niv_linie
before INSERT OR UPDATE OR DELETE ON emp FOR each ROW
BEGIN
    INSERT INTO spion VALUES(spion_sq.NEXTVAL,'inainte nivel linie');
END;
CREATE OR REPLACE TRIGGER spion_trigger_after
after INSERT OR UPDATE OR DELETE ON emp
BEGIN
    INSERT INTO spion VALUES(spion_sq.NEXTVAL,'dupa nivel comanda');
END;

CREATE OR REPLACE TRIGGER spion_trigger_after_niv_linie
after INSERT OR UPDATE OR DELETE ON emp FOR each ROW
BEGIN
    INSERT INTO spion VALUES(spion_sq.NEXTVAL,'dupa nivel linie');
END;

B
Creati un declansator care semnaleaza momentul cand se inregistreaza un comision pentru
un angajat care nu este "salesman".

CREATE OR REPLACE TRIGGER com_salesman
before INSERT OR UPDATE ON emp FOR each ROW
WHEN (NEW.comm IS NOT NULL AND UPPER(old.job) <> 'SALESMAN')
BEGIN
  dbms_output.put_line('se va inregistra un nou comision pentru un angajat de tip salesman');
END;

C
Creaţi o procedură PL/SQL pentru calcularea şi afişarea sumei salariilor la un departament,
al cărui nume este citit de la tastatură. Lansaţi procedura sub SQLPlus.

CREATE OR REPLACE PROCEDURE sum_ang_dep IS
v_nume emp.ename%TYPE:=&name;
v_sal emp.sal%TYPE;
BEGIN
    SELECT SUM(e.sal) INTO v_sal FROM emp e inner join dept d ON e.deptno = d.deptno WHERE d.dname=v_nume GROUP BY d.deptno;
    dbms_output.put_line('suma salariilor de la departamenul '||v_nume||' este '||v_sal);
END;

D
Creaţi un bloc PL/SQL pentru afişarea sumei salariilor la un departament, al cărui nume
este citit de la tastatură. Utilizaţi pentru acest calcul, o funcţie definită local, la nivelul
blocului.
DECLARE
  v_nume dept.dname%TYPE :=&name;
  v_sal  emp.sal%TYPE;
  FUNCTION fnc_sum_sal_dep(nume IN emp.ename%TYPE) RETURN emp.sal%TYPE AS
    sal emp.sal%TYPE;
  BEGIN
    SELECT SUM(sal)
      INTO sal
      FROM emp e
     inner join dept d
        ON e.deptno = d.deptno
     WHERE d.dname = v_nume
     GROUP BY d.deptno;
    RETURN sal;
  END fnc_sum_sal_dep;
BEGIN
  dbms_output.put_line('suma salariilor de la departamenul ' || v_nume ||
                       ' este ' || fnc_sum_sal_dep(v_nume));
END;



E
Creaţi un bloc PL/SQL pentru afişarea celui mai mic salariu mediu înre două departamente
ale căror nume sunt citite de la tastatură. Utilizaţi pentru calculul salariului mediu o funcţie
definită local, la nivelul blocului.
DECLARE
  v_dept1 dept.dname%TYPE := &dept1;
  v_sal1  emp.sal%TYPE;
  v_sal2  emp.sal%TYPE;
  v_dept2 dept.dname%TYPE := &dept2;
  FUNCTION fnc_sal_med(dept IN dept.dname%TYPE) RETURN emp.sal%TYPE AS
    sal emp.sal%TYPE;
  BEGIN
    SELECT AVG(e.sal)
      INTO sal
      FROM emp e
     inner join dept d
        ON e.deptno = d.deptno
     WHERE d.dname = dept
     GROUP BY d.deptno;
    RETURN sal;
  END fnc_sal_med;
BEGIN
  v_sal1 := fnc_sal_med(v_dept1);
  v_sal2 := fnc_sal_med(v_dept2);
  IF (v_sal1 < v_sal2) THEN
    dbms_output.put_line(v_dept1 || ' ' || v_sal1);
  ELSE
    dbms_output.put_line(v_dept2 || ' ' || v_sal2);
  END IF;
END;

F
Repetaţi problema precedentă, utilizând IN loc de funcţie, o procedură care transmite
rezultatul printr-un parametru de ieşire.
DECLARE
    v_dept1 dept.dname%TYPE:='SALES';
    v_sal1 emp.sal%TYPE;
    v_sal2 emp.sal%TYPE;
    v_dept2 dept.dname%TYPE:='ACCOUNTING';
    PROCEDURE proc_sum_sal_med(dept IN dept.dname%TYPE, sal OUT emp.sal%TYPE) AS
    BEGIN
        SELECT AVG(e.sal) INTO sal FROM emp e inner join dept d ON e.deptno = d.deptno WHERE d.dname=dept GROUP BY d.deptno;
    END proc_sum_sal_med;
BEGIN
    proc_sum_sal_med(v_dept1,v_sal1);
    proc_sum_sal_med(v_dept2,v_sal2);
    IF(v_sal1<v_sal2) THEN
        dbms_output.put_line(v_dept1||' '||v_sal1);
    ELSE
        dbms_output.put_line(v_dept2||' '||v_sal2);
    END IF;
END;

G
Craţi un bloc PL/SQL pentru afişarea angajaţilor cu salariul superior salariului mediu din
departamentul lor. Utilizaţi pentru calculul salariului mediu o funcţie definită local, la
nivelul blocului.

DECLARE
  v_sal emp.sal%TYPE;
  CURSOR dept_cursor IS
    SELECT * FROM dept;
  CURSOR ang_curso(v_sal emp.sal%TYPE, v_deptno emp.deptno%TYPE) IS
    SELECT * FROM emp WHERE sal > v_sal AND deptno = v_deptno;
  FUNCTION fnc_sal_med(dept IN dept.deptno%TYPE) RETURN emp.sal%TYPE AS
    sal emp.sal%TYPE;
  BEGIN
    SELECT AVG(e.sal) INTO sal FROM emp e WHERE e.deptno = dept;
    RETURN sal;
  END fnc_sal_med;
BEGIN
  FOR v_dept IN dept_cursor LOOP
    v_sal := fnc_sal_med(v_dept.deptno);
    dbms_output.put_line('departament: '||v_dept.dname||' sal: '||v_sal);
    FOR ang IN ang_curso(v_sal,v_dept.deptno) LOOP
         dbms_output.put_line(ang.ename||' '||ang.sal);
    END LOOP;
  END LOOP;
END;

H
Repetaţi problema precedentă, utilizând IN loc de funcţie, o procedură care transmite
rezultatul printr-un parametru de ieşire.

DECLARE
  v_sal emp.sal%TYPE;
  CURSOR dept_cursor IS
    SELECT * FROM dept;
  CURSOR ang_curso(v_sal emp.sal%TYPE, v_deptno emp.deptno%TYPE) IS
    SELECT * FROM emp WHERE sal > v_sal AND deptno = v_deptno;
  PROCEDURE proc_sal_med(dept IN dept.deptno%TYPE, sal OUT emp.sal%TYPE) AS
    BEGIN
        SELECT AVG(e.sal) INTO sal FROM emp e WHERE e.deptno=dept;
    END proc_sal_med;
BEGIN
  FOR v_dept IN dept_cursor LOOP
    proc_sal_med(v_dept.deptno,v_sal);
    dbms_output.put_line('departament: '||v_dept.dname||' sal: '||v_sal);
    FOR ang IN ang_curso(v_sal,v_dept.deptno) LOOP
         dbms_output.put_line(ang.ename||' '||ang.sal);
    END LOOP;
  END LOOP;
END;

I
Creaţi o funcţie PL/SQL pentru calculul salariului mediu a tuturor angajaţilor şi apoi, cu o
comandă SQL, afişaţi TOţi angajaţii care au salariul mai mic decât media.

CREATE OR REPLACE FUNCTION sal_med_ang RETURN emp.sal%TYPE IS
    v_sal emp.sal%TYPE;
BEGIN
    SELECT AVG(sal) INTO v_sal FROM emp;
    RETURN v_sal;
END;
SELECT * FROM emp WHERE sal<sal_med_ang();
J
Creaţi un pachet pentru gestiunea angajaţilor, cu următoarele posibilităţi:
- o funcţie care controlează existenţa unui angajat în tabela emp, plecând de la codul lui
- o procedură de eliminare a unui angajat
- o procedură cu doi parametri care măreşte salariul unui angajat (prevedeţi o excepţie
pentru absenţa angajatului şi pentru valoarea 0 a sumei cu care se măreşte salariul; în acest
caz, angajatul va primi salariul mediu).
Creaţi un script pentru validarea acestor subprograme.

CREATE OR REPLACE PACKAGE gest_ang IS
    FUNCTION exist_emp(v_empno_param emp.empno%TYPE) RETURN VARCHAR2;
    PROCEDURE delete_emp(v_empno emp.empno%TYPE);
    PROCEDURE encrase_sal(v_empno emp.empno%TYPE, v_marire NUMBER);
END gest_ang;

CREATE OR REPLACE PACKAGE BODY gest_ang IS

  FUNCTION exist_emp(v_empno_param emp.empno%TYPE) RETURN VARCHAR2 IS
        v_empno emp.empno%TYPE;
    BEGIN
        SELECT empno INTO v_empno FROM emp WHERE empno=v_empno_param;
    dbms_output.put_line('v_empno =['||v_empno||']');
        IF SQL%ROWCOUNT = 0 THEN
            RETURN 'Angajatul nu exista!';
        ELSE
            RETURN 'Angajatul exista!';
        END IF;
    END exist_emp;

    PROCEDURE delete_emp(v_empno emp.empno%TYPE) IS
    BEGIN
        DELETE FROM emp WHERE empno=v_empno;
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('A aparut o eroare in timpul executiei!');
    END;

  PROCEDURE encrase_sal(v_empno emp.empno%TYPE, v_marire NUMBER) IS
    e_ang_not_found EXCEPTION;
    v_emp emp%ROWTYPE;
    v_marire_sal NUMBER :=v_marire;
  BEGIN
    SELECT * INTO v_emp FROM emp WHERE empno=v_empno;
    IF SQL%ROWCOUNT = 0 THEN
      RAISE e_ang_not_found;
    END IF;
    IF(v_marire = 0) THEN
      SELECT AVG(sal) INTO v_marire_sal FROM emp;
    END IF;
    UPDATE emp SET sal=(sal + v_marire_sal) WHERE empno=v_empno;
  EXCEPTION
    WHEN e_ang_not_found THEN
      dbms_output.put_line('nu exista angajatul cu codul '||v_empno);
    WHEN OTHERS THEN
      dbms_output.put_line('a aparut o eroare in timpul executiei');
  END;
END gest_ang;