--ex1
drop table resultat;
create table resultat (
IDART number(20),
DESART varchar(30),
APU number(30),
NPU number(30)
);
accept idarticle prompt 'Donner lid article : '
declare
CURSOR arts is select idart, desart, pu from article where qtestk <= qtemin;
    ida number;
    da number;
    qa number;
    pa number;
FUNCTION cal_approv(code_art article.idart%type)
    RETURN number IS qte_app number;
        qtot number;
        qtestk number;
        qtemin number;
    begin
        select sum(QTECOM) into qtot from lig_com where idart=code_art;
        select qtestk into qtestk from article where idart=code_art;
        select qtemin into qtemin from article where idart=code_art;
        if (qtot/qtestk)>(2*qtemin) then
            qte_app := (qtot-qtestk)*1.5;
        else
            qte_app := qtot*1.2;
        end if;
        return qte_app;
    end;


begin



    for art in arts loop
        qa :=  cal_approv(art.idart);
        pa := art.pu*cal_approv(art.idart);
        exit when(arts%notfound);
        insert into resultat values

(art.idart,art.desart, qa,pa);
    end loop;
end;
/

select * from resultat;















--ex2