Previous | Table of Contents | Next

Page 201

COOLIDGE
JOHNSON
REAGAN
BUSH
JOHNSON
CLINTON
CARTER
FORD
NIXON
KENNEDY
EISENHOWER
TRUMAN
ROOSEVELT
HOOVER
WILSON

Notice that the Declare section contains only the cursor definition. It does not have any local variable definitions. The Executable section no longer contains any cursor commands. PL/SQL moves the cursor values into local variables qualified by the name of the cursor counting variable. The dbms_output command on line 6 illustrates this variable naming scheme. The Cursor For loop also opens the cursor, fetches each record into the loop, determines when
the last record was fetched, and closes the cursor. Each of these commands is implicit to the Cursor For loop.

The Cursor For loop also enables the developer to define the cursor inside the loop, as shown in Listing 9.8. The program in this listing defines the cursor's select statement in the For declaration line. Because there is not a cursor name, the name of the Cursor For loop qualifies the local variables used in the loop.

Listing 9.8 L_09_08.SQL—Defining the Cursor Inside the Cursor For Loop

SQL> set serveroutput on;
SQL> begin
 2  for cnt_var in (select * from employee)
 3   loop
 4    dbms_output.put_line (cnt_var.last_name);
 5   end loop;
 6 end;
 7 /
COOLIDGE
JOHNSON
.
.
ANTHONY
ROOSEVELT
PL/SQL procedure successfully completed.

Listing 9.8 embodies these built-in functions. You can see the beauty and sophistication of the Cursor For loop. It leaves all the work to Oracle to process the records in the cursor, enabling the developer to concentrate on the processing statements.

Page 202

Using the For Update Of

When you issue a select statement, Oracle does not lock the records in the table. Locking consists of marking a table record so that other users cannot modify the record. This is done for concurrency reasons. A user would not want another user to modify a record that he was in the process of modifying. Locking procedures prevent this from occuring. The update and delete commands lock the record. Normally, a select statement does not.

Sometimes the developer wants to lock the records selected for the cursor, preventing other users from updating the records until they have been released. A Rollback or Commit command releases locked records. The cursor For Update option enables you to lock the records selected by the cursor. Listing 9.9 illustrates an example of the For Update option. In this example, cursor "a" has a For Update option specified.

Listing 9.9 L_09_09.sql—Locking Cursor Records with the For Update Option

SQL> set serveroutput on;
SQL> declare
 2  old_lname    employee.last_name%type;
 3  pay#      employee.payroll_number%type;
 4  cursor a is
 5   select payroll_number, last_name from employee
 6    for update;
 7 begin
 8  open a;
 9  fetch a into pay#, old_lname;
 10  while (a%found) loop
 11   update employee set last_name = upper(last_name)
 12    where payroll_number = pay#;
 13   fetch a into pay#, old_lname;
 14  end loop;
 15  close a;
 16  commit;
 17 end;
 18 /

PL/SQL procedure successfully completed.

Because the select statement on line 5 does not have a where clause, all the records in the employee table will be selected for the cursor. The For Update option causes all the records in the table to be locked from update or delete. The record locking occurs when the cursor is opened. The While loop executes an update statement for each row of the table. The update statement has a where clause. It uses the payroll number of each employee to determine the record to be updated. Upon execution of the Commit command, the locks are released.

Page 203

CAUTION
You should not issue Commit commands until all the records in the cursor have been processed. Issuing a commit while a For Update cursor is open closes the cursor. The application will not be capable of fetching any records from the cursor. This does not cause your application to issue an exception statement, and it continues to execute any statements not needing the cursor. You may be deceived into thinking your application processed all the records when in reality only a partial set were processed.

You can follow the For Update clause with the keyword Of and some table column name s. This option provides as an easy mechanism to identify the columns you intend to modify with the cursor. If the For Update Of clause does not have a column contained in the set of fields contained in the cursor's select clause, the cursor rows will not be locked. In order to lock the fetched row, one of the columns must be contained in the For Update Of column list. If the select list and the For Update Of columns do not have to match. The cursor operates correctly, but the records will not be locked.

Using the Where Current Of Option

The Where Current Of option is used with the Update and Delete DML commands that were described in Chapter 7, "Modifying Your Tables with DML Commands." The option enables you to avoid having to define a where clause for the statements. The where clause is used with these statements to identify the records to update or delete. The Where Current Of option tells Oracle that the record to be modified is the current record in the cursor.

In Listing 9.9 (refer to the preceding section), the update statement contained in the While loop has a where clause specified. It needs this clause to identify the record from the employee table to update. Provisions need to be designed in the application to identify the primary key of the record to be updated. To cause the update statement to modify the correct record, you must also move this value to the where clause of the update statement.

The Where Current Of cursor name option simplifies this process. When this option is specified, Oracle updates or deletes the last record fetched by the cursor, eliminating the need to include where clauses in the Update or Delete commands. Listing 9.10 illustrates the use of this option.

Listing 9.10 L_09_10.SQL—Using the Where Current Of Option to Update Records

SQL> set serveroutput on;
SQL> declare
 2  old_lname    employee.last_name%type;
 3  cursor a is
 4   select last_name from employee
 5    for update;
 6 begin
 7  open a;

Previous | Table of Contents | Next