set serveroutput on
create or replace procedure proc1
(p_id in number)
is
cursor c
is
select c.nr_comanda, c.data
from rand_comenzi rc, comenzi c
where rc.id_produs=p_id
and rc.nr_comanda = c.nr_comanda
order by c.data desc;
begin
for i in c loop
dbms_output.put_line(i.nr_comanda||' '|| i.data);
exit when c%ROWCOUNT > 4;
end loop;
end;
/

begin
proc1(3129);
end;
/

set serveroutput on
create or replace trigger triger1
before insert or update of pret on rand_comenzi
for each row
declare
p_id rand_comenzi.id_produs%type;
p_min produse.pret_min%type;
p_max produse.pret_lista%type;
begin
select p.id_produs, p.pret_min, p.pret_lista into p_id, p_min, p_max
from produse p
where p.id_produs=:new.id_produs;
if inserting or updating
and :new.pret < p_min
or :new.pret > p_max
then
raise_application_error(-20202, 'Wrong price');
end if;
end;
/

insert into rand_comenzi values(56,3255,37,30);
update rand_comenzi set pret = 37 where nr_comanda = 2392;





set serveroutput on
create or replace function function1(order_id rand_comenzi.nr_comanda%type)
return number is p_number number;
begin
select sum(rc.pret*rc.cantitate) total into p_number
from rand_comenzi rc
where nr_comanda = order_id;
return p_number;
end;
/

set serveroutput on
declare
p_nr number(5);
begin
p_nr := function1(2380);
dbms_output.put_line('Total: '||p_nr);
end;
/


set serveroutput on
create or replace trigger trigger2
BEFORE insert on comenzi
for each row
declare
v_data angajati.data_angajare%type;
v_nr number;
v_job angajati.id_functie%type;
begin
select id_functie into v_job
from angajati
where id_angajat = :new.id_angajat;
select data_angajare into v_data
from angajati
where id_angajat = :new.id_angajat;
select (sysdate - v_data) into v_nr
from angajati
where id_angajat = :new.id_angajat;
if inserting and v_job <> 'SA_REP' or v_nr<30 then
raise_application_error(-20202, 'NU');
end if;
end;
/