Cursors

Previously, we discussed implicit cursors and their attributes. These cursors and automatically created and managed by Oracle for each DML statement submission. In contrast, the explicit cursors are those that explicitly declared and controlled by database programmers. So, when we say a PL/SQL cursor, we generally mean an explicit cursor defined in the PL/SQL code block.

How to Use an Explicit Cursor in Four Steps

A cursor involves a four-step lifecycle: declare, open, fetch, and close. Unlike implicit cursors, where these steps are automatically managed by Oracle, explicit cursors require you to explicitly write statements for each step. This direct control over cursors provides greater control and flexibility, allowing developers to handle individual rows from the query result set in a procedural manner.

Cursor Declaration

An explicit cursor is used to access each row in a query result set. To achieve this, the cursor must first be declared with a SELECT statement and an appropriate name in the declaration section. 

The general syntax for declaring a cursor is:

CURSOR cursor_name[(parameter1, parameter2, ...)] 
IS
SELECT column1, column2 FROM table
;

Observe that the INTO keyword does not exist in the CURSOR declaration. This is because the cursor merely defines the query; the INTO keyword is used later during the fetch operation to store the retrieved data into variables. 

In the cursor declaration, you can include an optional list of parameters. These parameters act as placeholders that can be passed to the SELECT statement when the cursor is opened, allowing the query to be adjusted based on the PL/SQL variables.

Open Cursor

Subsequently, in the executable section, you should open the declared cursor using the following syntax:

OPEN cursor_name[(parameter1, parameter2, ...)];

If you declared the cursor with parameters, you should provide the corresponding arguments when opening the cursor. These arguments will be passed to the parameters in the SELECT statement, ensuring that the query operates based on the specified input values.

Fetch Rows from the Cursor

Once the cursor is declared and opened, you can retrieve rows from the query result set one at a time and store them in a PL/SQL variable. To do this you must use an INTO clause to store a single row's values, typically within a LOOP statement for row-by-row processing. 

General syntax for this step is as follows:

LOOP
    FETCH cursor_name INTO variable1, variable2, ...;
    EXIT WHEN cursor_name%NOTFOUND;
END LOOP;

Of course, if you expect your query to retrieve only a single row, you can omit the loop structure and simply use FETCH-INTO. However, it is more common to fetch an explicit cursor within a loop, as the result from a SELECT statement typically contains more than one rows. In this case, an EXIT-WHEN statement is used with the NOTFOUND attribute. Similar to an implicit cursor, the NOTFOUND attribute of an explicit cursor returns true when no rows are returned. During each iteration, FETCH-INTO retrieves a row from the query result, and the loop will be terminated when there is no more row to fetch. 

The retrieved values, which are the column values of each row in the table, must correspond to the PL/SQL variables specified in the INTO clause. The number and data types of the variables in the INTO clause must be compatible with the columns in the SELECT statement to ensure proper assignment of the values; otherwise, Oracle will raise an error.

Close Cursor

The final step is to close the current cursor. This step is necessary as it release the resources associated with the cursor once processing is complete. Failing to close the cursor can lead to memory leaks and resource exhaustion, as each open cursor consumes system resources. 

CLOSE cursor_name;

Syntax is simple; just place the current cursor name after the keyword CLOSE. 

Now, let's examine an example of declaring, opening, fetching, and closing an explicit cursor within a PL/SQL code block. The code below retrieves the name of employees in department_id = 60 using a cursor.

The PL/SQL code block above demonstrates typical usage of an explicit cursor. It begins by declaring a variable, v_name, to store the fetched employee names. A cursor, cur_emp_dept is declared with a parameter cp_dept_id, which specified the department from which to fetch the employee names one at a time. Within the loop, the fetched row is assigned to v_name, and an EXIT-WHEN statement is used to terminate the loop when there are no more rows to fetch. Each employee name is printed using dbms_output.put_line(v_name). Finally, the cursor is closed after processing is complete, ensuring that resources are properly released.

Cursor Attributes

In addition to the NOTFOUND attribute mentioned earlier, here are some of the other attributes that are commonly used with explicit cursors:

• %FOUND:
• A Boolean attribute that indicates whether the last fetch operation was successful.
• Returns true if a row was fetched, false otherwise.
• %NOTFOUND:
• The opposite of %FOUND.
• Returns true if the last fetch operation did not find a row, false otherwise.
• %ROWTYPE:
• Defines the structure of a record variable that can hold a single row from the cursor's result set.
• Used to declare variables that match the column structure of a cursor's query.
• %ROWCOUNT:
• An integer attribute that returns the number of rows fetched so far by the cursor.
• %ISOPEN:
• A Boolean attribute that indicates whether the cursor is currently open.
• Returns true if the cursor is open, false otherwise.

For example:

In this example, the %ROWTYPE attribute is used to define the structure of vr_employee, the %NOTFOUND attribute is used to control the loop termination, and the %ROWCOUNT attribute is used to track the number of rows fetched. These attributes are often useful for writing flexible and efficient PL/SQL codes.

Using FOR-LOOP with Cursors

While it is possible to fetch a cursor with other kinds of loop structures, such as LOOP-EXIT-WHEN or WHILE-LOOP, most commonly, you'll use a FOR-LOOP to iterate through the fetched rows.

Recall that the basic syntax of the FOR-LOOP statement is:

FOR loop_variable IN [REVERSE] start_value..end_value
LOOP 
    -- statements to executes
END LOOP;

However, PL/SQL also has another form of FOR-LOOP specifically designated to be used with cursor.

FOR record_name IN cursor_name(argument1, argument2, ...) 
LOOP 
    -- statements to executes
END LOOP;

This form is particularly useful when retrieving multiple column values from a query. In this case, we use a record as a loop variable, which eliminates the need to specify separate PL/SQL variables for each column of the query. This allows for direct access to the column values using the record and dot notation, as well as simplified code with enhanced readability.

This practice also removes the necessity for manual opening, fetching, and closing of a cursor (although you still need to declare the cursor in the declaration section); the cursor is opened at the IN clause with the specified arguments and is implicitly closed after the loop ends. For example:

Declaring and Using Cursor Variables

So far, we used the cursor with the declared SQL query. We do not reassign another query to the cursor object. In this sense, we can think of the cursors we've seen so far as "cursor constants," not cursor variables; remember that the constant does not allow any reassignments once declared, whereas you can reassign values to variables as many times as needed.

Cursor variables, on the other hand, offer more flexibility. They are declared using the REF CURSOR type and can be assigned to different cursors during runtime. This allows you to dynamically determine the query to be executed based on various conditions or inputs. A cursor variable has the following features:

• Can be reassigned with a different query.
• Can be passed into subprograms as an argument.
• Can be used with cursor attributes.

Declaring a Cursor Variable

One of the ways to declare a cursor variable is to first define a reference type for the cursor using the REF CURSOR type, and then declare a cursor instance with the reference type:

-- Define the cursor type
TYPE name_curtype IS REF CURSOR [RETURN data_type];

-- declare an instance
cur_name name_curtype;

In the syntax, the RETURN data_type specifies the data type of each record returned by the SQL queries that will be associated to the cursor and is defined by the %ROWTYPE attribute. This part is optional and can be omitted. When you specify the RETURN data_type, it is called the strong cursor type, as it fixes the result set of the query, i.e., cursor variables declared with strong cursor type cannot change the column structure of the result set. On the other hand, if RETURN data_type is omitted, the cursor type is called as weak cursor type and allows some more flexibilities. 

Another way to declare a cursor variable is to use SYS_REFCURSOR, which is a built-in REF CURSOR type. In this method, you don't have to cursor type; you just need to declare a cursor variable as follows:

cur_variable SYS_REFCURSOR;

Since you don't specify the data type of the returning records, cursor variables created by this method are also weak.

Opening the Cursor Variable and Connecting it with an SQL Query

Opening a cursor variable requires a connection to an SQL query, using the following syntax:

OPEN cur_variable FOR <select statement>;

The SELECT statement specified in the FOR clause can be modified, allowing a cursor variable to be associated with many different SQL queries. This is particularly true for weakly defined cursor variables. For example, consider the following cursor definition:

TYPE test_curtype IS REF CURSOR RETURN hr.departments%ROWTYPE;
test_curvar test_curtype;

Strongly defined cursor types like this already have a specific record structure to be stored, so the SELECT statement in the OPEN-FOR must correspond to that record.

-- This works!
OPEN test_curvar FOR SELECT * FROM hr.departments;

-- These won't work!
OPEN test_curvar FOR SELECT department_id FROM hr.departments;
OPEN test_curvar FOR SELECT * FROM hr.employees;

Conversely, if the cursor variable is weakly defined as follows, all of the OPEN-FOR statements above will work:

TYPE test_curtype IS REF CURSOR;
test_curvar test_curtype;

Fetching the Result Set from the Cursor Variable

To retrieve rows from the query result set of a cursor variable, you should use the FETCH-INTO statement as follows:

FETCH cur_variable INTO variable1, variable2, ...;
FETCH cur_variable INTO record_name;

When you fetch the result set from the cursor variable, you don't have to worry about manually closing the cursor variable; it is automatically closed by the PL/SQL engine.

Now, let's examine the following example:

In this example, 

This time, let's rewrite the code block above using SYS_REFCURSOR:

 

We see that the same results are obtained without specifying a separate cursor type; when using a weak cursor type, you can take advantage of the Oracle built-in SYS_REFCURSOR.

Passing the Cursor Variable as an Argument

A cursor variable can also be passed into a subprogram as an argument. This is where strongly defined cursor type is preferred over SYS_REFCURSOR (or any other weakly defined cursor types): cursor variables declared with a strong cursor type enforce type checking, ensuring that the cursor variable passed to a subprogram has the correct structure and column types. This helps prevent runtime errors.

In the example above, 

Cursor Expressions

In PL/SQL, cursor expression refers to using cursors as a column in a SELECT statement. For example, let's consider the following SQL statement, using a subquery:

This query 

The subquery embedded in the SQL's subquery must not return multiple rows. Thus, if you modify the query above as below, 

However, within a PL/SQL code block, you can use a cursor expression instead of the subquery. This is because a PL/SQL cursor fetches rows from a query one at a time. For example: