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
|
SET SERVEROUTPUT ON
EXECUTE dbms_output.enable(1000000);
CREATE OR REPLACE PROCEDURE StatsVins (NumVin Vins.NV%type) AS
cursor Conso_Par_Ville_CUR is
SELECT Achats.Lieu As Ville, SUM(Achats.QTE) AS ConsoTotale
FROM Achats
WHERE Achats.NV = NumVin
GROUP BY Lieu
ORDER BY ConsoTotale;
cursor Conso_Ville_Annee_CUR is
SELECT SUM(Achats.QTE) AS ConsoParAnnee, Extract(Year FROM DAT) As Annee
FROM Achats
WHERE Achats.NV=NumVin
AND Achats.Lieu = Conso_Par_Ville_CUR.Lieu
GROUP BY Achats.Lieu, Extract(Year FROM DAT);
end loop;
BEGIN
--open Conso_Par_Ville_CUR;
dbms_output.new_line;
dbms_output.put_line('Vente de vins numero ' || NumVin);
for Conso_Par_Ville_Ligne in Conso_Par_Ville_CUR loop
dbms_output.new_line;
dbms_output.put_line(' Par ville : ' || Conso_Par_Ville_Ligne.Ville || ' (' || Conso_Par_Ville_Ligne.ConsoTotale || 'b)');
--open Conso_Ville_Annee_CUR;
for Conso_Ville_Annee_CUR_Ligne in Conso_Ville_Annee_CUR loop
dbms_output.new_line;
dbms_output.put_line(' Par annee : ' ||Conso_Ville_Annee_CUR_Ligne.Annee || ' :' || Conso_Ville_Annee_CUR_Ligne.ConsoParAnnee );
end loop;
--close Conso_Ville_Annee_CUR;
end loop;
--close Conso_Par_Ville_CUR;
END;
/
execute StatsVins(12); |
Partager