登 录
首先,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
抱歉!评论已关闭.