Re: Cursor concept

"Gerard Lacroix" <kochel_verz_at_yahoo.com> a écrit dans le message de news: 1134005308.764144.78180_at_g14g2000cwa.googlegroups.com...
| Hi !
| I tried to show a friend that pl/sql cursors are in fact pointers and
| do not retrive all rows when you open them. So I made this very simple
| test case, but I don't understand the output. I think the answer comes
| from the multiversioning side, but I 'd like to understand clearly this
| case with a cursor. May be it is not just a memory position isn't it ?
|
| Thanks.
|
| C:\>sqlplus scott/tiger
|
| SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 7 22:40:55 2005
|
| Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
|
|
| Connected to:
| Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
| With the Partitioning, OLAP and Oracle Data Mining options
| JServer Release 9.2.0.1.0 - Production
|
| SQL> create table t as select rownum a from user_objects where rownum <
| 10;
|
| Table created.
|
| SQL> declare
| 1 cursor cur_test is
| 2 select a
| 3 from t
| 4 order by a;
| 5 v_test t.a%type;
| 6 cont boolean := TRUE;
| 7 begin
| 8 open cur_test;
| 9 loop
| 10 fetch cur_test into v_test;
| 11 exit when cur_test%notfound;
| 12 if cont then
| 13 begin
| 14 delete from t where a = 9;
| 15 cont := FALSE;
| 16 commit;
| 17 end;
| 18 end if;
| 19 dbms_output.put_line(v_test);
| 20 end loop;
| 21 close cur_test;
| 22 end;
| 23 /
| 1
| 2
| 3
| 4
| 5
| 6
| 7
| 8
| 9
|
| PL/SQL procedure successfully completed.
|

You don't what you don't understand. Is this "why there is 9 in output?"?

1. Oracle guarantees the result of a query/cursor at the time it is opened. In your case before the delete, so 9 is in the (virtual) result set.
2. Moreover, as you have an "order by" clause in your clause, the result is actually materialized in sort segments and fetches come from these ones.

Regards
Michel Cadot Received on Thu Dec 08 2005 - 00:16:21 CST