tests sur requetes dans une procedure stockée

Version imprimable

bonjour, je me permet de faire appel a vous pour l'ecriture d'une procedure stockée.

comme d habitude je m'attaque a un truc enorme pour commencé... et j avoue que je ne sais pas trop comment debuté, j ai lu attentivement les cours sur les procédure, j ai l'impression d'etre avec un cas qui n'est pas d ecole.

en 2 mots voici le contexte :

j ai un dossier qui changer d etat celon s'il est utilisé (ou pas) il a plusieurs status qui dependent du status des objets qui l'utilisent.

voici donc l algo :

declaration de 2 arguments
libelle et tome

select count (*) from table_utilisation
where lib = @libelle
and tome =@tome

si le resultat de la requete est 0 alors je change le statue de mon dossier

si le resultat est >1 alors je fait mon test 2

test 2 est un select count et je fait pareil ... 7 fois avec des criteres differents

donc !!! si je ne m abuse mon code devrait ressembler a ceci :
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 CREATE OR REPLACE PROCEDURE calcul (p_libelle VARCHAR2, p_tome VARCHAR2, p_user VARCHAR2) IS BEGIN IF ( select count (*) from table_utilisation where lib = @p_libelle and tome =@p_tome) = 0 THEN UPDATE DOSSIER_MODIF SET OP_DATE_MAJ= SYSDATE, OP_MODI=@p_user, DM_ETAT='Créé' where 1=1 and DM_REFERENCE = @p_libelle and DM_TOME = @p_tome) ELSE IF elect count (*) from table_utilisation where statue = 'OK' and lib = @p_libelle and tome =@p_tome) = 0 THEN UPDATE DOSSIER_MODIF SET OP_DATE_MAJ= SYSDATE, OP_MODI=@p_user, DM_ETAT='Maj' where 1=1 and DM_REFERENCE = @libelle and DM_TOME = @tome) ELSE END; /
merci d avances pour votre aide et vos eclaircissements

OOPS j oubliais le preincipal : je suis sur ORACLE 9i

30/08/2007, 12h12
AL1986

A quoi sert exactement la condition

Code:

where 1=1

? Merci
30/08/2007, 12h16
morback

en fait c est juste l habitude de bosser sur des requetes generés

lorsque le developpeur reprend la requete il n ajoute que

select * from table
where 1=1
and toto = condition1
and toto = condition 2

en rouge c est fixe, ca consomme pas plus et ca permet au generateur de requete de pas se poser de question sur la place de l argument..
effectivement pas necessaire ici ;)
30/08/2007, 14h26
Phil_67
Dans la déclaration de ta procédure tu dois indiquer si tes paramètres sont des paramètres d'entrée ou de sortie de la manière suivante :
Code:

1 2 3 4 5 6 7 CREATE OR REPLACE PROCEDURE calcul (p_libelle VARCHAR2, p_tome VARCHAR2, p_user VARCHAR2) IS..... BEGIN ... END;
De plus tu références tes paramètre au sein de ta procédure avec @ devant, pour quelle raison?
Ensuite si ton paramètre s'appelle p_tome pourquoi écris-tu @tome?

Attention aux erreurs d'orthographe...
30/08/2007, 14h41
AL1986
Citation:
Envoyé par Phil_67

Dans la déclaration de ta procédure tu dois indiquer si tes paramètres sont des paramètres d'entrée ou de sortie de la manière suivante :

Code:

1 2 3 4 5 6 7 CREATE OR REPLACE PROCEDURE calcul (p_libelle VARCHAR2, p_tome VARCHAR2, p_user VARCHAR2) IS..... BEGIN ... END;
T'as dû zapper les In/Out au passage :mrgreen:

Citation:

Envoyé par Phil_67

De plus tu références tes paramètre au sein de ta procédure avec @ devant, pour quelle raison?

J'avais pas fait attention à ça, je pense que c'est seulement une erreur d'inattention de la part de morback à moins qu'il n'y ait de mystérieuses raisons derrière cette syntaxe.

Citation:

Envoyé par Phil_67

Attention aux erreurs d'orthographe...

Citation:

Envoyé par Phil_67

De plus tu références tes paramètres au sein de ta procédure avec @ devant, pour quelle raison?

:mouarf:

En s'abstenant pour rentrer dans le débat d'orthographe..
Ta procédure devrait ressembler à ça :

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
 CREATE OR REPLACE PROCEDURE calcul 
(p_libelle IN VARCHAR2, p_tome IN VARCHAR2, p_user IN VARCHAR2)
IS
    BEGIN
IF   ( select count (*) from table_utilisation
       where lib = p_libelle
       and tome =p_tome) = 0 
THEN 

    UPDATE DOSSIER_MODIF
    SET OP_DATE_MAJ= SYSDATE, 
          OP_MODI=p_user,
          DM_ETAT='Créé'
    where DM_REFERENCE = p_libelle
        and DM_TOME = p_tome)
ELSE
   IF select count (*) from table_utilisation
        where statue = 'OK' and 
        lib = p_libelle
       and tome =p_tome) = 0
   THEN 
      UPDATE DOSSIER_MODIF
       SET OP_DATE_MAJ= SYSDATE, 
             OP_MODI=p_user,
             DM_ETAT='Maj'
    where  DM_REFERENCE = libelle
         and DM_TOME = tome) 
END;
/

Plus ou moins:D

alors le IN / OUT je savais pas je viens de l ajouter

pour l'@ je pensais que c'etait obligatoire comme le %% en batch le $ en perl etc ...
donc la je les retire :aie:
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 calcul (p_libelle IN VARCHAR2, p_tome IN VARCHAR2, p_user IN VARCHAR2) IS IN BEGIN IF ( select count (*) from table_utilisation where lib = p_libelle and tome = p_tome) = 0 THEN UPDATE DOSSIER_MODIF SET OP_DATE_MAJ= SYSDATE, OP_MODI=p_user, DM_ETAT='Créé' where 1=1 and DM_REFERENCE = p_libelle and DM_TOME = p_tome ELSE IF (select count (*) from table_utilisation where statue = 'OK' and lib = p_libelle and tome =p_tome) = 0 THEN UPDATE DOSSIER_MODIF SET OP_DATE_MAJ= SYSDATE, OP_MODI=@p_user, DM_ETAT='Maj' where 1=1 and DM_REFERENCE = libelle and DM_TOME = p_tome ELSE END; /
mais la encore j ai des erreurs de compliation

je suis desolé du coté un peu basic de mes attentes, autant je commence a être bien à l'aise avec le SQL autant pour les procédure stockés, les cours me sembles vites abstraits

merci de votre aide

le principe est simple sur le papier faire un update d'un champs en fonction de resultat de requetes count... mais dois je mettre entre parethse, cote ou rien, bref je suis un peu perdu sur la syntaxe, il ne dois pas manquer grand chose (enfin j espere) ou peut etre que je tente des choses qui ne se font pas..

30/08/2007, 15h11
morback

merci lbo72, ca confirme mes changement j essaye de voir ou il reste des erreurs, il y avait eu qq fautes de frappes, et veuillez m excuser pour les fautes de francais, cela m arrive lorsque je prend pas le temps de me relire
j etais plutot matheux a l ecole :mrgreen:

j essaye tout ca et je reviens vers vous pour vour dire si ca marche ou pas ;)
30/08/2007, 15h21
AL1986

Citation:

Envoyé par morback

alors le IN / OUT je savais pas je viens de l ajouter

Je pense que si tu ne spécifies pas IN, tes paramètres sont IN par défaut, par contre le OUT doit être mis quand t'as des paramètres de sortie. A vérifier.

Citation:

Envoyé par morback

pour l'@ je pensais que c'etait obligatoire comme le %% en batch le $ en perl etc

Apparemment non, en fait c'est les paramètres qui sont généralement écris comme ça : @param. Mais je présume que ce n'est qu'une norme pour la nomenclature. A verifier aussi.

Citation:

Envoyé par morback

mais la encore j ai des erreurs de compliation

Quelles sont ces erreurs ?

Citation:

Envoyé par morback

il ne dois pas manquer grand chose

Peut-être faudrait-il ajouter des ; à la fin de tes select

Citation:

Envoyé par morback

ou peut etre que je tente des choses qui ne se font pas..

Peut-être bien que oui, peut-être bien que non.

En plsql, un SELECT est forcemment INTO une variable. (donc un IF (select) marche pas.
Manque des END IF;

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
CREATE OR REPLACE PROCEDURE calcul 
(p_libelle IN VARCHAR2, p_tome IN VARCHAR2, p_user IN VARCHAR2)
IS
 v NUMBER;
 
BEGIN
 
SELECT COUNT (*) 
INTO v
FROM table_utilisation
WHERE lib = p_libelle
AND tome =p_tome;
 
IF  v = 0 
THEN 
    UPDATE DOSSIER_MODIF
    SET OP_DATE_MAJ= SYSDATE, 
          OP_MODI=p_user,
          DM_ETAT='Créé'
    WHERE DM_REFERENCE = p_libelle
        AND DM_TOME = p_tome
ELSE
 
	SELECT COUNT (*) 
	INTO v
	FROM table_utilisation
	WHERE statue = 'OK' 
	AND lib = p_libelle
	AND tome =p_tome;
 
   IF v = 0
	THEN
     UPDATE DOSSIER_MODIF
       SET OP_DATE_MAJ= SYSDATE, 
             OP_MODI=p_user,
             DM_ETAT='Maj'
    WHERE  DM_REFERENCE = libelle
         AND DM_TOME = p_tome;
	END IF;
END IF;
 
END;

Essaye cela ... ça devrait tenir la route

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 calcul 
(p_libelle IN VARCHAR2, p_tome IN VARCHAR2, p_user IN VARCHAR2)
IS 
    res number := 1;
    BEGIN
        select count (*) into res from table_utilisation
       		where lib = p_libelle
       		   and tome = p_tome;  
	IF   res = 0  
	THEN 
       	     UPDATE DOSSIER_MODIF
              		SET OP_DATE_MAJ= SYSDATE, 
                  	OP_MODI=p_user,
                  	DM_ETAT='Créé'     		
             where  DM_REFERENCE = p_libelle
               	and DM_TOME = p_tome;
	ELSE
                  select count (*) into res from table_utilisation
           	where statue = 'OK'   and 
                      lib = p_libelle and
                      tome =p_tome; 
       		IF res = 0
                THEN 
                    UPDATE DOSSIER_MODIF
                       SET OP_DATE_MAJ= SYSDATE, 
                           OP_MODI=p_user,
                           DM_ETAT='Maj'
                    where  DM_REFERENCE = libelle and
                           DM_TOME = p_tome ;
                end if;
        end if;
END;
/

30/08/2007, 15h40
McM

Preums !! :mouarf:
30/08/2007, 15h59
morback

c est bien les points sur lesquels j avais des doutes a savoir sur le if direct sur la requete et les end pour les if, je test tout ca, mais je pense que ca va aller, je je place ici les conclusions et la procedure finale, pour le prochain qui aura ce pb

merci bcp
30/08/2007, 16h06
morback

il existe encore des erreurs de compliation

y a t il un moyen de les connaitre ou de les afficher ?
30/08/2007, 16h08
LBO72

Tu passes par show errors pour visualiser les erreurs

c etait libellé au leiue de p_libellé dans une requete

et d habitude j utilise TOAD, pas encore copain avec sqlplus,

donc ca marche !!!

voici le code correct (tres proche de ce que vous aviez fourni donc)

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
44
 
 
CREATE OR REPLACE PROCEDURE calcul 
(p_libelle IN VARCHAR2, p_tome IN VARCHAR2, p_user IN VARCHAR2)
IS 
    result number := 1;
    BEGIN
			    select count (*) into result from version_piquage
				       where  REFERENCE_PRODUIR = p_libelle
					          and TOME_PRODUIR = p_tome; 
 
	IF   result = 0  
	THEN 
       	     UPDATE DOSSIER_MODIF
              		SET OP_DATE_MAJ= SYSDATE, 
                  	OP_MODI=p_user,
                  	DM_ETAT='Créé'     		
             		where  DM_REFERENCE = p_libelle
               		and DM_TOME = p_tome;
	ELSE
 
	select count(*) into result from realisation_tranche rt, version_piquage vp
	 where vp.PP = rt.PP
	  and vp.NCHRONOAI = rt.NCHRONOAI
	 	 and vp.SE = rt.SE
		 	 and vp.VPIQ_TRANCHE = rt.VPIQ_TRANCHE
			 	 and vp.VERSION = rt.VERSION
				 	 and REFERENCE_PRODUIR = p_libelle
					 	 and TOME_PRODUIR = p_tome
						 	 and rt.ETAT <> 'perimée' 
							 	 and rt.ETAT <> 'Requalifiée'; 
 
     		IF result = 0
                THEN 
                    UPDATE DOSSIER_MODIF
                       SET OP_DATE_MAJ= SYSDATE, 
                           OP_MODI=p_user,
                           DM_ETAT='Programmé'
                    	   where  DM_REFERENCE = p_libelle 
						   and DM_TOME = p_tome ;
                end if;
        end if;
END;
/

30/08/2007, 16h24
LBO72

Ok, ça roule :D...
30/08/2007, 16h35
morback

desormais je dois rajpouter 7 test en plus

merci de votre aide, je note le sujet en resolu, j ai la bonne syntaxe desormais

Tu as aussi cette solution avec les 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
44
45
CREATE OR REPLACE PROCEDURE calcul 
(p_libelle IN VARCHAR2, p_tome IN VARCHAR2, p_user IN VARCHAR2)
IS 
    result NUMBER := 1;
BEGIN
	SELECT 1 INTO result 
	FROM version_piquage
	WHERE  REFERENCE_PRODUIR = p_libelle
	AND TOME_PRODUIR = p_tome 
	AND ROWNUM = 1; 
 
	BEGIN
		SELECT 1 INTO result 
		FROM realisation_tranche rt, version_piquage vp
		WHERE vp.PP = rt.PP
		AND vp.NCHRONOAI = rt.NCHRONOAI
		AND vp.SE = rt.SE
		AND vp.VPIQ_TRANCHE = rt.VPIQ_TRANCHE
		AND vp.VERSION = rt.VERSION
		AND REFERENCE_PRODUIR = p_libelle
		AND TOME_PRODUIR = p_tome
		AND rt.ETAT <> 'perimée' 
		AND rt.ETAT <> 'Requalifiée'
		AND ROWNUM = 1; 
	EXCEPTION
	WHEN NO_DATA_FOUND
	THEN
		UPDATE DOSSIER_MODIF
		SET OP_DATE_MAJ= SYSDATE, 
		OP_MODI=p_user,
		DM_ETAT='Programmé'
		WHERE  DM_REFERENCE = p_libelle 
		AND DM_TOME = p_tome ;
	END;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
	UPDATE DOSSIER_MODIF
	SET OP_DATE_MAJ= SYSDATE, 
	OP_MODI=p_user,
	DM_ETAT='Créé'     		
	WHERE  DM_REFERENCE = p_libelle
	AND DM_TOME = p_tome;
END;
/