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

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:

CURSOR cursor_name [ ( [ parameter_1 [, parameter_2 ...] ) ]
      [ RETURN return_specification ]
IS sql_select_statements
[FOR UPDATE [OF [column_list]];

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

  CURSOR cur_chief IS
      SELECT first_name,
             last_name,
             department_name
      FROM employees e
      INNER JOIN departments d ON d.manager_id = e.employee_id;

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:

OPEN cursor_name [ ( argument_1 [, argument_2 ...] ) ];

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:

OPEN cur_chief;

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:

FETCH cursor_name INTO record or variables

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:

LOOP
    -- fetch information from cursor into record
    FETCH cur_chief INTO r_chief;

    EXIT WHEN cur_chief%NOTFOUND;

    -- print department - chief
    DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' ||
                         r_chief.first_name || ',' ||
                         r_chief.last_name);
  END LOOP;

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:

CLOSE cursor_name;

And here is an example of closing the cur_chief cursor:

CLOSE cur_chief;

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:

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  -- declare a cursor
  CURSOR cur_chief IS
      SELECT first_name,
             last_name,
             department_name
      FROM employees e
      INNER JOIN departments d ON d.manager_id = e.employee_id;

  r_chief cur_chief%ROWTYPE;
BEGIN
  OPEN cur_chief;
  LOOP
    -- fetch information from cursor into record
    FETCH cur_chief INTO r_chief;

    EXIT WHEN cur_chief%NOTFOUND;

    -- print department - chief
    DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' ||
                         r_chief.first_name || ',' ||
                         r_chief.last_name);
  END LOOP;
  -- close cursor cur_chief
  CLOSE cur_chief;
END;
/

PL/SQL Cursor Attributes

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

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.