Oracle/PLSQL: where current of statement

Oracle/PLSQL: where current of statement

 

--------------------------------------------------------------------------------

If you plan on updating or deleting records that have been referenced by a select for update statement, you can use the where current of statement.
If you want to delete or update records referenced by select for update, you can use the where current of statement.
The syntax for the where current of statement is either:
Update table_name
Set set_clause
Where current of cursor_name;
Or
Delete from table_name
Where current of cursor_name;
 
The where current of statement allows you to update or delete the record that was last fetched by the cursor.
The where current of statement allows you to update or delete the records finally obtained by cursor.
Updating using the where current of statement
Here is an example where we are updating records using the where current of statement:
The following example uses where current of to update a record:
Create or replace function findcourse
(Name_in in varchar2)
Return number
Is
Cnumber number;
Cursor C1
Is
Select course_number, instructor
From courses_tbl
Where course_name = name_in
For update of policuctor;

Begin
Open C1;
Fetch C1 into cnumber;

If C1 % notfound then
Cnumber: = 9999;
Else
Update courses_tbl
Set policuctor = 'Smith'
Where current of C1;
Commit;
End if;

Close C1;
Return cnumber;
End;
 
Deleting using the where current of statement
Here is an example where we are deleting records using the where current of statement:
The following example uses where current of to delete a record:
Create or replace function findcourse
(Name_in in varchar2)
Return number
Is
Cnumber number;
Cursor C1
Is
Select course_number, instructor
From courses_tbl
Where course_name = name_in
For update of policuctor;

Begin
Open C1;
Fetch C1 into cnumber;

If C1 % notfound then
Cnumber: = 9999;
Else
Delete from courses_tbl
Where current of C1;
Commit;
End if;

Close C1;
Return cnumber;
End;

Article Source: http://www.diybl.com/course/7_databases/oracle/2007114/84247.html