Lab 15.1 Using Parameters with Cursors and FOR UPDATE Cursors
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?
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 :
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 ...