Untitled
unknown
plain_text
2 years ago
2.7 kB
6
Indexable
--------------------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;
Editor is loading...