PL/SQL Cursor

Summary: in this tutorial, you will learn about PL/SQL cursor and its usage.

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 an 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:

PL/SQL 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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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 end.

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 to which you can reference in the following format:

cursor_name%attribute
Code language: SQL (Structured Query Language) (sql)

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 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 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.

Orders and Order_items tables

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: SQL (Structured Query Language) (sql)

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

Suppose you need to develop a anonymous block that:

  1. Reset credit limits of all customers to zero.
  2. Fetch customers sorted by sales in descending order and gives 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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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

In the execution section, we perform the following:

  • First, reset 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 update 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: SQL (Structured Query Language) (sql)

Result:

PL/SQL Cursor example

As you can see clearly from the result, only the first few customers have the 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: SQL (Structured Query Language) (sql)
SUM(CREDIT_LIMIT) ----------------- 1000000

Now, you should understand PL/SQL cursors including implicit and explicit cursors, and how to use them effectively to process data, row by row, from a table.

Was this tutorial helpful?