Curseur dans un autre curseur

[Nimp0rtequi]

Bonjour,

Encore une fois désolé pour l'intitulé peu prècis.

Voici mon problème : Je voudrais créer une procédure qui boucle sur chaque ID d'une table, et pour cet ID, insérer des valeurs d'une autre table dans une nouvelle table.

Voici, en gros, ce que je voudrais obtenir :

C1 CURSOR FOR SELECT ID FROM Table1;
Pour chaque élément de C1
{
C2 CURSOR FOR SELECT champ FROM Table2;
Pour chaque élément de C2
{
INSERT INTO Table3 VALUES (ID, champ);
}
}

Cependant, je ne connais que cette syntaxe pour les boucles :

DECLARE x INT;
DECLARE done INT DEFAULT 0;
DECLARE C1 CURSOR FOR SELECT ID FROM Table1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN C1;

REPEAT
FETCH C1 INTO x;
...
UNTIL done END REPEAT;

Je ne peux pas déclarer une deuxième fois : "DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;". Je ne pourrais donc pas recréer une boucle à l'intérieur de la première ...


Comment pourrais-je procéder ? (ah le PL/SQL ... c'est bien plus simple =P)


Merci du temps passé à essayer de m'aider. Si je n'ai pas été suffisamment clair dans mon explication, n'hésitez pas à me poser des questions.


Cordialement.

[Nimp0rtequi]

Bonjour,

Ce matin, en allant au boulot, j'ai pensé à quelquechose que j'ai réalisé :

J'ai déclaré le second curseur dans une autre procédure que j'appelle à chaque passage dans la boucle de la première procédure ...

Cependant, voici ce que cela fait :

Le premier ID est bien selectionné, l'insertion dans la Table3 de tous les tuples dont j'avais besoin est bien réalisée, mais lors du parcours du 2ème ID, je n'insère qu'un seul tuple (au lieu d'une dizaine ...) et la procédure est terminée.

Je suppose que le problème vient toujours du "SQL STATE '02000'" ...

Je vois joins mon code, pour être plus clair ^^

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
CREATE PROCEDURE maProc()
BEGIN
	DECLARE Date_creation DATE;
	DECLARE Date_resolution DATE;
	DECLARE x INT;
	DECLARE done INT DEFAULT 0;
	DECLARE BUG CURSOR FOR SELECT id FROM mantis_bug_table;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
	CREATE TABLE MaTable (ID_Anomalie INT(10), ID_DATE DATE);
 
	OPEN BUG;
	REPEAT
		FETCH BUG INTO x;
		IF NOT done THEN
 
			SELECT STR_TO_DATE(FROM_UNIXTIME(date_submitted, '%d/%m/%Y'), '%d/%m/%Y') INTO Date_creation
			FROM mantis_bug_table
			WHERE id = x;
 
			SELECT STR_TO_DATE(FROM_UNIXTIME(min(date_modified), '%d/%m/%Y'), '%d/%m/%Y') INTO Date_resolution
			FROM mantis_bug_history_table
			WHERE bug_id = x AND field_name='status' AND new_value='80'
			GROUP BY bug_id;
 
			/* Si l'anomalie n'a pas été résolue, elle est donc encore ouverte aujourd'hui */
			IF (Date_resolution IS NULL) THEN
				SELECT STR_TO_DATE(DATE_FORMAT(CURDATE(), '%d/%m/%Y'), '%d/%m/%Y') INTO Date_resolution FROM DUAL;
			END IF;
 
			CALL maProc2(x, Date_creation, Date_resolution);
 
		END IF;
	UNTIL done END REPEAT;
	CLOSE BUG;
END//
 
 
CREATE PROCEDURE maProc2(ID INT, Date_creation DATE, Date_resolution DATE)
BEGIN
	DECLARE x DATE;
	DECLARE done2 INT DEFAULT 0;
	DECLARE JOUR CURSOR FOR SELECT DISTINCT STR_TO_DATE(ID_DATE, '%d/%m/%Y') FROM CALENDRIER WHERE STR_TO_DATE(ID_DATE, '%d/%m/%Y') >= Date_creation AND STR_TO_DATE(ID_DATE, '%d/%m/%Y') <= Date_resolution;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
 
	OPEN JOUR;
	REPEAT
		FETCH JOUR INTO x;
		IF NOT done2 THEN
			INSERT INTO MaTable VALUES (ID, x);
		END IF;
	UNTIL done2 END REPEAT;
	CLOSE JOUR;
END//

Et donc, pour le 1er ID_Anomalie j'obtiens donc les dates dont j'ai besoin, et pour le 2è ID_Anomalie, j'obtiens uniquement la première date dont j'ai besoin et c'est tout ...

Merci !

[ced]

Bonjour,

Avec si peu d'éléments, c'est difficile d'évaluer exactement ce que tu souhaites faire, mais je ne suis pas certain qu'il faille se compliquer à ce point la vie, en passant par une fonction et des curseurs...
Un INSERT INTO... SELECT... pourrait peut-être suffire.
Seulement, il faudrait qu'on ait plus de détail sur les tables, leur contenu et ce que tu souhaites insérer exactement.

ced

[Nimp0rtequi]

Bonjour ced,

En fait, dans ma base, je dispose de 2 tables : l'une est un simple calendrier (ID_DATE, ANNEE, SEMESTRE, TRIMESTRE, ...), et l'autre contient des informations sur des bugs (rencontrés lors des tests). Dans cette seconde table, je dispose d'une part de l'ID du bug et d'informations sur celui-ci (état actuel, priorité, sévérité, etc.) et d'autre part de la date de création.

Je dois réaliser un document Qlikview permettant de visualiser les bugs actifs (un bug est actif entre le moment où il est créé et le moment où il a été résolu) lorsque je choisis,par exemple, la semaine 10 de l'année 2011.

Cependant, uniquement avec la date de création, je ne peux pas obtenir les bugs actifs pour une période quelconque. Il me faut donc une table de référence contenant l'ID du bug et les dates où il était actif.

exemple : un bug (ID = 6) a été créé le 2 mars, et résolu le 6 mars. Dans ma table de référence, j'aurais besoin d'obtenir ce résultat :

ID_Anomalie ; ID_DATE
6 ; 02/03/2011
6 ; 03/03/2011
6 ; 04/03/2011
6 ; 05/03/2011
6 ; 06/03/2011

Si le bug n'était pas résolu au jour d'aujourd'hui, j'obtiendrais autant de ligne que de nombre de jours entre la date de création du bug et aujourd'hui.

Cette table de référence doit être alimentée tous les jours, automatiquement (c'est pour cela que j'ai créé une procédure stockée, afin de pouvoir automatiser son lancement).

[ced]

OK, ça commence à être plus clair.
Et comment sait-on qu'un bug est résolu (sans passer par les dates) ?

ced

[Nimp0rtequi]

L'outil utilisé pour la gestion des anomalies est Mantis (peut-être tu connais).
Pour qu'un bug soit considéré comme résolu, le développeur qui corrige le bug le fait savoir à travers cet outil.
Dans ma base, le statut 'résolu' est traduit par la valeur '80' dans la colonne new_value de la table mantis_bug_history_table

Code :

1
2
3
4
SELECT STR_TO_DATE(FROM_UNIXTIME(min(date_modified), '%d/%m/%Y'), '%d/%m/%Y') INTO Date_resolution
			FROM mantis_bug_history_table
			WHERE bug_id = x AND field_name='status' AND new_value='80'
			GROUP BY bug_id;

Les tables mantis_bug_history_table et mantis_bug_table sont liées de la sorte : mantis_bug_table.id = mantis_bug_history_table.bug_id

Si, pour le bug numéro 'x', il n'y a pas de date de résolution, alors c'est qu'il n'est pas encore résolu (logique), je voudrais donc insérer dans la table BugActif l'ID numéro 'x' et toutes les dates à partir de la création de ce bug, jusqu'à la date du jour.

[ericd69]

La solution est très simple en fait...

vous devez sauvegarder l'état du booléen avant l'utilisation du curseur interne et le restituer après comme dans cet exemple:

Code sql :

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
-- uniquement pour le test
DROP DATABASE IF EXISTS hotels;
CREATE DATABASE hotels DEFAULT character SET utf8 collate utf8_general_ci;
-- on sélectionne la bd quoi qu'il arrive pour bien travailler avec elle par défaut
USE hotels; -- à toi d'adapter
 
-- on définit les table et on les remplit uniquement pour le test
CREATE TABLE hotel(
	id		int(4) NOT NULL AUTO_INCREMENT,
	nom	varchar(255) NOT NULL,
	constraint pk_hotel PRIMARY KEY(id)
)engine=myisam AUTO_INCREMENT=1;
CREATE TABLE personne(
	id		int(4) NOT NULL AUTO_INCREMENT,
	nom	varchar(255) NOT NULL,
	constraint pk_personne PRIMARY KEY(id)
)engine=myisam AUTO_INCREMENT=1;
-- la table de liaison, je ne mets pas de contrainte dessus car je la crée avec le moteur myisam qui ne s'en occupe pas
CREATE TABLE personne_a_dormi(
	id				int(4) NOT NULL AUTO_INCREMENT,
	idPersonne 	int(4) NOT NULL,
	idHotel		int(4) NOT NULL,
	constraint pk_personne_a_dormi PRIMARY KEY(id)
)engine=myisam AUTO_INCREMENT=1;
 
INSERT INTO hotel(nom)VALUES("hotel1"),("hotel2"),("hotel3");
INSERT INTO personne(nom)VALUES("personne1"),("personne2"),("personne3"),("personne4");
INSERT INTO personne_a_dormi(idPersonne,idHotel)VALUES(1,1),(1,2),(2,2),(3,1),(3,3),(4,1),(4,2),(4,3);
 
-- on définit la procédure stockée
DROP procedure IF EXISTS recapitulatif;
delimiter |
CREATE procedure recapitulatif()
begin
	declare ligne text DEFAULT "personne";
	declare tmp varchar(255);
	declare done,test bool;
	declare cpers,chotel int(4);
	declare colonnes cursor FOR SELECT nom FROM hotel;
	declare lignes cursor FOR SELECT id,nom FROM personne;
	declare lithotel cursor FOR SELECT id FROM hotel;
	declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	-- on crée l'entête
	open colonnes;
	SET done=false;
	repeat
		fetch colonnes INTO tmp;
		IF NOT done then
			SET ligne=concat(ligne,",",tmp);
		end IF;
	until done end repeat;
	close colonnes;
	SELECT ligne AS entete;
	SET done=false;
	open lignes;
	repeat
		fetch lignes INTO cpers,tmp;
		IF NOT done then
			SET test=done;
			SET ligne=tmp;
			open lithotel;
			repeat
				fetch lithotel INTO chotel;
				IF NOT done then
					SELECT IF(count(id)>0,"Oui","Non") INTO tmp FROM personne_a_dormi WHERE idPersonne=cpers AND idHotel=chotel;
					SET ligne=concat(ligne,",",tmp);
				end IF;
			until done end repeat;
			close lithotel;
			SELECT ligne;
			SET done=test;
		end IF;
	until done end repeat;
	close lignes;
end|
delimiter ;
-- fin de la procédure stockée
 
-- on teste en appelant la procédure
call recapitulatif();