CURSOR expressions

A CURSOR expression returns a nested cursor. An expression in this form is equivalent to the PL/SQL REF CURSOR and can be passed to a function as a REF CURSOR argument.

Syntax

CURSOR (subquery)

When the CURSOR expression is evaluated, a nested cursor is implicitly opened. For example, if a CURSOR expression appears in the select list, a nested cursor is opened each time the query fetches a row.

A nested cursor is closed only in the following cases:

• The nested cursor is explicitly closed by the user.

• The parent cursor is re-executed.

• The parent cursor is closed.

• The parent cursor is canceled.

• An error occurred during the fetch on one of its parent cursors. In this case, the nested cursor is closed as part of the clean-up.

Limits

A CURSOR expression has the following limits:

• If the enclosing statement is not a SELECT statement, the nested cursor can only be used as a procedural REF CURSOR argument.

• If the enclosing statement is a SELECT statement, the nested cursor can also appear in the outermost select list of the query specification or the outermost select list of another nested cursor.

• Nested cursors cannot appear in views.

• You cannot perform BIND and EXECUTE operations on nested cursors.

Examples

• Use the CURSOR expression in the select list of a query.

  obclient> CREATE TABLE dept(  
      deptno           NUMBER(2,0),  
      dname            VARCHAR(15),  
      location         VARCHAR(20),   
     CONSTRAINT pk_dept PRIMARY KEY(deptno)  
  );
  Query OK, 0 rows affected 
  
  obclient> CREATE TABLE emp(  
     empno         NUMBER(4,0),  
     empname       VARCHAR(10),  
     job           VARCHAR(10),  
     mgr           NUMBER(4,0),  
     hiredate      DATE,  
     sal           NUMBER(7,2),  
     comm          NUMBER(7,2),        
     deptno        NUMBER(2,0),   
     CONSTRAINT PK_emp PRIMARY KEY (empno)
  );
  Query OK, 0 rows affected 
  
  obclient> INSERT INTO dept VALUES (10,'ACCOUNTING','Los Angeles')
      ,(30,'OPERATIONS','CHICAGO')
      ,(40,'SALES','NEW YORK');
  Query OK, 3 rows affected 
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> INSERT INTO emp VALUES (1839,'KING','PRESIDENT',null, '04-JAN-20',5000,3,10)
      ,(1698,'BLAKE','MANAGER',1839,'01-MAY-1981',2850,2,30)
      ,(1782,'CLARK', 'MANAGER',1839, '09-JUN-81', 2450,2,10)
      ,(1566,'JONES','MANAGER',1839, '02-APR-81',2975,2,40)
      ,(1788,'SCOTT','ANALYST',1566, '15-JUL-87',3000,1,20)
      ,(1369,'SMITH','CLERK',1902,'17-OCT-80',800,1,20);
  Query OK, 6 rows affected 
  Records: 6  Duplicates: 0  Warnings: 0
  
  obclient> SELECT dname, CURSOR(SELECT sal comm FROM emp e WHERE e.deptno= d.deptno) 
     FROM dept d ORDER BY dname;
  +------------+-----------------------------------------------------+
   DNAME       CURSOR(SELECTSALCOMMFROMEMPEWHEREE.DEPTNO=D.DEPTNO) 
  +------------+-----------------------------------------------------+
   ACCOUNTING  -1                                                  
   OPERATIONS  -1                                                  
   SALES       -1                                                  
  +------------+-----------------------------------------------------+
  3 rows in set
  

• Use the CURSOR expression as a function argument. Delete all employees of a department from the emp table, and delete departments with no employees from the dept table.

  obclient> DECLARE
           v_dept_id emp.deptno%TYPE := 10;
       BEGIN
           DELETE FROM emp WHERE deptno=v_dept_id;
           IF SQL%NOTFOUND THEN
               DELETE FROM dept WHERE deptno=v_dept_id;
           END IF;
  END;
  /
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM emp;
  +-------+---------+---------+------+-----------+------+------+--------+
   EMPNO  EMPNAME  JOB      MGR   HIREDATE   SAL   COMM  DEPTNO 
  +-------+---------+---------+------+-----------+------+------+--------+
    1369  SMITH    CLERK    1902  17-OCT-80   800     1      20 
    1566  JONES    MANAGER  1839  02-APR-81  2975     2      40 
    1698  BLAKE    MANAGER  1839  01-MAY-81  2850     2      30 
    1788  SCOTT    ANALYST  1566  15-JUL-87  3000     1      20 
  +-------+---------+---------+------+-----------+------+------+--------+
  4 rows in set