mysql转国产数据库Gbase 8s 常见函数脚本
一、find_in_set
find_in_set(s1,s)函数,在s字符串集中查找s1的位置
-- find_in_set
drop function if exists find_in_set;
create function find_in_set(str varchar(255),strlist varchar(8192)) returns int;
define currp int;
define lastp int;
define sepnum int;
define strlen int;
define strtmp varchar(8192);
let lastp = 1;
let sepnum = 0;
let strtmp = strlist || ",";
let strlen = length(strtmp);
FOR currp = 1 TO strlen
IF substr(strtmp,currp,1) = ',' THEN
let sepnum = sepnum + 1;
IF substr(strtmp,lastp,currp - lastp) = str THEN
RETURN sepnum;
END IF;
let lastp = currp + 1;
END IF;
END FOR;
RETURN 0;
end function;
二、hexstr
hexstr(str)函数,替代hex,将字符串转换成16进制字符串
-- hexstr, instead of hex
drop function if exists hexstr;
create function hexstr(pstr varchar(8192))
returns varchar(8192) with (not variant)
define curpos smallint;
define curval smallint;
define tmpstr varchar(8192);
on exception
return null;
end exception;
let tmpstr = "";
for curpos = 1 to length(pstr)
let curval = ascii(substr(pstr,curpos,1));
let tmpstr = tmpstr || substr(hex(curval),9);
end for;
return tmpstr;
end function;
三、unhex
-- unhex
drop function if exists unhex;
create function unhex(pstr varchar(8192))
returns varchar(8192) with (not variant)
define curpos smallint;
define curval smallint;
define tmpint smallint;
define tmpstr varchar(8192);
on exception
return null;
end exception;
let tmpint = 0;
let tmpstr = "";
for curpos = 1 to length(pstr)
let curval = 0;
case lower(substr(pstr,curpos,1))
when 'a' then let curval = 10;
when 'b' then let curval = 11;
when 'c' then let curval = 12;
when 'd' then let curval = 13;
when 'e' then let curval = 14;
when 'f' then let curval = 15;
else let curval = substr(pstr,curpos,1);
end case;
if mod(curpos,2) = 1 then
let tmpint = curval * 16;
else
let tmpint = tmpint + curval;
let tmpstr = tmpstr || chr(tmpint);
end if;
end for;
return tmpstr;
end function;
四、group_concat
group_concat聚集函数
-- group_concat
drop aggregate if exists group_concat;
create aggregate group_concat with
(
init = str_sum_init,
iter = str_sum_iter,
combine = str_sum_combine
);
五、aes_encrypt
aes_encrypt(str,key)函数,AES加密函数
-- aes_encrypt
drop function if exists aes_encrypt;
create function aes_encrypt(p1 varchar(8192), p2 varchar(8192))
returns varchar(8192);
if p1 is null or p1 = '' then
return null;
end if;
set encryption password(p2);
return encrypt_aes(p1);
end function;
六、aes_decrypt
aes_decrypt(str,key)函数,AES解密函数
-- aes_decrypt
drop function if exists aes_decrypt;
create function aes_decrypt(p1 varchar(8192), p2 varchar(8192))
returns varchar(8192);
if p1 is null or p1 = '' then
return null;
end if;
set encryption password(p2);
return decrypt_char(p1);
end function;
七、weekofyear
weekofyear(dt)函数,日期dt是所在年的第几周
drop function if exists weekofyear(datetime year to second);
-- day of year, first week is 01-01 ~ 01-07
create function weekofyear(p_datetime datetime year to second)
returns int with (not variant);
return trunc(1 + (p_datetime - TRUNC(p_datetime, 'YEAR')) / 7);
end function;
八、weekofmonth
weekofmonth(dt)函数,日期dt是所在月的第几天
drop function if exists weekofmonth(datetime year to second);
-- day of month, first week is 01~07
create function weekofmonth(p_datetime datetime year to second)
returns int with (not variant);
return trunc(1 + (p_datetime - TRUNC(p_datetime, 'MONTH')) / 7);
end function;
九、dayofyear
dayofyear(dt)函数,日期dt是所在年的第几天
drop function if exists dayofyear(datetime year to second);
-- day of year
create function dayofyear(p_datetime datetime year to second)
returns int with (not variant);
return trunc(1 + (p_datetime - TRUNC(p_datetime, 'YEAR')));
end function;
十、datediff
datediff(dt1,dt1)函数,两个日期的差值
-- datediff
drop function if exists datediff;
create function datediff(p1 datetime year to fraction(5), p2 datetime year to fraction(5))
returns int with (not variant);
on exception
return null;
end exception;
return substr(p1 - p2,1,9)::int;
end function;
十一、unix_timestamp
unix_timestamp(dt)函数,将日期时间dt转换为自1970-01-01 08:00:00开始的秒数
drop function if exists UNIX_TIMESTAMP(datetime year to second);
-- unix_timestamp
create function UNIX_TIMESTAMP(datestr datetime year to second)
returns bigint with (not variant);
define rc_char varchar(30);
if datestr < datetime(1970-01-01 08:00:00) year to second then
let rc_char = datetime(1970-01-01 08:00:00) year to second - datestr;
return 0 - ((replace(substr(rc_char,1,9),'-')*86400)::bigint + substr(rc_char,17,2)*1 + substr(rc_char,14,2)*60 + substr(rc_char,11,2)*3600);
else
let rc_char = datestr - datetime(1970-01-01 08:00:00) year to second;
return ((substr(rc_char,1,9)*86400)::bigint + substr(rc_char,17,2)*1 + substr(rc_char,14,2)*60 + substr(rc_char,11,2)*3600);
end if;
end function ;
十二、from_unixtime
from_unixtime§函数,生成自1970-01-01 08:00:00开始p秒的日期时间值
drop function if exists from_unixtime;
-- from_unixtime
create function from_unixtime(p bigint)
returns varchar(20) with (not variant);
define v_day int;
define v_hour int;
define v_min int;
define v_sec int;
define v_dt datetime year to second;
on exception
return null;
end exception;
let v_day = p / 86400;
let v_dt = datetime(1970-01-01 08:00:00) year to second + v_day units day;
let v_hour = mod(p,86400) / 3600;
let v_dt = v_dt + v_hour units hour;
let v_min = mod(p,3600) / 60;
let v_dt = v_dt + v_min units minute;
let v_sec = mod(p,60);
let v_dt = v_dt + v_sec units second;
return to_char(v_dt,'yyyy-mm-dd hh24:mi:ss');
end function;
十三、makedate
makedate(d,y)函数,生成d年第y天的日期
drop function if exists makedate;
-- makedate
create function makedate(dt int,dy int)
returns date with (not variant);
return mdy(1,1,dt) + (dy - 1) units day;
end function;
十四、maketime
maketime(h,m,s)函数,生成时间hh:mi:ss
drop function if exists maketime;
-- maketime
create function maketime(dh int, dm int, ds int)
returns char(8) with (not variant);
if dh < 0 or dh > 23 or dm < 0 or dm > 59 or ds < 0 or ds > 59 then
return null;
else
return lpad(dh,2,'0') || ':' || lpad(dm,2,'0') || ':' || lpad(ds,2,'0');
end if;
end function;
十五、strcmp
strcmp(s1,s2)函数,比较s1,s2两个字符串
drop function if exists strcmp;
-- strcmp
create function strcmp(str1 varchar(8192), str2 varchar(8192))
returns smallint with (not variant);
on exception
return null;
end exception;
if str1 = str2 then
return 0;
elif str1 > str2 then
return 1;
else
return -1;
end if;
end function;
十六、repeat
repeat(s,n)函数,输出n个s
-- repeat
drop function if exists repeat;
create function repeat(str varchar(1024), n smallint)
returns varchar(32765) with (not variant);
define rc varchar(32765);
define i smallint;
on exception
return null;
end exception;
let rc = '';
for i = 1 to n
let rc = rc || str;
end for;
return rc;
end function;
十七、now
now() 函数,获取当前系统时间
-- now
drop function if exists now;
create function now()
returns datetime year to fraction(5);
return current year to second;
end function;
十八、date_format
date_format(dt,fmt)函数,格式化日期时间
-- date_format
drop function if exists date_format;
create function date_format(p1 datetime year to fraction(5), p2 varchar(20))
returns varchar(40) with (not variant);
define fmt varchar(20);
on exception
return null;
end exception;
let fmt = replace(p2,'i','M');
let fmt = replace(fmt,'s','S');
return gbase_to_char(p1,fmt);
end function;
十九、str_to_date
str_to_date(s,fmt)函数,将字符串格式化转换为日期时间
-- str_to_date
drop function if exists str_to_date;
create function str_to_date(p1 varchar(40), p2 varchar(20))
returns datetime year to fraction(5) with (not variant);
define fmt varchar(20);
on exception
return null;
end exception;
let fmt = replace(p2,'i','M');
let fmt = replace(fmt,'s','S');
return gbase_to_date(p1,fmt);
end function;
二十、datediff
datediff(dt1,dt2)函数,计算两个时间的天数差值
-- datediff
drop function if exists datediff;
create function datediff(p1 datetime year to fraction(5), p2 datetime year to fraction(5))
returns int with (not variant);
on exception
return null;
end exception;
return substr(p1 - p2,1,9)::int;
end function;
二十一、to_days
to_days(dt)函数,将日期转换为自0000-00-00开始的天数
-- to_days
drop function if exists to_days;
create function to_days(p1 datetime year to fraction(5))
returns int with (not variant);
on exception
return null;
end exception;
return 366 + substr(p1 - mdy(1,1,1),1,9)::int;
end function;
二十二、from_days
from_days§函数,计算自0000-00-00开始天数p的日期
-- from_days
drop function if exists from_days;
create function from_days(p1 int)
returns varchar(40) with (not variant);
on exception
return null;
end exception;
if p1 < 366 then
return '0000-00-00';
else
return gbase_to_char(mdy(1,1,1) + (p1 - 366) units day,'%Y-%m-%d');
end if;
end function;
二十三、period_diff
period_diff(p1,p2)函数,格式化日期的月份差
-- period_diff
drop function if exists period_diff;
create function period_diff(p1 int, p2 int)
returns int with (not variant);
define vm1 int;
define vm2 int;
on exception
return null;
end exception;
if p1 < 9999 then
let p1 = p1 + 200000;
end if;
let vm1 = (p1 / 100 - 1970) * 12 + mod(p1, 100);
if p2 < 9999 then
let p2 = p2 + 200000;
end if;
let vm2 = (p2 / 100 - 1970) * 12 + mod(p2, 100);
return vm1 - vm2;
end function;
后续再添加
- 点赞
- 收藏
- 关注作者
评论(0)