CURSOR expressions

A CURSOR expression returns a nested cursor. An expression in this form is equivalent to the PL 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.

Limitations

A CURSOR expression has the following limitations:

• 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