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

---