[Chapter 6] 6.2 Cursors in PL/SQL

6.2 Cursors in PL/SQL

When you execute a SQL statement from PL/SQL, the Oracle RDBMS assigns a private work area for that statement. This work area contains information about the SQL statement and the set of data returned or affected by that statement. The PL/SQL cursor is a mechanism by which you can name that work area and manipulate the information within it.

In its simplest form, you can think of a cursor as a pointer into a table in the database. For example, the following cursor declaration associates the entire employee table with the cursor named employee_cur:

CURSOR employee_cur IS SELECT * FROM employee;

Once I have declared the cursor, I can open it:

OPEN employee_cur;

And then I can fetch rows from it:

FETCH employee_cur INTO employee_rec;

and, finally, I can close the cursor:

CLOSE employee_cur;

In this case, each record fetched from this cursor represents an entire record in the employee table. You can, however, associate any valid SELECT statement with a cursor. In the next example I have a join of three tables in my cursor declaration:

DECLARE
   CURSOR joke_feedback_cur
   IS
      SELECT J.name, R.laugh_volume, C.name
        FROM joke J, response R, comedian C
       WHERE J.joke_id = R.joke_id
         AND J.joker_id = C.joker_id;
BEGIN
   ...
END;

Here, the cursor does not act as a pointer into any actual table in the database. Instead, the cursor is a pointer into the virtual table represented by the SELECT statement (SELECT is called a virtual table because the data it produces has the same structure as a table -- rows and columns -- but it exists only for the duration of the execution of the SQL statement). If the triple-join returns 20 rows, each row containing the three columns in the preceding example, then the cursor functions as a pointer into those 20 rows.

6.2.1 Types of Cursors

You have lots of options in PL/SQL for executing SQL, and all of them occur as some type of cursor. Generally, there are two types of SQL that you can execute in PL/SQL: static and dynamic. SQL is static if the content of the SQL statement is determined at compile time. A SQL statement is dynamic if it is constructed at runtime and then executed.

Dynamic SQL is made possible in PL/SQL only through the use of the DBMS_SQL built-in package (see Appendix C, Built-In Packages ). All other forms of SQL executed inside a PL/SQL program represent static SQL; these forms of cursors are the focus of the remainder of this chapter.

Even within the category of static SQL, we have further differentiation. With the advent of PL/SQL Release 2.3, you can choose between two distinct types of cursor objects:

Static cursor objects

These are the really static cursors of PL/SQL. The SQL is determined at compile time, and the cursor always refers to one SQL statement, which is known at compile time. The examples shown earlier in this chapter are static cursors.

Unless otherwise noted, any reference to "static cursor" refers to this sub-category of static (as opposed to dynamic) cursors.

Cursor variables

You can declare a variable which references a cursor object in the database. Your variable may refer to different SQL statements at different times (but that SQL is defined at compile time, not run time).

The cursor variable is one of the newest enhancements to PL/SQL and will be unfamiliar to most programmers. Cursor variables act as references to cursor objects. As a true variable, a cursor variable can change its value as your program executes. The variable can refer to different cursor objects (queries) at different times. You can also pass a cursor variable as a parameter to a procedure or function. Cursor variables are discussed later in this chapter.

Static PL/SQL cursors have been available since PL/SQL Version 1. The static version of cursors "hardcodes" a link between the cursor name and a SELECT statement. The static cursor itself comes in two flavors: implicit and explicit.

PL/SQL declares and manages an implicit cursor every time you execute a SQL DML statement, such as an INSERT or a SELECT that returns a single row.

You, the programmer, define your own explicit cursors in your code. You must use an explicit cursor when you need to retrieve more than one row of data at a time through a SELECT statement. You can then use the cursor to fetch these rows one at a time. The set of rows returned by the query associated with an explicit cursor is called the active set or result set of the cursor. The row to which the explicit cursor points is called the current row of the result set.

The bulk of this chapter is devoted to the management of static, explicit cursors. All information about cursor variables is localized in Section 6.12, "Cursor Variables" . Any references to PL/SQL cursors and cursor characteristics outside of that section will pertain to static cursors.

6.2.2 Cursor Operations

Regardless of the type of cursor, PL/SQL performs the same operations to execute a SQL statement from within your program:

PARSE: The first step in processing an SQL statement is to parse it to make sure it is valid and to determine the execution plan (using either the rule-based or cost-based optimizer).
BIND: When you bind, you associate values from your program (host variables) with placeholders inside your SQL statement. For static SQL, the SQL engine itself performs these binds. When you use dynamic SQL, you explicitly request a binding of variable values.
OPEN: When you open a cursor, the bind variables are used to determine the result set for the SQL statement. The pointer to the active or current row is set to the first row. Sometimes you will not explicitly open a cursor; instead the PL/SQL engine will perform this operation for you (as with implicit cursors).
EXECUTE: In the execute phase, the statement is run within the SQL engine.
FETCH: If you are performing a query, the FETCH command retrieves the next row from the cursor's result set. Each time you fetch, PL/SQL moves the pointer forward in the result set. When working with explicit cursors, remember that if there are no more rows to retrieve, then FETCH does nothing (it does not raise an error).
CLOSE: The CLOSE statement closes the cursor and releases all memory used by the cursor. Once closed, the cursor no longer has a result set. Sometimes you will not explicitly close a cursor; instead the PL/SQL engine will perform this operation for you (as with implicit cursors).

Figure 6.1 shows how some of these different operations are used to fetch information from the database into your PL/SQL program.

6.2 Cursors in PL/SQL

6.2.1 Types of Cursors

6.2.2 Cursor Operations

Figure 6.1: Using cursor operations to fetch database information into your program