SET SERVEROUTPUT ON

--1
CREATE OR REPLACE PROCEDURE job_nou (id_job IN functii.id_functie%TYPE,
titlu_job IN functii.denumire_functie%TYPE,
sal_min IN functii.salariu_min%TYPE) IS
sal_max functii.salariu_max%TYPE := 2 * sal_min;
BEGIN
INSERT INTO functii VALUES(id_job,titlu_job,sal_min,sal_max);
DBMS_OUTPUT.PUT_LINE('S-a introdus jobul cu id-ul '||id_job);
END;

BEGIN
job_nou('SY_ANAL','System Analyst',6000);
END;

SELECT * FROM functii WHERE id_functie = 'SY_ANAL';
/
--2
CREATE OR REPLACE PROCEDURE istoric_job (id_ang IN istoric_functii.id_angajat%TYPE,
id_job IN istoric_functii.id_functie%TYPE) IS
id angajati.id_angajat%TYPE;
data_start angajati.data_angajare%TYPE;
BEGIN
INSERT INTO istoric_functii
SELECT id_angajat,data_angajare,SYSDATE,id_functie,id_departament
FROM angajati WHERE id_angajat = id_ang;
UPDATE angajati SET
data_angajare = SYSDATE, id_functie = id_job, salariul=(SELECT salariu_min FROM functii WHERE id_functie = id_job)
WHERE id_angajat = id_ang;
END;
/
BEGIN
istoric_job(106,'SY_ANAL');
END;
/
SELECT * FROM angajati WHERE id_angajat = 106;
SELECT * FROM istoric_functii WHERE id_angajat = 106;

ROLLBACK;

--3
CREATE OR REPLACE PROCEDURE modif_sal (id_job IN functii.id_functie%TYPE,
sal_min IN functii.salariu_min%TYPE,
sal_max IN functii.salariu_max%TYPE) IS
e EXCEPTION;
sal EXCEPTION;
BEGIN
IF sal_max < sal_min
THEN RAISE sal;
END IF;
UPDATE functii SET salariu_min = sal_min, salariu_max = sal_max
WHERE id_functie = id_job;
IF SQL%ROWCOUNT = 0 THEN
RAISE e;
END IF;

EXCEPTION
WHEN sal THEN DBMS_OUTPUT.PUT_LINE('Salariul maxim dat este mai mic decat cel minim dat');
WHEN e THEN DBMS_OUTPUT.PUT_LINE('Nu exista functia cu id-ul dat');
END;

BEGIN
modif_sal('SY_ANAL',7000,140);
END;

BEGIN
modif_sal('SY_ANAL',7000,14000);
END;

SELECT * FROM functii WHERE id_functie = 'SY_ANAL';