526互联

kingbase ES 关于NULL及其相关函数

发布时间 2023-09-19 18:46:05作者: KINGBASE研究院

文章概要：

本文对主要就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;