Cursor Quick Reference - Oracle

Table of Contents [Hide/Show]

   Logic Flow for Explicit Cursors
   Cursors with Parameters
   FOR UPDATE Clause
   WHERE CURRENT OF Clause
   Cursor Attributes

{outline||<1> - |

.<1> - }

Logic Flow for Explicit Cursors¶

Declare	`CURSOR cursor_name IS select_statement;`	• Do not include an `INTO` clause. • If processing in a specific order is important, use and `ORDER BY` clause.
Open	`OPEN cursor_name;`	• Opening the cursor executes the query and identifies the active set • If the query returns no rows, no exception is raised • Use cursor attributes to test the outcome after a fetch
Fetch	`FETCH cursor_name INTO [variable1, variable2, . . .] \| [record_name];`	• Within a loop, fetch one record at a time until empty. • Retrieves the current row values into variables • Include the same number of variables and in the same order as the columns • Test to see if the cursor contains rows.
Cursor `FOR` Loop	`FOR record_name IN cursor_name LOOP statements; END LOOP;`	• Implicit open, fetch, and close occur • The record is implicitly declared.
Close	`CLOSE cursor_name;`	• Close after done processing • Re-open if necessary • Do not attempt to fetch data after the cursor is closed

Cursors with Parameters¶

Syntax

CURSOR cursor_name [(parameter_name datatype, . . .)] IS select_statement;

Notes

Pass parameter values to a cursor when the cursor is opened and the query is executed.
Open an explicit cursor several time with a different active set each time.

Example

DECLARE   CURSOR emp_cursor
  (p_deptno NUMBER, p_job VARCHAR2) IS
  SELECT empno, ename
  FROM emp
  WHERE deptno = p_deptno
  AND job = p_job;
BEGIN
  OPEN emp_cursor(10, 'CLERK');
  . . .

FOR UPDATE Clause¶

Syntax

SELECT . . . 
FROM . . .
ORDER BY . . .
FOR UPDATE [OF column_reference [, column_reference]] [NOWAIT];

Notes

Locks the affected rows when the cursor is opened
Do not commit across fetches from an explicit cursor if FOR UPDATE is used (because Oracle releases locks at the end of a the transaction).
In a multi-table query, confines the locking to the tables containing the column references
Exclusive row locks are taken on the rows in the active set before the OPEN returns
NOWAIT returns an Oracle error if the rows are locked by another session

WHERE CURRENT OF Clause¶

Syntax

WHERE CURRENT OF cursor;

Notes

Used to reference the current row from an explicit cursor, allowing you to apply updates and deletes to the row currently addressed.
Must use the FOR UPDATE clause with this clause

Example

DECLARE
   CURSOR sal_cursor IS
     SELECT sal
     FROM emp
     WHERE deptno = 30
     FOR UPDATE OF sal NOWAIT;
BEGIN
   FOR emp_record IN sal_cursor LOOP
     UPDATE emp
     SET sal = emp_record.sal * 1.10
     WHERE CURRENT OF sal_cursor;
   END LOOP;
   COMMIT;
END;

Cursor Attributes¶

Attribute	Description
`SQL%ROWCOUNT`	Integer - number of rows affected by the most recent SQL statement
`SQL%FOUND`	Boolean - `TRUE` if the most recent SQL statement affects one or more rows
`SQL%NOTFOUND`	Boolean - `TRUE` if the most recent SQL statement affects no rows
`SQL%ISOPEN`	Boolean - `TRUE` if the cursor is open. Always `FALSE` for an implicit cursor because PL/SQL closes them immediately after execution