PL/SQL Explicit Cursor with Examples

An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

There are four steps in using an Explicit Cursor.

1)       DECLARE the cursor in the declaration section.

2)       OPEN the cursor in the Execution Section.

3)       FETCH the data from cursor into PL/SQL variables or records in the Execution      Section.

4)       CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

These are the three steps in accessing the cursor.

1)   Open the cursor.
2)   Fetch the records in the cursor one at a time.
3)   Close the cursor. 

Example: Fetch the Records in the Cursor

Example: Using Simple Loop

Example: Using WHILE Loop

Example: Using FOR Loop

Deletion or Updation Using Cursor:

In all the previous examples I show how to retrieve data using cursors. Now the next example is relate to modify or delete rows in a table using cursors. In order to Update or Delete rows, the cursor must be defined with the FOR UPDATE clause. The Update or Delete statement must be declared with WHERE CURRENT OF