PL/SQL Explicit Cursors

In PL/SQL a Explicit Cursors cursors provides more control over context area. Explicit cursors are explicitly declared in the DECLARE section of the PL/SQL block. In explicit cursor DECLARE,OPEN,FETCH,and CLOSE operations are done by the programmer.

The working process of an explicit cursor:

Declare: The cursor is initialised into temporary memory area.
Open: The cursor is opened and the temporary memory area is allotted.
Fetch: Cursor opened and ready to retrieve rows from data.
Close: The CLOSE statement disables the cursor, and releases the temporary memory area.

PL/SQL Explicit Cursor Syntax

Syntax

CURSOR cursor_name IS select_statement;

PL/SQL Declare a Cursor Syntax

Syntax

CURSOR s_user IS  
SELECT statement;

Where:

s_user: Declaring the cursor with cursor name.

statement: Associated SELECT statement with declared cursor.

PL/SQL Opening a Cursor Syntax

Syntax

OPEN s_user;

Where:

s_user: To open a declared cursor allocates the memory and makes it ready for fetching.

PL/SQL Fetching a Cursor Syntax

Syntax

FETCH s_user INTO s_fname, s_lname, s_addr;

Where:

FETCH: Fetching the cursor involves accessing one row at a time.

PL/SQL Closing a Cursor Syntax

Syntax

CLOSE s_user;

Where:

CLOSE: Closing the cursor means releasing the allocated memory.

PL/SQL Explicit Cursor Example

Let's take an example to show how to use explicit cursor. In this example, we are using the already created tblCustomerLoan table.

Example

DECLARE 
   c_pk user.pk%type; 
   c_fname user.firstName%type; 
   c_lname user.lastName%type;
   c_addr user.address%type; 
   c_aadh user.aadharNo%type; 
   CURSOR c_user is 
      SELECT pk, firstName,lastName, address, aadharNo FROM tblCustomerLoan; 
BEGIN 
   OPEN c_user; 
   LOOP 
   FETCH c_user INTO c_pk, c_fname, c_lname, c_addr, c_aadh; 
      EXIT WHEN c_user%notfound; 
      DBMS_OUTPUT.PUT_LINE(c_pk || ' ' || c_fname || ' ' || c_lname || ' ' || c_addr || ' ' || c_aadh); 
   END LOOP; 
   CLOSE c_user; 
END;

When the above code is executed at the SQL prompt, it produces the following result: