Wednesday, September 10, 2008

How to use cursor

Implicit cursors - For SQL queries returning single row PL/SQL declares implicit cursors.
Explicit Cursors - Explicit cursors are used in queries that return multiple rows.

Cursor declaration:
without parameters-
CURSOR cursor_name
IS
    SELECT_statement;
    
with parameters-
CURSOR cursor_name (parameter_list)
IS
   SELECT_statement;
   
Open, fetch and close cursor:
Syntax for open - OPEN ;        
Syntax for close - CLOSE ;
Syntax for fetch - FETCH INTO ;

Cursor (explicit) attributes:
%NOTFOUND: Boolean, which evaluates to true, if the last fetch failed.
%FOUND: Boolean, which evaluates to true if the last fetch, succeeded. 
%ROWCOUNT: numeric, which returns number of rows fetched by the cursor so far. 
%ISOPEN: Boolean, which evaluates to true if the cursor is opened otherwise to false.

Loop for cursor:
 - Cursor For Loop itself opens a cursor, read records then closes the cursor automatically. Hence OPEN, FETCH and CLOSE statements are not necessary in it. 

Cursor updattion/deletion:
Steps:
  1. delcare cursor with 'FOR UPDATE' clause
  2. Add 'WHERE CURRENT OF ' in update or delete statement

Example of cursor usage:
1. Open and loop through
OPEN cursor_name FOR
            SELECT * FROM table_name;
         LOOP
            FETCH INTO variables;
         END LOOP;
CLOSE cursor_name;



---
Resource:
1. http://www.exforsys.com/tutorials/oracle-9i/oracle-cursors.html
2. http://www.techonthenet.com/oracle/cursors/declare.php
---