参考地址:https://bbs.kingbase.com.cn/thread-15275-1-1.html
自定义函数解决kingbase不支持的mysql内置函数
curdate()
CREATE OR REPLACE FUNCTION curdate()
RETURNS text
AS
DECLARE
BEGIN
RETURN CURRENT_DATE();
END;
date_format(para1 text, para2 text)
CREATE OR REPLACE FUNCTION date_format(para1 text, para2 text)
RETURNS text
AS
DECLARE
form1 TEXT;
BEGIN
IF REPLACE(para1, ' ', '') IS NULL THEN RETURN NULL;
END IF;
form1 = REPLACE(para2, '%W', 'Day');
form1 = REPLACE(form1, '%Y', 'YYYY');
form1 = REPLACE(form1, '%a', 'Dy');
form1 = REPLACE(form1, '%d', 'DD');
form1 = REPLACE(form1, '%e', 'DD');
form1 = REPLACE(form1, '%m', 'MM');
form1 = REPLACE(form1, '%c', 'MM');
form1 = REPLACE(form1, '%b', 'Mon');
form1 = REPLACE(form1, '%j', 'DDD');
form1 = REPLACE(form1, '%H', 'HH24');
form1 = REPLACE(form1, '%k', 'HH24');
form1 = REPLACE(form1, '%l', 'HH');
form1 = REPLACE(form1, '%i', 'MI');
form1 = REPLACE(form1, '%r', 'HH:MI:SS');
form1 = REPLACE(form1, '%T', 'HH24:MI:SS');
form1 = REPLACE(form1, '%s', 'SS');
form1 = REPLACE(form1, '%%', '%');
RETURN to_char(to_date(para1), form1);
END;
date_sub(v_date text, v_interval pg_catalog.interval)
CREATE OR REPLACE FUNCTION date_sub(v_date text, v_interval pg_catalog.interval)
RETURNS text
AS
declare
v_rt text;
begin
select to_char(v_date::timestamp(0) - v_interval,'yyyy-mm-dd hh24:mi:ss' ) into v_rt;
if length(v_date) = 10 and v_rt like '% 00:00:00' then
select substr(v_rt,0,10) into v_rt;
end if;
return v_rt;
end;
datediff(_start text, _end text)
CREATE OR REPLACE FUNCTION datediff(_start text, _end text)
RETURNS bigint
AS DECLARE
BEGIN
RETURN days_between(_start, _end);
END;
find_in_set(str text, strlist text)
CREATE OR REPLACE FUNCTION find_in_set(str text, strlist text)
RETURNS integer
AS
declare
etv int;
len1 int;
arrlen int;
coun int;
tempstr text;
temparr text[];
begin
if strlist is null or strlist=''
then return 0;
end if;
temparr=string_to_array(strlist, ',');
arrlen=array_length(temparr,1);
raise notice 'arrlen=%',arrlen;
coun=1;
loop if coun>arrlen then exit;
end if;
if str=temparr[coun] then return coun;
end if;
coun=coun+1;
end loop;
return 0;
end;
locate(a1 varchar, a2 varchar)
CREATE OR REPLACE FUNCTION locate(a1 varchar, a2 varchar)
RETURNS varchar
AS
BEGIN
return position(a1 in a2 );
END;
rand()
CREATE OR REPLACE FUNCTION rand()
RETURNS double precision
AS DECLARE
BEGIN
RETURN random();
END;
to_days(_date text)
CREATE OR REPLACE FUNCTION to_days(_date text)
RETURNS bigint
AS DECLARE
BEGIN
RETURN days_between(to_date(_date) , to_date('0000-01-01 00:00:00'));
END;
unix_timestamp(_value text)
CREATE OR REPLACE FUNCTION curdate()
RETURNS text
AS
DECLARE
BEGIN
RETURN CURRENT_DATE();
END
;
CREATE OR REPLACE FUNCTION date_format(para1 text, para2 text)
RETURNS text
AS
DECLARE
form1 TEXT;
BEGIN
IF REPLACE(para1, ' ', '') IS NULL THEN RETURN NULL;
END IF;
form1 = REPLACE(para2, '%W', 'Day');
form1 = REPLACE(form1, '%Y', 'YYYY');
form1 = REPLACE(form1, '%a', 'Dy');
form1 = REPLACE(form1, '%d', 'DD');
form1 = REPLACE(form1, '%e', 'DD');
form1 = REPLACE(form1, '%m', 'MM');
form1 = REPLACE(form1, '%c', 'MM');
form1 = REPLACE(form1, '%b', 'Mon');
form1 = REPLACE(form1, '%j', 'DDD');
form1 = REPLACE(form1, '%H', 'HH24');
form1 = REPLACE(form1, '%k', 'HH24');
form1 = REPLACE(form1, '%l', 'HH');
form1 = REPLACE(form1, '%i', 'MI');
form1 = REPLACE(form1, '%r', 'HH:MI:SS');
form1 = REPLACE(form1, '%T', 'HH24:MI:SS');
form1 = REPLACE(form1, '%s', 'SS');
form1 = REPLACE(form1, '%%', '%');
RETURN to_char(to_date(para1), form1);
END
;
CREATE OR REPLACE FUNCTION date_sub(v_date text, v_interval pg_catalog.interval)
RETURNS text
AS
declare
v_rt text;
begin
select to_char(v_date::timestamp(0) - v_interval,'yyyy-mm-dd hh24:mi:ss' ) into v_rt;
if length(v_date) = 10 and v_rt like '% 00:00:00' then
select substr(v_rt,0,10) into v_rt;
end if;
return v_rt;
end
;
CREATE OR REPLACE FUNCTION "datediff"(_start text, _end text)
RETURNS bigint
AS DECLARE
BEGIN
RETURN days_between(_start, _end);
END
;
CREATE OR REPLACE FUNCTION find_in_set(str text, strlist text)
RETURNS integer
AS
declare
etv int;
len1 int;
arrlen int;
coun int;
tempstr text;
temparr text[];
begin
if strlist is null or strlist=''
then return 0;
end if;
temparr=string_to_array(strlist, ',');
arrlen=array_length(temparr,1);
raise notice 'arrlen=%',arrlen;
coun=1;
loop if coun>arrlen then exit;
end if;
if str=temparr[coun] then return coun;
end if;
coun=coun+1;
end loop;
return 0;
end
;
CREATE OR REPLACE FUNCTION locate(a1 varchar, a2 varchar)
RETURNS varchar
AS
BEGIN
return position(a1 in a2 );
END
;
CREATE OR REPLACE FUNCTION rand()
RETURNS double precision
AS DECLARE
BEGIN
RETURN random();
END
;
CREATE OR REPLACE FUNCTION to_days(_date text)
RETURNS bigint
AS DECLARE
BEGIN
RETURN days_between(to_date(_date) , to_date('0000-01-01 00:00:00'));
END
;
CREATE OR REPLACE FUNCTION unix_timestamp(_value text)
RETURNS bigint
AS DECLARE
BEGIN
RETURN date_part('epoch', CAST(_value AS TIMESTAMP WITH TIME ZONE));
END;
代码适配
1、INTERVAL类型
INTERVAL 1 DAY 可以用 INTERVAL ‘1’ DAY替换。
2、ANY_VALUE()函数
分组情况下MAX()替换,不分组情况下直接删除。
3、INSERT语句,插入数据值为null的字段。如果有表结构设置了默认值,mysql会使用默认值而不是null,kingbase会使用null而不是默认值。
4、替换isnull() 函数
isnull在kingbase中是关键字,所以不能写自定义函数,可以使用COALESCE函数替换。
举例:isnull(name) => COALESCE(name, ‘y’) = ‘y’。
5、kingbase中select 列表字段别名,如果是关键字则需要用AS 提示,否则报错。