Cursor Quick Reference - Oracle

{outline||<1> - |

.<1> - }

Logic Flow for Explicit Cursors

DeclareCURSOR cursor_name IS select_statement; • Do not include an INTO clause.
• If processing in a specific order is important, use and ORDER BY clause.
OpenOPEN 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
FetchFETCH 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 LoopFOR record_name IN cursor_name LOOP
  statements;
END LOOP;
• Implicit open, fetch, and close occur
• The record is implicitly declared.
CloseCLOSE 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
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

WHERE CURRENT OF Clause

Syntax
WHERE CURRENT OF cursor;

Notes
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