PL/SQL - Explicit Cursors

As discussed earlier, there are two types of cursors: Implicit cursors and explicit cursors. In this section, we will focus on explicit cursors and their functionality.

Explicit cursor

Explicit cursors are programmer defined cursors with more control over the context area. The explicit cursor should be defined in the declaration section of the PL/SQL block.

The syntax for the an explicit cursor:

CURSOR cursor_name IS select_statement; 

Below are the steps for defining explicit cursors.

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

Let’s dive into the details of defining an explicit cursor.

Declare a cursor

Declaring the cursor defines the cursor with a name and select statement associated with it.

CURSOR c_stocks is 
    SELECT * FROM stock_master; 

Fore more read here.

Open a cursor

Before fetching data from the cursor you must open it. To open a cursor you can use the following syntax:

OPEN c_stocks; 

When you open a cursor, Oracle parses the query, binds variables, executes the associated SQL statement.

Oracle also determines an execution plan, associates host variables and cursor parameters with the placeholders in the SQL statement, determines the result set, and positions the cursor at the first row.

Fetch from a cursor

The FETCH statement places the content of the current row into variables. Below is the syntax of the FETCH.

FETCH c_stocks into p_stockrows; 

You need to fetch till last row to retrieve all the rows in a result set.

Closing the Cursor

After fetching all rows, you need to close the cursor with the CLOSE statement:

CLOSE c_stocks; 

Closing a cursor instructs Oracle to release allocated memory at an appropriate time.

Example

Following is a complete example of explicit cursors.

DECLARE
   
    CURSOR c_stocks is 
    SELECT * FROM stock_master; 
    
    p_stockrows c_stocks%ROWTYPE; 
BEGIN 
   OPEN c_stocks; 
   LOOP 
   FETCH c_stocks into p_stockrows; 
      EXIT WHEN c_stocks%notfound; 
      dbms_output.put_line(p_stockrows.stock_code || ' ' || p_stockrows.stock_name); 
   END LOOP; 
   CLOSE c_stocks; 
END;

References

Oracle tutorial, Tutorials point