Oracle PLSQL之cursor取得是open时的数据
Oracle PLSQL之cursor取得是open时的数据
当我们定义了一个很复杂的cursor,这个cursor需要执行很长时间,在这个cursor执行的过程中,其它用户又修改了该cursor所引用的表的数据时,cursor得到的是修改前还是修改后的数据呢?
答案是cursor得到的始终是cursor在open时的数据,接下来我们将通过一个小实验来验证。
首先,session1执行以下匿名块,该匿名块通过cursor取得t1表的所有数据,不过在open cursor后将暂停30秒,在这30秒中我们将在session2中删除t1表的所有数据:
- DECLARE
- CURSOR c IS
- SELECT deptno
- ,dname
- ,loc
- FROM t1;
- TYPE dept_tab IS TABLE OF c%ROWTYPE;
- l_depts dept_tab;
- BEGIN
- dbms_output.put_line('opening c: ' ||
- to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
- OPEN c;
- dbms_lock.sleep(30);
- dbms_output.put_line('after sleep: ' ||
- to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
- FETCH c BULK COLLECT
- INTO l_depts;
- CLOSE c;
- FOR i IN l_depts.FIRST .. l_depts.LAST
- LOOP
- dbms_output.put_line(l_depts(i).deptno || ', ' || l_depts(i)
- .dname || ', ' || l_depts(i).loc);
- END LOOP;
- END;
- 第二步,session2执行以下语句:
- 22:35:21 SQL> select * from t1;
- DEPTNO DNAME LOC
- ------ -------------- -------------
- 10 ACCOUNTING NEW YORK
- 20 RESEARCH DALLAS
- 30 SALES CHICAGO
- 40 OPERATIONS BOSTON
- 22:35:29 SQL> delete from t1;
- 4 rows deleted
- 22:35:33 SQL> commit;
- Commit complete
- 22:35:35 SQL> select * from t1;
- DEPTNO DNAME LOC
- ------ -------------- -------------
- 22:35:38 SQL>
- 最后,观察session1的输出:
- opening c: 2011-10-26 22:35:25
- after sleep: 2011-10-26 22:35:55
- 10, ACCOUNTING, NEW YORK
- 20, RESEARCH, DALLAS
- 30, SALES, CHICAGO
- 40, OPERATIONS, BOSTON
- 由于在22:35:25我们就已经打开了游标,所以结果依然能输出t1表的所有数据,尽管在22:35:35之前我们已经删除了t1表的所有数据并提交,而cursor取数据(fetch)发生在22:35:55之后。
- ref:
- The OPEN statement executes the query associated with a cursor. It allocates database resources to process the query and identifies the result set -- the rows that match the query conditions.
- http://download.Oracle.com/docs/cd/B19306_01/appdev.102/b14261/open_statement.htm#i35173
评论暂时关闭