人大金仓(KingBase)数据库自定义函数语句

最新推荐文章于 2024-05-16 09:24:09 发布

Im徐小天

最新推荐文章于 2024-05-16 09:24:09 发布

阅读量6.8k

点赞数 8

分类专栏：人大金仓 KingBase 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_35705891/article/details/117294949

版权

人大金仓同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

KingBase

1 篇文章 0 订阅

订阅专栏

1.直接上干货：

Ifnull 函数

CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.IFNULL(CHARACTER VARYING, CHARACTER VARYING) 
 RETURNS CHARACTER VARYING
 LANGUAGE PLSQL
 IMMUTABLE STRICT
AS $function$
DECLARE
BEGIN
RETURN ISNULL($1,$2);
END;
$function$;

FIND_IN_SET 函数

CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.FIND_IN_SET(CHARACTER VARYING, CHARACTER VARYING) 
 RETURNS CHARACTER VARYING
 LANGUAGE PLSQL
 IMMUTABLE STRICT
AS $function$
DECLARE
tokens varchar[];
BEGIN
tokens := sys_catalog.string_to_array($2, ',');
RETURN ISNULL(sys_catalog.array_position(tokens, $1),0) > 0;
END;
$function$;

JSON_EXTRACT 函数

CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.JSON_EXTRACT(CHARACTER VARYING, CHARACTER VARYING) 
 RETURNS CHARACTER VARYING
 LANGUAGE PLSQL
 IMMUTABLE STRICT
AS $function$
DECLARE
BEGIN
RETURN sys_catalog.json_extract_path($1, sys_catalog.replace($2,'$.',''));
END;
$function$;

substring_index 函数

CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.substring_index(CHARACTER VARYING, CHARACTER VARYING, INTEGER) 
 RETURNS CHARACTER VARYING
 LANGUAGE PLSQL
 IMMUTABLE STRICT
AS $function$
DECLARE
tokens varchar[];
length integer ;
indexnum integer;
BEGIN
tokens := sys_catalog.string_to_array($1, $2);
length := sys_catalog.array_upper(tokens, 1);
indexnum := length - ($3 * -1) + 1;
IF $3 >= 0 THEN
RETURN sys_catalog.array_to_string(tokens[1:$3], $2);
ELSE
RETURN sys_catalog.array_to_string(tokens[indexnum:length], $2);
END IF;
END;
$function$;