Discussion :

[ncode]

Bonjour à tous,

Je sollicite votre aide car je ne trouve l'information nulle part....

En PL/SQL, je souhaite réaliser des curseurs imbriqués mais mon problème se situe au niveau du fetch.

Tout d'abord je déclare mon curseur - jusque là pas de soucis :

Code :

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

1
2
3
4
5
6
7
8
9
TYPE cur_type_test IS REF CURSOR;
cur_test cur_type_test;
 
CURSOR C1 IS
    SELECT t1.champs1,...,t1.champsN, CURSOR (
                  SELECT t2.champs1,...,t2.champsN, CURSOR (
                                                          SELECT t3.champs1,...,t3.champsN FROM t3 WHERE t3.clé = t2.clé)
                 FROM t2.clé = t1.clé)
FROM t1;

Ces déclarations étant réalisées, ensuite je rentre dans le corps :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
BEGIN
OPEN c1;
LOOP
 FETCH c1 INTO v_t1chamsp1,..,v_t1chamspN, cur_test
 EXIT WHEN c1%notfound;
    LOOP
    FETCH cur_test INTO v_t2chamsp1,..,v_t2chamspN, -- ! Ici est mon problème !
    EXIT WHEN cur_test%notfound;
       LOOP
        FETCH ??? INTO v_t3chamsp1,..,v_t3chamspN,
        EXIT WHEN ??%notfound;

Je sais le faire marcher pour deux niveaux par contre je n'arrive pas à le faire fonctionner pour plus...

Est-ce que cela est possible ?

Merci pour votre aide.

[McM]

Je ne comprends pas le curseur: ça marche en PL, ça ?

Code :

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

1
2
3
4
5
SELECT t1.champs1,...,t1.champsN, CURSOR (
SELECT t2.champs1,...,t2.champsN, CURSOR (
SELECT t3.champs1,...,t3.champsN FROM t3 WHERE t3.clé = t2.clé)
FROM t2.clé = t1.clé)
FROM t1

[Waldar]

En PL/SQL, je souhaite réaliser des curseurs imbriqués.

C'est en général une mauvaise idée, tueuse de performance.
Que souhaitez-vous faire ?

[ncode]

Merci pour vos réponses.

Tout d'abord, pour répondre à la première question, cela fonctionne oui et ensuite je pense que l'avantage de passer comme ceci c'est qu'on peut récupérer toute l'information de nos tables liées en mémoire avec un seul accès mémoire avec l'utilisation de référence ( REF CURSOR).

Cependant, je n'ai pas encore pu le tester. Il est possible d'avoir d'autre solution plus pertinente.

Ce que je souhaite, c'est pouvoir avoir en mémoire mes informations concernant les tables liées et pouvoir réaliser des traitements.

Ncode

[Invité]

Tout d'abord, pour répondre à la première question, cela fonctionne oui et ensuite je pense que l'avantage de passer comme ceci c'est qu'on peut récupérer toute l'information de nos tables liées en mémoire avec un seul accès mémoire avec l'utilisation de référence ( REF CURSOR).

Je pense que c'était ironique comme question, car que votre syntaxe est original.

[ncode]

Cette syntaxe "original" existe dans la documentation ORACLE PLSQL 10g mais avec un seul curseur imbriqué.
Mon problème réside dans le fait que j'ai 4 tables, ce qui implique 3 curseurs imbriqués.

[McM]

Je pense que c'était ironique comme question, car que votre syntaxe est original.

Même pas.. j'avais juste jamais vu cette syntaxe.. en même temps voyant le code qu'il faut faire pour lire les données, je comprends

Et puis niveau debugage, c'est un peu dur à lire.

Je préfère la bonne vieille méthode de 3 curseurs FOR LOOP imbriqués.

[dgi77]

Je confirme que ça marche pour l'avoir mis en place sur une version 9i il y a 3 ans (avec 4 niveaux d'imbrication), et surtout c'est bien plus performant que la fameuse bonne vieille méthode qui ouvre et ferme des milliers de curseurs.
Je ne sais pas comment Oracle gère ça lors de l'exécution, mais j'ai réduit mes temps de traitements d'un rapport de 1 à 15 depuis que j'ai réécrit mon code de cette manière ! Mon traitement quotidien n'avait pas le temps de tourner dans la nuit auparavant, grâce aux REF CURSOR, c'est résolu.
Il faut savoir que ça m'a imposé de fortement augmenter la valeur du paramètre open_cursors (passé à 100 000 contre 300 par défaut) : si des DBA peuvent fournir des explications, ça m'intéresse, car dans mon entreprise, les DBA ne se sont jamais interessés à cela, si ce n'est pour critiquer par méconnaissance, un peu comme les commentaires "originaux" de cette discussion. Messieurs, ce n'est pas parce que vous ne connaissez pas que ça ne marche pas. Maintenant, si vous avez encore plus performant, je suis preneur.

A l'origine, cela a été mis en place par Oracle afin de faciliter la génération de fichiers XML. Mais on peut récupérer les données dans des collections, avec le BULK COLLECT qui va bien dans le curseur de plus bas niveau, et ça dépote !
Cela permet de récupérer les données brutes en les structurant, en les hiérarchisant dans des collections, et de déplacer la lourdeur des règles fonctionnelles de la requête du curseur vers les collections de données chargées en mémoire.

[mnitu]

...
En PL/SQL, je souhaite réaliser des curseurs imbriqués mais mon problème se situe au niveau du fetch.
...
Je sais le faire marcher pour deux niveaux par contre je n'arrive pas à le faire fonctionner pour plus...

Est-ce que cela est possible ?

Merci pour votre aide.

Voilà un 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
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
 
Declare
  Cursor crs Is Select location_id, street_address, postal_code, city, 
                     Cursor(Select department_name, manager_id, 
                                   Cursor (Select e.first_name, e.last_name, e.salary
                                             From hr.employees e
                                            Where e.department_id = d.department_id 
                                          )
                              From hr.departments d
                             Where d.location_id = l.location_id)                
              From hr.locations l;
  --
  l_location_id    hr.locations.location_id%Type;
  l_street_address hr.locations.street_address%Type;
  l_postal_code    hr.locations.postal_code%Type;
  l_city           hr.locations.city%Type;
  rcrs_dept        sys_refcursor;
  --
  l_department_name  hr.departments.department_name%Type;
  l_manager_id       hr.departments.manager_id%Type;
  rcrs_emp         sys_refcursor;  
  --
  l_first_name     hr.employees.first_name%Type; 
  l_last_name      hr.employees.last_name%Type;
  l_salary         hr.employees.salary%Type;
Begin
  Open crs;
  Loop
    Fetch crs Into l_location_id, l_street_address, l_postal_code, l_city, rcrs_dept;
    Exit When crs%NOTFOUND;
    Dbms_Output.put_line('Location :'||l_location_id);
    Loop
      Fetch rcrs_dept Into l_department_name, l_manager_id, rcrs_emp;
      Exit When rcrs_dept%NOTFOUND;
      Dbms_Output.put_line('  Department :'||l_department_name);
      Loop
        Fetch rcrs_emp Into l_first_name, l_last_name, l_salary;
        Exit When rcrs_emp%NOTFOUND;
        Dbms_Output.put_line('    Employee :'||l_first_name);
      End Loop;
    End Loop;
  End Loop;  
  Close crs;
End;

[ncode]

Bonjour,

Merci beaucoup pour vos réponses très utiles !

J'ai cependant une dernière question (qui fait suite à l'implémentation de ces curseurs)

Lorsque je réalise au sein de mon curseur la jointure avec deux champs de type NCHAR, cela ne fonctionne pas.

==> Est-ce que ce comportement vous surprends ? ( par contre cela fonctionne très bien lorsque je réalise une requête normale).

Est il nécessaire d'avoir des champs de type NUMBER ?

Merci encore pour votre aide

Bonne journée

Ncode

[cala]

Bonjour,
Les curseur imbriqué fonctionne. Cependant je déconseil également l'utilisation car les performance sont nettement défavorable.

Si la raison est de monter une requête SQL, je propose une solution qui me paraît plus pertinence, l'utilisation de sys_refcursor. De plus la requête comporterais des jointures qui optimiserais la lecture des données et l'utilisation des indexes.

Tu crées ta requête dans une variable de type varchar2 et ensuit tu l'ouvre à l'aide de "open .. for ... ".

Example :
----------

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
 
Declare
 
  crs sys_refcursor;
  sql_stmt VARCHAR2(32727) := 'SELECT l.location_id, l.street_address, l.postal_code, l.city,d.department_name, d.manager_id,e.first_name, e.last_name, e.salary'
                            ||'  FROM hr.locations l'
                            ||'  INNER JOIN hr.departments d ON d.location_id = l.location_id'
                            ||'  INNER JOIN hr.employees e on e.department_id = d.department_id';
  --
  l_location_id    hr.locations.location_id%Type;
  l_street_address hr.locations.street_address%Type;
  l_postal_code    hr.locations.postal_code%Type;
  l_city           hr.locations.city%Type;
  rcrs_dept        sys_refcursor;
  --
  l_department_name  hr.departments.department_name%Type;
  l_manager_id       hr.departments.manager_id%Type;
  rcrs_emp         sys_refcursor;  
  --
  l_first_name     hr.employees.first_name%Type; 
  l_last_name      hr.employees.last_name%Type;
  l_salary         hr.employees.salary%Type;
Begin
 
 
  Open crs for sql_stmt;
  Loop
    Fetch crs INTO l_location_id, l_street_address, l_postal_code, l_city, rcrs_dept;
    Exit When crs%NOTFOUND;
    Dbms_Output.put_line('Location :'||l_location_id);
    Loop
      Fetch rcrs_dept INTO l_department_name, l_manager_id, rcrs_emp;
      Exit When rcrs_dept%NOTFOUND;
      Dbms_Output.put_line('  Department :'||l_department_name);
      Loop
        Fetch rcrs_emp INTO l_first_name, l_last_name, l_salary;
        Exit When rcrs_emp%NOTFOUND;
        Dbms_Output.put_line('    Employee :'||l_first_name);
      End Loop;
    End Loop;
  End Loop;  
  Close crs;
End;

[mnitu]

Avez vous testé votre example ?

Code :

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

1
2
3
4
 
 
ORA-00932: types de données incohérents ; attendu : - ; obtenu : -
ORA-06512: à ligne 27

[cala]

Bonjour,
Malheureusement pas.
Je n'ai pas la base exemple installée où je me trouve et sans droit de l'installé.
Mais c'est un exemple pour démontrer d'autres méthodes.

En bref, les curseurs imbriqués fonctionnent très bien mais suivant ces besoins on perd beaucoup de performance. J'ai fait des tests sur une de nos base de données, et j'ai des performances de 10 fois plus lent pour les curseurs imbriqué que une simple requête inner join et on traite l'info ensuite.


Cala