Cursor in PL/SQL: When an SQL statement is processed, the Oracle engine creates a memory area which is known as “context area”. This context area is a memory region inside the Process Global Area (PGA). The cursor is nothing but a pointer to this context area. Normally, the cursor is used when we need to process multiple rows of data. The cursor contains all the information needed to process the SQL statement. This information basically contains all select statements and rows which are accessed by the select statement.
Why we need to use cursor?
As we mentioned above, the cursor is needed when the select statement returns more than one row. In a PL/SQL program, when we need to process multiple rows of records the cursor comes into the picture. To understand clearly why we need a cursor, let’s take an example.
Consider a table “ITEM_MASTER” in the database –
If we write an SQL statement in a PL/SQL program like “SELECT UNIT_PRICE INTO var1 FROM ITEM_MASTER”, it will return error.
Because we are trying to store more than one records “UNIT_PRICE” into a single variable. So, in this situation we need to use cursor.
Types of cursors in PL/SQL:
There are two types of cursors available in PL/SQL. They are –
- Implicit Cursor
- Explicit Cursor
The implicit cursors are the predefined cursor, which is associated with DML statements. They are automatically generated by oracle wherever an SQL statement is processed. That is why they are also known as SQL cursors. The user can’t control the behavior of these cursors. The oracle server creates an implicit cursor for any SQL statement as long as there is no explicit cursor exists for that SQL statements. There are some important attributes that are relatively used with PL/SQL cursor. Some of them are as followed –
This returns TRUE if a DML statement like INSERT, DELETE, and UPDATE affects at least one or more row.
This works opposite to the previous one. Means, it will return TRUE only if no statement is affected by a DML statement.
This returns always FALSE for implicit cursor and returns TRUE for explicit cursor only when the cursor is opened.
It returns the number of rows affected.
These four are the important attributes related to the cursor which seems to be commonly used when we write programs in PL/SQL using cursor.
Example: Consider the table “ITEM_MASTER” on which we will update available quantity of all the items by 500 using cursor.
SET SERVEROUTPUT ON
SET VERIFY OFF
UPDATE item_master SET available_quantity = available_quantity + 500;
IF sql%notfound THEN
dbms_output.put_line("No row Updated!");
ELSEIF sql%found THEN
dbms_output.put_line(total_rows || "rows Updated!");
The PL/SQL explicit cursors are declared explicitly by the developers, so they are the user defined cursors. The explicit cursors are normally used to gain more control over the context area. The explicit cursors are used on a select statement which returns more than one row. The complete processing of a PL/SQL explicit cursor can be defined by using a flowchart as follows –
So, the PL/SQL explicit cursor can be divided into four different parts – DECLARE, OPEN, FETCH and CLOSE. Now we will discuss all these four parts one by one in details. After that we will see an example of PL/SQL program using explicit cursor.
A cursor declaration initializes the cursor into memory. We can define explicit cursor in declaration section of our PL/SQL block.
In order to put that cursor to work, we have to open it first. When we open a cursor, the memory is allotted to it.
Fetching is the process of retrieving the data from the cursor. Once the cursor is declared and opened, we can retrieve the data from it.
Once we are done working with cursor, it is advisable to close it. When the server comes across the closing statement of a cursor it will relinquish all the resources associated with it.
Syntax for creating PL/SQL explicit cursor:
Declaring a Cursor:
CURSOR cursor_name IS SELECT Statement;
Opening a Cursor:
Fetching a Cursor:
FETCH cursor_name into variable;
Closing a Cursor:
Example: Consider the table “ITEM_MASTER” from which we will display all the data using a explicit cursor.
SET SERVEROUTPUT ON
SET VERIFY OFF
CURSOR item is
SELECT i_id, i_name, unit_price, available_quantity FROM item_master;
FETCH item into i_id, i_name, unit_price, available_quantity;
EXIT WHEN item%notfound;
dbms_output.put_line(i_id || ' ' || i_name || ' ' || unit_price || ' ' || available_quantity);