Untitled

--------------------Q1-------------------

create or replace function nb_visite(vNpat Patient.npat%type)
return number
is

    nb number;

begin

    select count(Npat)into nb
    from ORDONNANCE 
    where Npat=vnpat;

    return nb;

end;


--------------------Q2-------------------

create or replace procedure maj_nature
is

    cursor c1 is 
    select Npat
    from Patient;
    
    nb number;

begin


    for wc1 in c1 loop
    
        nb:=nb_visite(wc1.Npat);
        if nb>10 then
        update Patient set Nature='Fidele' where Npat=wc1wc1.Npat;
    
    end loop;

end;


--------------------Q3-------------------

create or replace function NB_TYPE(vType MEDICAMENT.TypeM%type)
return number
is
    
    nb number;
    
Begin

    select sum(QteAdministrée) into nb 
    from MEDICAMENT m ,ord_MEDICAMENT o
    where m.CodMed=o.CodMed and TypeM=vType;
    
    return nb;

end;

--------------------Q4-------------------

declare

cursor c2 is 
select distinct TypeM
from Medicament;

total ORD_MEDICAMENT.QteAdministrée%type;
typeMax MEDICAMENT.TypeM%type;
maxTaux number:=0;
taux number;

begin

    select sum(QteAdministrée) into total
    from ORD_MEDICAMENT;

for wc2 in c2 loop

    taux:=NB_TYPE(wc2.TypeM)/total;
    if taux>maxTaux then
        typeMax:=wc2.TypeM;
    end if;
    dbms_output.put_line(wc2.TypeM);
    dbms_output.put_line(taux);

end loop;

dbms_output.put_line(typeMax);

end;


--------------------Q5-------------------

create or replace trigger MAJ_QTE 
before
insert or update of QteAdministrée on ORD_MEDICAMENT
for each row

qmax MEDICAMENT.QteMax%type;
begin

select QteMax into qmax
from MEDICAMENT
where CodMed=:new.CodeMed;

if :new.QteAdministrée>qmax then
    
    

end;



--------------------Q6-------------------

alter table PATIENT
add NbCons number default 0;

create or replace trigger trig_con
after
insert on ORDONNANCE
for each row

begin

update PATIENT set NbCons=NbCons+1 where Npat=:new.npat;

end;


--------------------Q7-------------------

alter table MEDICAMENT
add tauxCons number default 0;


create or replace trigger trig_7
after
insert or delete on ORD_MEDICAMENT
for each row

total ORD_MEDICAMENT.QteAdministrée%type;

begin

 select sum(QteAdministrée) into total
    from ORD_MEDICAMENT;
if inserting then   
update MEDICAMENT set tauxCons = (tauxCons + :new.QteAdministrée)/total;
end if;

if deleting then   
update MEDICAMENT set tauxCons = (tauxCons - :old.QteAdministrée)/total;
end if;

end;