Explain about cursor in oracle.
well ill try to show a couple things here but i would read the oracle documentation if i were you :)
there are 2 forms of cursors, explicit and implicit.
explicit is the kind that you declare, cursor my_cur is select * from my_table;
implicit is of the form for rec in (select * from my_table) loop
in both cases the data within the cursor is a snapshot of a certain point and time. what i mean is when the cursor is opened it will contain the data that the query would have returned had you just run it in sqlplus. if your session adds more data to the table queried by the cursor, it will not be in the cursor itself unless that cursor is closed and opened again.
thought of another way, a cursor is a result set in memory, that result set doesnt ever change. if the cursor is closed and opened again, it is a new result set in memory... a new cursor.
-
[138]dave@ORADB> CREATE TABLE t (col NUMBER);
-
-
Table created.
-
-
[138]dave@ORADB> INSERT INTO t
-
2 SELECT DBMS_RANDOM.VALUE(0, 20)
-
3 FROM DUAL
-
4 CONNECT BY ROWNUM <= 4;
-
-
4 rows created.
-
-
[138]dave@ORADB> DECLARE
-
2 CURSOR c_rows
-
3 IS
-
4 SELECT col
-
5 FROM t;
-
6
-
7 var NUMBER;
-
8 v_count NUMBER := 0;
-
9 BEGIN
-
10 OPEN c_rows;
-
11
-
12 LOOP
-
13 FETCH c_rows
-
14 INTO var;
-
15
-
16 EXIT WHEN c_rows%NOTFOUND;
-
17
-
18 INSERT INTO t
-
19 VALUES (DBMS_RANDOM.VALUE(0, 20) );
-
20
-
21 v_count := v_count + 1;
-
22
-
23 END LOOP;
-
24
-
25 DBMS_OUTPUT.put_line('initial cursor count is ' || v_count);
-
26 DBMS_OUTPUT.put_line('but we inserted a new row for each existing row');
-
27
-
28 CLOSE c_rows;
-
29
-
30 v_count := 0;
-
31
-
32 OPEN c_rows;
-
33
-
34 LOOP
-
35 FETCH c_rows
-
36 INTO var;
-
37
-
38 EXIT WHEN c_rows%NOTFOUND;
-
39 v_count := v_count + 1;
-
40 END LOOP;
-
41
-
42 DBMS_OUTPUT.put_line('new cursor count is ' || v_count);
-
43
-
44 CLOSE c_rows;
-
45
-
46 v_count := 0;
-
47
-
48 FOR rec IN (SELECT col
-
49 FROM t) LOOP
-
50 v_count := v_count + 1;
-
51 END LOOP;
-
52
-
53 DBMS_OUTPUT.put_line('and the for loop implicit cursor count is ' || v_count);
-
54 END;
-
55 /
-
initial cursor count is 4
-
but we inserted a new row for each existing row
-
new cursor count is 8
-
and the for loop implicit cursor count is 8
-
-
PL/SQL procedure successfully completed.
-
-
-