数据库游标是否接受对基础数据的更改? [英] Do database cursors pick up changes to the underlying data?

问题描述

有关游标(特别是Oracle游标)的快速问题.

Quick question about cursors (in particular Oracle cursors).

比方说，我有一个名为"my_table"的表，该表具有两列，即ID和名称.有数百万行，但名称列始终是字符串"test".

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'.

然后我运行以下PL/SQL脚本:

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; 
 /

如果我正在运行，请运行以下SQL:

if I, while this is running, run this SQL:

 update my_table 
   set name = 'error'
  where id = <max id>;
commit;

PL/SQL块中的光标会拾取该更改并打印出错误错误错误"并退出吗?还是根本不接受更改...还是允许更新到my_table?

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?

谢谢！

推荐答案

游标有效地运行SELECT，然后让您遍历结果集，该结果集保存在数据库状态的快照中.由于您的结果集已经被提取，因此不会受到UPDATE语句的影响. (否则，在每次移动光标时都需要重新运行查询！)

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!)

请参阅:

http://www.techonthenet.com/oracle/cursors/declare.php

这篇关于数据库游标是否接受对基础数据的更改?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！