--1
SET SERVEROUTPUT ON
DECLARE
id angajati.id_departament%TYPE := &id;
BEGIN
UPDATE angajati SET salariul = salariul + 100 WHERE id_departament = id;
END;

--2
CREATE OR REPLACE FUNCTION doi (id IN angajati.id_angajat%TYPE)
RETURN NUMBER IS valoare rand_comenzi.pret%TYPE;
BEGIN
SELECT MAX(SUM(pret*cantitate)) INTO valoare FROM comenzi INNER JOIN rand_comenzi USING(nr_comanda) WHERE id_angajat = id GROUP BY nr_comanda;
RETURN valoare;
END;

DECLARE
id angajati.id_angajat%TYPE := &id;
BEGIN
DBMS_OUTPUT.PUT_LINE(doi(id));
END;

--3
CREATE OR REPLACE PROCEDURE trei
IS
CURSOR c IS SELECT id_angajat,doi(id_angajat) AS val FROM comenzi ORDER BY val DESC;
BEGIN
FOR b IN c LOOP
DBMS_OUTPUT.PUT_LINE(b.id_angajat||' '||b.val);
EXIT WHEN c%ROWCOUNT=3;
END LOOP;
END;

BEGIN
trei;
END;

--4
BEGIN
EXECUTE IMMEDIATE 'DROP FUNCTION doi';
EXECUTE IMMEDIATE 'DROP PRODCEDURE trei';
END;