Day 10

Manipulating Data with
Cursors, DELETE, and
UPDATE

by Tom Luers

PL/SQL cursors provide a way for your program to select multiple rows of data from the database and then to process each row individually. Specifically, a cursor is a name assigned by Oracle to every SQL statement processed. This is done in order to provide Oracle a means to direct and control all phases of the SQL processing. This chapter discusses using cursors and how to define them.

What Is a Cursor?

NEW TERM Two kinds of cursors are used in Oracle: implicit and explicit. PL/SQL implicitly declares a cursor for every SQL statement used. It needs to do this in order to manage the processing of the SQL statement. Implicit cursors are declared by Oracle for each UPDATE, DELETE, and INSERT SQL command. Explicit cursors are declared and used by the user to process multiple rows returned by a SELECT statement. Explicitly defined cursors are constructs that enable the user to name an area of memory to hold a specific statement for access at a later time.

As you recall from earlier in this book, SELECT statements can return zero, one, or many rows of data. When a PL/SQL cursor query returns multiple rows of data, the resulting group of rows is called the active set. This active set is stored by Oracle in the explicitly defined and named cursor that you create. The Oracle cursor is a mechanism used to easily process multiple rows of data. Without cursors, the Oracle developer would have to explicitly fetch and manage each individual row that is selected by the cursor query.

Another feature of the cursor is that it contains a pointer that keeps track of the current row being accessed, which enables your program to process the rows one at a time. Figure 10.1 illustrates an Oracle cursor: It shows the active set, consisting of the rows returned by the cursor's SELECT statement, and the pointer indicating the latest row fetched from the active set.

Figure 10.1.
An Oracle multirow
cursor.

Explicit Cursors

Explicit cursors are defined by the programmer to process a multiple-row active set one record at a time. The following are the steps to using explicitly defined cursors within PL/SQL:

1. Declare the cursor.
2. Open the cursor.
3. Fetch data from the cursor.
4. Close the cursor.

Declaring a Cursor

The first step is to declare the cursor in order for PL/SQL to reference the returned data. This must be done in the declaration portion of your PL/SQL block. Declaring a cursor accomplishes two goals:

• It names the cursor.
• It associates a query with the cursor.

The name you assign to a cursor is an undeclared identifier, not a PL/SQL variable. You cannot assign values to a cursor name or use it in an expression. This name is used in the
PL/SQL block to reference the cursor query.

The Syntax for Defining Cursors

DECLARE cursor_name
is
SELECT statement

In this syntax, cursor_name is the name you assign to the cursor. SELECT statement is the query that returns rows to the cursor active set.

In the following example, the cursor named c_names is defined with a SELECT statement that queries the employee table:

DECLARE c_names
  is
     SELECT emp_name from employee
    WHERE pay_type = `S';

The only constraint that can limit the number of cursors is the availability of memory to manage the cursors. Oracle system administrators will use the OPEN_CURSOR parameter in the init.ora file to help manage this memory use.

Opening the Cursor

Opening the cursor activates the query and identifies the active set. When the OPEN command is executed, the cursor identifies only the rows that satisfy the query. The rows are not actually retrieved until the cursor fetch is issued. OPEN also initializes the cursor pointer to just before the first row of the active set.

The Syntax for the OPEN Command

OPEN cursor_name;

In this syntax, cursor_name is the name of the cursor that you have previously defined.

After the OPEN command is issued, the cursor will look like Figure 10.2, which shows that the active set has retrieved its data from the database. The cursor establishes its pointer at the very top of the the active set; the pointer is before the first row because the FETCH command has not been issued yet.

Figure 10.2.
View of the opened
cursor.

If you try to open a cursor that is already open, you receive the following error:

ORA-06511: PL/SQL: cursor already open

If this error occurs, check your logic or close the cursor earlier in the block and reopen it when needed. You can check the status of the cursor by using the cursor %isopen attribute. Later in the chapter, additional cursor attributes are covered in the section "Explicit Cursor Attributes." The %open attribute is used as demonstrated in the following example:

IF not employee%isopen
THEN
OPEN employee;
END IF;

Fetching Data in a Cursor

Getting data into the cursor is accomplished with the FETCH command. The FETCH command retrieves the rows in the active set one row at a time. The FETCH command is usually used in conjunction with some type of iterative process. The first FETCH statement sorts the active set as necessary. In the iterative processes, the cursor advances to the next row in the active set each time the FETCH command is executed. The FETCH command is the only means to navigate through the active set.

The Syntax for the FETCH Command

FETCH cursor_name INTO record_list;

In this syntax, cursor_name is the name of the previously defined cursor from which you are now retrieving rows—one at a time. record_list is a list of variables that will receive the columns from the active set. The FETCH command places the results of the active set into these variables.