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 67 68 69 70 71 72 73 74
| CREATE OR REPLACE
PACKAGE P_ACHAT AS
TYPE achNUPLET IS RECORD(
NUM_ACHAT ACHATS.NUM_ACHAT%TYPE,
DATE_ACHAT ACHAT.DATE_ACHAT%TYPE,
REFERENCE_BL_DISTRIBUTEUR ACHATS.REFERENCE_BL_DISTRIBUTEUR%TYPE,
ID_LIVRE ligne_achat.id_livre%TYPE,
QUANTITE_ACHETEE ligne_achat.quantite_achetee%type );
CURSOR les_achats return achNUPLET;
procedure afficher;
procedure ajout_Achat(NUM_ACHAT integer,DATE_ACHAT DATE, REFERENCE_BL_DISTRIBUTEUR varchar2,ID_LIVRE INTEGER, QUANTITE_ACHETEE integer);
procedure supprimer_achat(num integer );
procedure modifier_achat(NUM_ACHAT integer,ID_LIVRE INTEGER, QUANTITE_ACHETEE INTEGER);
procedure modifier_Lachat(NUM_ACHAT integer,DATE_ACHAT DATE, REFERENCE_BL_DISTRIBUTEUR varchar2);
END ;
--shows errors
create or replace PACKAGE BODY P_ACHAT AS
CURSOR les_achats return achNUPLET is SELECT * FROM LIGNE_ACHAT li INNER JOIN ACHATS ach ON ach.NUM_ACHAT= li.NUM_ACHAT
INNER JOIN LIVRES L ON L.ID_LIVRE= li.ID_livre;
procedure afficher IS
D achNUPLET;
BEGIN
FOR D IN les_achats LOOP
DBMS_OUTPUT.PUT_LINE(D.NUM_ACHAT||' '||D.DATE_ACHAT||' '||D.REFERENCE_BL_DISTRIBUTEUR||' '||D.TITRE_LIVRE);
END LOOP;
END;
procedure ajout_achat(num integer,Da Date,ref_BL_dist varchar2,id_livre INTEGER, quantite integer) IS
BEGIN
insert into achats VALUES (num,da,ref_BL_dist);
insert into ligne_achat Values (num, id_livre, quantite);
--da=sysdate
dbms_output.put_line('achat ajouter !');
END;
--supprimer un achat- supprimer une ligne d'achat
procedure supprimer_achat(num integer ) IS
BEGIN
delete from achats where num_achat=num;
delete from ligne_achat where num_achat=num;
END;
procedure modifier_achat(NUM_ACHAT integer,DATE_ACHAT DATE, REFERENCE_BL_DISTRIBUTEUR varchar2)IS
BEGIN
--execution begin update_distributeur_adresse(7,'charpenne','Lyon',69123,'RHONE' ); end;
Update Achats set
NUM_ACHAT = num,
DATE_ACHAT = DATE_ACHAT,--sysdate
reference_bl_distributeur = REFERENCE_BL_DISTRIBUTEUR
WHERE NUM_ACHAT= NUM_ACHAT;
END;
procedure modifier_Lachat(NUM_ACHAT integer,ID_LIVRE INTEGER, QUANTITE_ACHETEE INTEGER) IS
BEGIN
Update ligne_achat set
NUM_ACHAT = NUM_ACHAT,
ID_LIVRE = ID_LIVRE,
QUANTITE_ACHETEE = QUANTITE_ACHETEE
WHERE NUM_ACHAT= NUM_ACHAT;
END;
END ;--fin package
--SHOW ERRORS; |
Partager