利用ROWID批量删除数据可能会误删数据

SQL> CREATE TABLE TEST(ID INT,NAME CHAR(6),COL1 CHAR(2000) DEFAULT 'YSP',

COL2 CHAR(2000) DEFAULT 'YSP',COL3 CHAR(2000) DEFAULT 'YSP');

Table created.

SQL> INSERT INTO TEST(ID,NAME) VALUES(1,'A');

1 row created.

SQL> INSERT INTO TEST(ID,NAME) VALUES(2,'B');

1 row created.

SQL> INSERT INTO TEST(ID,NAME) VALUES(3,'C');

1 row created.

SQL> INSERT INTO TEST(ID,NAME) VALUES(4,'D');

1 row created.

SQL> INSERT INTO TEST(ID,NAME) VALUES(5,'E');

1 row created.

SQL> COMMIT;

SQL> SELECT  ROWID,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,T.ID,T.NAME FROM TEST T;

ROWID                  BLOCK#         ID NAME
------------------ ---------- ---------- ------------
AAA4WEAAGAAAADMAAA        204          1 A
AAA4WEAAGAAAADNAAA        205          2 B
AAA4WEAAGAAAADOAAA        206          3 C
AAA4WEAAGAAAADPAAA        207          4 D
AAA4WEAAGAAAADQAAA        208          5 E

SQL> var mycursor refcursor;

SQL> exec open :mycursor for select ROWID FROM TEST T;

PL/SQL procedure successfully completed.

此时打开会话2 ，删除表TEST的数据，如下所示：

SQL> SELECT  ROWID,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,T.ID,T.NAME FROM TEST T;

ROWID                  BLOCK#         ID NAME
------------------ ---------- ---------- ------------
AAA4WEAAGAAAADMAAA        204          1 A
AAA4WEAAGAAAADNAAA        205          2 B
AAA4WEAAGAAAADOAAA        206          3 C
AAA4WEAAGAAAADPAAA        207          4 D
AAA4WEAAGAAAADQAAA        208          5 E

SQL> DELETE FROM TEST WHERE ID=5;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> DELETE FROM TEST WHERE ID=1;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> DELETE FROM TEST  WHERE ID=4;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT  ROWID,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,T.ID,T.NAME FROM TEST T;

ROWID                  BLOCK#         ID NAME
------------------ ---------- ---------- ------------
AAA4WEAAGAAAADNAAA        205          2 B
AAA4WEAAGAAAADOAAA        206          3 C

SQL> INSERT INTO TEST(ID,NAME) VALUES(6,'F');

1 row created.

SQL> INSERT INTO TEST(ID,NAME) VALUES(7,'G');

1 row created.

SQL> INSERT INTO TEST(ID,NAME) VALUES(8,'H');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT  ROWID,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,T.ID,T.NAME FROM TEST T;

ROWID                  BLOCK#         ID NAME
------------------ ---------- ---------- ------------
AAA4WEAAGAAAADMAAA        204          7 G
AAA4WEAAGAAAADNAAA        205          2 B
AAA4WEAAGAAAADOAAA        206          3 C
AAA4WEAAGAAAADPAAA        207          8 H
AAA4WEAAGAAAADQAAA        208          6 F

可以看到 ID=7和ID=1的ROWID一样，ID=8和ID=2的ROWID一样，ID=6和ID=5的ROWID一样。

此时到达会话1  此时删除ROWID=AAA4WEAAGAAAADMAAA  那么你将会删除ID=7的记录 而不是ID=1的记录。

因此使用如下方式删除数据 可能会误删数据。

DECLARE
  type rowid_table_type is table of rowid index by binary_integer;
  rowid_table rowid_table_type;
  cursor mycursor is select rowid from test where fieldname=xxx;
BEGIN
  open mycursor;
  loop
    fetch mycursor bulk collect into rowid_table limit 3;
    forall i in 1..rowid_table.count
      execute immediate 'delete from test where rowid=:rid' using rowid_table(i); 
    exit when rowid_table.count<3;
    end loop;
    commit;
  close mycursor;
END;

因此如果使用ROWID方式删除记录，在打开游标这段时间中，最好对表无任何操作。

譬如：

会话1                         会话2

T1  打开游标       

                              T2 删掉ROWID记录为'XXXX' 重新插入一条记录 如果ROWID重用

T3  删除ROWID='XXXX'的记录
 

那么此时将会误删数据.