例子:
BEGIN
IF '1234' = NULL THEN
DBMS_OUTPUT.PUT_LINE('''1234'' = NULL'||' 结果是 '||'TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('''1234'' = NULL'||' 结果是 '||'FALSE');
END IF;
IF '1234' != NULL THEN
DBMS_OUTPUT.PUT_LINE('''1234'' != NULL'||' 结果是 '||'TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('''1234'' != NULL'||' 结果是 '||'FALSE');
END IF;
IF '1234' ^= NULL THEN
DBMS_OUTPUT.PUT_LINE('''1234'' ^= NULL'||' 结果是 '||'TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('''1234'' ^= NULL'||' 结果是 '||'FALSE');
END IF;
IF '1234' ~= NULL THEN
DBMS_OUTPUT.PUT_LINE('''1234'' ~= NULL'||' 结果是 '||'TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('''1234'' ~= NULL'||' 结果是 '||'FALSE');
END IF;
IF '1234' <> NULL THEN
DBMS_OUTPUT.PUT_LINE('''1234'' <> NULL'||' 结果是 '||'TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('''1234'' <> NULL'||' 结果是 '||'FALSE');
END IF;
IF '1234' IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('''1234'' IS NOT NULL'||' 结果是 '||'TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('''1234'' IS NOT NULL'||' 结果是 '||'FALSE');
END IF;
END;
输出结果如下:
'1234' = NULL 结果是 FALSE
'1234' != NULL 结果是 FALSE
'1234' ^= NULL 结果是 FALSE
'1234' ~= NULL 结果是 FALSE
'1234' <> NULL 结果是 FALSE
'1234' IS NOT NULL 结果是 TRUE
从上面的结果可以看出
使用=,~=,^=,<>与NULL比较的结果都是FALSE,所以在与NULL比较的时候,应该使用IS NOT NULL 或者IS NULL。
思路是对的,写出来的SQL也不会报错,但是查询结果永远返回FALSE值。
代码摘自XXXXXXXX,XXXXXXX功能:
select b.aab069 as aab069,
a.aac003 as aac003,
a.aac002 as aac002,
a.aac001 as aac001,
c.aae140 as aae140,
c.aaz289 as aaz289,
a.aac010 as aac010,
c.aab033 as aab033,
0 as aad009
from ac01 a, ae10 b, ac20 c,af02 d,ac22 e
where a.aaz070 = b.aaz001 <!-- 社区ID -->
and a.aac001 = c.aac001
and a.aac001 = e.aac001
and a.aaz070 = d.aaz070
and c.aab033 = '3'
AND NOT EXISTS (SELECT 1 from ac41 f,ac40 g where f.aaz192=g.aaz192
and #aae041#>=f.aae041
and f.aae042>=#aae042#
and g.aae016 in('0','1')
and f.aaz289 != null
AND a.aac001=g.aac001)