PL/SQL Cursor

Summary: in this tutorial, we will introduce you to PL/SQL cursor. You will learn step by step how to use a cursor to loop through a set of rows and process each row individually.

Introducing to PL/SQL Cursor

When you work with Oracle database, you work with a complete set of rows returned from a SQL SELECT statement. However the application in some cases cannot work effectively with entire result set, therefore the database server needs to provide a mechanism for the application to work with one row or a subset of result set at a time. As the result, Oracle created PL/SQL cursor to provide these extensions.

A PL/SQL cursor is a pointer that points to the result set of a SQL query against database tables.

Working with PL/SQL Cursor

The following picture describes steps that you need to follow when you work with a PL/SQL cursor:

PL/SQL Cursor
PL/SQL Cursor

Let’s examine each steps in greater detail.

Declaring PL/SQL Cursor

To use PL/SQL cursor, first you must declare it in the declaration section of PL/SQL block or in a package as follows:

  • First, you declare the name of cursor cursor_name after the CURSOR keyword . The name of the cursor can have up to 30 characters in length and follows the naming rules of identifiers in PL/SQL. It is important to note that cursor’s name is not a variable so you cannot use it as a variable such as assigning it to other cursor or using it in an expression. The  parameter1 , parameter2… are optional elements in the cursor declaration. These parameters allow you to pass arguments into the cursor. The RETURN return_specification is also an optional part.
  • Second, you specify a valid SQL statement that returns a result set where the cursor points to.
  • Third, you can indicate a list of columns that you want to update after the FOR UPDATE OF. This part is optional so you can omit it in the CURSOR declaration.

Here is an example of declaring a cursor:

We retrieved data from employees and departments tables using SELECT with INNER JOIN statement and set the cur_chief cursor to this result set.

Opening a PL/SQL Cursor

After declaring a cursor, you can open it by using the following syntax:

You have to specify the cursor’s name cursor_name after the keyword OPEN. If the cursor was defined with a parameter list, you need to pass corresponding arguments to the cursor.

When you OPEN the cursor, PL/SQL executes the SQL SELECT statement and identifies the active result set. Notice that the OPEN action does not actually retrieve records from database. It happens in the FETCH step. If the cursor was declared with FOR UPDATE clause, PL/SQL locks all the records in the result set.

We can open the cur_chief cursor as follows:

Fetching Records from PL/SQL Cursor

Once the cursor is open, you can fetch data from the cursor into a record that has the same structure as the cursor. Instead of fetching data into a record, you can also fetch data from the cursor to a list of variables.

The fetch action retrieves data and fills the record or the variable list. You can manipulate this data in memory. You can fetch the data until there is no record found in active result set.

The syntax of FETCH is as follows:

You can test the cursor’s attribute %FOUND or %NOTFOUND to check if the fetch against the cursor is succeeded. The cursor has more  attributes that we will cover in the next section.

We can use PL/SQL LOOP statement together with the FETCH to loop through all records in active result set as follows:

Closing PL/SQL Cursor

You should always close the cursor when it is no longer used. Otherwise, you will have a memory leak in your program, which is not expected.

To close a cursor, you use CLOSE statement as follows:

And here is an example of closing the cur_chief cursor:

A complete PL/SQL Cursor Example

The following is a complete example of cursor for printing a list of chief and name of departments as follows:

PL/SQL Cursor Attributes

These are the main attributes of a PL/SQL cursor and their descriptions.

Attribute Description
cursor_name%FOUND returns TRUE if record was fetched successfully by cursor cursor_name
cursor_name%NOTFOUND returns TRUE if record was not fetched successfully by cursor cursor_name
cursor_name%ROWCOUNT returns the number of records fetched from the cursor cursor_name at the time we test %ROWCOUNT attribute
cursor_name%ISOPEN returns TRUE if the cursor cursor_name is open

In this tutorial, you’ve learned how to use PL/SQL Cursor to loop through a set of rows with all necessary steps that need to be done including DECLARE, OPEN, FETCH and CLOSE.