由于业务需求，应用需要批量更新表上一个字段的值，以下是脚本：
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 is select 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 is select card_no from prepaid_card where 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
|
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 |