由于业务需求，应用需要批量更新表上一个字段的值，以下是脚本：

SET FEEDBACK ON

SET PAGESIZE 49999

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

v_card_no prepaid_card.card_no%type;

i integer;

cursor card_cur isselect card_no from prepaid_card;

BEGIN

open card_cur;

i := 0;

LOOP

FETCH card_cur INTO v_card_no;

EXIT when card_cur%notfound;

i := i +1;

update prepaid_card set param = '1000000000' where card_no = v_card_no;

IF mod(i, 10000) = 0 THEN

commit;

END IF;

END LOOP;

commit;

dbms_output.put_line('处理总行数'||i);

close card_cur;

exception

when no_data_found then

dbms_output.put_line('无数据');

when others then

dbms_output.put_line('错误代码:'||sqlcode||'.'||'错误描述：'||sqlerrm||'.');

END;

/

exit;

原来的想法是打开游标开始查询，之后根据fetch得到的记录进行更新，把所有记录的param字段都设置为1000000000。

由于查询打开游标时间过长，更新数据时出现ORA-1555错，快照过旧。

在第二次批量更新字段值时，考虑到该批量操作耗时较长，所以将脚本改成如下内容，期望可以避免重复更新：

SET FEEDBACK ON

SET PAGESIZE 49999

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

v_card_no prepaid_card.card_no%type;

i integer;

cursor card_cur isselect card_no from prepaid_cardwhere param <> '1000000000';

BEGIN

open card_cur;

i := 0;

LOOP

FETCH card_cur INTO v_card_no;

EXIT when card_cur%notfound;

i := i +1;

update prepaid_card set param = '1000000000' where card_no = v_card_no;

IF mod(i, 10000) = 0 THEN

commit;

END IF;

END LOOP;

commit;

dbms_output.put_line('处理总行数'||i);

close card_cur;

exception

when no_data_found then

dbms_output.put_line('无数据');

when others then

dbms_output.put_line('错误代码:'||sqlcode||'.'||'错误描述：'||sqlerrm||'.');

END;

/

exit;

这里问题出现了。param <> '1000000000'其实不是param = '1000000000'的补集，这里遗漏了一种可能：NULL(空值)。NULL是不参与字段比较的，将sql语句改写后将会忽略param字段为NULL类型的记录，这么做导致了应用逻辑处理出现问题，最终进行了紧急修复。

做个简单实验证明一下：

SQL> create table test (num number,name varchar2(10));

Table created.

SQL> insert into test values(1,'0');

1 row created.

SQL> insert into test values(2,'1');

1 row created.

SQL> insert into test values(3,' ');

1 row created.

SQL>insert into test values(4,null);

1 row created.

SQL>commit;

Commit complete.

SQL> select * from test where name <>'0';

NUM NAME

---------- ----------

2 1

3

SQL>select * from test where name is not null;

NUM NAME

---------- ----------

1 0

2 1

3

SQL> select * from test where name is null;

NUM NAME

---------- ----------

4

SQL> select * from test where name='0';

NUM NAME

---------- ----------

1 0

Conditions Containing Nulls

Condition

Value of A

Evaluation

a IS NULL

10

FALSE

a IS NOT NULL

10

TRUE

a IS NULL

NULL

TRUE

a IS NOT NULL

NULL

FALSE

a = NULL

10

UNKNOWN

a != NULL

10

UNKNOWN

a = NULL

NULL

UNKNOWN

a != NULL

NULL

UNKNOWN

a = 10

NULL

UNKNOWN

a != 10

NULL

UNKNOWN