Laborator 8-9

Laborator8-9.plsql

-- Exercitiul 1 - return functie

CREATE OR REPLACE FUNCTION afla_functie
(
    v_emp_id IN angajati.employee_id%TYPE)
RETURN angajati.job_id%TYPE IS
functie angajati.job_id%TYPE;
BEGIN
    SELECT (job_id) INTO functie FROM angajati WHERE employee_id = v_emp_id;
    RETURN functie;
END afla_functie;

BEGIN
dbms_output.put_line(afla_functie(237));
END;

-- Exercitiul 2 -- calcul impozit
CREATE OR REPLACE FUNCTION calcul_impozit
(
    v_emp_id IN angajati.employee_id%TYPE)
RETURN angajati.salary%TYPE IS
salariu angajati.salary%TYPE;
BEGIN
    SELECT (salary) INTO salariu FROM angajati WHERE employee_id = v_emp_id;
    IF salariu < 5000 THEN
        RETURN salariu*0.05;
    ELSIF salariu >= 5000 AND salariu < 10000 THEN
        RETURN salariu*0.1;
    ELSIF salariu > 10000 THEN
        RETURN salariu*0.15;
    END IF;
END calcul_impozit;

BEGIN
dbms_output.put_line(calcul_impozit(201));
END;

-- Ex 3 (data dictionary)
DECLARE
    PROCEDURE display_nume_obiecte (tip IN user_objects.object_type%TYPE) IS
    CURSOR c_obiecte IS
    SELECT object_name FROM USER_OBJECTS WHERE object_type=tip;
    v_obiecte c_obiecte%ROWTYPE;
    v_total_obiecte NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_total_obiecte FROM user_objects WHERE object_type=tip;
        dbms_output.put_line('Total ' || tip || ': ' || v_total_obiecte);
        OPEN c_obiecte;
        LOOP
            FETCH c_obiecte INTO v_obiecte;
                EXIT WHEN c_obiecte%notfound;
                DBMS_OUTPUT.PUT_LINE(v_obiecte.object_name);
        END LOOP;
        CLOSE c_obiecte;
    END display_nume_obiecte;
BEGIN
    display_nume_obiecte('PROCEDURE');
END;

-- ex 4 - interogare linii cod ex2
SELECT text FROM all_source WHERE name='CALCUL_IMPOZIT' and type='FUNCTION';

-- ex 5 - triggger delete
CREATE OR REPLACE TRIGGER stergere_angajati 
AFTER DELETE ON angajati 
FOR EACH ROW
DECLARE 
BEGIN 
INSERT INTO copie_angajati (employee_id, first_name, last_name, email, hire_date, job_id)
    VALUES (:OLD.employee_id, :OLD.first_name, :OLD.last_name, :OLD.email, :OLD.hire_date, :OLD.job_id);
END;


DELETE FROM angajati WHERE employee_id IN (SELECT 
            max(employee_id)
        FROM
            angajati);

-- ex 6 jurnalizare:
CREATE OR REPLACE TRIGGER stergere_angajati 
AFTER UPDATE OF salary ON angajati
FOR EACH ROW
DECLARE
    utilizator_id INT;
BEGIN 
SELECT sys_context('USERENV', 'SESSION_USERID') INTO utilizator_id  FROM dual;
INSERT INTO jurnalizare (utilizator, data_ora, id_angajat, salariu_vechi, salariu_nou, mentiuni)
    VALUES (utilizator_id, SYSDATE, :OLD.employee_id, :OLD.salary, :NEW.salary, 'Aceasta este o mentiune');
DBMS_output.put_line('Introdus in tabela jurnalizare.');
END;

UPDATE angajati
SET salary=69593
WHERE employee_id=698;

-- Ex 7 (exemplu, nu merge din cauza privilegilor insuficiente)
CREATE OR REPLACE TRIGGER user_login
AFTER LOGON ON DATABASE
BEGIN
  DBMS_OUTPUT.PUT_LINE('Bine ai venit ' || USER);
END;

-- Ex 8 (exemplu, nu merge din cauza privilegilor insuficiente)
CREATE OR REPLACE TRIGGER user_login
AFTER LOGOFF ON DATABASE
BEGIN
  DBMS_OUTPUT.PUT_LINE('Bine ai venit ' || USER);
END;


-- PACKAGES

-- Ex 1
CREATE OR REPLACE PACKAGE salarizare AS
    FUNCTION afla_functie(v_emp_id IN angajati.employee_id%TYPE)
        RETURN angajati.job_id%TYPE;

    FUNCTION calcul_impozit(v_emp_id IN angajati.employee_id%TYPE)
        RETURN angajati.salary%TYPE;

    PROCEDURE concediez_ultim;
END salarizare;


CREATE OR REPLACE PACKAGE BODY salarizare AS

    v_globala VARCHAR2(100) := 'Exemplu de variabila globala';
    FUNCTION afla_functie(v_emp_id IN angajati.employee_id%TYPE)
    RETURN angajati.job_id%TYPE IS
        functie angajati.job_id%TYPE;
    BEGIN
        SELECT job_id INTO functie FROM angajati WHERE employee_id = v_emp_id;
        RETURN functie;
    END afla_functie;

    FUNCTION calcul_impozit(v_emp_id IN angajati.employee_id%TYPE)
    RETURN angajati.salary%TYPE IS
        salariu angajati.salary%TYPE;
    BEGIN
        SELECT salary INTO salariu FROM angajati WHERE employee_id = v_emp_id;
        IF salariu < 5000 THEN
            RETURN salariu * 0.05;
        ELSIF salariu >= 5000 AND salariu < 10000 THEN
            RETURN salariu * 0.1;
        ELSE
            RETURN salariu * 0.15;
        END IF;
    END calcul_impozit;

    PROCEDURE concediez_ultim IS
        v_angj_id angajati.employee_id%TYPE;
        PROCEDURE afisare_confirmare
        (
            ang_id IN angajati.employee_id%TYPE
        )
        IS
        BEGIN
            dbms_output.put_line('S-a sters angajatul cu ID ' || ang_id );
            dbms_output.put_line('Ii uram succes in gasirea unui nou JOB. :(');
        END afisare_confirmare;
    BEGIN
        SELECT MAX(employee_id) INTO v_angj_id FROM angajati;
        DELETE FROM angajati WHERE employee_id = v_angj_id;
        afisare_confirmare(v_angj_id);
    END concediez_ultim;

END salarizare;


BEGIN
salarizare.concediez_ultim;
END;