Procédure SQL avec paramètre entrée et paramètre sortie

Version imprimable

Bonjour,

Je suis en train d'écrire une procédure qui doit retourner le montant (paramètre de sortie) de la dernière commande à un fournisseur donné (paramètre en entrée).

J'ai une table FOURNIS(PK_NUMFOU NUMBER (numéro fournisseur), NOMFOU VARCHAR2 (nom fournisseur)), une table COMMANDE (PK_NUMCOM NUMBER(numéro commande), #NUMFOU NUMBER(numéro fournisseur), DATCOM DATE(date commande)) et une table LIGCOM(#NUMCOM NUMBER(numéro commande), #CODART NUMBER (code artcile), NUMLIG NUMBER(numéro de ligne de commande), QTECDE NUMBER(quantitée commandée), PRINUI NUMBER(prix unitaire)). LIGCOM étant une jointure entre une table PRODUIT(non détaillée ici) et la table COMMANDE.

Donc la dernière commande, c'est le NUMCOM où NUMFOU (numéro fournisseur) est égal au paramètre passé en entrée de la procédure et où DATCOM = MAX(DATCOM) du fournisseur NUMFOU (égal au paramètre passé en entrée de la procédure).

Ensuite, il me faut récupérer toutes les lignes de LIGCOM où figure le NUMCOM récupéré ci-dessus pour faire la somme des produits de la quantité d'articles par le prix unitaire (SUM(QTECDE * PRIUNI)).

Pour cela, j'ai écrit ceci mais je rame :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 CREATE OR REPLACE PROCEDURE PROC_MONTANT ( FOU IN NUMBER , MONT OUT NUMBER ) AS num_fou commande.numfou%TYPE; montant_comm NUMBER; command NUMBER; CURSOR cur_com (foucur NUMBER) IS SELECT c.NUMCOM FROM COMMANDE c WHERE c.NUMFOU = foucur AND c.DATCOM = (SELECT MAX(c1.DATCOM) FROM COMMANDE c1 WHERE c1.NUMFOU = foucur); BEGIN OPEN cur_com(foucurs); FETCH cur_com INTO command; SELECT SUM(PRIUNI*QTECDE) FROM LIGCOM WHERE NUMCOM = command; END PROC_MONTANT;
Je vous remercie d'avance pour votre aide !

En TSQL, cela donnerait quelque chose de la sorte :

Code:

1
2
3
4
5
6
7
8
SELECT @MONT = SUM(PRIUNI*QTECDE)
FROM LIGCOM
        JOIN (
                SELECT MAX(DATCOM) , NUMCOM
                FROM COMMANDE
                WHERE NUMFOU = @FOU 
                ) C
        ON LIGCOM.NUMCOM = C.NUMCOM

J'espère que cela va t'aider.
! Attention si le fournisseur a plus d'une commande par jour !

21/07/2014, 07h59
francky74

SAL oracle

Bonjour et merci pour cette response.

Le code que je souhaite obtenir est du SQL oracle.

Je vais voir avec le TSQL --> malheureusement le TSQL ne fonctionne pas sous oracle.

Bon, j'ai trouvé ma solution :

Code:

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
create or replace PROCEDURE PROC_MONTANT 
(
  FOU IN NUMBER 
, MONT OUT NUMBER 
) AS
 
command NUMBER;
 
CURSOR cur_com (foucur NUMBER) IS
  SELECT c.NUMCOM FROM COMMANDE c
  WHERE c.NUMFOU = foucur AND c.DATCOM = (SELECT MAX(c1.DATCOM) FROM COMMANDE c1 WHERE c1.NUMFOU = foucur);
 
BEGIN
  OPEN cur_com(FOU);
 
  FETCH cur_com INTO command;
 
  SELECT SUM(PRIUNI*QTECDE) INTO MONT
  FROM LIGCOM
  WHERE NUMCOM = command;
 
END PROC_MONTANT;
 
/*code pour appeler cette procédure :
***************************************
 
SET SERVEROUTPUT ON;
DECLARE MONTANT INTEGER;
BEGIN
  PROC_MONTANT(120, MONTANT);
  dbms_output.put_line('montant : ' || MONTANT);
END;
*/

Merci !!!

Bonjour,

Vous pouvez le faire directement avec une requête, sans passer par un curseur.

Quelque chose comme :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH CTE AS(
    SELECT 
        SUM(PRIUNI*QTECDE) As Montant
        ,ROW_NUMBER() OVER(ORDER BY DATCOM DESC) AS Rn
    FROM COMMANDE c
    INNER JOIN LIGCOM l
        ON l.NUMCOM = c.PK_NUMCOM
    WHERE NUMFOU = FOU
    GROUP BY PK_NUMCOM, DATCOM 
)
SELECT Montant
FROM CTE
WHERE Rn = 1

22/07/2014, 10h43
francky74

Merci bien !

Bonjour,

Une autre solution intégrant des exceptions !

Code:

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
create or replace PROCEDURE MONTANT_DER_COM_ERROR(num_fou IN NUMBER, montant OUT NUMBER) IS 
  num_com NUMBER;
  nbr_four NUMBER;
  pas_de_fournisseur EXCEPTION;
BEGIN
 
-- REQUETE QUI VA VERIFIER L'EXISTANCE DU NUMERO DE FOURNISSEUR
  SELECT COUNT(*) INTO nbr_four 
  FROM FOURNIS 
  WHERE NUMFOU = num_fou;
-- SI LA VARIABLE nbr_four (INITIALISEE AVEC LE INTO DE LA REQUETE PRECEDENT) EST EGALE A ZERO
-- ALORS IL Y A UNE ERREUR
  IF nbr_four < 1 THEN
  RAISE pas_de_fournisseur;
  END IF;
 
-- EXECUTION DE L'INSTRUCTION PRINCIPALE  
  SELECT C.NUMCOM INTO num_com
  FROM COMMANDE C
  WHERE C.NUMFOU = num_fou AND ROWNUM <= 1 
  ORDER BY C.NUMCOM DESC;
 
  SELECT SUM(L.QTELIV*L.PRIUNI) INTO montant
  FROM LIGCOM L  WHERE L.NUMCOM = num_com;
 
-- TRAITEMENT DES EXCEPTIONS
EXCEPTION
  WHEN pas_de_fournisseur THEN
  DBMS_OUTPUT.PUT_LINE('Ce numero de fournisseur n existe pas');
--  WHEN NO_DATA_FOUND THEN
--  DBMS_OUTPUT.PUT_LINE('Ce numero de fournisseur n existe pas');
END MONTANT_DER_COM_ERROR;
 
/*code pour appeler la procédure
********************************
 
SET SERVEROUTPUT ON;
DECLARE MONTANT INTEGER;
BEGIN
MONTANT_DER_COM_ERROR('120', MONTANT);
dbms_output.put_line('montant : ' || MONTANT);
END;
*/