The Cursor Problem:

How do you scan through multiple rows if a cursor is designed to fetch not one row but a whole rowset.


You use a cursor for loop.

Syntax for Oracle 9i:

for Recordset_Name in Cursor_Name loop
statement block
end loop;

Oracle automatically delares the variable Recordset_Name as a %ROWSET datatype and stores the rows fetched by cursor in it. So if you wanted access to any attributes of the row, Recordset_Name.Attribute_Name would work.

If you wanted access to an attribute in the third row, then Recordset_Name.Attribute_name in the third iteration of the loop would do it.

for example:

/*cursor Crsr_Location selects all rows from the table "dept"*/

cursor Crsr_Location is select dept_id,dept_location,dept_name from dept;

/*The for loop scans through the fetched rowset row by row and relocates the sales dpeartment to dallas and all other departments to NY*/
for Location_Rec in Crsr_Location loop
if Location_Rec.dept_name = 'Sales' then
update dept set dept_location = 'Dallas' where dept_id = Location_Rec.dept_id;
update dept set dept_location = 'New York' where dept_id = Location_Rec.dept_id;
end if;
end loop;