由于项目的需要,需要把用户编号和用户微信号在公众号下的唯一标识(openId)进行绑定,于是乎写了一段SQL用于更新用户表的openId
,逻辑很简单,如果用户已经绑定则修改,没有绑定则添加。
假设表名为USER_WECHAT_BIND
,其中有两列,分别为userId和openId,在SQL中有这么一段:
select openId into v_tmpId from USER_WECHAT_BIND where userId = v_userId;
if v_tmpId <> v_opneId then
update USER_WECHAT_BIND set openId = v_openId where userId = v_userId;
end if;
在最初的时候,userId
和openId
都是不允许空值的,所以这段SQL执行正常,也能达到预期更新的效果,随着业务的变化,后来将表结构进行了修改,openId
允许为空,在测试的时候发现,当openId
为空的记录执行这段SQL的时候并不能按照预期进行修改操作。
当openId
为空时,那么v_tmpId为空,则v_tmpId <> v_opneId
的结果为UNKNOWN
,可以认为是条件不成立,所以下面的更新语句就不会执行。
在http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm与对null
的描述。
A condition that evaluates to
UNKNOWN
acts almost likeFALSE
. For example, aSELECT
statement with a condition in theWHERE
clause that evaluates toUNKNOWN
returns no rows. However, a condition evaluating toUNKNOWN
differs fromFALSE
in that further operations on anUNKNOWN
condition evaluation will evaluate toUNKNOWN
. Thus,NOT FALSE
evaluates toTRUE
, butNOT UNKNOWN
evaluates to UNKNOWN.
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 |
为了解决这个问题,可以将SQL作如下修改,增加null的判断:
select openId into v_tmpId from USER_WECHAT_BIND where userId = v_userId;
if v_tmpId is null or v_tmpId <> v_opneId then
update USER_WECHAT_BIND set openId = v_openId where userId = v_userId;
end if;