Untitled

------- Q3 -----------
declare

cursor c3 is
select refa ,sum(QteCD)as SC
from LIGNE_COMMANDE l ,article a,commande c
where l.refa=a.refa 
and c.nc = l.nc
and categorie='informatique'
and  to_char(DATECOM,'yyyy')='2022';
group by refa;


begin 

delete from ARTICLE where refa not in (select refa from  LIGNE_COMMANDE) and CATEGORIE='informatique'; 



for wc3 in c3 loop
if wc3.SC > 100000 then
update ARTICLE set  QTEMIN=QTEMIN*1.2 where refa=wc3.refa;
else 
update ARTICLE set  QTEMIN=QTEMIN*1.1 where refa=wc3.refa;
end if ;
end loop;

Exception
when no_dat_found then
    dbmus_outpu.put_line('');

end;

------------Q4---------------
DECLARE
    CURSOR c4 IS
    SELECT
        refa,
        nf,
        nc,
        qtecd
    FROM
        ligne_commande l,
        tarif          t
    WHERE
        qtecd > 500
        AND pu > 2000;

    vnf tarif.nf%TYPE;
BEGIN
    FOR wc4 IN c4 LOOP
        SELECT
            nf
        INTO vnf
        FROM
            tarif
        WHERE
            refa = wc4.refa
            AND pu = (
                SELECT
                    MIN(pu)
                FROM
                    tarif
                WHERE
                    refa = wc4.refa
            );

        IF vnf != wc4.nf THEN
            UPDATE ligne_commande
            SET
                qtecd = qtecd * 0.5
            WHERE
                nc = wc4.nc;

            INSERT INTO commande VALUES (
                nc + 1,
                sysdate,
                vnf
            );
             INSERT INTO ligne_commande VALUES (
                nc + 1,
                wc4.refa,
                wc4.QTECD*0.5
            );
            

        END IF;

    END LOOP;

END;

-------------Q5-----------------
DECLARE

cursor c5 is 
select NF , REFA 
from TARIF t , FOURNISSEUR f
where t.nf=f.nf and Ville='Tunis';

cursor c6(refe tarif.refa%type) is 
select pu 
from TARIF t , FOURNISSEUR f
where t.nf=f.nf and Ville='Sfax'
    and refa=refe
    and pu=(select min(pu)
        from tarif t , FOURNISSEUR f 
        where t.nf=f.nf  and Ville='Sfax' and refa=refe);


BEGIN

for wc5 in c5 loop

    for wc6 in c6(wc5.refa) loop
        update Tarif set PU=wc6.pu*0.9 where nf=wc5.nf and refa=wc5.refa;

    end loop;
end loop;


END;