SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');
1 row created.
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2
3 v_rowid ROWID;
4 v_rowcount NUMBER := 0;
5
6 CURSOR emp_cur1 IS SELECT rowid FROM emp WHERE id > 50;
7
8 CURSOR emp_cur2 IS SELECT rowid FROM emp WHERE id > 50;
9
10 BEGIN
11
12 OPEN emp_cur1;
13
14 DELETE FROM emp WHERE id > 50;
15
16 OPEN emp_cur2;
17
18
19 FETCH emp_cur1 INTO v_rowid;
20 IF emp_cur1%ROWCOUNT > 0
21 THEN
22 DBMS_OUTPUT.PUT_LINE('Cursor 1 includes the deleted rows');
23 ELSE
24 DBMS_OUTPUT.PUT_LINE('Cursor 1 does not include the deleted rows');
25 END IF;
26
27 v_rowcount := 0;
28
29 FETCH emp_cur2 INTO v_rowid;
30 IF emp_cur2%ROWCOUNT > 0
31 THEN
32 DBMS_OUTPUT.PUT_LINE('Cursor 2 includes the deleted rows');
33 ELSE
34 DBMS_OUTPUT.PUT_LINE('Cursor 2 does not include the deleted rows');
35 END IF;
36
37 CLOSE emp_cur1;
38 CLOSE emp_cur2;
39
40 ROLLBACK;
41
42 EXCEPTION
43 WHEN OTHERS
44 THEN
45 DBMS_OUTPUT.PUT_LINE(sqlerrm);
46 END;
47 /
Cursor 1 does not include the deleted rows
Cursor 2 does not include the deleted rows
PL/SQL procedure successfully completed.
SQL>
SQL> SET ESCAPE OFF
SQL>
SQL> drop table emp;
Table dropped.
SQL>