What WHERE CURRENT OF clause does in a cursor?
- 2 Answers
- 3154 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
Answer / kamala k n
return latest fetch row to perform DML on it it is unique
| Is This Answer Correct ? | 0 Yes | 0 No |
What is a heap related to database ?
1 Answers TCS, University of Edinburgh,
What is a Shared SQL pool ?
How do I recompile a procedure in oracle?
How to export your own schema?
Table1- have two column filename data AFGDFD-20112011 hi how r u bsdasd-23042011 name shoud be in bold Now i want output like filename data AFGDFD hi how r u bsdasd name shoud be in bold Kindly answer this
What do you mean by a deadlock?
What is varray?
What is Library Cache in Oracle?
how to we delete a row using varray
SQL> CREATE TABLE to_table 2 (col1 NUMBER); Table created. SQL> CREATE OR REPLACE TRIGGER statement_trigger 2 AFTER INSERT ON to_table 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('After Insert Statement Level'); 5 END; 6 / Trigger created. SQL> CREATE OR REPLACE TRIGGER row_trigger 2 AFTER INSERT ON to_table 3 FOR EACH ROW 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE('After Insert Row Level'); 6 END; 7 / Trigger created. SQL> INSERT INTO TO_TABLE VALUES(1); After Insert Row Level After Insert Statement Level 1 row created. SQL> BEGIN 2 INSERT INTO TO_TABLE VALUES(2); 3 INSERT INTO TO_TABLE VALUES(3); 4 INSERT INTO TO_TABLE VALUES(4); 5 INSERT INTO TO_TABLE VALUES(5); 6 INSERT INTO TO_TABLE VALUES(6); 7 INSERT INTO TO_TABLE VALUES(7); 8 INSERT INTO TO_TABLE VALUES(8); 9 INSERT INTO TO_TABLE VALUES(9); 10 INSERT INTO TO_TABLE VALUES(0); 11 END; 12 / WAT LL BE THE O/P??? XPLAIN IT>>>>
How to export several tables together?
How to list all indexes in your schema?
Oracle General 
