PL/SQL Cursor

Summary: In this tutorial you will explore PL/SQL cursor feature. You will learn step-by-step how to use a cursor to loop through multiple rows.

Introducing to PL/SQL Cursor

When you work with Oracle database, you work with a complete set of rows that is known as result set returned by SQL SELECT statement. However applications in some cases cannot work effectively with entire result set therefore database server needs to provide a mechanism for these applications to work with one row or a subset of result set at a time. As the result, Oracle introduced Cursor concept to provides these extensions.

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

Working with PL/SQL Cursor

There are several steps you need to do when yo work with PL/SQL cursor as describe in the image below:

PL/SQL Cursor

PL/SQL Cursor

Let’s examine each steps in details.

Declaring PL/SQL Cursor

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

  1. CURSOR cursor_name [ ( [ parameter_1 [, parameter_2 ...] ) ]
  2.       [ RETURN return_specification ]
  3. IS sql_select_statements
  4. [FOR UPDATE [OF [column_list]];
  • First you declare the name of cursor cursor_name after the keyword CURSOR. The name of cursor can have up to 30 characters in length and follows the 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 assign it to other cursor or use it in an expression.
  • parameter1 , parameter2… are optional section in cursor declaration. These parameter allows you to pass arguments into the cursor.
  • RETURN return_specification is an optional part
  • Next you specify the valid SQL statement which returns a result set where the cursor points to.
  • Finally you can indicate a list of columns 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:

  1. CURSOR cur_chief IS
  2.       SELECT first_name,
  3.              last_name,
  4.              department_name
  5.       FROM employees e
  6.       INNER JOIN departments d ON d.manager_id = e.employee_id;

Opening a  PL/SQL Cursor

After declaring a cursor you can use open it by following the below syntax:

  1. 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 also. When you OPEN the cursor, PL/SQL executes the SQL SELECT statement and identifies the active result set. Note 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 our cursor cur_chief above as follows:

  1. OPEN cur_chief;

Fetching Records from PL/SQL Cursor

Once cursor is open, you can fetch data from the cursor into a record that has structure corresponding to the cursor. You can also fetch data from cursor to a list of variables. The fetch action retrieve data and fill the record or variable. You then 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:

  1. 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 succeeded. There are more cursor’s attributes which 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:

  1. LOOP
  2.     -- fetch information from cursor into record
  3.     FETCH cur_chief INTO r_chief;
  4.  
  5.     EXIT WHEN cur_chief%NOTFOUND;
  6.  
  7.     -- print department - chief
  8.     DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' ||
  9.                          r_chief.first_name || ',' ||
  10.                          r_chief.last_name);
  11.   END LOOP;

Closing PL/SQL Cursor

You should always close the cursor when you’re done with it. Otherwise you will have memory leak in your program which is not good. The close cursor syntax is very simple as follows:

  1. CLOSE cursor_name;

And here is an example of closing cursor:

  1. CLOSE cur_chief;

A Complete PL/SQL Cursor Example

We can have a complete example of cursor for printing chief of department and department name as follows:

  1. SET SERVEROUTPUT ON SIZE 1000000;
  2. DECLARE
  3.   -- declare a cursor
  4.   CURSOR cur_chief IS
  5.       SELECT first_name,
  6.              last_name,
  7.              department_name
  8.       FROM employees e
  9.       INNER JOIN departments d ON d.manager_id = e.employee_id;
  10.  
  11.   r_chief cur_chief%ROWTYPE;
  12. BEGIN
  13.   OPEN cur_chief;
  14.   LOOP
  15.     -- fetch information from cursor into record
  16.     FETCH cur_chief INTO r_chief;
  17.  
  18.     EXIT WHEN cur_chief%NOTFOUND;
  19.  
  20.     -- print department - chief
  21.     DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' ||
  22.                          r_chief.first_name || ',' ||
  23.                          r_chief.last_name);
  24.   END LOOP;
  25.   -- close cursor cur_chief
  26.   CLOSE cur_chief;
  27. END;
  28. /

PL/SQL Cursor Attributes

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

AttributeExplanation
cursor_name%FOUNDreturns TRUE if record was fetched successfully by cursor cursor_name
cursor_name%NOTFOUNDreturn TRUE if record was not fetched successfully by cursor cursor_name
cursor_name%ROWCOUNTreturns the number of records fetched from the  cursor cursor_name at the time we test %ROWCOUNT attribute
cursor_name%ISOPENreturns TRUE if the cursor cursor_name is open

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