What is Cursor in PL/SQL – Types of Cursors with Example.

What is Cursor in PL/SQL – Types of Cursors with Example.

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 –

item-master

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

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

%FOUND

This returns TRUE if a DML statement like INSERT, DELETE, and UPDATE affects at least one or more row.

%NOTFOUND

This works opposite to the previous one. Means, it will return TRUE only if no statement is affected by a DML statement.

%ISOPEN

This returns always FALSE for implicit cursor and returns TRUE for explicit cursor only when the cursor is opened.

%ROWCOUNT

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
DECLARE
total_rows number(4);
BEGIN
UPDATE item_master SET available_quantity = available_quantity + 500;
IF sql%notfound THEN
 dbms_output.put_line("No row Updated!");
ELSEIF sql%found THEN
 total_rows:= sql%rowcount;
 dbms_output.put_line(total_rows || "rows Updated!");
ENDIF;
END;
/

Explicit Cursor:

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.

Declare:

A cursor declaration initializes the cursor into memory. We can define explicit cursor in declaration section of our PL/SQL block.

Open:

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.

Fetch:

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.

Close:

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:

Open cursor_name;

Fetching a Cursor:

FETCH cursor_name into variable;

Closing a Cursor:

CLOSE cursor_name;

Example: Consider the table “ITEM_MASTER” from which we will display all the data using a explicit cursor.

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  i_id number(11);
  i_name varchar2(100);
  unit_price number(11);
  available_quantity number(11);
CURSOR item is
  SELECT i_id, i_name, unit_price, available_quantity FROM item_master;
BEGIN
  OPEN item;
  LOOP
  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);
  END LOOP;
  CLOSE item;
END;
/

Liked this article? Then please like our Facebook Page & stay connected with us. 

You may also like- ER Diagram in DBMS.

Feel Free to Share this:

Debarshi Das

Debarshi Das is a passionate blogger & full-stack JavaScript developer from Guwahati, Assam. He has a deep interest in robotics too. He holds a BSc degree in Information Technology & currently pursuing Masters of Computer Application (MCA) from a premier govt. engineering college. He is also certified as a chip-level computer hardware expert from an ISO certified institute.

Leave a Reply

Close Menu