Lab 15.1 Using Parameters with Cursors and FOR UPDATE Cursors

Lab Objectives

After this Lab, you will be able to:

Use Parameters in a Cursor

Use a FOR UPDATE Cursor

Use the WHERE CURRENT OF Clause

Cursors with Parameters

A cursor can be declared with parameters. This enables a cursor to generate a specific result set, which is, on the one hand, narrow, but, on the other hand, reusable. A cursor of all the data from the ZIPCODE table may be very useful, but it would be more useful for certain data processing if it held information for only one state. At this point, you know how to create such a cursor. But wouldn't it be more useful if you could create a cursor that could accept a parameter of a state and then run through only the city and zip for that state?

FOR EXAMPLE

 CURSOR c_zip (p_state IN zipcode.state%TYPE) IS    SELECT zip, city, state      FROM zipcode     WHERE state = p_state;

The main points to keep in mind for parameters in cursors are as follows :

Cursor parameters make the cursor more reusable.
Cursor parameters can be assigned default values.
The scope of the cursor parameters is local to the cursor.
The mode of the parameters can only be IN.

When a cursor has been declared as taking a parameter, it must be called with a value for that parameter. The c_zip cursor that was just declared is called as follows:

 OPEN c_zip (parameter_value)

The same cursor could be opened with a FOR CURSOR loop as follows:

 FOR r_zip IN c_zip('NY') LOOP  ...