ciurca/ex1.plsql

-- Exercitiul 2 (Numele, prenumele si salariul reprezentantilor de vanzari)
DECLARE
    CURSOR c_reprezentanti IS
    SELECT first_name, last_name, salary FROM angajati
    WHERE job_id='SA_REP';
    v_first_name angajati.first_name%TYPE;
    v_last_name angajati.last_name%TYPE;
    v_salary angajati.salary%TYPE;
    v_salariu_mediu angajati.salary%TYPE;
BEGIN
    OPEN c_reprezentanti;
    LOOP
        FETCH c_reprezentanti INTO v_first_name, v_last_name, V_salary;
            EXIT WHEN c_reprezentanti%notfound;
            DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ' ' || v_salary);
    END LOOP;
    CLOSE c_reprezentanti;
    SELECT AVG(salary) INTO v_salariu_mediu FROM ANGAJATI WHERE job_id='SA_REP';
    DBMS_OUTPUT.PUT_LINE('Salariu mediu: ' || v_salariu_mediu);
END;

-- Exercitiul 3 (Atributele cursorului)
DECLARE
    CURSOR c_reprezentanti IS
    SELECT first_name, last_name, salary FROM angajati
    WHERE job_id='SA_REP';
    v_first_name angajati.first_name%TYPE;
    v_last_name angajati.last_name%TYPE;
    v_salary angajati.salary%TYPE;
    v_salariu_mediu angajati.salary%TYPE;
BEGIN
    OPEN c_reprezentanti;
    IF (c_reprezentanti%ISOPEN) THEN
    dbms_output.put_line('Cursorul este deschis');
    END IF;
    LOOP
        FETCH c_reprezentanti INTO v_first_name, v_last_name, V_salary;
            EXIT WHEN c_reprezentanti%notfound;
            IF (c_reprezentanti%FOUND) THEN
            DBMS_OUTPUT.PUT_LINE(c_reprezentanti%ROWCOUNT || '.' || v_first_name || ' ' || v_last_name || ' ' || v_salary);
            END IF;
    END LOOP;
    CLOSE c_reprezentanti;
    SELECT AVG(salary) INTO v_salariu_mediu FROM ANGAJATI WHERE job_id='SA_REP';
    DBMS_OUTPUT.PUT_LINE('Salariu mediu: ' || v_salariu_mediu);
END;

-- Exercitiul 4 (folosind record)
DECLARE
CURSOR c_reprezentanti IS
SELECT first_name, last_name, salary FROM angajati
WHERE job_id='SA_REP';
v_salariu_mediu angajati.salary%TYPE;
r_reprezentanti c_reprezentanti%ROWTYPE;
BEGIN
OPEN c_reprezentanti;
IF (c_reprezentanti%ISOPEN) THEN
dbms_output.put_line('Cursorul este deschis');
END IF;
LOOP
FETCH c_reprezentanti INTO r_reprezentanti;
EXIT WHEN c_reprezentanti%notfound;
IF (c_reprezentanti%FOUND) THEN
DBMS_OUTPUT.PUT_LINE(c_reprezentanti%ROWCOUNT || '.' || r_reprezentanti.first_name || ' ' || r_reprezentanti.last_name || ' ' || r_reprezentanti.salary);
END IF;
END LOOP;
CLOSE c_reprezentanti;
SELECT AVG(salary) INTO v_salariu_mediu FROM ANGAJATI WHERE job_id='SA_REP';
DBMS_OUTPUT.PUT_LINE('Salariu mediu: ' || v_salariu_mediu);
END;

-- Exercitiul 5 (FOR Loop)
DECLARE
    CURSOR c_reprezentanti IS
    SELECT first_name, last_name, salary FROM angajati
    WHERE job_id='SA_REP';
    v_salariu_mediu angajati.salary%TYPE;
BEGIN
    FOR r_reprezentant IN c_reprezentanti
    LOOP
    DBMS_OUTPUT.PUT_LINE(c_reprezentanti%ROWCOUNT || '.' || r_reprezentant.first_name || ' ' || r_reprezentant.last_name || ' ' || r_reprezentant.salary);
    END LOOP;
    SELECT AVG(salary) INTO v_salariu_mediu FROM ANGAJATI WHERE job_id='SA_REP';
    DBMS_OUTPUT.PUT_LINE('Salariu mediu: ' || v_salariu_mediu);
END;

-- Exercitiul 6 (subquery)
DECLARE
    CURSOR c_reprezentanti IS
    SELECT first_name, last_name, salary, 
    (SELECT COUNT (salary)
          FROM angajati
         WHERE job_id = 'SA_REP')
           AS nr_reprezentanti
    FROM angajati
    WHERE job_id='SA_REP';
    v_salariu_mediu angajati.salary%TYPE;
BEGIN
    FOR r_reprezentant IN c_reprezentanti
    LOOP
    DBMS_OUTPUT.PUT_LINE(c_reprezentanti%ROWCOUNT || '.' || r_reprezentant.first_name || ' ' || r_reprezentant.last_name || ' ' || r_reprezentant.salary);
    DBMS_OUTPUT.PUT_LINE('Nr reprezentati ' || r_reprezentant.nr_reprezentanti);
    END LOOP;
    SELECT AVG(salary) INTO v_salariu_mediu FROM ANGAJATI WHERE job_id='SA_REP';
    DBMS_OUTPUT.PUT_LINE('Salariu mediu: ' || v_salariu_mediu);
END;

-- Exercitiul 7  (cursor cu parametru)
DECLARE
    CURSOR c_reprezentanti (id_job VARCHAR2) IS
    SELECT first_name, last_name, salary FROM angajati
    WHERE job_id=id_job;
    v_first_name angajati.first_name%TYPE;
    v_last_name angajati.last_name%TYPE;
    v_salary angajati.salary%TYPE;
    v_salariu_mediu angajati.salary%TYPE;
BEGIN
    OPEN c_reprezentanti('SA_REP');
    LOOP
        FETCH c_reprezentanti INTO v_first_name, v_last_name, V_salary;
            EXIT WHEN c_reprezentanti%notfound;
            DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ' ' || v_salary);
    END LOOP;
    CLOSE c_reprezentanti;
    SELECT AVG(salary) INTO v_salariu_mediu FROM ANGAJATI WHERE job_id='SA_REP';
    DBMS_OUTPUT.PUT_LINE('Salariu mediu: ' || v_salariu_mediu);
END;

-- Exercitiul 8  (vechime)
DECLARE
    CURSOR c_reprezentanti (id_job VARCHAR2, p_vechime NUMBER) IS
    SELECT first_name, last_name, salary FROM angajati
    WHERE job_id=id_job AND extract(year from hire_date) <= extract(year from SYSDATE) - p_vechime;
    v_first_name angajati.first_name%TYPE;
    v_last_name angajati.last_name%TYPE;
    v_salary angajati.salary%TYPE;
    v_salariu_mediu angajati.salary%TYPE;
BEGIN
    OPEN c_reprezentanti('SA_REP', 7);
    LOOP
        FETCH c_reprezentanti INTO v_first_name, v_last_name, V_salary;
            EXIT WHEN c_reprezentanti%notfound;
            DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ' ' || v_salary);
    END LOOP;
    CLOSE c_reprezentanti;
    SELECT AVG(salary) INTO v_salariu_mediu FROM ANGAJATI WHERE job_id='SA_REP';
    DBMS_OUTPUT.PUT_LINE('Salariu mediu: ' || v_salariu_mediu);
END;

-- Exercitiul 9  (functii)
DECLARE
    CURSOR c_reprezentanti (id_job VARCHAR2) IS
    SELECT first_name, last_name, salary FROM angajati
    WHERE job_id=id_job
    ORDER BY first_name, last_name;
    v_first_name angajati.first_name%TYPE;
    v_last_name angajati.last_name%TYPE;
    v_salary angajati.salary%TYPE;
    v_salariu_mediu angajati.salary%TYPE;
    v_fond_lunar angajati.salary%TYPE:=0;
    PROCEDURE functii_dept(id_dept IN VARCHAR2) IS 
    BEGIN 
        OPEN c_reprezentanti(id_dept);
        LOOP
            FETCH c_reprezentanti INTO v_first_name, v_last_name, V_salary;
                EXIT WHEN c_reprezentanti%notfound;
                DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ' ' || v_salary);
                v_fond_lunar:=v_fond_lunar+v_salary;
        END LOOP;
        CLOSE c_reprezentanti;
        SELECT AVG(salary) INTO v_salariu_mediu FROM ANGAJATI WHERE job_id=id_dept;
        DBMS_OUTPUT.PUT_LINE('-------------');
        DBMS_OUTPUT.PUT_LINE('Salariu mediu pentru ' || id_dept || ': ' || v_salariu_mediu);
        DBMS_OUTPUT.PUT_LINE('Fond salarial pentru ' || id_dept || ': ' || v_fond_lunar);
        v_fond_lunar:=0;
        DBMS_OUTPUT.PUT_LINE('');
    END;   
BEGIN
    functii_dept('SA_REP');
    functii_dept('ST_CLERK');
END;

-- Exercitiul 10 (scadere salariala)
DECLARE
    CURSOR c_reprezentanti IS
    SELECT employee_id, salary FROM angajati
    WHERE job_id='SA_REP' OR job_id='SA_MAN'
    FOR UPDATE WAIT 5;
    r_reprezentanti c_reprezentanti%ROWTYPE;
BEGIN
    OPEN c_reprezentanti;
    LOOP
        FETCH c_reprezentanti INTO r_reprezentanti;
            EXIT WHEN c_reprezentanti%notfound;
            UPDATE angajati
            SET salary = r_reprezentanti.salary - (r_reprezentanti.salary*5/100)
            WHERE employee_id = r_reprezentanti.employee_id;
    END LOOP;
    COMMIT;
    dbms_output.put_line('Nr. de personal a caror salarii au fost scazute cu 5%: ' || c_reprezentanti%ROWCOUNT);
    CLOSE c_reprezentanti;
END;