--Creati un bloc prin care sa se adauge stoc in produse cu stoc>0
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE produse ADD stoc2 NUMBER CHECK (stoc2 > 0)';
END;

--Creati un bloc prin care sa se adauge valori si nr de modificari
SET SERVEROUTPUT ON;
BEGIN
UPDATE produse SET stoc2 = (CASE WHEN id_produs IN (SELECT DISTINCT id_produs FROM rand_comenzi) THEN 20 ELSE 30 END);
DBMS_OUTPUT.PUT_LINE('S-au facut '||SQL%ROWCOUNT||' modificari');
END;

--Sa se creeze un trigger care sa nu permita comandarea unui produs in cantitate mai mare decat stocul aferent
-- IF :NEW.cantitate > 0 nu este obligatoriu, merge direct cu SELECT stoc2...
CREATE OR REPLACE TRIGGER verifica_stoc
BEFORE INSERT OR UPDATE ON rand_comenzi
FOR EACH ROW
DECLARE
total NUMBER;
BEGIN
IF :NEW.cantitate > 0 THEN SELECT stoc2 INTO total FROM produse WHERE id_produs = :NEW.id_produs;
IF :NEW.cantitate > total THEN RAISE_APPLICATION_ERROR(-20002, 'Stocul nu este suficient');
ELSE UPDATE produse SET stoc2 = stoc2- :NEW.cantitate WHERE id_produs = :NEW.id_produs;
END IF;
END IF;
END;

INSERT INTO rand_comenzi VALUES (2380,2211,42,21);

INSERT INTO rand_comenzi VALUES (2380,1910,42,19);

--Sa se creese o functie care sa returneze numarul de angajati cu salariul mai mare decat media, care fac parte din departamentul al carui id este dat ca parametru
--Exceptii cazul in care nu exista departamentul precum si pe cel in care nu exista angajati in departament
CREATE OR REPLACE FUNCTION sal_med (id IN departamente.id_departament%TYPE)
RETURN NUMBER IS numar_ang angajati.id_angajat%TYPE;
dep departamente.id_departament%TYPE;
e EXCEPTION;
e1 EXCEPTION;
BEGIN
SELECT COUNT(id_angajat) INTO numar_ang FROM angajati WHERE salariul > (SELECT AVG(salariul) FROM angajati) AND id_departament = id;
SELECT COUNT(id_departament) INTO dep FROM departamente WHERE id_departament = id;
IF numar_ang > 0 THEN RETURN numar_ang;
ELSE RAISE e;
END IF;
IF dep = 0 THEN RAISE e1;
END IF;
EXCEPTION WHEN e1 THEN DBMS_OUTPUT.PUT_LINE('Nu exista departamentul');
RETURN -1;
WHEN e THEN DBMS_OUTPUT.PUT_LINE('Nu exista angajati in departament');
RETURN -2;
END;

BEGIN
DBMS_OUTPUT.PUT_LINE(sal_med(10));
END;

BEGIN
DBMS_OUTPUT.PUT_LINE(sal_med(130));
END;

BEGIN
DBMS_OUTPUT.PUT_LINE(sal_med(500));
END;

--Creati o procedura care sa afiseze informatii despre primele 3 departamente care au cei mai multi angajati cu salariul mai mare decat media