Do database cursors pick up changes to the underlying data?
Quick question about cursors (in particular Oracle cursors).
Let's say I have a table called "my_table" which has two columns, an ID and a name. There are millions of rows, but the name column is always the string 'test'.
I then run this PL/SQL script:
declare
cursor cur is
select t.id, t.name
from my_table t
order by 1;
begin
for cur_row in cur loop
if (cur_row.name = 'test') then
dbms_output.put_line('everything is fine!');
else
dbms_output.put_line('error error error!!!!!');
exit;
end if;
end loop;
end;
/
开发者_JAVA技巧
if I, while this is running, run this SQL:
update my_table
set name = 'error'
where id = <max id>;
commit;
will the cursor in the PL/SQL block pick up that change and print out "error error error" and exit? or will it not pick up the change at all ... or will it even allow the update to my_table?
thanks!
A cursor effectively runs a SELECT and then lets you iterate over the result set, which is kept in a snapshot of the DB state. Because your result set has already been fetched, it won't be affected by the UPDATE statement. (Handling things otherwise would require you to re-run the query every time you advanced your cursor!)
See:
http://www.techonthenet.com/oracle/cursors/declare.php
精彩评论