kingbase ES 关于NULL及其相关函数
文章概要：
本文对主要就NULL值及其相关处理函数进行讨论，同时也介绍了ora_input_emptystr_isnull参数
一，关于NULL值
1，sql中的null值
null 值代表未知数据，或者说不确定的值，它与空字符串、0 是不一样的
null 值的处理方式与其他值不同, NULL与任何值进行计算结果也是NULL,比如 =、< 或 <>。
判断值是否为NULL 必须使用 IS NULL 或 IS NOT NULL 进行判断。
注意：空值('')表示长度为0的字符串，和NULL是不同的值。
2，各种数据库中常见的NULL值相关的判断函数
（部分数据库产品相关NULL值相关函数可能未完全列举）
sqlserver：
1.isnull( expr1 , expr2 )函数：
--如果expr1是null，返回expr2，否则返回expr1。
2.nullif(expr1,expr2)
--如果expr1= expr2 成立，那么返回值为null，否则返回值为expr1。
mysql：
1.isnull(exper)
-- 判断exper是否为空，是则返回1，否则返回0
2.ifnull(expr1,expr2)
--如果expr1的值为null,那么函数返回expr2的值,否则就返回expr1的值。
3.nullif(expr1,expr2)
-- 如果expr1= expr2 成立，那么返回值为null，否则返回值为expr1。
oracle：
1.nvl(expr1,expr2)
--如果expr1的值为null,那么函数返回expr2的值,否则就返回expr1的值。
2.nvl2(expr1,expr2,expr3)
--如果expr1的值为null，那么函数返回expr3，否则函数返回expr2。
3.nullif(expr1,expr2)
--如果expr1= expr2 成立，那么返回值为null，否则返回值为expr1。
kes：
1.nvl(expr1,expr2)
--同oracle
2.nvl2(expr1,expr2,expr3)
--同oracle
3.nullif(expr1,expr2)
--同oracle
4.ifnull(expr1,expr2)
--同mysql
5.isnull(exper)
--等价于sqlserver的isnull函数和kes中的nvl函数
6.isnull( expr1 , expr2 )函数：
--同sqlsevrer
3，通用的COALESCE函数
COALESCE函数是标准的SQL函数，被许多数据库系统支持。如MySQL、PostgreSQL、SQL Server、Oracle均支持
COALESCE函数是SQL中的一个非常实用的函数，它可以用于返回一系列参数中的第一个非空值。
如果所有参数都为NULL，COALESCE函数将返回NULL。
语法：
COALESCE(value1, value2, ..., valueN)
其中，value1、value2等为要检查的值，可以是列名、表达式或常量。函数会从左到右检查这些值，返回第一个非空值。
二，一个NULL值处理的案例
【问题描述】
SELECT..INTO..语句查询无结果时，抛出异常NO_DATA_FOUND（客户希望此情况获取NULL值，后续代码继续执行），导致后续的语句无法继续执行
【问题原因】
此情况V8默认抛出异常NO_DATA_FOUND的，内部有comp_v7_select_into_strict_attr可以兼容V7的行为，也可以改写的方式实现。
【解决方案】
1，需要配置兼容参数
1），set comp_v7_select_into_strict_attr=on;（session级）
2），在kingbase.conf中配置,comp_v7_select_into_strict_attr=on（all session）
2，如果项目版本不支持comp_v7_select_into_strict_attr参数，可以考虑使用nvl函数转null结果：
--转换前的语句
Select name INTO var_name from test WHERE id = 1;
--nvl函数改写
Select nvl(name,null) INTO var_name from test WHERE id = 1;
【解决方案验证】
comp_v7_select_into_strict_attr参数的验证
--创建测试函数
create table test(id int,name varchar(20));
insert into test values(2,'mwh');
select * from test;
CREATE OR REPLACE FUNCTION public.test_fun()
RETURNS character varying
AS
DECLARE
var_name name;
var_name2 name;
begin
Select name INTO var_name from test WHERE id = 1; ---查询结果为空，会抛出异常
raise notice '-1--->var_name:%',var_name;
Select name INTO var_name2 from test WHERE id = 2; ---存在该值
raise notice '-1--->var_name2:%',var_name2;
RETURN 'ok';
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise notice '-2--->var_name:%',var_name;
raise notice '-2--->var_name2:%',var_name2;
RETURN 'error';
end;
---设置comp_v7_select_into_strict_attr之前
test=# select test_fun();
test-# /
NOTICE: -2--->var_name:<NULL>
NOTICE: -2--->var_name2:<NULL>
test_fun
----------
(1 row)
---设置comp_v7_select_into_strict_attr为on之后
test=# set comp_v7_select_into_strict_attr=on;
test-# /
SET
test=# select test_fun();
test-# /
NOTICE: -1--->var_name:<NULL>
NOTICE: -1--->var_name2:mwh
test_fun
----------
mwh
(1 row)
使用nvl函数改写验证：
---创建测试函数
CREATE OR REPLACE FUNCTION public.test_fun1()
RETURNS character varying
AS
DECLARE
var_name1 name;
var_name2 name;
begin
Select nvl(name,null) INTO var_name1 from test WHERE id = 2;
raise notice '-1--->var_name1:%',var_name1;
Select nvl(name,null) INTO var_name2 from test WHERE id = 1;
raise notice '-1--->var_name2:%',var_name2;
RETURN 'ok';
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise notice '-2--->var_name1:%',var_name1;
raise notice '-2--->var_name2:%',var_name2;
RETURN 'error';
end;
--测试验证
test=# select test_fun1();
test-# /
NOTICE: -1--->var_name1:mwh
NOTICE: -2--->var_name2:<NULL>
test_fun1
-----------
(1 row)
三，关于ora_input_emptystr_isnull参数
对于oracle，默认的行为就是将''直接转化为NULL插入表中
CREATE TABLE test_null(id int, info varchar(32) DEFAULT '');
INSERT INTO test_null(id) values(1);
INSERT INTO test_null values(2, '');
INSERT INTO test_null values(3, NULL );
INSERT INTO test_null values(4, 'test');
执行查询语句：
select * from test_null;
结果如下：
1 null
2 null
3 null
4 test
而ora_input_emptystr_isnull 参数就是起兼容oracle行为的作用。位于data/kingbase.conf配置文件中，默认为true，既当输入的为空字符串’’时，是否转换为null（true则转化，否则不转化）。
test=# SHOW ora_input_emptystr_isnull;
test-# /
ora_input_emptystr_isnull
---------------------------
on
(1 row)
依然执行建表操作，并分别使用null和''进行查询，结果如下（oracle也是如此）
CREATE TABLE test_null(id int, info varchar(32) DEFAULT '');
INSERT INTO test_null(id) values(1);
INSERT INTO test_null values(2, '');
INSERT INTO test_null values(3, NULL );
INSERT INTO test_null values(4, 'test');
test=# select * from test_null where info is null; --显然，id
test-# /
id | info
----+------
1 |
2 |
3 |
(3 rows)
test=# select * from test_null where info = '';
test-# /
id | info
----+------
(0 rows)
将参数ora_input_emptystr_isnull设置为off，再次进行建表测试，显然行为已变，''不再等价于NULL。
test=# set ora_input_emptystr_isnull to off;
test-# /
SET
drop table test_null;
CREATE TABLE test_null(id int, info varchar(32) DEFAULT '');
INSERT INTO test_null(id) values(1);
INSERT INTO test_null values(2, '');
INSERT INTO test_null values(3, NULL );
INSERT INTO test_null values(4, 'test');
test=# select * from test_null where info is null;
test-# /
id | info
----+------
3 |
(1 row)
test=# select * from test_null where info ='';
test-# /
id | info
----+------
1 |
2 |
(2 rows)
test=#
四，KES中手动实现ISNULL函数
使用基础函数COALESCE函数来实现，考虑到和KES内置的isnull冲突，命令为isnull2编写验证
CREATE FUNCTION sys.isnull2(text,text) RETURNS text AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(text,text) TO PUBLIC;
CREATE FUNCTION sys.isnull2(boolean,boolean) RETURNS boolean AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(boolean,boolean) TO PUBLIC;
CREATE FUNCTION sys.isnull2(smallint,smallint) RETURNS smallint AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(smallint,smallint) TO PUBLIC;
CREATE FUNCTION sys.isnull2(integer,integer) RETURNS integer AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(integer,integer) TO PUBLIC;
CREATE FUNCTION sys.isnull2(bigint,bigint) RETURNS bigint AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(bigint,bigint) TO PUBLIC;
CREATE FUNCTION sys.isnull2(real,real) RETURNS real AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(real,real) TO PUBLIC;
CREATE FUNCTION sys.isnull2(double precision, double precision) RETURNS double precision AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(double precision, double precision) TO PUBLIC;
CREATE FUNCTION sys.isnull2(numeric,numeric) RETURNS numeric AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(numeric,numeric) TO PUBLIC;
CREATE FUNCTION sys.isnull2(date, date) RETURNS date AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(date,date) TO PUBLIC;
CREATE FUNCTION sys.isnull2(timestamp,timestamp) RETURNS timestamp AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(timestamp,timestamp) TO PUBLIC;
CREATE FUNCTION sys.isnull2(timestamp with time zone,timestamp with time zone) RETURNS timestamp with time zone AS $$
SELECT COALESCE($1,$2);
$$
LANGUAGE SQL STABLE;
GRANT EXECUTE ON FUNCTION sys.isnull2(timestamp with time zone,timestamp with time zone) TO PUBLIC;
KINGBASE研究院
