Tp Plsql N2 (correction)

Module : Administration et Sécurité des Bases de Données Correction Travaux Pratiques N°2 /*Exe1*/

declare cursor C1 is select nom, adresse from pilote where sal>22000; v_nom pilote.nom%type; v_adresse pilote.adresse%type; begin open C1; loop fetch C1 into v_nom, v_adresse; exit when C1%notfound; dbms_output.put_line('Adresse du pilote '||v_nom||' est: '||v_adresse); end loop; close C1; end; /

/*Exe2*/

declare cursor c2 is select * from avion where nbhvol > (select avg(nbhvol) + 25 from avion); v_avion avion%rowtype; begin open c2; loop fetch c2 into v_avion; exit when c2%notfound; dbms_output.put_line(' avion: '||v_avion.nuavion||', type: '|| v_avion.type|| ', année de mise en service: '|| v_avion.annserv||', Nom avion: '||v_avion.nom||', Nombre heure de vol: '||v_avion.nbhvol); end loop; close c2; end; /

/*Exe3*/ /* Plusieurs solutions ont été proposé, parmi les quelles nous allons mentionner trois méthodes*/

/*1ière méthode */

declare cursor c3 is select nopilot from pilote p where

1= (select Count(vol) from affectation where affectation.pilote =

p.nopilot); type Tab_pil is table of pilote.nopilot%type index by binary_integer; T1 Tab_pil; type Tab_com is table of pilote.comm%type index by binary_integer;

T2 Tab_com; T3 Tab_com; i binary_integer:=1; j binary_integer:=1; p binary_integer:=1; v_pilote pilote.nopilot%type; begin dbms_output.put_line(‘La liste des pilotes:’); open c3; Loop fetch c3 into t1(i); exit when c3%notfound; dbms_output.put_line(i||’- ‘||t1(i)); i:=i+1; end loop; close c3; dbms_output.put_line(‘La liste des commissions avant la réduction:’); open c3; Loop fetch c3 into v_pilote; exit when c3%notfound; select comm into T2(j) from pilote where nopilot = v_pilote; dbms_output.put_line(j||’- ‘||T2(j)); j:=j+1; end loop; close c3; dbms_output.put_line(‘La liste des commissions après la réduction:’); open c3; Loop fetch c3 into v_pilote; exit when c3%notfound; select comm-(comm*0.05) into T3(p) from pilote where nopilot = v_pilote; dbms_output.put_line(p||’- ‘||T3(p)); p:=p+1; end loop; close c3; end; /

/*2ième méthode */

declare cursor c3 is select pilote from affectation group by pilote having count(*)=1; cursor c4 is select comm from pilote,affectation where pilote=nopilot group by pilote,comm having count(*)=1; cursor c5 is select (comm-(comm*0.05)) from pilote,affectation where pilote=nopilot group by pilote,comm having count(*)=1;

type T1 is table of pilote.nopilot%type index by binary_integer; type T2 is table of pilote.comm%type index by binary_integer; t_1 T1; t_2 T2; t_3 T2; i binary_integer:=1; j binary_integer:=1; k binary_integer:=1; begin open c3; dbms_output.put_line('La liste de pilote :'); loop fetch c3 into t_1(i); exit when c3%notfound; dbms_output.put_line(i||'-'||t_1(i)); i:=i+1; end loop; close c3; open c4; dbms_output.put_line('La liste des commissions avant la réduction:'); loop fetch c4 into t_2(j); exit when c4%notfound; dbms_output.put_line(j||'-'||t_2(j)); j:=j+1; end loop; close c4; open c5; dbms_output.put_line('La liste des commision aprés la reduction:'); loop fetch c5 into t_3(k); exit when c5%notfound; dbms_output.put_line(k||'-'||t_3(k)); k:=k+1; end loop; close c5; end; /

/*3ièmme méthode */

declare Cursor c3 is select nopilot,comm from pilote where 1=(select count(*) from affectation where pilote.nopilot=affectation.pilote group by pilote); r c3%rowtype; type tab_pilote is table of pilote.nopilot%type index by binary_integer; type tab_comm is table of pilote.comm%type index by binary_integer; t1 tab_pilote; t2 tab_comm; t3 tab_comm; i integer; j integer; begin open c3; i:=1;

loop fetch c3 into r; exit when c3%NOTFOUND; t1(i):=r.nopilot; t2(i):=r.comm; t3(i):=r.comm*0.95; i:=i+1; end loop; close c3; j:=1; dbms_output.put_line('La liste des pilotes : '); loop dbms_output.put_line(j||'-'||t1(j)); j:=j+1; exit when j=i; end loop; j:=1; dbms_output.put_line('La liste des commisions avant la reduction :'); loop dbms_output.put_line(j||'-'||t2(j)); j:=j+1; exit when j=i; end loop; j:=1; dbms_output.put_line('La liste des commisions apres la reduction :'); loop dbms_output.put_line(j||'-'||t3(j)); j:=j+1; exit when j=i; end loop; end; /

/*Exe4*/

declare type pilote_id is table of pilote.nopilot%type index by binary_integer; type pilote_comm is table of pilote.comm%type index by binary_integer; T1 pilote_id; T2 pilote_comm; T3 pilote_comm; i binary_integer:=1; j binary_integer; cursor c3 is select nopilot,comm from pilote p where 1=(select count(*) from affectation where pilote=p.nopilot group by pilote); v_pilote c3%rowtype; begin open c3; loop fetch c3 into v_pilote; exit when c3% notfound ; T1(i):=v_pilote.nopilot; T2(i):=v_pilote.comm; T3(i):=0.95*T2(i); i:=i+1; end loop;

close c3; dbms_output.put_line('la liste des pilote:'); for j in 1..i-1 loop dbms_output.put_line(j||'- '||T1(j)); end loop; dbms_output.put_line('la liste des commissions avant la réduction est:'); for j in 1..i-1 loop dbms_output.put_line(j||'-'||T2(j)); end loop; dbms_output.put_line('la liste des comm apres red:'); for j in 1..i-1 loop dbms_output.put_line(j||'-'||T3(j)); end loop; end; /

/*Exe5*/

/* 1ière méthode: Curseur non paramétré*/ declare cursor C4 is select nom, embauche from pilote where sal>1500 and comm < sal*0.20 and comm is not null; v_nom pilote.nom%type ; v_embauche pilote.embauche%type ; begin dbms_output.put_line(‘Noms : Date Embauche: ’); open C4 ; loop fetch C4 into v_nom, v_embauche; exit when c4%notfound; dbms_output.put_line(v_nom||’ ‘||v_embauche); end loop; close C4; end; / /* 2ième méthode: Curseur paramétré*/ declare cursor C4(v_sal pilote.sal%type,v_comm pilote.comm%type)is select nom, embauche from pilote where sal>v_sal and comm < v_comm*v_sal; v_nom pilote.nom%type ; v_embauche pilote.embauche%type; begin dbms_output.put_line(‘Noms : Date Embauche: ’); open C4(1500,0.2) ; loop fetch C4 into v_nom, v_embauche; exit when c4%notfound; dbms_output.put_line(v_nom||’ ‘||v_embauche); end loop; close C4; end;

/

/*Exception*/ /*Exe6*/

/* l’’exception when_no_data_found */ drop table erreur_app; create table erreur_app(x varchar2(50), y varchar2(50)); / declare v_nom pilote.nom%type; v_adresse pilote.adresse%type; v_comm pilote.comm%type ; v_sal pilote.sal%type; excep_sup EXCEPTION; excep_null EXCEPTION; begin select nom, adresse, sal, comm into v_nom, v_adresse, v_sal, v_comm from pilote where nopilot='1700'; dbms_output.put_line('Adresse du pilote '||v_nom||' est: '|| v_adresse||'sal: '||v_sal||'comm: '||v_comm); if v_sal < v_comm then raise excep_sup; elsif v_comm is null then raise excep_null; end if; EXCEPTION When no_data_found Then insert into erreur_app values('Aucune donnée trouvée',null); when excep_sup then insert into erreur_app values(v_nom, 'sal
/*Exe7*/

drop table pilote_sal; create table pilote_sal(nompilote varchar2(30), salaire number(8,2)); / declare cursor C4 is select nom, comm from pilote where sal>15900; v_nom pilote.nom%type ; v_sal pilote.sal%type ; v_comm pilote.comm%type ; excep_trait EXCEPTION; comm_nulle EXCEPTION; begin open C4 ; loop fetch C4 into v_nom,v_comm; exit when c4%notfound;

dbms_output.put_line('Nom du pilote: '||v_nom); if v_comm is null then RAISE comm_nulle; end if; select sal into v_sal from pilote where nom = v_nom; if v_sal>30000 then RAISE excep_trait; End if; end loop; close C4; EXCEPTION When comm_nulle then dbms_output.put_line('la Commission du pilote '|| v_nom||' est nulle'); when excep_trait then insert into pilote_sal values(v_nom,v_sal); end; / select * from pilote_sal; /