PL/SQL Cursor

A cursor is a pointer that points to a result of a query. PL/SQL has two types of cursors: implicit cursors and explicit cursors.

Implicit cursors

Whenever Oracle executes an SQL statement such as SELECT INTO, INSERT, UPDATE, and DELETE, it automatically creates an implicit cursor.

Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor’s information and statuses such as SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND, and SQL%NOTFOUND.

The implicit cursor is not elegant when the query returns zero or multiple rows which cause NO_DATA_FOUND or TOO_MANY_ROWS exception respectively.

Explicit cursors

An explicit cursor is a SELECT statement declared explicitly in the declaration section of the current block or a package specification.

For an explicit cursor, you have control over its execution cycle from OPEN, FETCH, and CLOSE.

Oracle defines an execution cycle that executes an SQL statement and associates a cursor with it.

The following illustration shows the execution cycle of an explicit cursor:

Let’s examine each step in detail.

Declare a cursor

Before using an explicit cursor, you must declare it in the declaration section of a block or package as follows:

CURSOR cursor_name IS query;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the name of the cursor after the CURSOR keyword.
Second, define a query to fetch data after the IS keyword.

Open a cursor

Before start fetching rows from the cursor, you must open it. To open a cursor, you use the following syntax:

OPEN cursor_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the cursor_name is the name of the cursor declared in the declaration section.

When you open a cursor, Oracle parses the query, binds variables, and 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 sets the cursor to the first row in the result set.

More about parameterized cursor in the next tutorial.

Fetch from a cursor

The FETCH statement places the contents of the current row into variables. The syntax of FETCH statement is as follows:

FETCH cursor_name INTO variable_list;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To retrieve all rows in a result set, you need to fetch each row till the last one.

Closing a cursor

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

CLOSE cursor_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

If you declare a cursor in an anonymous block, procedure, or function, the cursor will automatically be closed when the execution of these objects ends.

However, you must explicitly close package-based cursors. Note that if you close a cursor that has not opened yet, Oracle will raise an INVALID_CURSOR exception.

Explicit Cursor Attributes

A cursor has four attributes which you can reference in the following format:

cursor_name%attributeCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

where cursor_name is the name of the explicit cursor.

1) %ISOPEN

This attribute is TRUE if the cursor is open or FALSE if it is not.

2) %FOUND

This attribute has four values:

NULL before the first fetch
TRUE if a record was fetched successfully
FALSE if no row is returned
INVALID_CURSOR if the cursor is not opened

3) %NOTFOUND

This attribute has four values:

NULL before the first fetch
FALSE if a record was fetched successfully
TRUE if no row is returned
INVALID_CURSOR if the cursor is not opened

3) %ROWCOUNT

The %ROWCOUNT attribute returns the number of rows fetched from the cursor. If the cursor is not opened, this attribute returns INVALID_CURSOR.

PL/SQL cursor example

We will use the orders and order_items tables from the sample database for the demonstration.

The following statement creates a view that returns the sales revenues by customers:

CREATE VIEW sales AS
SELECT customer_id,
       SUM(unit_price * quantity) total,
       ROUND(SUM(unit_price * quantity) * 0.05) credit
FROM order_items
INNER JOIN orders USING (order_id)
WHERE status = 'Shipped'
GROUP BY customer_id;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The values of the credit column are 5% of the total sales revenues.

Suppose you need to develop an anonymous block that:

Reset the credit limits of all customers to zero.
Fetch customers sorted by sales in descending order and give them new credit limits from a budget of 1 million.

The following anonymous block illustrates the logic:

DECLARE
  l_budget NUMBER := 1000000;
   -- cursor
  CURSOR c_sales IS
  SELECT  *  FROM sales  
  ORDER BY total DESC;
   -- record    
   r_sales c_sales%ROWTYPE;
BEGIN

  -- reset credit limit of all customers
  UPDATE customers SET credit_limit = 0;

  OPEN c_sales;

  LOOP
    FETCH  c_sales  INTO r_sales;
    EXIT WHEN c_sales%NOTFOUND;

    -- update credit for the current customer
    UPDATE 
        customers
    SET  
        credit_limit = 
            CASE WHEN l_budget > r_sales.credit 
                        THEN r_sales.credit 
                            ELSE l_budget
            END
    WHERE 
        customer_id = r_sales.customer_id;

    --  reduce the budget for credit limit
    l_budget := l_budget - r_sales.credit;

    DBMS_OUTPUT.PUT_LINE( 'Customer id: ' ||r_sales.customer_id || 
' Credit: ' || r_sales.credit || ' Remaining Budget: ' || l_budget );

    -- check the budget
    EXIT WHEN l_budget <= 0;
  END LOOP;

  CLOSE c_sales;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In the declaration section, we declare three variables.

The first one is l_budget whose initial value is 1,000,000.

The second variable is an explicit cursor variable named c_sales whose SELECT statement retrieves data from the sales view:

CURSOR c_sales IS
      SELECT  *  FROM sales  
      ORDER BY total DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The third variable is a cursor-based record named c_sales.

In the execution section, we perform the following:

First, reset the credit limits of all customers to zero using an UPDATE statement.
Second, open the c_sales cursor.
Third, fetch each row from the cursor. In each loop iteration, we updated the credit limit and reduced the budget. The loop terminates when there is no row to fetch or the budget is exhausted.
Finally, close the cursor.

The following query retrieves data from the customers table to verify the update:

SELECT customer_id,
       name,
       credit_limit
FROM customers
ORDER BY credit_limit DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result:

As you can see clearly from the result, only the first few customers have credit limits. If you sum up all credit limits, the total should be 1 million as shown follows:

SELECT
  SUM( credit_limit )
FROM
  customers;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

SUM(CREDIT_LIMIT)
-----------------
          1000000