[DB2] déclarer des procédures stockées

[rémi_tounul]

apparemment c'est pas comme ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
CREATE procedure INTEGRITE01S01 AS
 
cursor cur_tablefk IS 
   SELECT PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME
      FROM sysibm.sqlforeignkeys;
 
BEGIN
 
END;
/

alors comment ?

[Fatah93]

Pour une procédure stockée :

Code :

1
2
3
4
5
6
7
8
CREATE procedure <nomprocedure> (IN <nomvariable> type , IN ...... )
 
LANGUAGE sql
begin
 
...
 
end

et pour les curseur :

Code :

1
2
 
declare <nomcurseur> cursor FOR SELECT ....

[rémi_tounul]

et mes curseurs, je les déclare où par rapport à la procédure ?
(ainsi que le variables éventuelles)

[Fatah93]

Il me semble qu'il faut déclarer les curseurs après la déclaration de tes variables locales. Mais après la déclaration des curseurs tu ne peux plus déclarer d'autres variables. Je suis pas très sur de tout ca puisque je travaille justement sur ca aussi et je teste.

[rémi_tounul]

ah, et bien quand tu as une réponse préviens-moi : moi j'ai essayé de déclarer le cursor au début du Begin et ça marche mais après j'arrive pas à l'utiliser, mais peut être que je m'y prends mal aussi :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE procedure INTEGRITE01S01
 
LANGUAGE sql
BEGIN
 
declare cur_tablefk cursor FOR
   SELECT PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME
      FROM sysibm.sqlforeignkeys;
 
 
FOR cur_tablefk_ligne IN cur_tablefk LOOP
.....

-> erreur (mais bon c'est mes premiers pas en DB2, je sors d'Oracle moi)

[Fatah93]

Bah pareil lol
Sinon voila un exemple de curseur simple mais ca te donne la syntaxe:

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
 
DECLARE at_end INT DEFAULT 0;
DECLARE not_found
             CONDITION FOR '02000';             // '02000' est le signal de fin de curseur //
 
DECLARE <nomcurseur> CURSOR FOR
 SELECT <colonnes> FROM <table> WHERE <condition> ;
 
DECLARE CONTINUE HANDLER FOR not_found 
         SET at_end = 1;      // si le signal est recu ON met at_end à 1 //
 
OPEN CURSEUR;
 
FETCH CURSEUR INTO NUM, DATE1, TEXTE ;
 
while at_end = 0  do
 
 INSERT INTO TEST4 VALUES (NUM,DATE1,TEXTE) ;
 
 FETCH CURSEUR INTO NUM, DATE1, TEXTE ;    // déplacement du curseur automatique //
 
end while;
 
CLOSE CURSEUR ;

[rémi_tounul]

Où NUM, DATE1 et TEXTE ont été déclarés juste avant le Cursor je suppose ?

Mais alors comment est-ce que je dois faire pour obtenir quelque chose qui voudrait dire à peu près :

Code :

1
2
3
4
5
6
FETCH cur_tablefk INTO schemapk, tablepk, colpk, schemafk, tablefk, colfk;
 
 
WHILE at_end = 0 DO
 
	SELECT count(*) FROM schemapk || '.' || tablepk;

parce que ça a pas l'air de l'inspirer beaucoup mon compilateur

[Fatah93]

heu attend le résultat de ton select qu'est ce que tu veux en faire ?
Et puis " from schemapk || '.' || tablepk; " je suppose que schemapk est une variable contenant le nom d'un schema et tablepk celui d'une table , jsui pas sur que db2 accepte ce genre de d'ecriture mais ptet.

[rémi_tounul]

sous cette forme je veux rien en faire, j'essaye un début mais à la fin la requête sera plus compliquée, mais si déjà ça ça marche pas...
(d'ailleurs ça ne marche pas)


En fait, mon curseur attaque la table sysibm.sqlforeignkeys qui contient les infos sur les clés étrangères utilisées dans la base :
il me retourne les infos sur la clé étrangère (nom de schéma, table et colonne) et sur la clé primaire associée (nom de schéma, table et colonne).
partant de là, je veux aller vérifier si pour cette clé étrangère, toutes les valeurs qu'elle contient existent bien dans la colonne clé primaire de l'autre table.

Et non non, il a pas l'air d'accepter, il trouve même cela "inattendu", m'a-til dit.

[Fatah93]

essaie ca :

DECLARE at_end INT DEFAULT 0;
DECLARE not_found
CONDITION FOR '02000';

DECLARE cur_tablefk CURSOR FOR
SELECT <tes colonnes> from sqlforeignkeys ;

DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

OPEN cur_tablefk ;

FETCH cur_tablefk INTO schemapk, tablepk, colpk, schemafk, tablefk, colfk;


while at_end = 0 do

<tu fais ici tes vérifications> ;

FETCH cur_tablefk INTO schemapk, tablepk, colpk, schemafk, tablefk, colfk;


end while;

CLOSE CURSEUR ;

[rémi_tounul]

C'est déjà exactement ce que je fais, ce que je n'arrive pas, c'est la partie 'vérifications', parce qu'il faudrait que j'arrive à faire quelque chose du style de ce que j'ai mis dans le message du 27 avril, 16:21.

[Fatah93]

Dans la partie déclaration fais :

DECLARE TEXTE VARCHAR(200);

ensuite tu initialise ainsi :

SET TEXTE = 'select count(*) from ?.? ' ;

oui c bien un point d'interrogation suivi d'un point et ensuite d'un point d'interrogation lol
ensuite:

PREPARE REQUETE FROM TEXTE;

tu reprend ce que ta deja fait et dans la partie vérification dans le while tu fais :

EXECUTE TEXTE USING schemapk,tablepk;

Mais bon tu pourra pas voir le résultat du select dans je te conseille de le ranger dans une variable, ce qui donnerai pour la variable TEXTE:

SET TEXTE = 'select count(*) into <nomvariable> from ?.? ' ;

[rémi_tounul]

Mon code est de plus en plus brutal (je suis désolé d'infliger ça un vendredi matin, mais mon patron attend que je lui montre un truc qui marche aujourd'hui, alors j'aimerais pas avoir trop de retard).
Alors j'en suis là : (tout y est cette fois, pas simplifié) :


l'erreur se situe sur la ligne
"declare CONTINUE HANDLER FOR not_found_ref SET at_end_ref = 1"

avec le message suivant :
Une condition ou une valeur SQLSTATE spécifiée dans un gestionnaire n'est pas autorisée.

cela dit ce n'est sûrement pas la seule erreur, mais il veut me les donner qu'une par une.

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
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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
--drop procedure INTEGRITE01S01;
 
CREATE procedure INTEGRITE01S01
 
LANGUAGE sql
BEGIN
 
declare schemapk VARCHAR(30);
declare tablepk VARCHAR(30);
declare colpk VARCHAR(30);
declare schemafk VARCHAR(30);
declare tablefk VARCHAR(30);
declare colfk VARCHAR(30);
 
 
 
-- utilisation d'un varchar qui permettra de stocker la requête à exécuter
-- (permet ainsi d'utiliser des nom de variables dans la requête)
declare TEXTE VARCHAR(200);
-- nombre de clés primaires retournées correspondant à la clé étrangère testée
declare nbpk INTEGER;
 
 
 
 
 
declare at_end INT DEFAULT 0;
declare not_found CONDITION FOR '02000';
-- '02000' est le signal de fin de curseur
 
declare at_end_ref INT DEFAULT 0;
declare not_found_ref CONDITION FOR '02000';
 
 
-- de la même façon, on utilise un autre VARCHAR pour écrire la requête de edéclaration du cursor 
-- qui doit utiliser des variables
declare TXTCUR VARCHAR(200);
 
 
-- si le signal est recu on met at_end à 1 
declare CONTINUE HANDLER FOR not_found SET at_end = 1;
 
-- ? pour faire ça, il faut que ça marche à chaque fois qu'il rencontre la fin :
declare CONTINUE HANDLER FOR not_found_ref SET at_end_ref = 1;
 
 
 
-- renvoie une ligne de la table des clés étrangères, donnant des infos sur la clé étrangère 
--et sur la clé primaire qui y correspond
declare cur_tablefk cursor FOR
   SELECT PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME
      FROM sysibm.sqlforeignkeys;
 
 
 
-- intialisation de cette variable
SET TXTCUR = 'declare cur_ref cursor for select ? from ?.?' ;
 
 
PREPARE REQUETE FROM TXTCUR;
EXECUTE TXTCUR USING colpk, schemapk, tablepk;
--declare cur_ref cursor for select colpk from schemapk.tablepk ;
 
 
 
-- cur-ref parcourt la table de référence (celle qui possède les clés étrangères)
--declare cur_ref cursor for
  -- select colpk from schemapk || '.' || tablepk ;
 
 
 
 
 
 
 
-- initialisation de la variable TEXTE
SET TEXTE = 'select count(*) into nbpk from ?.? WHERE colpk = ?';
 
PREPARE REQUETE FROM TEXTE;
 
 
 
 
 
 
 
OPEN cur_tablefk;
 
FETCH cur_tablefk INTO schemapk, tablepk, colpk, schemafk, tablefk, colfk;
 
 
WHILE at_end = 0 DO
 
	SET at_end_ref = 0;
 
	OPEN cur_ref;
 
	FETCH cur_ref INTO cle_etrangere;
 
	WHILE at_end_ref = 0 DO
		-- ? on compte le nombre de fois où la donnée que l'on a trouvée en tant que clé étrangère
		--  apparaît dans la table en tant que clé primaire
--		select count(*) from schemapk || '.' || tablepk
--			where colpk = cle_etrangere;
 
		EXECUTE TEXTE USING schemapk, tablepk, cle_etrangere;
 
 
		-- SI 0 fois ou >1 : erreur
 
		FETCH cur_ref INTO cle_etrangere;
 
	END WHILE;
 
	CLOSE cur_ref;
 
	FETCH cur_tablefk INTO schemapk, tablepk, colpk, schemafk, tablefk, colfk;
 
END WHILE;
 
CLOSE cur_tablefk;
 
END