1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
| create or replace procedure maProcedure(pNumClient in client.cli_num%type) as
--curseurs
cursor cuRepVeh is
select rep_num, veh_marque, veh_modele
from reparation, vehicule
where reparation.rep_veh_num_imm = vehicule.veh_num_imm
and vehicule.veh_cli_num = pNumClient ;
cursor cuFournisseur(pRepNum in reparation.rep_num%type) is
select Fou_nom
from fournisseur, client, vehicule, reparation, est_utilise, piece, fourni
where pRepNum = reparation.rep_num
and reparation.rep_veh_num_imm = vehicule.veh_num_imm
and client.cli_num = vehicule.veh_cli_num
and fournisseur.fou_num = fourni.for_fou_num
and piece.pie_num = fourni.for_pie_num
and piece.pie_num = est_utilise.est_pie_num
and reparation.rep_num = est_utilise.est_rep_num
order by fou_nom;
cursor cuPiece(pFournisseur in fournisseur.fou_num%type) is
select Pie_design, pie_prix_unit
from piece, fournisseur, fourni
where pFournisseur = fournisseur.fou_num
and piece.pie_num = fourni.for_pie_num
and fournisseur.fou_num = fourni.for_fou_num
order by piece.pie_prix_unit;
--Variables
vCli_Nom client.cli_nom%type;
vCli_prenom client.cli_prenom%type;
vCli_Num_Tel client.cli_num_tel%type;
vAss_Nom assurance.ass_nom%type;
--exceptions
eAucunePiece exception;
eAucunFournisseur exception;
--ajouter une autre exception
begin
select cli_nom, cli_prenom, cli_num_tel, ass_nom
into vCli_Nom, vCli_prenom, vCli_Num_Tel, vAss_Nom
from client, assurance
where client.cli_num = pNumClient
and client.cli_ass_code = assurance.ass_code;
dbms_output.put_line('Prenom Nom '||vCli_nom||' '||vCli_prenom);
dbms_output.put_line('Num Téléphone '||vCli_Num_Tel);
dbms_output.put_line('Assurance '||vAss_Nom);
for var in cuRepVeh loop
dbms_output.put_line('Num réparation '||var.Rep_Num);
dbms_output.put_line('Véhicule '||var.Veh_Marque||' '||var.Veh_Modele);
dbms_output.new_line;
for ver in cuFournisseur(var.rep_num) loop
dbms_output.put_line(ver.fou_nom);
for vir in cuPiece(ver.fou_num) loop
dbms_output.put_line(vir.Pie_design||' '||vir.pie_prix_unit);
end loop;
end loop;
END LOOP;
END maProcedure; |
Partager