Basic Cursor Writing In PLSQL Procedures


A cursor is a memory area allocated to work with select statements and is declared by a name.

Need For Cursor
In any PLSQL Statement which is IMPLICIT you may or maynot use a cursor but if the Statement is Explicit the use of cursor in a plsql programs becomes mandatory

Basic operations while working with a cursor.

1. Declare a cursor.
Declaration of a cursor is always in the declare section of the PLSQL block. Basically it is of following format.
Cursor cursor_name is Select .. , .... , ... from Table_Name;

The above declaration creates a Cursor;

2. Open Cursor.

This is another operation that one has to work with. All cursors that are declared has to be opened to execute the select statement which is stored in the Cursor Area. Same cursor cannot be opened twice in a Plsql Statment

3. Fetch. Cursor  This operation is for fetching the records into a declared variable. Please notes in a basic plsql bloch where a cursor is declared with a select statement the total number of select statement should match with the fetch variable.

Eg cursor has a select statement like ..Select col1,col2,col3 from table.
In the the fetch operation with be fetch cursor into a,b,c (these have to be declared in the declare section.) col1 is fetch in a,col2 is fetch into b and col3 is fetched into c. where a,b,c are 3 declared variables.

4. Close Cursor. : the is the close the cursor and release the allocated memory area. Whenever a PLSQL programs is complete after successfull completion or terminates due do some error the cursor is alway closed by itself.

Some other topics that one should be aware of while working with cursors are
Cursor Attributes
Exception Handling
Different types of Cursors.
Parameter Passing In Cursors.