After a FETCH has been issued, the cursor will look like Figure 10.3, which shows that the results in the active set are fetched into the PL/SQL variables for use within that PL/SQL block. After each fetch, the cursor pointer moves to the next row in the active set.

Figure 10.3.
View of the opened
cursor after the FETCH
command is issued.

The record list, or variable list, is the PL/SQL structure that receives the fetched rows of data. For each column value retrieved by the cursor's query, there must be a corresponding variable in the INTO list. Additionally, their datatypes must be compatible. If you fetch into a record, the number of columns in the record must match the number of expressions in the select list in the cursor.

If you want to revisit a previously fetched row, you must close and reopen the cursor and then fetch each row in turn. If you want to change the active set, you must assign new values to the input variables in the cursor query and reopen the cursor. This re-creates the active set with the results of the revised query statement.

Closing the Cursor

The CLOSE statement closes or deactivates the previously opened cursor and makes the active set undefined. Oracle will implicitly close a cursor when the user's program or session is terminated. After the cursor is closed, you cannot perform any operation on it, or you will receive an invalid_cursor exception.

The Syntax for the CLOSE Command

CLOSE cursor_name;

In this syntax, cursor_name is the name of the previously opened cursor.

Explicit Cursor Attributes

Each cursor, whether it is explicitly or implicitly defined, carries with it attributes that provide useful data regarding the results of a multirow SELECT. The four cursor attributes are %isopen,

%found, %notfound, and %rowcount. These attributes can be used in any PL/SQL statement. Cursor attributes cannot be used against closed cursors; an invalid_cursor error will be issued if you attempt this.

The %isopen Attribute

The %isopen attribute indicates whether a cursor is open. If the named cursor is open, then this attribute equates to true; otherwise, it will be false. The following example uses the %isopen attribute to open a cursor if it is not already open:

IF c_names%isopen THEN
       process_data_procedure
ELSE
     OPEN  c_names;
END IF;

The %found Attribute
The %found attribute equates to true if the last FETCH statement affects one or more rows. Therefore, the %found attribute is the logical opposite of the %notfound attribute. The %found attribute equates to false when no rows are fetched. Like the %notfound, this attribute also equates to null prior to the first fetch.

The following example illustrates a practical use of the %found attribute:

LOOP
FETCH c_names INTO record_names;
IF c_names%found THEN
process_names_function;
ELSE
EXIT;
END IF;
END LOOP;

The %notfound Attribute

The %notfound attribute is useful in telling you whether a cursor has any rows left in it to be fetched. The %notfound attribute equates to false when there are rows remaining in the cursor. It will equate to true when there are no more rows remaining. After the fetching has started until and including the fetch on the last row, %notfound will be false. Prior to the first fetch, this attribute will equate to null. An error will be returned should you evaluate %notfound on a cursor that is not opened.

The following example illustrates a practical use of the %notfound attribute:

LOOP
 FETCH c_names INTO record_names
  EXIT when c_names%notfound
END LOOP;

The %rowcount Attribute

This attribute returns the number of rows fetched so far for the cursor. Prior to the first fetch, %rowcount is zero. There are many practical applications of the %rowcount attribute. The following example will perform a commit after the first 250 employees' salaries are processed:

LOOP
FETCH c_names INTO record_names
IF c_names%rowcount = 250
COMMIT
ELSE
EXIT;
END IF;
END LOOP;

Explicit Cursor Example

The following example illustrates the use of all four components of a PL/SQL cursor:

DECLARE

v_emp_name   VARCHAR2(32);
v_salary_rate   NUMBER(9,2);
v_payroll_total  NUMBER(9,2);
v_pay_type     CHAR;

CURSOR c_emp is                                     -- cursor declaration
SELECT emp_name, pay_rate, pay_type from employee
WHERE emp_dept_id = 3;

BEGIN

OPEN c_emp;                                           -- opening cursor
LOOP
FETCH  c_emp INTO v_emp_name, v_salary_rate, v_pay_type;    -- FETCH command
EXIT when c_emp%notfound;

IF v_pay_type = `S' THEN
v_payroll_total :=  (v_salary_rate *  1.25);
ELSE
v_payroll_total := (v_salary_rate * 40);
END IF;
INSERT INTO weekly_salary values (v_payroll_total);
END LOOP;

CLOSE c_emp;                                         -- closing cursor
END;

Automated Explicit Cursors

The previous section illustrates the basic mechanics of declaring and using cursors. In many programming situations, there is more than one way to code your logic. This also applies to PL/SQL cursors; there are opportunities to streamline or simplify the coding and usage of

them. One such way is to place the cursor within a FOR loop. This is known as a CURSOR FOR loop. A CURSOR FOR loop will implicitly

• Declare the loop index
• Open the cursor
• Fetch the next row from the cursor for each loop iteration
• Close the cursor when all rows are processed or when the loop exits

CURSOR FOR loops are ideal when you want to loop through all records returned by the cursor. With CURSOR FOR loops, you should not declare the record that controls the loop. Likewise, you should not use CURSOR FOR loops when the cursor operations have to be handled manually. Listing 10.1 illustrates the use of CURSOR FOR loops.

INPUT
Listing 10.1. Using CURSOR FOR loops.

DECLARE
CURSOR c_employees is
      SELECT * from employees
      WHERE pay_type = `H';

BEGIN                                        -- implicit cursor open
  FOR emp_record  in c_employees loop        -- implicit cursor fetch
       process_monthly_hourly_checks
 END LOOP;                                   -- implicit cursor close
 COMMIT;
END;

Implicit Cursors

As mentioned earlier in this chapter, Oracle creates and opens a cursor for every SQL statement that is not part of an explicitly declared cursor. The most recent implicit cursor can be referred to as the SQL cursor. You cannot use the OPEN, CLOSE, and FETCH commands with the implicit cursor. However, you can use the cursor attributes to access information about the most recently executed SQL statement through the SQL cursor.

In the following example, PL/SQL creates an implicit cursor to identify the set of rows that are affected by the UPDATE command:

UPDATE employee
set pay_rate=pay_rate*1.08
WHERE pay-type='S'