Discussion :

[Lolitaaa]

Bonjour,
je maitrise pas bien le SQL, je sais faire une boucle simple avec un compteur,
mais comment faire pour une boucle sur une liste de résultats déjà retournée par une requête, ex :

je fais

select cu_id from table1 where criteria1 and criteria2;

après pour tous les cu_id retournés je veux lancer des update, comment faire ce genre de boucle??

Merci

[Waldar]

En SQL justement on ne fait pas de boucle, on traite les données en tant qu'un seul ensemble.

Expliquez-nous votre besoin complet (dans la limite du raisonnable) !

[Lolitaaa]

C'est bon je pense avoir trouvé la bonne solution :

j'utiliserai un curseur comme suivant:

Code :

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

1
2
3
4
5
6
 
BEGIN
FOR indice IN requete sql LOOP
/* traitement */
END LOOP;
END;

[Lolitaaa]

ma solution est la suivante :

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
 
for contractToUpdate in (
SELECT co_id
  FROM pr_serv_status_hist c1
 WHERE sncode = 46
   AND histno = (SELECT MAX (histno)
                   FROM pr_serv_status_hist c2
                  WHERE c1.co_id = c2.co_id AND c1.status = 'O')
   AND NOT EXISTS (SELECT *
                     FROM mdsrrtab
                    WHERE co_id = c1.co_id)
)
 
loop
 
end loop;

mais sur TOAD ça me sort une erreur "Unrecognized command or Statement"
est ce le curseur "contractToUpdate" faut il le déclarer qque part? Ou autre chose??

[Lolitaaa]

En SQL justement on ne fait pas de boucle, on traite les données en tant qu'un seul ensemble.

Expliquez-nous votre besoin complet (dans la limite du raisonnable) !

comme indiqué dans ma réponse, j'ai besoin de gérer un curseur danslequel je mettrai les valeurs récupérées, mais ma question est ce le remplissage se fait direct? ou faut il passer par une variable?

je connais pas les curseurs

[Waldar]

Que voulez-vous faire de vos co_id une fois récupérés ?

[Lolitaaa]

comme ci-dessous je veux enchainer des mises à jour :

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
 
for contractToUpdate in (SELECT co_id
FROM   pr_serv_status_hist C1
WHERE  sncode = 46 
AND    histno = (SELECT max(histno) 
               FROM   pr_serv_status_hist C2
               WHERE  C1.co_id = C2.co_id 
			   AND C1.STATUS='O')
AND NOT EXISTS (select * from mdsrrtab where co_id=C1.co_id))
 
loop
 
delete from contr_basic_services_cug where sncode=46 and co_id=contractToUpdate.co_id;
 
delete from contr_services_cug where sncode=46 and co_id=contractToUpdate.co_id ;
 
delete from pr_serv_spcode_hist where sncode=46 and co_id=contractToUpdate.co_id ;
 
delete from  pr_serv_status_hist where sncode=46 and co_id=contractToUpdate.co_id ;
 
delete from contr_services_bsg where sncode=46 and co_id=contractToUpdate.co_id ;
 
delete from pr_serv_spcode_hist where sncode=46 and co_id=contractToUpdate.co_id ;
 
delete from  pr_serv_status_hist where sncode=46 and co_id=contractToUpdate.co_id ;
 
delete from contr_services_bsg where sncode=46 and co_id=contractToUpdate.co_id ;
 
delete from parameter_value where sncode=46 and co_id=contractToUpdate.co_id   ;
 
delete from profile_service where sncode=46 and co_id=contractToUpdate.co_id  ;
 
delete from contract_service where sncode=46 and co_id=contractToUpdate.co_id  ;
 
end loop;

je sais pas si ça marche pas car :
1- je connais mal comment ça marche les curseurs?? (faut il le déclarer)
2- ou c'est le remplissage du curseur?

Merci

[CinePhil]

Si les 'contractToUpdate' sont uniques dans la table 'contr_basic_services_cug', la requête suivante devrait suffire à répondre au besoin ; pas besoin de boucle, c'est SQL qui s'en charge tout seul grâce au IN :

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
DELETE FROM contr_basic_services_cug
WHERE contractToUpdate IN (
  SELECT co_id
  FROM pr_serv_status_hist C1
  WHERE sncode = 46 
    AND histno = (
      SELECT max(histno) 
      FROM pr_serv_status_hist C2
      WHERE C1.co_id = C2.co_id 
    AND C1.STATUS='O'
    )
    AND NOT EXISTS (
      SELECT * 
      FROM mdsrrtab 
      WHERE co_id=C1.co_id
    )
)

S'ils ne sont pas uniques, il faut peut-être maintenir la condition supplémentaire 'sncode=46' :

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
DELETE FROM contr_basic_services_cug
WHERE contractToUpdate IN (
  SELECT co_id
  FROM pr_serv_status_hist C1
  WHERE sncode = 46 
    AND histno = (
      SELECT max(histno) 
      FROM pr_serv_status_hist C2
      WHERE C1.co_id = C2.co_id 
    AND C1.STATUS='O'
    )
    AND NOT EXISTS (
      SELECT * 
      FROM mdsrrtab 
      WHERE co_id=C1.co_id
    )
)
  AND sncode=46

[Lolitaaa]

OK merci
mais faut il déclarer qque part contractToUpdate ou pas ??

Merci

[Lolitaaa]

Si les 'contractToUpdate' sont uniques dans la table 'contr_basic_services_cug', la requête suivante devrait suffire à répondre au besoin ; pas besoin de boucle, c'est SQL qui s'en charge tout seul grâce au IN :

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
DELETE FROM contr_basic_services_cug
WHERE contractToUpdate IN (
  SELECT co_id
  FROM pr_serv_status_hist C1
  WHERE sncode = 46 
    AND histno = (
      SELECT max(histno) 
      FROM pr_serv_status_hist C2
      WHERE C1.co_id = C2.co_id 
    AND C1.STATUS='O'
    )
    AND NOT EXISTS (
      SELECT * 
      FROM mdsrrtab 
      WHERE co_id=C1.co_id
    )
)

S'ils ne sont pas uniques, il faut peut-être maintenir la condition supplémentaire 'sncode=46' :

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
DELETE FROM contr_basic_services_cug
WHERE contractToUpdate IN (
  SELECT co_id
  FROM pr_serv_status_hist C1
  WHERE sncode = 46 
    AND histno = (
      SELECT max(histno) 
      FROM pr_serv_status_hist C2
      WHERE C1.co_id = C2.co_id 
    AND C1.STATUS='O'
    )
    AND NOT EXISTS (
      SELECT * 
      FROM mdsrrtab 
      WHERE co_id=C1.co_id
    )
)
  AND sncode=46

En terme de performance c'est pas mieux de retrouver ces co_id et enchainer les delete d'un seul coup??

Merci

[Waldar]

Je pense que le plus rapide serait d'insérer tous vos ID dans une table de travail, et de supprimer depuis celle-ci.

Par exemple :

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
INSERT INTO TMP_SUPPR_CO_ID (co_id)
SELECT co_id
  FROM pr_serv_status_hist c1
 WHERE sncode = 46
   AND histno = (SELECT MAX (histno)
                   FROM pr_serv_status_hist c2
                  WHERE c1.co_id = c2.co_id AND c1.STATUS = 'O')
   AND NOT EXISTS (SELECT *
                     FROM mdsrrtab
                    WHERE co_id = c1.co_id);
 
DELETE FROM contr_basic_services_cug
WHERE sncode = 46 AND co_id IN (select co_id from TMP_SUPPR_CO_ID);
 
DELETE FROM contr_services_cug
WHERE sncode = 46 AND co_id IN (select co_id from TMP_SUPPR_CO_ID);
 
-- Et cetera.

[Lolitaaa]

Je pense que le plus rapide serait d'insérer tous vos ID dans une table de travail, et de supprimer depuis celle-ci.

Par exemple :

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
INSERT INTO TMP_SUPPR_CO_ID (co_id)
SELECT co_id
  FROM pr_serv_status_hist c1
 WHERE sncode = 46
   AND histno = (SELECT MAX (histno)
                   FROM pr_serv_status_hist c2
                  WHERE c1.co_id = c2.co_id AND c1.STATUS = 'O')
   AND NOT EXISTS (SELECT *
                     FROM mdsrrtab
                    WHERE co_id = c1.co_id);
 
DELETE FROM contr_basic_services_cug
WHERE sncode = 46 AND co_id IN (select co_id from TMP_SUPPR_CO_ID);
 
DELETE FROM contr_services_cug
WHERE sncode = 46 AND co_id IN (select co_id from TMP_SUPPR_CO_ID);
 
-- Et cetera.

Cette solution le convient car à un certain moment la table de laquelle je récupère les id est mise à jour alors que jai besoin de id!

sinon une autre question c'est quoi la mieulleure façon pour créer et supprimer une table temporaire dans mon script
je maitrise pas le sql

[Lolitaaa]

Je pense que le plus rapide serait d'insérer tous vos ID dans une table de travail, et de supprimer depuis celle-ci.

Par exemple :

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
INSERT INTO TMP_SUPPR_CO_ID (co_id)
SELECT co_id
  FROM pr_serv_status_hist c1
 WHERE sncode = 46
   AND histno = (SELECT MAX (histno)
                   FROM pr_serv_status_hist c2
                  WHERE c1.co_id = c2.co_id AND c1.STATUS = 'O')
   AND NOT EXISTS (SELECT *
                     FROM mdsrrtab
                    WHERE co_id = c1.co_id);
 
DELETE FROM contr_basic_services_cug
WHERE sncode = 46 AND co_id IN (select co_id from TMP_SUPPR_CO_ID);
 
DELETE FROM contr_services_cug
WHERE sncode = 46 AND co_id IN (select co_id from TMP_SUPPR_CO_ID);
 
-- Et cetera.

Pour gérer la table temporaire est ce suffisant de faire comme ci-dessous :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
-- TO DO create temporary table : TMP_SUPPR_CO_ID
 
CREATE GLOBAL TEMPORARY TABLE TMP_SUPPR_CO_ID
(
	co_id	NUMBER
) ON COMMIT PRESERVE ROWS ;
 
-- TO DO delete temporary table: TMP_SUPPR_CO_ID
DROP TABLE TMP_SUPPR_CO_ID;

[Lolitaaa]

alors mon script final est comme suivant :

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
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
 
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
 
 
-- TO DO create temporary table : TMP_SUPPR_CO_ID
 
CREATE GLOBAL TEMPORARY TABLE TMP_SUPPR_CO_ID
(
	co_id	NUMBER
) ON COMMIT PRESERVE ROWS ;
 
 
-- récupérer les contrats ayant un état incohérent : Service "en attente" et sans requête dans mdsrrtab 
 
INSERT INTO TMP_SUPPR_CO_ID (co_id)
SELECT co_id
  FROM pr_serv_status_hist c1
 WHERE sncode = 46
   AND histno = (SELECT MAX (histno)
                   FROM pr_serv_status_hist c2
                  WHERE c1.co_id = c2.co_id AND c1.STATUS = 'O')
   AND NOT EXISTS (SELECT *
                     FROM mdsrrtab
                    WHERE co_id = c1.co_id);
 
-- delete from contr_basic_services_cug
DELETE FROM contr_basic_services_cug
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
-- table contr_services_cug
-- delete from contr_services_cug
 
DELETE FROM contr_services_cug
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
-- table pr_serv_spcode_hist
--delete from pr_serv_spcode_hist 
 
DELETE FROM pr_serv_spcode_hist
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
 
-- pr_serv_status_hist
-- delete from  pr_serv_status_hist
 
DELETE FROM pr_serv_status_hist
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
--contr_services_bsg
--delete from contr_services_bsg 
 
DELETE FROM contr_services_bsg
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
 
-- pr_serv_spcode_hist
-- delete from pr_serv_spcode_hist 
DELETE FROM pr_serv_spcode_hist
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
 
--pr_serv_status_hist
--delete from  pr_serv_status_hist where sncode=46 and co_id=contractToUpdate.co_id ;
DELETE FROM pr_serv_status_hist
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
 
-- contr_services_bsg
--delete from contr_services_bsg where sncode=46 and co_id=contractToUpdate.co_id ;
DELETE FROM contr_services_bsg
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
 
 
-- parameter_value
--delete from parameter_value where sncode=46 and co_id=contractToUpdate.co_id   ;
DELETE FROM parameter_value
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
-- profile_service
--delete from profile_service where sncode=46 and co_id=contractToUpdate.co_id  ;
DELETE FROM profile_service
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
 
-- contract_service
--delete from contract_service where sncode=46 and co_id=contractToUpdate.co_id  ;
DELETE FROM contract_service
WHERE sncode = 46 AND co_id IN (SELECT co_id FROM TMP_SUPPR_CO_ID);
 
-- TO DO delete temporary table: TMP_SUPPR_CO_ID
DROP TABLE TMP_SUPPR_CO_ID;
 
COMMIT;
 
 
EXIT;

en le lançant sur Toad j'ai une erreur "Invalid Character" au niveau de la création de la table temporaire!!