Bonjour
pouvez vous m'expliquer les 3procédures avec leur requetes suivantes svp?
merci pour la réponse (procedures facturer,procedure declecher commande,procedure traiter commande)
//Procédure Facturer :
create or replace procedure facturer
(
NumeroClient number,
Article number,
quantite number,
prix number
)
is
numfacture number;
begin
select max(numero) into num10:39 24/02/2007facture from facture where payee <> 'O' and num_client = NumeroClient;
if numfacture is null
then
insert into Facture values (Seq_Facture.NEXTVAL,NumeroClient,sysdate,'0','N');
end if;
select max(numero) into numfacture from facture where payee <> 'O' and num_client = NumeroClient;
insert into Ligne_Facture Values (numfacture,Article,prix,quantite);
end;
/
//Procédure Declencher_Commande :
create or replace procedure Declencher_Commande
(
Nombre_a_commander Number,
Indice_rupture number
)
is
ref_art number;
BEGIN
DECLARE cursor curs is Select ref_article from stock group by ref_article having sum(quantite) < Indice_rupture;
BEGIN
Open curs;
LOOP
fetch curs into ref_art;
exit when curs%notfound;
insert into commande
SELECT seq_commande.nextval,ref_art,F.REFERENCE, sysdate,P.PRIX,Nombre_a_commander
from Propose P, Fournisseur F,minprix M
where F.REFERENCE=P.REF_FOURNISSEUR
and M.REFERENCE=ref_art
and M.prixmin=P.PRIX
and P.ref_article=ref_art;
END LOOP;
END;
END;
// procédures traiter commande
CREATE VIEW SOMME_ENS AS
SELECT REF_ENTREPOT , SUM (Quantite ) as SOMME
FROM STOCK
GROUP BY REF_ENTREPOT;
CREATE OR REPLACE PROCEDURE Traiter_Commande (num NUMBER)
is
ref NUMBER;
BEGIN
SELECT min(REF_ENTREPOT) INTO REF FROM SOMME_ENS
WHERE SOMME= (SELECT min (SOMME) from SOMME_ENS)
;
INSERT INTO STOCK select ref_article, ref, prix, quantite from commande where reference = num ;
DELETE FROM COMMANDE WHERE REFERENCE = num;
END
;