Cursors in PL/SQL

Cursor in SQL
To execute SQL statements, a work area is used by the Oracle engine for its internal processing and storing the information. This work area is private to SQL’s operations. The ‘Cursor’ is the PL/SQL construct that allows the user to name the work area and access the stored information in it.

Use of Cursor
The major function of a cursor is to retrieve data, one row at a time, from a result set, unlike the SQL commands which operate on all the rows in the result set at one time. Cursors are used when the user needs to update records in a singleton fashion or in a row by row manner, in a database table.
The Data that is stored in the Cursor is called the Active Data Set. Oracle DBMS has another predefined area in the main memory Set, within which the cursors are opened. Hence the size of the cursor is limited by the size of this pre-defined area.

Cursor Actions

Declare Cursor: A cursor is declared by defining the SQL statement that returns a result set.
Open: A Cursor is opened and populated by executing the SQL statement defined by the cursor.
Fetch: When the cursor is opened, rows can be fetched from the cursor one by one or in a block to perform data manipulation.
Close: After data manipulation, close the cursor explicitly.
Deallocate: Finally, delete the cursor definition and release all the system resources associated with the cursor.

Types of Cursors
Cursors are classified depending on the circumstances in which they are opened.

Implicit Cursor: If the Oracle engine opened a cursor for its internal processing it is known as an Implicit Cursor. It is created “automatically” for the user by Oracle when a query is executed and is simpler to code.
Explicit Cursor: A Cursor can also be opened for processing data through a PL/SQL block, on demand. Such a user-defined cursor is known as an Explicit Cursor.

Explicit cursor
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. A suitable name for the cursor.

General syntax for creating a cursor:

CURSOR cursor_name IS select_statement;

cursor_name – A suitable name for the cursor.
select_statement – A select query which returns multiple rows

How to use Explicit Cursor?

There are four steps in using an Explicit Cursor.

DECLARE the cursor in the Declaration section.
OPEN the cursor in the Execution Section.
FETCH the data from the cursor into PL/SQL variables or records in the Execution Section.
CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

Syntax:

DECLARE variables;
 records;
 create a cursor;
 BEGIN 
OPEN cursor; 
FETCH cursor;
 process the records;
 CLOSE cursor; 
END;

Reference:
https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/01_oview.htm#740

Article Tags :

Misc

SQL

SQL-PL/SQL