Explicit Cursors

Introduction to Oracle SQL & PL/SQL
Updated 03/20/2005

Concepts of Implicit (Static) Cursors

Oracle uses cursors to process all SQL statements.  From SQL*Plus, you issue a command and Oracle takes care of creating a cursor and processing the command.  These types of cursors are called implicit cursors, because you (the user) cannot not name or control the cursor directly. In PL/SQL you also use implicit cursors for DML statements and single select statements. 

Implicit (static) cursor: commonly refers to the good old SELECT INTO, in which Oracle implicitly opens, executes and closes the cursor for you, depositing any selected values of a single row INTO program data structures.
CREATE OR REPLACE PROCEDURE show_title (author_in IN magazine.author%TYPE)
IS
   l_title magazine.title%TYPE;
BEGIN
   SELECT title INTO l_title
     FROM magazine
    WHERE author = author_in;
END;

The single select is a simple solution, but insufficient to solve the following problems.

To address these problems you need to use explicit cursors.

Working with explicit cursors

Explicit cursors work the same way implicit cursors work, but you control the execution explicitly.

DECLARE
  v_ename VARCHAR2(12);
  v_empno NUMBER:=7839;

  CURSOR ename_cursor IS
    SELECT ename 
      FROM emp
      WHERE empno=v_empno;
BEGIN
  OPEN ename_cursor;
  FETCH ename_cursor INTO v_ename;
  CLOSE ename_cursor;
END;
/

Examining each statement in turn:

DECLARE
...
  CURSOR ename_cursor IS
    SELECT ename 
      FROM emp
      WHERE empno=v_empno;

Oracle allocates memory and processes the query

 

 
BEGIN
  OPEN ename_cursor;

Oracle binds variables, and executes query identifying the active set.

 
  FETCH ename_cursor INTO v_ename;

Oracle fetches a row from the active set, sets the value of v_ename, and advances the pointer to the active set.  

 
  FETCH ename_cursor INTO v_ename;

Oracle fetches a row from the active set (as above).  

 
  CLOSE ename_cursor;
END;
/

Oracle releases memory area.


Cursor Attributes

Use cursor attributes to determine whether the row was found and what number the row is.

Cursor Attributes

Attribute

Description

cur%ISOPEN

Returns TRUE if cursor is open.

cur%NOTFOUND

Returns FALSE if the last FETCH found a row.

cur%FOUND

Returns TRUE if the last FETCH found a row..  (Logical inverse of %NOTFOUND).

cur%ROWCOUNT

Returns the number of rows modified by the DML statement.

SQL%BULK_ROWCOUNT

 Returns the number of rows processed for each execution of the bulk DML operation.

Example using cursor attributes:

DECLARE
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;

  CURSOR emp_cursor IS
    SELECT empno, ename
      FROM emp;
BEGIN
    OPEN emp_cursor;
    LOOP
      FETCH emp_cursor INTO v_empno, v_ename;
      EXIT WHEN emp_cursor%ROWCOUNT>10 or emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(INITCAP(v_ename)||' ['||v_empno||']');
    END LOOP;
    CLOSE emp_cursor;
END;
/

Records and %ROWTYPE

Instead of fetching values into a collection of variables, you could fetch the entire row into a record like so.

DECLARE
  CURSOR emp_cursor IS
    SELECT empno, ename, sal, job, deptno
      FROM emp
      WHERE deptno=30;
  -- This creates a record named emp_row 
  -- based on the structure of the cursor emp_cur
  emp_row emp_cursor%ROWTYPE;  
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor 
      INTO emp_row;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emp_row.ename||' ['
    ||emp_row.empno||'] makes '||TO_CHAR(emp_row.sal*12,'$99,990.00'));
  END LOOP;
END;
/

You can reference the fields of a record using the syntax record_name.field_name.

In addition to basing a record on a cursor, you can also define records based on tables like so.

DECLARE
  CURSOR emp_cursor IS
    SELECT *
      FROM emp
      WHERE deptno=30;

  emp_row emp%ROWTYPE;  -- This creates a record named EMP_ROW 
                        -- based on the structure of the EMP table
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor 
      INTO emp_row;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emp_row.ename||' ['||emp_row.empno
        ||'] makes '||TO_CHAR(emp_row.sal*12,'$99,990.00'));
  END LOOP;
END;
/