Introduction
Il est possible dans la partie SELECT d'un SELECT d'utiliser une sous-requête, qu'on appelle SELECT imbriqué, mais celle-ci doit obligatoirement retourner une seule valeur et une seule ligne. A noter que cette limitation n'existe pas pour les sous-requêtes dans les FROM et WHERE. Cette limite peut devenir très gênante, par exemple pour une relation entre deux tables trop complexe à écrire sous forme de jointure. Pour répondre à ce besoin, Oracle a inventé le CURSOR expression.


 

Points d'attention
NA.

 


 

Base de tests
N'importe quelle base Oracle. Testé sur une 12.1.

 



Exemples

Exemple classique de SELECTs imbriqués
Classiquement, des SELECTs imbriqués au niveau du SELECT ne doivent retourner qu'un seul enregistrement d'une seule colonne.     

          SQL> select sysdate, (select count(*) from dba_tables) AS "Nb tables", (select count(*) from dba_roles) AS "Nb roles" from dual;
          SYSDATE    Nb tables   Nb roles
          --------- ---------- ----------
          10-MAY-18    2648         94

Maintenant imaginons que l'on veuille afficher la liste des rôles associés à un utilisateur.
Avec une jointure nous obtenons le bon résultat MAIS il est possible de faire différemment, en utilisant un SELECT imbriqué!
     SQL> select u.USERNAME, r.granted_role, r.ADMIN_OPTION
     from dba_users u, dba_role_privs r
     where r.grantee = u.username and u.USERNAME in ('HR','HRREST','SCOTT','XFILES');
     order by u.USERNAME, r.granted_role;
     USERNAME               GRANTED_ROLE              ADM
     ------------------------------ ------------------------------ ---
     HR                   SELECT_CATALOG_ROLE          NO
     HR                   EXECUTE_CATALOG_ROLE          NO
     HR                   DBA                  NO
     SCOTT                   RESOURCE               NO
     SCOTT                   CTXAPP                  NO
     SCOTT                   SELECT_CATALOG_ROLE          NO
     SCOTT                   XFILES_USER              NO
     SCOTT                   CONNECT                  NO
     HRREST                   SELECT_CATALOG_ROLE          NO
     HRREST                   EXECUTE_CATALOG_ROLE          NO
     XFILES                   XFILES_USER              NO
     XFILES                   CONNECT                  NO
     XFILES                   RESOURCE               NO
     XFILES                   CTXAPP                  NO

     14 rows selected.

Les problèmes des SELECTs imbriquéss    

Dans le cas d'un SELECT imbriqué, on rencontrera les deux problèmes suivants.
Premier problème : trop de colonnes.

     SQL> select u.USERNAME, (select r.ADMIN_OPTION, r.granted_role  from dba_role_privs r where r.grantee = u.username) from dba_users u order by 1;
     select u.USERNAME, (select r.ADMIN_OPTION, r.granted_role  from dba_role_privs r where r.grantee = u.username) from dba_users u order by 1
                         *
     ERROR at line 1:
     ORA-00913: too many values
     
     
Deuxième problème : le SELECT contient une colonne MAIS celui-ci nous retourne trop de lignes.
     SQL> select u.USERNAME, (select r.ADMIN_OPTION from dba_role_privs r where r.grantee = u.username) from dba_users u order by 1;
     select u.USERNAME, (select r.ADMIN_OPTION from dba_role_privs r where r.grantee = u.username) from dba_users u order by 1
                         *
     ERROR at line 1:
     ORA-01427: single-row subquery returns more than one row


Le CURSOR expression : exemple simple    
Une solution est d'utiliser le CURSOR expression!
OK, l'affichage n'est pas terrible mais on voit bien la relation Père/Fils (ou Maître/Esclave) entre les deux SELECTs.
     SQL> select u.USERNAME, cursor(select granted_role, ADMIN_OPTION from dba_role_privs where grantee = u.username ) AS CURSEUR from dba_users u where u.USERNAME in ('HR','HRREST','SCOTT','XFILES') order by 1;
     USERNAME               CURSEUR
     ------------------------------ --------------------
     HR                   CURSOR STATEMENT : 2
     
     CURSOR STATEMENT : 2
     GRANTED_ROLE               ADM
     ------------------------------ ---
     SELECT_CATALOG_ROLE           NO
     EXECUTE_CATALOG_ROLE           NO
     DBA                   NO
     
    
     HRREST                   CURSOR STATEMENT : 2
     CURSOR STATEMENT : 2
     GRANTED_ROLE               ADM
     ------------------------------ ---
     SELECT_CATALOG_ROLE           NO
     EXECUTE_CATALOG_ROLE           NO
    
     
     SCOTT                   CURSOR STATEMENT : 2
     CURSOR STATEMENT : 2
     GRANTED_ROLE               ADM
     ------------------------------ ---
     RESOURCE               NO
     CTXAPP                   NO
     SELECT_CATALOG_ROLE           NO
     XFILES_USER               NO
     CONNECT                NO
     
    
     XFILES                   CURSOR STATEMENT : 2
     CURSOR STATEMENT : 2
     GRANTED_ROLE               ADM
     ------------------------------ ---
     XFILES_USER               NO
     CONNECT                NO
     RESOURCE               NO
     CTXAPP                   NO


Le CURSOR expression : exemple complexe        
Vous me direz, plutôt que d'utiliser cette fonctionnalité, pourquoi ne pas faire une jointure comme précedemment? Dans cet exemple, c'est effectivement plus simple mais certains SELECTs sont trop complexes pour cela. Par exemple si on veut afficher en un seul SELECT la liste des plans d'exécution pour des ordres SQL.

Premier SELECT : pas de ORDER BY.

     SQL> select EMPLOYEE_ID from emp where salary > 12000;
     EMPLOYEE_ID
     -----------
         100
         101
         102
         145
         146
         201
     6 rows selected.
    
     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 1445457117
     -------------------------------------------------------------------------------
     | Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
     -------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |          |    59 |   472 |     3    (0)| 00:00:01 |
     |*  1 |  TABLE ACCESS FULL| EMPLOYEES |    59 |   472 |     3    (0)| 00:00:01 |
    
-------------------------------------------------------------------------------
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - filter("SALARY">12000)

Deuxième SELECT : avec le ORDER BY, le plan hash value n'est plus le même.     
     SQL> select EMPLOYEE_ID from emp where salary > 12000 order by EMPLOYEE_ID;
     EMPLOYEE_ID
     -----------
         100
         101
         102
         145
         146
         201
     6 rows selected.

     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 2186312383
   
  -----------------------------------------------------------------------------------
     | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    
-------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT        |            |     59 |    472 |      3   (0)| 00:00:01 |
     |*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES|   59 |    472 |      3   (0)| 00:00:01 |
     |   2 |   INDEX FULL SCAN        | EMP_EMP_ID_PK| 107 |        |      1   (0)| 00:00:01 |
    
-----------------------------------------------------------------------------------------
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - filter("SALARY">12000)


    
Maintenant notre besoin est d'afficher en un seul SELECT les plans d'exécutions des ordres précédents. Nous allons pour cela utiliser le fameux  CURSOR car sans lui, je ne vois pas comment arriver, simplement, au même résultat.
     SQL> select sql_id, plan_hash_value, sql_text, cursor(SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id))) from v$sqlarea where upper(sql_text) like upper('%select EMPLOYEE_ID from emp%') and upper(sql_text) not like '%V$SQLAREA%';

     SQL_ID          PLAN_HASH_VALUE SQL_TEXT                  CURSOR(SELECT*FROMTA
     ------------- --------------- ------------------------------------------------
     4xzcwgc2d5gck       2186312383 select EMPLOYEE_ID from emp where salary > 12000 order by EMPLOYEE_ID  CURSOR STATEMENT : 4

     CURSOR STATEMENT : 4
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------------------------
     SQL_ID    4xzcwgc2d5gck, child number 0
     -------------------------------------
     select EMPLOYEE_ID from emp where salary > 12000 order by EMPLOYEE_ID

     Plan hash value: 2186312383
     -----------------------------------------------------------------------------------------
     | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    
-----------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT        |            |        |        |      3 (100)|        |
     |*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES|   59 |    472 |      3   (0)| 00:00:01 |
     |   2 |   INDEX FULL SCAN        | EMP_EMP_ID_PK| 107 |        |      1   (0)| 00:00:01 |
     -----------------------------------------------------------------------------------------

     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - filter("SALARY">12000)
     19 rows selected.
    

     

     1j7y2pdmc6mh5       1445457117 select EMPLOYEE_ID from emp where salary > 12000    CURSOR STATEMENT : 4
     
     CURSOR STATEMENT : 4
     PLAN_TABLE_OUTPUT
     ---------------------------------------------------------------------------------
     SQL_ID    1j7y2pdmc6mh5, child number 0
     -------------------------------------
     select EMPLOYEE_ID from emp where salary > 12000
     
     Plan hash value: 1445457117
     -------------------------------------------------------------------------------
     | Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
     -------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |          |       |       |     3 (100)|          |
     |*  1 |  TABLE ACCESS FULL| EMPLOYEES |    59 |   472 |     3    (0)| 00:00:01 |
     -------------------------------------------------------------------------------
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - filter("SALARY">12000)
     18 rows selected.
     

Le CURSOR expression : limites
Pour plus d'info, la fonctionnalité CURSOR EXPRESSION est apparue avec Oracle 9i, en PL/SQL.
Plusieurs restrictions existent; voici un extrait du site d'Oracle :
"     - If the enclosing statement is not a SELECT statement, then nested cursors can appear only as REF CURSOR arguments of a procedure.

     - If the enclosing statement is a SELECT statement, then nested cursors can also appear in the outermost select list of the query specification or in the outermost select list of another nested cursor.
     - Nested cursors cannot appear in views.
     - You cannot perform BIND and EXECUTE operations on nested cursors."