Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Cursor Quick Reference - Oracle

RSS
Modified on Thu, Jan 08, 2009, 6:25 PM by Administrator Categorized as Oracle, Quick Reference
{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
  • 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.