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
---
