Cursor structures

In PL/SQL, a cursor structure allows the processing of a SELECT statement and accesses the result returned by that query. Each and every SQL statement in a PL/SQL block is a cursor. A cursor is a handle to the chunk of the memory area where the SQL statements are processed and the result is stored. For a dedicated database, the chunk of memory is in the User Global Area (UGA) while, for shared server connections, the cursor context area is allocated in the System Global Area (SGA).

Cursors can be of two types:

• Implicit cursors: Every SQL query in the executable or exception section of a PL/SQL block is an implicit cursor. SELECT..INTO, SELECT..BULK COLLECT INTO, SELECT in CURSOR FOR loop, INSERT, UPDATE, DELETE, and MERGE are implicit cursors.
• Explicit cursors: A cursor defined by the user or developer in the declaration section of a PL/SQL program is an explicit cursor.

Cursor execution cycle

A cursor is a handler to execute an SQL query and lives for the life of a session. Once the current session ends, the cursor no longer exists. After the cursor gets created implicitly or explicitly, it goes through the following stages of execution.

• OPEN: As soon as the cursor gets created, Oracle allocates a private area in the session's user global area (UGA). This private area is used for SQL statement processing. Prior to opening a cursor, it remains as a null pointer variable.

  Note

  The initialization parameter OPEN_CURSORS governs the maximum number of cursors (from the library cache) that can be opened in a session.

• PARSE: Oracle checks the SQL statement for the syntactical correctness, semantics, and privileges.
• BIND: If the SQL statement needs additional input values for processing, the respective placeholders are replaced by actual values.
• EXECUTE: The SQL statement is executed following the conventional execution process. Oracle generates the hash value for the SQL statements and places it in the shared pool. Oracle also performs library cache lookup to search for any past executions of the same SQL. A successful lookup in the library cache avoids hard parsing of SQL statement. If the hash is not found, a new execution plan is generated and the SQL is processed. Once the SQL query is executed, the result set is placed in the UGA.
• FETCH: Fetch the record from the result set corresponding to the current position of the record pointer. The record pointer leaps forward by one after every successful fetch.
• CLOSE: The cursor handle is closed and the private context area is flushed out.

You can query the V$OPEN_CURSOR view to get the list of cursors used in the current session. Let us execute the following PL/SQL anonymous block and check the entries in V$OPEN_CURSOR:

connect scott/tiger
/*Declare a quick PL/SQL block */
DECLARE
 count_emp NUMBER;
 count_dep NUMBER;
BEGIN
 /*Create two implicit cursors */
 SELECT COUNT(*) INTO count_emp FROM emp;
 SELECT COUNT(*) INTO count_dep FROM dept;
END;
/

PL/SQL procedure successfully completed.

Let us query the V$OPEN_CURSOR view to check the open and PL/SQL cached cursors:

conn sys/oracle as sysdba
SELECT cursor_type,
        sql_text
FROM v$open_cursor
WHERE user_name='SCOTT'
AND cursor_type != 'DICTIONARY LOOKUP CURSOR CACHED'
ORDER BY cursor_type
/

CURSOR_TYPE          SQL_TEXT
----------------------- ------------------------------
OPEN                 declare  count_rec number; be
                     in  select count(*) into count

PL/SQL CURSOR CACHED    SELECT COUNT(*) FROM DEPT
PL/SQL CURSOR CACHED    SELECT COUNT(*) FROM EMP

In the preceding output, the two SELECT queries (or implicit cursors of the PL/SQL block) are PL/SQL cursor-cached while the PL/SQL block is in the OPEN state.

Cursor attributes

Cursor attributes reveal the necessary information about the last active cursor. Cursor attributes are not persisted in the database but are aligned along with the query result set in the session memory. These attributes are %ROWCOUNT, %ISOPEN, %FOUND, and %NOTFOUND.

Note

%BULK_ROWCOUNT and %BULK_EXCEPTIONS are additional cursor attributes used in bulk processing using the FORALL statement.

The cursor attributes are briefly explained as below:

• %ROWCOUNT: The attribute returns the number of rows fetched or affected by the SQL statement in the context area. It must be referenced within the cursor execution cycle. If referenced outside, it raises the INVALID_CURSOR exception.
• %ISOPEN: The attribute is set to TRUE if the cursor is currently open; otherwise it is FALSE. Programmers use this attribute outside the cursor execution cycle to check if the cursor is open or closed.
• %FOUND: The attribute returns TRUE if the row pointer points to a valid record. After the last record of the result set is reached, the attribute is set to FALSE.
• %NOTFOUND: The attribute returns the reverse of the %FOUND attribute.

Implicit cursors

Every SQL statement in the executable or exception section of a PL/SQL block is an implicit cursor. The database takes full charge of its entire execution cycle, meaning that the implicit cursor is auto-created, auto-opened, auto-fetched, and auto-closed. All of these steps are taken care by the Oracle Database. SQL statements can be SELECT, INSERT, UPDATE, DELETE, or MERGE, thus making an implicit cursor an SQL cursor.

The SELECT statement forming an implicit cursor is expected to return exactly one row. If it fails to return a single row, the implicit cursor raises TOO_MANY_ROWS or NO_DATA_FOUND exception. Exceptions can be trapped and handled with an informational message. If the cursor SQL is expected to return more than one row, you must create an explicit cursor.

Note that SQL% prefixes the cursor attributes for implicit cursors.

Note

In Oracle Database 12c, the maximum number returned by SQL%ROWCOUNT is 4,294,967,295.

The following PL/SQL block contains a SELECT...INTO statement in the executable section of the block:

/*Enable the SERVEROUTPUT to print the results */
SET SERVEROUTPUT ON
/*Demonstrate implicit cursor in PL/SQL execution block*/
DECLARE
   l_ename emp.ename%TYPE;
   l_sal emp.sal%TYPE;
BEGIN
/*Select name and salary of employee 7369 */
   SELECT ename, sal
   INTO l_ename, l_sal
   FROM emp
   WHERE empno = 7369;
   DBMS_OUTPUT.PUT_LINE('Rows selected:'||SQL%ROWCOUNT);
END;
/

Rows selected:1

PL/SQL procedure successfully completed.

The preceding PL/SQL block returns 1 because empno is the primary key in the emp table and there exists only one row against the value 7369.

Now let us try to update a multi-row data set in the employees table. The following PL/SQL block increases the salary of employees who are working in department 10:

/*Enable the SERVEROUTPUT to print the results */
SET SERVEROUTPUT ON
/*Demonstrate the cursor attribute during DML in a PL/SQL block*/
BEGIN
/*Increase the salary of employees from department 10*/
   UPDATE emp
   SET sal = sal + 1000
   WHERE deptno = 10;
   DBMS_OUTPUT.PUT_LINE('Rows updated:'||SQL%ROWCOUNT);
END;
/

Rows updated:3

PL/SQL procedure successfully completed.

Explicit cursors

Application developers can choose to create a cursor manually, perform open and fetch operations, and close the cursor. Such cursors are known as explicit cursors. They are more developer-friendly as they allow users to manage their execution stages and, most importantly, handle multi-row data sets.

An explicit cursor can be associated with SELECT queries only. The cursor prototype, defined in the DECLARE section of a PL/SQL block, should contain a valid name. The following PL/SQL block shows the cursor prototyping and handling stages in the executable section.

DECLARE
   CURSOR [Cursor Name] [Parameters]
   RETURN [Return type]
   IS
   [SELECT statement];
BEGIN
   OPEN [Cursor Name];
   FETCH…INTO [ scalar or composite variables ];
   CLOSE [Cursor Name];
END;

In the executable section of a PL/SQL block, a user has to open a cursor as OPEN [cursor name]. The data can be fetched using FETCH [cursor name] INTO [variables or record variable]. Once the fetch operation is over, a cursor can be closed using the CLOSE [cursor name] statement. Here is what happens at each of these stages:

• OPEN stage:
  1. Open cursor: It allocates a private work area in the user's session memory for cursor processing.
  2. Parse SQL: It validates the SQL query for syntax and privileges.
  3. Bind SQL: This provides an input value to the bind variables in the query.
  4. Execute the query: It executes the parsed SQL statement.
• FETCH stage: This stage iterates the data set for each fetch request. It fetches the data into block variables (or records) and increments the record pointer.
• CLOSE stage: This stage closes the cursor and releases the memory back to SGA.

Oracle supports parameterization of explicit cursors. If a SELECT statement has to be executed with the same predicates but different values, it is advisable to use parameterized cursors. Parameterization of a cursor is a powerful programming feature as it can improve coding standards by reducing the number of explicit cursor constructs in a program.

Structurally, a parameterized cursor is an explicit cursor with parameters. Parameters may or may not have default values. The developer supplies the parameter values at the time of opening the cursor in the program body. Optionally, you can also strongly prototype a parameterized cursor by specifying RETURN clause.The following cursor definition takes the department number as a parameter:

/*Cursor to fetch employee details from a department*/
CURSOR CUR_EMP (P_DEPTNO NUMBER)
IS
   SELECT *
   FROM emp
   WHERE deptno = P_DEPTNO;

You can also specify default value for the cursor parameters. For example:

/*Cursor to fetch employee details from a department*/
CURSOR CUR_EMP (P_DEPTNO NUMBER DEFAULT 10)
IS
   SELECT *
   FROM emp
   WHERE deptno = P_DEPTNO;

You can restrict the structure of cursor return type to protect its access.

/*Cursor to fetch employee details from a department*/
CURSOR CUR_EMP (P_DEPTNO NUMBER)
RETURN emp%ROWTYPE
IS
   SELECT *
   FROM emp
   WHERE deptno = P_DEPTNO;

If you wish to perform a transaction (update or delete) on a cursor result set, you can use the WHERE CURRENT OF clause in a DML statement. The WHERE CURRENT OF clause updates or deletes a the current row of the cursor result set. It is mandatory to declare the cursor with a SELECT FOR UPDATE query to secure a row-level exclusive lock on the cursor result set. The lock is released only after the transaction is committed or rolled back.

For example, the cursor cur_inc_comm in the following PL/SQL block locks the employee records in the cursor result set. The UPDATE statement modifies the employee's commission.

DECLARE
  CURSOR cur_inc_comm IS 
    SELECT empno, comm
    FROM emp
    FOR UPDATE OF comm;
BEGIN

  FOR i IN cur_inc_comm 
  LOOP
    UPDATE emp
    SET comm = comm*1.2 
    WHERE CURRENT OF cur_inc_comm;
  END LOOP;
END;
/

Note that you can reproduce the WHERE CURRENT OF scenario by using the ROWID pseudocolumn.

Cursor attributes play a key role in accessing the explicit cursor execution cycle. The attributes are auto-set at each stage and the following table shows the behavioral flow: