I’ve written blog posts over time that address common questions about PLSQL fundamentals online such as in forums. It’s become a really useful exercise to have posts about key fundamentals as reference articles. So, I’m going to keep that going, continuing here.

In a previous post, I wrote about SELECT INTO (see below for link). As I mentioned, in that post, SELECT INTO is useful for retrieving a single row of data. But what about if you need to retrieve more than a single row of data? This brings me onto cursors. Cursors allow you to retrieve results of a query. They act as a pointer to those query results and provide a structured way for you to process the data.

There are a few fundamentals posts in the series now, so I will link them below:

• Oracle PL/SQL: SELECT INTO
• Oracle PL/SQL: SELECT INTO Common Errors
• Oracle PL/SQL: GROUPS  BY
• Oracle PL/SQL: LOOPs Explained
• Oracle PL/SQL: Conditional Statements

Contents

In this post, I cover the following topics:

1. Declaring a Cursor
2. OPEN/FETCH/CLOSE Statements
3. Cursor FOR LOOPs
4. Cursor Attributes

Oracle PL/SQL Cursors

For the examples in this post, I will use the widely known “hr schema”. The entity-relationship diagram is as below.

Declaring a Cursor

Before you can use a cursor within your PL/SQL logic, it must be first declared. In the same way that we define variables in the DECLARE section of a PL/SQL block, we also define cursors here. Ultimately, this is just defining a SELECT statement that returns one or more rows.

Syntax

Declaring a cursor is quite simple. Just write a SQL Query and give it a name using the syntax below:

CURSOR cursor_name IS
  SELECT_statement;

Example

In the below example, I have defined a simple cursor (within a PL/SQL block) that retrieves the employee_id, first_name and last_name of all employees that work in the Human Resources department. Note – I am note providing the results of running this PL/SQL block since defining/declaring a cursor alone does not do anything with that query. At this stage, no data has been retrieved, the cursor is simply a named query that can be called upon later

DECLARE 
    -- Define a cursor that retrieves details of employees who work in the human resources department
    CURSOR c_emp IS
        SELECT emp.employee_id, 
               emp.first_name, 
               emp.last_name
        FROM   employees   emp,
               departments dep
        WHERE  emp.department_id   = dep.department_id
        AND    dep.department_name = 'Human Resources';
BEGIN 
  -- Code to execute
    
END;

You can also declare a cursor that consumes (and uses) a parameter. Like the example below where I use the same cursor declaration as above, but this time I use a department name parameter so that I can later dynamically pass into the query what the department name is.

DECLARE 
    -- Define a cursor that retrieves details of employees who work in the human resources department
    CURSOR c_emp (p_department_name IN VARCHAR2) IS
        SELECT emp.employee_id, 
               emp.first_name, 
               emp.last_name
        FROM   employees   emp,
               departments dep
        WHERE  emp.department_id   = dep.department_id
        AND    dep.department_name = p_department_name;
BEGIN 
  -- Code to execute
    
END;

OPEN/FETCH/CLOSE Statements

Syntax

Now that we have declared a cursor (in the above example), we must use the OPEN statement to open that cursor so that it can be used within PL/SQL logic. The syntax to open a cursor is below:

OPEN cursor_name;

When the cursor is opened, the query that the cursor points to will be evaluated to identify the rows that will be processed. It is important to note, that the OPEN statement does not run the query, so no data is returned at this point. That’s why we then need to use the FETCH statement. FETCH is what we use to retrieve the data from that query, one row at a time. The syntax to fetch a cursor is below:

FETCH cursor_name INTO variable_list;

After all rows in the query results have been fetched, we must close the cursor using the CLOSE statement. Closing a cursor releases the memory associated to it and avoids unnecessary resource usage in the database. The syntax to close a cursor is below:

CLOSE cursor_name;

So, if we put all of that together and couple with a typical way to process data from a cursor (a LOOP), we get the following syntax:

OPEN cursor_name;
  LOOP 
    FETCH cursor_name INTO variable_list;
    EXIT WHEN cursor_name%NOTFOUND;
  END LOOP;
CLOSE cursor_name;

I will cover cursor_name%NOTFOUND attribute later in this post.

Example

In the below example, I have defined a simple cursor that retrieves the employee_id, first_name and last_name of all employees that work in the Human Resources department. I then open the cursor, loop through the results, print the results and ultimately close the cursor.

DECLARE 
    -- Define necessary variables
    v_employee_id INTEGER;
    v_first_name  VARCHAR2(100);
    v_last_name   VARCHAR2(100);
    -- Define a cursor that retrieves details of employees who work in the human resources department
    CURSOR c_emp IS
        SELECT emp.employee_id, 
               emp.first_name, 
               emp.last_name
        FROM   employees   emp,
               departments dep
        WHERE  emp.department_id   = dep.department_id
        AND    dep.department_name = 'Human Resources';
BEGIN 
  
  OPEN c_emp; -- Open the cursor
    
    LOOP -- Open a basic loop
        FETCH c_emp INTO v_employee_id, 
                         v_first_name, 
                         v_last_name; -- Fetch a row of data into the defined variables
        
        EXIT WHEN c_emp%NOTFOUND; 
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ' ' ||
                             'Name: '        || v_first_name  || ' ' || v_last_name); -- Output the data
    
    END LOOP; -- Close the loop;
  CLOSE c_emp; -- Close the cursor;
END;

The results of running this PL/SQL block are as follows – note, I only have one employee assigned to the Human Resources department.

If I change my cursor to instead look for employees from the “Executive” department, the results are below:

Cursor FOR Loop

I covered this in a previous post, but for completeness, I am also including here as an alternative to using the basic loop structure used in the above examples. You would use a CURSOR FOR LOOP when you want to fetch and process every record in a cursor efficiently and systematically. This control structure simplifies the retrieval of rows from a database by handling iteration through each record. The CURSOR FOR LOOP terminates when all of the records have been fetched and processed.

Syntax

The syntax for using a cursor FOR loop is below:

FOR record_index IN cursor_name LOOP
  -- Code block that you wish to execute a number of times
END LOOP;

Though, in reality, it really needs to look like this:

DECLARE
  CURSOR cursor_name IS
    SELECT row_name
    FROM   table_name
    WHERE  condition;
BEGIN 
  FOR rec in c1 LOOP 
    -- Code block that you wish to execute a number of times
  END LOOP;
END; 

Example

The following example uses a cursor for loop with the same logic I have used previously in this post and outputs the record values.

DECLARE 
    -- Define a cursor that retrieves details of employees who work in the human resources department
    CURSOR c_emp IS
        SELECT emp.employee_id, 
               emp.first_name, 
               emp.last_name
        FROM   employees   emp,
               departments dep
        WHERE  emp.department_id   = dep.department_id
        AND    dep.department_name = 'Executive';
BEGIN 
  FOR employee_rec IN c_emp LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_rec.employee_id || ' ' ||
                         'Name: '        || employee_rec.first_name  || ' ' || employee_rec.last_name); -- Output the data
  END LOOP;
END;

The results of running this PL/SQL block are below:

How to know when to use a cursor FOR LOOP vs the OPEN/FETCH/CLOSE statements:

Cursor Attributes

When handling cursors in your PL/SQL logic, you may find that you have to determine the current status of your cursor. In my earlier example, I used the %NOTFOUND attribute. In PL/SQL the following cursor attributes are available: