Procédure stockée dans fonction

**RomJo** · 15/04/2010, 19h44

Bonjour a tous,

Mon problème est que je n'arrive pas a transférer ma variable :

Voici ma procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE PROCEDURE cherche2 
	(ID VARCHAR(36), OUT GR INTEGER) 
	 READS SQL DATA 
	BEGIN 
	...
	RETURN GR;

Ma fonction (elle doit se servir de ma procédure) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE FUNCTION cherche(ID VARCHAR(36)) 
	RETURNS INTEGER 
	BEGIN ATOMIC 
	DECLARE GR INTEGER; 
	call cherche2(ID, GR); 
	RETURN GR;  -> ici ce situe l'erreur
	END

Je ne sais pas comment récupérer la variable GR dans ma procédure afin de la transmettre dans ma fonction et la retourner.

**K2R400** · 16/04/2010, 08h44

Que viens faire ton RETURN dans ta procédure ?????
Enlève le

**RomJo** · 16/04/2010, 09h41

je l'ai mis pour pouvoir le faire transmettre a la fonction.
J'ai essayé a la place SET GR = 1; mais j'ai toujours se problème.
PS: y'a t'il un moyen de mettre un curseur dans une fonction ??

**K2R400** · 16/04/2010, 13h02

Tu fais ce que tu veux dans une fonction, aucun problème pour déclarer et utiliser un curseur.

**RomJo** · 16/04/2010, 13h46

J'ai essayé de mettre un cureur dans ma fonction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE FUNCTION CHERCHE
 (ID VARCHAR(36), USERS VARCHAR(250), PERMS VARCHAR(250)) 
		RETURNS INTEGER
		 BEGIN ATOMIC
		DECLARE ALLUSERS VARCHAR(250); 
		DECLARE ALLPERMS VARCHAR(250); 
		DECLARE FIRST INTEGER; 
		DECLARE CURID VARCHAR(36); 
		DECLARE NEWID VARCHAR(36); 
		DECLARE GR INTEGER;
		DECLARE PE VARCHAR(250); 
		DECLARE US VARCHAR(250); 
		DECLARE v_sqlstatus INTEGER DEFAULT 0; 
		SET ALLUSERS = '%' || USERS || '%'; 
		SET ALLPERMS = '%' || PERMS || '%'; 
		SET FIRST = 1; 
		SET CURID = ID; 
		WHILE CURID IS NOT NULL DO 
		DECLARE CC CURSOR FOR 
		SELECT GRANT, PERMISSION, USER FROM ACLS 
		WHERE ACLS.ID = CURID ORDER BY POS; 
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_sqlstatus = 1; 
		 OPEN CC; 
		   REPEAT 
			FETCH FROM CC INTO GR, PE, US; 	
                                         IF ALLUSERS LIKE ('%' || US || '%') AND ALLPERMS LIKE ('%' || PE || '%') THEN
                                            CLOSE CC; 	 
                                              RETURN GR; 
                                      END IF;
			  UNTIL (v_sqlstatus = 1) END REPEAT;
		END WHILE; 
	CLOSE CC;
		SELECT parentid INTO newid FROM hierarchy WHERE hierarchy.id = curid; 
		         IF FIRST = 1 AND NEWID IS NULL THEN 
			SELECT versionableid INTO newid FROM versions WHERE versions.id = curid; 
		          END IF; 
			SET FIRST = 0; 
			SET CURID = NEWID; 				                                RETURN 0;
				 END

mais j'ai cette erreur :

An unexpected token "DECLARE CC CURSOR FOR
SELECT " was found following "S NOT NULL DO
". Expected tokens may include: "<values>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.8.86

**K2R400** · 16/04/2010, 13h54

Essaye plutôt :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE FUNCTION CHERCHE
 (ID VARCHAR(36), USERS VARCHAR(250), PERMS VARCHAR(250)) 
		RETURNS INTEGER
 
		DECLARE ALLUSERS VARCHAR(250); 
		DECLARE ALLPERMS VARCHAR(250); 
		DECLARE FIRST INTEGER; 
		DECLARE CURID VARCHAR(36); 
		DECLARE NEWID VARCHAR(36); 
		DECLARE GR INTEGER;
		DECLARE PE VARCHAR(250); 
		DECLARE US VARCHAR(250); 
		DECLARE v_sqlstatus INTEGER DEFAULT 0; 
 
		DECLARE CC CURSOR FOR 
		SELECT GRANT, PERMISSION, USER FROM ACLS 
		WHERE ACLS.ID = CURID ORDER BY POS; 
 
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_sqlstatus = 1; 
 
 
		SET ALLUSERS = '%' || USERS || '%'; 
		SET ALLPERMS = '%' || PERMS || '%'; 
		SET FIRST = 1; 
		SET CURID = ID; 
 
....

Il te faut tes déclaratives dans l'ordre

Exemple d'une procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE PROCEDURE CREDITP
(IN i_perinc DECIMAL(3,2), 
OUT o_numrec DECIMAL(5,0)) 
LANGUAGE SQL
BEGIN ATOMIC 
DECLARE proc_cusnbr CHAR(5);
DECLARE proc_cuscrd DECIMAL(11,2);
DECLARE numrec DECIMAL(5,0);
DECLARE at_end INT DEFAULT 0;
DECLARE not_found
CONDITION FOR '02000';
DECLARE c1 CURSOR FOR
SELECT cusnbr, cuscrd
FROM ordapplib.customer;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
SET numrec = 0;
OPEN c1;
FETCH c1 INTO proc_cusnbr, proc_cuscrd;
WHILE at_end = 0 DO
SET proc_cuscrd = proc_cuscrd +(proc_cuscrd * i_perinc);
UPDATE ordapplib.customer
SET cuscrd = proc_cuscrd
WHERE CURRENT OF c1;
SET numrec = numrec + 1;
FETCH c1 INTO proc_cusnbr, proc_cuscrd;
END WHILE;
SET o_numrec = numrec;
CLOSE c1;
END

**RomJo** · 16/04/2010, 14h05

J'ai suivi ton conseil mais j'ai toujours une erreur de curseur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE FUNCTION CHERCHER
	(ID VARCHAR(36), USERS VARCHAR(250), PERMS VARCHAR(250)) 
				   RETURNS INTEGER
				   BEGIN ATOMIC
 
				   DECLARE ALLUSERS VARCHAR(250); 
				   DECLARE ALLPERMS VARCHAR(250); 
				   DECLARE FIRST INTEGER; 
				   DECLARE CURID VARCHAR(36); 
				   DECLARE NEWID VARCHAR(36); 
				   DECLARE GR INTEGER;
				   DECLARE PE VARCHAR(250); 
				   DECLARE US VARCHAR(250); 
				   DECLARE v_sqlstatus INTEGER DEFAULT 0; 
 
				   DECLARE CC CURSOR FOR 
				   SELECT GRANT, PERMISSION, USER 
				   FROM ACLS 
				   WHERE ACLS.ID = CURID
				   ORDER BY POS; 
 
				   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_sqlstatus = 1; 
 
 
				     SET ALLUSERS = '%' || USERS || '%'; 
				     SET ALLPERMS = '%' || PERMS || '%'; 
				     SET FIRST = 1; 
				     SET CURID = ID; 
 
				     WHILE CURID IS NOT NULL DO 
 
				       OPEN CC; 
				      	 REPEAT 
				         FETCH FROM CC INTO GR, PE, US; 
				 	  					IF ALLUSERS LIKE ('%' || US || '%') AND ALLPERMS LIKE ('%' || PE || '%') THEN	
				      						CLOSE CC; 
				       					RETURN GR; 
				     					END IF;
				      	 UNTIL (v_sqlstatus = 1) END REPEAT;
				      	END WHILE; 
				       CLOSE CC;
 
				      SELECT parentid INTO newid FROM hierarchy WHERE hierarchy.id = curid; 
				       IF FIRST = 1 AND NEWID IS NULL THEN 
				         SELECT versionableid INTO newid FROM versions WHERE versions.id = curid; 
				       END IF; 
				      	  SET FIRST = 0; 
				      	  SET CURID = NEWID; 				     
  			     RETURN 0;
				 END

An unexpected token "DECLARE CC CURSOR FOR
SELECT GRA" was found following "T 0". Expected tokens may include: "

".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.8.86

**K2R400** · 16/04/2010, 14h28

Ton problème vient de l'éxécution ou de la création de la fonction ?
Je vois un return 0. Essaye un Return INT(0);

**RomJo** · 16/04/2010, 14h31

cela ne change rien

**K2R400** · 16/04/2010, 14h33

Celà ne répond pas à ma précédente question

**RomJo** · 16/04/2010, 14h35

A l'execution .
J'ai l'erreur même en faisant ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE FUNCTION CHERCHER
				 (ID VARCHAR(36), USERS VARCHAR(250), PERMS VARCHAR(250)) 
				   RETURNS INTEGER 
				   BEGIN ATOMIC
 DECLARE CC CURSOR FOR 
				   SELECT GRANT, PERMISSION, USER 
				   FROM ACLS 
				   WHERE ACLS.ID = CURID
				   ORDER BY POS;

**K2R400** · 16/04/2010, 14h46

Avant ton OPEN CC, ajoute CLOSE CC;
Fais un STRDBG.
Puis mets toi sous STRSQL pour lancer ta fonction.
Copie/colle l'historique.

**RomJo** · 16/04/2010, 14h54

Je ne sais pas ce qu'est STRDBG, moi je travail sous RAD.
Je peut te donner l'historique de RAD :

CREATE FUNCTION CHERCHER
(ID VARCHAR(36), USERS VARCHAR(250), PERMS VARCHAR(250))
RETURNS INTEGER
BEGIN ATOMIC

DECLARE ALLUSERS VARCHAR(250);
DECLARE ALLPERMS VARCHAR(250);
DECLARE FIRST INTEGER;
DECLARE CURID VARCHAR(36);
DECLARE NEWID VARCHAR(36);
DECLARE GR INTEGER;
DECLARE PE VARCHAR(250);
DECLARE US VARCHAR(250);
DECLARE v_sqlstatus INTEGER DEFAULT 0;

DECLARE CC CURSOR FOR
SELECT GRANT, PERMISSION, USER
FROM ACLS
WHERE ACLS.ID = CURID
ORDER BY POS;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_sqlstatus = 1;

SET ALLUSERS = '%' || USERS || '%';
SET ALLPERMS = '%' || PERMS || '%';
SET FIRST = 1;
SET CURID = ID;

WHILE CURID IS NOT NULL DO
OPEN CC;
REPEAT
FETCH FROM CC INTO GR, PE, US;
IF ALLUSERS LIKE ('%' || US || '%') AND ALLPERMS LIKE ('%' || PE || '%') THEN
CLOSE CC;
RETURN GR;
END IF;
UNTIL (v_sqlstatus = 1) END REPEAT;
END WHILE;
CLOSE CC;

SELECT parentid INTO newid FROM hierarchy WHERE hierarchy.id = curid;
IF FIRST = 1 AND NEWID IS NULL THEN
SELECT versionableid INTO newid FROM versions WHERE versions.id = curid;
END IF;
SET FIRST = 0;
SET CURID = NEWID;
RETURN 0;
END

An unexpected token "DECLARE CC CURSOR FOR
SELECT GRA" was found following "

". Expected tokens may include: "<space>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.8.86

**K2R400** · 16/04/2010, 15h12

Franchement on a du mal a comprendre ce que tu veux faire au final avec ton curseur. Je me dis qu'il y a surement plus simple. Mais le problème n'est pas là.

Essaye de créer ta fonction avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 FUNCTION CHERCHER
(ID VARCHAR(36), USERS VARCHAR(250), PERMS VARCHAR(250)) 
RETURNS INTEGER 
BEGIN ATOMIC
 
DECLARE ALLUSERS VARCHAR(250); 
DECLARE ALLPERMS VARCHAR(250); 
DECLARE FIRST INTEGER; 
DECLARE CURID VARCHAR(36); 
DECLARE NEWID VARCHAR(36); 
DECLARE GR INTEGER;
DECLARE PE VARCHAR(250); 
DECLARE US VARCHAR(250); 
DECLARE v_sqlstatus INTEGER DEFAULT 0; 
 
DECLARE CC CURSOR FOR 
SELECT GRANT, PERMISSION, USER 
FROM ACLS 
WHERE ACLS.ID = CURID 
ORDER BY POS; 
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_sqlstatus = 1; 
 
SET ALLUSERS = '%' || USERS || '%'; 
SET ALLPERMS = '%' || PERMS || '%'; 
SET FIRST = 1; 
SET CURID = ID; 
 
CLOSE CC;
OPEN CC; 
FETCH CC INTO GR, PE, US; 
RETURN INT(0);
END

Teste pour voir ce que celà donne.
J'ai enlevé aussi le FROM dans ton FETCH

**RomJo** · 16/04/2010, 15h14

J'ai le problème suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
An unexpected token "DECLARE CC CURSOR FOR  
SELECT GRANT, PER" was found following "GER DEFAULT 0".  Expected tokens may include:  "  
 
".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.8.86

**K2R400** · 16/04/2010, 15h29

Certains mots clés sont réservés par SQL.
C'est le cas de GRANT, USER, etc....

Si c'est le nom de tes colonnes de table, mets les entre guillemets.

SELECT "GRANT", "USER" etc...

Sinon par exemple USER récupère l'utilisateur en cours

**RomJo** · 19/04/2010, 10h53

J'ai changé de technique. J'ai fait une procédure avec le curseur et une méthode qui appelle la procédure, mais j'ai un problème au niveau de la procédure( dans les paramètres).
En effet, si je mets out, pour la variable R, j'ai un soucis et même inout.
Comment faire pour que la fonction retourne la bonne valeur de la variale R de la procédure.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE PROCEDURE proc_cherche 
				 (ID VARCHAR(36), USERS VARCHAR(250), PERMS VARCHAR(250), R INTEGER) 
				   MODIFIES SQL DATA
				   BEGIN ATOMIC
				   DECLARE ALLUSERS VARCHAR(250); 
				   DECLARE ALLPERMS VARCHAR(250); 
				   DECLARE FIRST INTEGER; 
				   DECLARE CURID VARCHAR(36) ; 
				   DECLARE NEWID VARCHAR(36); 
				   DECLARE GR INTEGER; 
				   DECLARE PE VARCHAR(250); 
				   DECLARE US VARCHAR(250); 
				   DECLARE v_sqlstatus INTEGER DEFAULT 0; 
				   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_sqlstatus = 1; 
				     SET ALLUSERS = '%%' || USERS || '%%'; 
				     SET ALLPERMS = '%%' || PERMS || '%%';
				     SET FIRST = 1; 
				     SET CURID = ID; 
 
		 	       WHILE CURID IS NOT NULL DO 	     
				     begin
 
				      DECLARE CC CURSOR FOR 
				      SELECT GRANT, PERMISSION, USER FROM ACLS 
				      WHERE ACLS.ID = CURID ORDER BY POS ; 
 
						OPEN CC;       
 
				      	 REPEAT 
				         FETCH FROM CC INTO GR, PE, US;
				 	  					IF ALLUSERS LIKE ('%%' || US || '%%') AND ALLPERMS LIKE ('%%' || PE || '%%') THEN	
				      						CLOSE CC; 
				      						SET R = GR; 
				       					RETURN R; 
				     					END IF; 
				      	 UNTIL (v_sqlstatus = 1) END REPEAT;			   
 
				    	CLOSE CC; 
				      end;			    
 
				      SELECT parentid INTO newid FROM hierarchy WHERE hierarchy.id = curid; 
				       IF FIRST = 1 AND NEWID IS NULL THEN 
				         SELECT versionableid INTO newid FROM versions WHERE versions.id = curid; 
				       END IF; 
				      	  SET FIRST = 0; 
				      	  SET CURID = NEWID; 
				    END WHILE; 
				  RETURN 0;  
				 END

et ma fonction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE FUNCTION cherche(ID VARCHAR(36), USERS VARCHAR(250), PERMS VARCHAR(250)) 
				RETURNS INTEGER 
				BEGIN ATOMIC 
				DECLARE GR INTEGER; 
				 CALL proc_cherche (ID, USERS, PERMS, GR); 
				  RETURN GR; --> ici
				 END

**K2R400** · 19/04/2010, 13h10

M'enfin, j'aurais tout mis dans la fonction.
Je ne vois pas à quoi celà te sert de faire une fonction + 1 procédure.
Sinon, je me répète :

GRANT, USER etc... sont des mots clés réservés, celà ne marchera pas si tu les mets pas entre guillemets. Sauf pour USER, si tu veux récupérer l'utilisateur en cours.

Et pourquoi mettre un RETURN dans ta procédure ?

**RomJo** · 19/04/2010, 13h32

Pour ce qui est des guillemets pour les mots clés, j'ai testé, mais j'avais toujours le même soucis.
Maintenant, avec la procédure, je n'ai plus de soucis, mais je n'arrive pas a récupérer dans ma fonction le résultat de ma procédure. (le R en paramètre de ma procédure et GR pour ma fonction)
Le return, c'est seulement pour sortir de la procédure.

CALL proc_cherche (ID, USERS, PERMS, GR); fonctionne quand je mets dans ma procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(ID VARCHAR(36), USERS VARCHAR(250), PERMS VARCHAR(250), INOUT R INTEGER)

Procédure stockée dans fonction

DB2

Discussions similaires

Partager

Partager