Discussion :

[GoLDoZ]

bonjour

une petite question me turlupine concernant les curseurs

j'ai 2 tables: TABLE1(col1, col2) et TABLE2(col1,col2)

supposons que j'ai un curseur déclaré comme tel

Code :

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

1
2
3
4
5
6
CURSOR cur_exemple(p_param) IS
 SELECT t1.col1
  FROM TABLE1 t1, TABLE2 t2
 WHERE t2.col1 = t1.col2
 AND t2.col1=p_param
FOR UPDATE;

si dans le code je fais:

Code :

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

DELETE WHERE CURRENT OF cur_exemple;

qu'est-ce que j'efface?
la ligne de TABLE1? la ligne de TABLE2 correspondante?

et dans ce cas:

Code :

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

1
2
3
4
5
CURSOR cur_exemple IS
 SELECT t1.col1, t2.col1
  FROM TABLE1 t1, TABLE2 t2
 WHERE t2.col1 = t1.col2
FOR UPDATE;

ou bien manque-t-il une précision dans le FOR UPDATE?

merci de m'éclairer sur ce point

[SheikYerbouti]

Vous agissez sur le ROWID correspondant au Select.

Dans le 1er cas, il s'agit de la table T1
Dans le 2ème, cela ne m'étonnerait pas que vous obteniez une erreur.

Pourquoi ne faites-vous pas l'essai ?

[GoLDoZ]

Vous agissez sur le ROWID correspondant au Select.

si je traduis bien, cela ne peut concerner qu'une seule table

Dans le 2ème, cela ne m'étonnerait pas que vous obteniez une erreur.

...une erreur sur le DELETE ou sur la déclaration?

la question finale est:
peut-on ramener les colonnes de différentes tables dans un curseur en vue de supprimer les lignes de l'une d'entre elles?

(je fais le test dès que je peux)

[SheikYerbouti]

C'est le même problème que pour la mise à jour des vues constituées de plus d'une table. Si la requête ne permet pas d'identifier clairement la ligne de la table, vous obtiendrez une erreur.

[GoLDoZ]

mes tables test1 et test2 contiennent:
test1: (1;1) et (1;2)
test2: (2;1) et (3;1)

j'ai fait le test 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
SQL> DECLARE
  2  CURSOR cur_exemple IS
  3  SELECT t1.col1, t2.col2
  4    FROM TEST1 t1, TEST2 t2
  5   WHERE t2.col1 = t1.col2
  6  FOR UPDATE;
  7  ls_col1 VARCHAR2(100);
  8  ls_col2 VARCHAR2(100);
  9  BEGIN
 10  OPEN cur_exemple;
 11  LOOP
 12  FETCH cur_exemple INTO ls_col1, ls_col2;
 13  EXIT WHEN cur_exemple%NOTFOUND;
 14  dbms_output.put_line(ls_col1 ||'--'|| ls_col2);
 15   DELETE FROM TEST1 WHERE CURRENT OF cur_exemple;
 16   DELETE FROM TEST2 WHERE CURRENT OF cur_exemple;
 17  END LOOP;
 18  CLOSE cur_exemple;
 19  COMMIT;
 20  END;
 21  /
1--1
SQL>

il m'a donc retourné '1--1' sans erreur, mais n'a rien effacé du tout...
pourquoi, d'après vous, il ne m'a pas sorti d'erreur, mais n'a pourtant effacé aucune ligne?


ps: j'en profite pour vous remercier, Sheik, ainsi que les autres rédacteurs, pour les tutoriaux, FAQ et autres précieux documents que j'utilise depuis plusieurs mois (actuellement, celui sur l'optimisation m'est très utile )

[terence]

La raison de ce comportement est la suivante:

la clause "where current of" se content d'automatiser un update/delete par rowid (comme cela a déjà été dit).

Dès lors lorsque tu références 2 tables dans ta requêtes comme c'est le cas ici avec test1 et test2 tout en ne précisant pas lesquelles sont "updatables" : tu as utilisé la clause "for update" par défaut qui lock les lignes des 2 tables, ou bien en précisant une clause du type "for update of test1.col1, test2.col1" qui lock explicitement les lignes des 2 tables : oracle ne sait pas quel rowid utiliser et les update/delete (delete dans ton cas) ne font rien.

si tu voulais deleter juste dans une table, cela marche:

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
 
me@myOracle> select * from test1;
 
      COL1       COL2
---------- ----------
         1          2
         1          1
 
me@myOracle> select * from test2;
 
      COL1       COL2
---------- ----------
         3          1
         2          1
 
me@myOracle> select t1.col1, t2.col2 from test1 t1, test2 t2 where t2.col1=t1.col2;
 
      COL1       COL2
---------- ----------
         1          1
 
me@myOracle> declare
  2      cursor l_cur is  select t1.col1, t2.col2 from test1 t1, test2 t2 where t2.col1=t1.col2 for update of t1.col1;
  3      begin
  4         for cur_rec in l_cur
  5         loop
  6                delete test1 where current of l_cur;
  7         end loop;
  8         commit;
  9      end;
 10  /
 
PL/SQL procedure successfully completed.
 
me@myOracle>  select * from test1;
 
      COL1       COL2
---------- ----------
         1          1
 
me@myOracle> select * from test2;
 
      COL1       COL2
---------- ----------
         3          1
         2          1

Si tu veux vraiment faire le delete sur les 2 tables, laisse tomber l'utilisation de la clause "where current of" et préfère l'utilisation des rowid qui vont bien, ton code serait alors:

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
 
me@myOracle> select * from test1;
 
      COL1       COL2
---------- ----------
         1          2
         1          1
 
me@myOracle> select * from test2;
 
      COL1       COL2
---------- ----------
         3          1
         2          1
 
me@myOracle> select t1.col1, t2.col2 from test1 t1, test2 t2 where t2.col1=t1.col2;
 
      COL1       COL2
---------- ----------
         1          1
 
me@myOracle> declare
  2      cursor l_cur is  select t1.rowid test1_rowid, t1.col1,
  3                                 t2.rowid test2_rowid, t2.col2
  4                              from test1 t1, test2 t2
  5                              where t2.col1=t1.col2
  6                          for update of t1.col1, t2.col1;
  7      begin
  8         for cur_rec in l_cur
  9         loop
 10                delete test1 where rowid=cur_rec.test1_rowid;
 11                delete test2 where rowid=cur_rec.test2_rowid;
 12         end loop;
 13         commit;
 14      end;
 15  /
 
PL/SQL procedure successfully completed.
 
 
me@myOracle> select * from test1;
 
      COL1       COL2
---------- ----------
         1          1
 
me@myOracle> select * from test2;
 
      COL1       COL2
---------- ----------
         3          1

[GoLDoZ]

merci terence!