Curseurs imbriqués

**ncode** · 28/07/2010, 10h43

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** · 28/07/2010, 10h52

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** · 28/07/2010, 10h56

Envoyé par ncode

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** · 28/07/2010, 11h26

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é · 28/07/2010, 14h51

Envoyé par ncode

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** · 28/07/2010, 15h58

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** · 28/07/2010, 17h56

Envoyé par Jerome_Mtl

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** · 02/08/2010, 16h21

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** · 03/08/2010, 10h03

Envoyé par ncode

...
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** · 31/08/2010, 12h13

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** · 02/09/2010, 10h57

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** · 02/09/2010, 11h46

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** · 02/09/2010, 14h20

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

**mnitu** · 02/09/2010, 17h10

Envoyé par 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

Bref, une bonne jointure en SQL est de loin supérieure à une jointure fait en PL/SQL il n’y pas de doute pour ça. Mais, nul besoin de passer par des sql dynamique dans ce cas !

**cala** · 03/09/2010, 17h44

Envoyé par mnitu

Bref, une bonne jointure en SQL est de loin supérieure à une jointure fait en PL/SQL il n’y pas de doute pour ça. Mais, nul besoin de passer par des sql dynamique dans ce cas !

Absolument d'accord.
Finalment ncode, avons-nous pu t'aider ?

Cala

**ncode** · 06/09/2010, 11h12

Salut Cala,

Tous vos avis me permettent d'avoir plusieurs approches.
Je suis actuellement en train de tester tous cela.
Je vous ferai un retour sur les performances que j'aurai observer asap

Par contre je ne comprends toujours pas pourquoi lorsque je fais mes jointures avec des types en varchar2 dans mes curseurs imbriqués cela ne fonctionne pas ....

Merci beaucoup en tout cas.

@soon

Ncode

**cala** · 06/09/2010, 11h29

Envoyé par ncode

Par contre je ne comprends toujours pas pourquoi lorsque je fais mes jointures avec des types en varchar2 dans mes curseurs imbriqués cela ne fonctionne pas ....

Ncode

Salut,
Quel erreur as-tu ?
Si cela te retourne aucun résultat, vérifie la cohérence des données entre les différentes tables (ex. espace, accent, etc..).

voici un example que tu peux tester :

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
98
99
100
101
 
CREATE TABLE CLIENT_ORDER
  (
    PO_NUMBER     VARCHAR2(12) NOT NULL ENABLE       ,
    QTY        NUMBER(12,6)                          ,
    CONSTRAINT PK_MANUFACTURE_ORDER PRIMARY KEY (PO_NUMBER)
  )
;
/
 
CREATE TABLE SO_SHOP_ORDER
  (
    PO_NUMBER           VARCHAR2(12) NOT NULL       ,
    NUMOF               VARCHAR2(6) NOT NULL        ,
    QTE_OF              NUMBER(10,5)                ,
    CONSTRAINT PK_SO_SHOP_ORDER PRIMARY KEY (NUMOF),
    CONSTRAINT FK_CLIENT_ORDER FOREIGN KEY (PO_NUMBER) REFERENCES
      CLIENT_ORDER (PO_NUMBER) ON DELETE CASCADE ENABLE
  );
/
 
CREATE TABLE SO_SUBMIXING
  (
    NUMOF        VARCHAR2(6) NOT NULL        ,
    IDSM         VARCHAR2(12) NOT NULL       ,
    QTE_SM       NUMBER(10,5)                ,
    CONSTRAINT PK_SO_SUBMIXING PRIMARY KEY (NUMOF, IDSM) USING INDEX
    CONSTRAINT FK_SHOP_ORDER_SUBMIXING FOREIGN KEY (NUMOF) REFERENCES
      SO_SHOP_ORDER (NUMOF) ON DELETE CASCADE ENABLE,
  );
 
/
 
INSERT INTO CLIENT_ORDER VALUES('100106160',100);
INSERT INTO CLIENT_ORDER VALUES('100106161',2356.56);
COMMIT;
/
 
INSERT INTO SO_SHOP_ORDER VALUES('100160160','5621',50);
INSERT INTO SO_SHOP_ORDER VALUES('100160160','5622',50);
INSERT INTO SO_SHOP_ORDER VALUES('100160161','6001',785.52);
INSERT INTO SO_SHOP_ORDER VALUES('100160161','6002',785.52);
INSERT INTO SO_SHOP_ORDER VALUES('100160161','6003',785.52);
COMMIT;
/
 
INSERT INTO SO_SUBMIXING VALUES ('5621','3200',25);
INSERT INTO SO_SUBMIXING VALUES ('5621','3201',25);
INSERT INTO SO_SUBMIXING VALUES ('5622','3202',25);
INSERT INTO SO_SUBMIXING VALUES ('5622','3203',25);
INSERT INTO SO_SUBMIXING VALUES ('6001','1200',785.52);
INSERT INTO SO_SUBMIXING VALUES ('6002','1201',785.52);
INSERT INTO SO_SUBMIXING VALUES ('6003','1202',785.52);
COMMIT;
/
 
 
set serveroutput on;
Declare
  Cursor crs IS SELECT l.po_number,l.QTY,
                     Cursor(SELECT d.numof,d.QTE_OF,
                                   Cursor (SELECT e.idsm,e.qte_sm
                                             FROM so_submixing e
                                            WHERE e.numof = d.numof
                                          )
                              FROM so_shop_order d
                             WHERE d.po_number = l.po_number)                
              FROM CLIENT_ORDER l;
  --
  l_po_number    CLIENT_ORDER.po_number%Type;
  l_qty_po       CLIENT_ORDER.qty%Type;
  rcrs_dept        sys_refcursor;
  --
    l_numof  so_shop_order.numof%Type;
  l_qty_of       so_shop_order.qte_of%Type;
  rcrs_emp         sys_refcursor;  
  --
  l_idsm     so_submixing.idsm%Type; 
  l_qty_sm      so_submixing.qte_sm%Type;
Begin
  Open crs;
  Loop
    Fetch crs INTO l_po_number, l_qty_po, rcrs_dept;
    Exit When crs%NOTFOUND;
    Dbms_Output.put_line('Po_number :'||l_po_number);
    Dbms_Output.put_line('Qty :'||to_char(l_qty_po));
    Loop
      Fetch rcrs_dept INTO l_numof, l_qty_of, rcrs_emp;
      Exit When rcrs_dept%NOTFOUND;
      Dbms_Output.put_line('  Num Of :'||l_numof);
      Dbms_Output.put_line('  Qty Of :'||to_char(l_qty_of));
      Loop
        Fetch rcrs_emp INTO l_idsm, l_qty_sm;
        Exit When rcrs_emp%NOTFOUND;
        Dbms_Output.put_line('    IDSM :'||l_idsm);
        Dbms_Output.put_line('    Qty :'||to_char(l_qty_sm));
      End Loop;
    End Loop;
  End Loop;  
  Close crs;
End;

Cala

**ncode** · 06/09/2010, 18h46

Salut Cala,

Merci beaucoup pour ton exemple.
Grace à cela j'ai pu trouver mon problème : mes champs présents dans ma jointures étaient en NCHAR.
Je les ai passé en VARCHAR2 et cela fonctionne très bien.
Merci pour ton aide

Ncode

**cala** · 07/09/2010, 14h43

Ce fut un plaisir de t'aider.

A+

**dgi77** · 09/09/2010, 11h44

Envoyé par 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

On n'a jamais dit que les curseurs imbriqués étaient plus performants qu'une requête. Dès l'instant où tu arrives à résoudre ton problème fonctionnel par de l'ensembliste (une requête), dans 99% des cas ce sera plus performant que de passer par des curseurs, on est d'accord.

Sauf que des fois, le problème posé ne peut pas être résolu par une seule requête, même complexe. Dans ces cas-là, on est obligé d'utiliser des curseurs, et parfois des curseurs imbriqués, c'est-à-dire que tu fetches un premier curseur, puis ouvre un second en passant en paramètre une valeur récupérée par le premier, puis à chaque fetch du second, ouvre un troisième en passant en paramètre une valeur récupérée par le second, etc... ce qui est très coûteux en temps de traitement. Et bien dans ces cas-là, l'utilisation des REF CURSOR permet un gain de performances assez énorme par rapport à l'utilisation classique des curseurs (ouvre, ferme, ouvre, ferme, ...). Il y a des exemples dans cette discussion qui permettent de voir comment ça se code. Toujours est-il que Oracle semble arriver à regrouper les ouvertures de ces curseurs, je ne sais pas comment il se démerde, mais c'est bien plus performant.

Bref, je te compare curseurs indépendants imbriqués avec REF CURSOR, alors que toi tu compares curseurs avec ensembliste...

Curseurs imbriqués

PL/SQL Oracle

Discussions similaires

Partager

Partager