PL/SQL Tutorial | PL/SQL Explicit Cursors

Explicit cursors

 

Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.
The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;

Working with an explicit cursor involves four steps:

• Declaring the cursor for initializing in the memory
• Opening the cursor for allocating memory
• Fetching the cursor for retrieving data
• Closing the cursor to release allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example:
CURSOR c_customers IS SELECT id, name, address FROM customers;

Opening the Cursor

Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open above-defined cursor as follows:
OPEN c_customers;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows:
FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close above-opened cursor as follows:
CLOSE c_customers;

Example:

Following is a complete example to illustrate the concepts of explicit cursors:

DECLARE c_id customers.id%type; 
c_name customers.name%type; 
c_addr customers.address%type; 
CURSOR c_customers is SELECT id, name, address FROM customers;  
BEGIN OPEN c_customers; 
LOOP FETCH c_customers into c_id, c_name, c_addr; 
EXIT WHEN c_customers%notfound; 
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
END LOOP; 
CLOSE c_customers;  
END; / 

When the above code is executed at SQL prompt, it produces the following result:
1 Ramesh Ahmedabad 
2 Khilan Delhi 
3 kaushik Kota 
4 Chaitali Mumbai 
5 Hardik Bhopal 
6 Komal MP 

 PL/SQL procedure successfully completed.