当两者都为NULL时，SP中的变量不会相等 [英] Variables in SP do not compare as equal when both are NULL
问题描述
用于Linux DB2 8.2，FP 10（还对DB2 8.2同样为Windoes，FP
11）。
使用SAMPLE数据库，表EMP和EMLOYEE。
在followng存储过程，2个NULL列（COMM）被选择成2个
不同SP变量和相等比较。它们都是NULL，但是不能比较等于
。当比较Not NULL列（SALARY）时，它们
比较相等。
这是否有原因？
语句终结是@
- 设置佣金为NULL，两样本桌EMPNO '' 000010 ''
UPDATE EMP SET COMM = NULL EMPNO =''000010''@
更新员工设置COMM = NULL EMPNO =''000010''@
----------------------------------------------- ---
DROP PROCEDURE TEST_SP @
CREATE PROCEDURE TEST_SP
（
OUT EMP_COMM DECIMAL （9,2），
OUT EMP_SALARY DECIMAL（9,2），
OUT EMPLOYEE_COMM DECIMAL（9,2），
OUT EMPLOYEE_SALARY DECIMAL（9,2），
OUT v_ERRMSG_1 VARCHAR（500），
OUT v_ERRMSG_2 VARCHAR（500）
）
SPECIFIC TEST_SP
结果设置0
修改SQL数据
语言SQL
BEGIN
----------------------------------------- ----
- 声明变量
-------------------------- -------------------
DECLARE not_found SQLSTATE''02000''的条件;
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE A_EMPNO CHAR（6）;
DECLARE A_EMP_COMM DECIMAL（9,2）;
DECLARE A_EMP_SALARY DECIMAL（9,2）;
DECLARE B_EMPNO CHAR（6）;
DECLARE B_EMPLOYEE_COMM DECIMAL（9,2）;
DECLARE B_EMPLOYEE_SALARY DECIMAL（9,2）;
------------------------------ ---------------
- 申报退出处理程序
-------------- -------------------------------
DECLARE EXIT HANDLER for SQLEXCEPTION
获取诊断异常1 v_ERRMSG_1 = MESSAGE_TEXT;
DECLARE CONTINUE HANDLER for not_found
SET at_end = 1;
DECLARE EXIT HANDLER FOR SQLWARNING
GET DIAGNOSTICS例外1 v_ERRMSG_1 = MESSAGE_TEXT;
--------------------- ------------------------------------------
- 开始处理逻辑
------------------------------------- --------------------------
SELECT EMPNO，COMM，SALARY INTO A_EMPNO，A_EMP_COMM，A_EMP_SALARY
来自DB2INST1.EMP
WHERE EMPNO =''000010'';
SELECT EMPNO，COMM，SALARY INTO B_EMPNO， B_EMPLOYEE_COMM，B_EMPLOYEE_SALARY
来自DB2INST1.EMPLOYEE
WHERE EMPNO = A_EMPNO;
如果A_EMP_COMM = B_EMPLOYEE_COMM那么
SET V_ERRMSG_1 ='''NULLS COMPARE as EQUAL'';
ELSE
SET V_ERRMSG_1 =''NULLS不能比较平等';
结束IF;
如果A_EMP_SALARY = B_EMPLOYEE_SALARY那么
SET V_ERRMSG_2 ='''NUL-NULLS COMPARE as EQUAL'';
ELSE
SET V_ERRMSG_2 =''NON-NU LLS不要比较等于'';
结束IF;
SET EMP_COMM = A_EMP_COMM;
SET EMP_SALARY = A_EMP_SALARY;
SET EMPLOYEE_COMM = B_EMPLOYEE_COMM;
SET EMPLOYEE_SALARY = B_EMPLOYEE_SALARY;
END @
CALL TEST_SP（？，？，？，？，？，？）@
输出参数值
---- ----------------------
参数名称：EMP_COMM
参数值： -
参数名称：EMP_SALARY
参数值：52750.00
参数名称：EMPLOYEE_COMM
参数值： -
参数名称：EMPLOYEE_SALARY
参数值：52750.00
参数名称：V_ERRMSG_1
参数值：NULLS不能平等比较
参数名称：V_ERRMSG_2
参数值：NUN-NULLS比较均等
返回状态= 0
在followng存储过程中，2个空列（COMM）被选择为2
个不同的SP变量和相等的。它们都是NULL，但是不能比较等于
。当比较Not NULL列（SALARY）时，它们
比较相等。
有这个原因吗？
是的，这就是应该的样子。
db2" values（case when（nullif（0,0）= nullif（0,0））然后1 else 0 end）
1
----- ------
0
1条记录被选中。
db2" values （（nullif（0,0）为null且nullif（0,0）为空时的情况）
然后>
1
-----------
1
1条记录被选中。
" mike"< _l ***** @ yahoo.comwrote in message
news：11 ********** ***********@h48g2000cwc.googlegro ups.com ...
是的，这就是它的假设要是。
db2" values（（nullif（0,0）= nullif（0,0）时的情况）然后1，0结束）"
< br $>
1
-----------
0
1条记录（s）选择。
db2" values（case whe n（nullif（0,0）为null AND nullif（0,0）IS NULL）
然后>
1
-----------
1
已选择1条记录。
是的，我发现它是如何运作的。是否有理由这样做？
" Mark A" < no **** @ nowhere.comschreef在bericht
新闻：f _ ************************** ****@comcast.com。 ..
" mike" < _l ***** @ yahoo.comwrote in message
news：11 ********************* @ h48g2000cwc.googlegro ups.com ...
>是的，这就是应该的样子。
db2" ;值（大小写时（nullif（0,0）= nullif（0,0））然后1其他0结束）"
1
--------- -
0
选择了1条记录。
db2" values（（nullif（0,0）为空且无效的情况（0） ，0）IS NULL）
然后>
1
-----------
1
1选择的结果。
是的，我想通了，是如何工作的。是有原因？
是的，有。
它在NULL的定义中。
NULL表示特定值未知，本身为NULL不是价值
可以与任何其他价值相匹配。
-
Jeroen
DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP
11).
Using the SAMPLE database, tables EMP and EMLOYEE.
In the followng stored procedure, 2 NULL columns (COMM) are selected into 2
different SP variables and compared for equal. They are both NULL, but do
not compare as equal. When the Not NULL columns (SALARY) are compared, they
do compare as equal.
Is there a reason for this?
Statement terminator is @.
-- SET THE COMMISION TO NULL ON TWO SAMPLE TABLES FOR EMPNO ''000010''
UPDATE EMP SET COMM = NULL WHERE EMPNO = ''000010''@
UPDATE EMPLOYEE SET COMM = NULL WHERE EMPNO = ''000010''@
--------------------------------------------------
DROP PROCEDURE TEST_SP@
CREATE PROCEDURE TEST_SP
(
OUT EMP_COMM DECIMAL(9,2),
OUT EMP_SALARY DECIMAL(9,2),
OUT EMPLOYEE_COMM DECIMAL(9,2),
OUT EMPLOYEE_SALARY DECIMAL(9,2),
OUT v_ERRMSG_1 VARCHAR(500),
OUT v_ERRMSG_2 VARCHAR(500)
)
SPECIFIC TEST_SP
RESULT SETS 0
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
---------------------------------------------
-- Declare variables
---------------------------------------------
DECLARE not_found CONDITION FOR SQLSTATE ''02000'';
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE A_EMPNO CHAR(6);
DECLARE A_EMP_COMM DECIMAL(9,2);
DECLARE A_EMP_SALARY DECIMAL(9,2);
DECLARE B_EMPNO CHAR(6);
DECLARE B_EMPLOYEE_COMM DECIMAL(9,2);
DECLARE B_EMPLOYEE_SALARY DECIMAL(9,2);
---------------------------------------------
-- Declare exit handlers
---------------------------------------------
DECLARE EXIT HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1 v_ERRMSG_1 = MESSAGE_TEXT;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
DECLARE EXIT HANDLER FOR SQLWARNING
GET DIAGNOSTICS EXCEPTION 1 v_ERRMSG_1 = MESSAGE_TEXT;
---------------------------------------------------------------
-- Begin processing logic
---------------------------------------------------------------
SELECT EMPNO, COMM, SALARY INTO A_EMPNO, A_EMP_COMM, A_EMP_SALARY
FROM DB2INST1.EMP
WHERE EMPNO = ''000010'';
SELECT EMPNO, COMM, SALARY INTO B_EMPNO, B_EMPLOYEE_COMM, B_EMPLOYEE_SALARY
FROM DB2INST1.EMPLOYEE
WHERE EMPNO = A_EMPNO;
IF A_EMP_COMM = B_EMPLOYEE_COMM THEN
SET V_ERRMSG_1 = ''NULLS COMPARE AS EQUAL'';
ELSE
SET V_ERRMSG_1 = ''NULLS DO NOT COMPARE AS EQUAL'';
END IF;
IF A_EMP_SALARY = B_EMPLOYEE_SALARY THEN
SET V_ERRMSG_2 = ''NON-NULLS COMPARE AS EQUAL'';
ELSE
SET V_ERRMSG_2 = ''NON-NULLS DO NOT COMPARE AS EQUAL'';
END IF;
SET EMP_COMM = A_EMP_COMM;
SET EMP_SALARY = A_EMP_SALARY;
SET EMPLOYEE_COMM = B_EMPLOYEE_COMM;
SET EMPLOYEE_SALARY = B_EMPLOYEE_SALARY;
END@
CALL TEST_SP (?,?,?,?,?,?)@
Value of output parameters
--------------------------
Parameter Name : EMP_COMM
Parameter Value : -
Parameter Name : EMP_SALARY
Parameter Value : 52750.00
Parameter Name : EMPLOYEE_COMM
Parameter Value : -
Parameter Name : EMPLOYEE_SALARY
Parameter Value : 52750.00
Parameter Name : V_ERRMSG_1
Parameter Value : NULLS DO NOT COMPARE AS EQUAL
Parameter Name : V_ERRMSG_2
Parameter Value : NON-NULLS COMPARE AS EQUAL
Return Status = 0
解决方案In the followng stored procedure, 2 NULL columns (COMM) are selected into 2different SP variables and compared for equal. They are both NULL, but do
not compare as equal. When the Not NULL columns (SALARY) are compared, they
do compare as equal.
Is there a reason for this?Yep, that''s the way it''s supposed to be.
db2 "values (case when (nullif(0,0) = nullif(0,0)) then 1 else 0 end)"
1
-----------
0
1 record(s) selected.
db2 "values (case when (nullif(0,0) is null AND nullif(0,0) IS NULL)
then>
1
-----------
1
1 record(s) selected.
"mike" <_l*****@yahoo.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...Yep, that''s the way it''s supposed to be.
db2 "values (case when (nullif(0,0) = nullif(0,0)) then 1 else 0 end)"
1
-----------
0
1 record(s) selected.
db2 "values (case when (nullif(0,0) is null AND nullif(0,0) IS NULL)
then>
1
-----------
1
1 record(s) selected.
Yes, I figured out that is how it works. Is there is reason for this?
"Mark A" <no****@nowhere.comschreef in bericht
news:f_******************************@comcast.com. .."mike" <_l*****@yahoo.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...>Yep, that''s the way it''s supposed to be.
db2 "values (case when (nullif(0,0) = nullif(0,0)) then 1 else 0 end)"
1
-----------
0
1 record(s) selected.
db2 "values (case when (nullif(0,0) is null AND nullif(0,0) IS NULL)
then>
1
-----------
1
1 record(s) selected.
Yes, I figured out that is how it works. Is there is reason for this?
Yes, there is.
It is in the definition of NULL''s.
A NULL means the specific value is unknown, NULL in itself isn''t a value
that can be matched against any other value.
--
Jeroen
这篇关于当两者都为NULL时，SP中的变量不会相等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
