Answer Posted / tvgiriprasad
If you want to UPDATE or DELETE record from table by using cursor which is defined from same table,"WHERE CURRENT OF" clause can be used. We have to create cursor with "FOR UPDATE" clause to use above clause.
The most recent record fetched from the table (by looping of cursor records) should be updated or deleted by using "WHERE CURRENT OF". When a cursor open with FOR UPDATE clause ,cursor's active set will have row level Exclusive Lock. Other sessions can query the table records, and not able to delete/update or SELECT with FOR UPDATE clause.
Example: Want to update value for GRADE column to '1' for the student who has Id = '1'.
Declare
cursor C1 is
select st_id, grade,st_last_name from student where st_id = 3;
for update of grade;
[ variable declaration ……] ;
begin
open C1;
fetch C1 into v_id,v_grade,v_name;
if C1%notfound then
dbms_output.put_line (‘No Record is found.’);
else
update student set grade=1 WHERE CURRENT OF;
COMMIT;
END IF;
CLOSE C1;
end;
| Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
What is the quickest way to export a table to a flat file?
What are the general rules on data consistency?
How to see free space of each tablespace?
How do you increase the OS limitation for open files (LINUX and/or Solaris)?
Explain self joins in oracle?
What is a data dictionary and how can it be created?
What is the data type of dual table?
How to use like conditions in oracle?
Explain the use of grant option in exp command.
How to update values in a table in oracle?
How to define an oracle sub procedure?
How to convert characters to times in oracle?
How many categories of data types in oracle?
Please send me Informatica 8.1 certification dumps, my mail id mona85gupta@gmail.com
What is truncate oracle?
