CURSOR Expressions

A CURSOR expression returns a nested cursor. This form of expression is equivalent to a REF CURSOR in PL/SQL and can be passed as a REF CURSOR parameter to a function.

CURSOR expression syntax

CURSOR (subquery)

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

A nested cursor is closed only in the following situations:

• 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 occurs while retrieving a row from the parent cursor (the nested cursor is cleaned up after it is closed).

Limitations

The CURSOR expression has the following limitations:

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

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

• The nested cursor cannot appear in a view.

• You cannot perform BIND or EXECUTE operations on a nested cursor.

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 parameter. Delete all employees in a department from the emp table, and if the department has no employees left, delete the department 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