mysql 转国产数据库 Gbase 8s 常见函数脚本
作者：@下一站
- 2022-12-15 陕西
本文字数：4899 字
阅读完需：约 16 分钟
一、find_in_setfind_in_set(s1,s)函数，在 s 字符串集中查找 s1 的位置
-- find_in_setdrop 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;
复制代码
二、hexstrhexstr(str)函数，替代 hex，将字符串转换成 16 进制字符串
-- hexstr, instead of hexdrop 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
-- unhexdrop 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_concatgroup_concat 聚集函数
-- group_concatdrop aggregate if exists group_concat;create aggregate group_concat with( init = str_sum_init, iter = str_sum_iter, combine = str_sum_combine);
复制代码
五、aes_encryptaes_encrypt(str,key)函数，AES 加密函数
-- aes_encryptdrop 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_decryptaes_decrypt(str,key)函数，AES 解密函数
-- aes_decryptdrop 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;
复制代码
七、weekofyearweekofyear(dt)函数，日期 dt 是所在年的第几周
drop function if exists weekofyear(datetime year to second);-- day of year, first week is 01-01 ~ 01-07create 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;
复制代码
八、weekofmonthweekofmonth(dt)函数，日期 dt 是所在月的第几天
drop function if exists weekofmonth(datetime year to second);-- day of month, first week is 01~07create 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;
复制代码
九、dayofyeardayofyear(dt)函数，日期 dt 是所在年的第几天
drop function if exists dayofyear(datetime year to second);-- day of yearcreate function dayofyear(p_datetime datetime year to second) returns int with (not variant); return trunc(1 + (p_datetime - TRUNC(p_datetime, 'YEAR')));end function;
复制代码
十、datediffdatediff(dt1,dt1)函数，两个日期的差值
-- datediffdrop 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_timestampunix_timestamp(dt)函数，将日期时间 dt 转换为自 1970-01-01 08:00:00 开始的秒数
drop function if exists UNIX_TIMESTAMP(datetime year to second);-- unix_timestampcreate 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_unixtimefrom_unixtime(p)函数，生成自 1970-01-01 08:00:00 开始 p 秒的日期时间值
drop function if exists from_unixtime;-- from_unixtimecreate 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;
复制代码
十三、makedatemakedate(d,y)函数，生成 d 年第 y 天的日期
drop function if exists makedate;-- makedatecreate function makedate(dt int,dy int)returns date with (not variant); return mdy(1,1,dt) + (dy - 1) units day;end function;
复制代码
十四、maketimemaketime(h,m,s)函数，生成时间 hh:mi:ss
drop function if exists maketime;-- maketimecreate 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;
复制代码
十五、strcmpstrcmp(s1,s2)函数，比较 s1，s2 两个字符串
drop function if exists strcmp;-- strcmpcreate 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;
复制代码
十六、repeatrepeat(s,n)函数，输出 n 个 s
-- repeatdrop 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;
复制代码
十七、nownow() 函数，获取当前系统时间
-- nowdrop function if exists now;create function now()returns datetime year to fraction(5); return current year to second;end function;
复制代码
十八、date_formatdate_format(dt,fmt)函数，格式化日期时间
-- date_formatdrop 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_datestr_to_date(s,fmt)函数，将字符串格式化转换为日期时间
-- str_to_datedrop 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;
复制代码
二十、datediffdatediff(dt1,dt2)函数，计算两个时间的天数差值
-- datediffdrop 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_daysto_days(dt)函数，将日期转换为自 0000-00-00 开始的天数
-- to_daysdrop 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_daysfrom_days(p)函数，计算自 0000-00-00 开始天数 p 的日期
-- from_daysdrop 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_diffperiod_diff(p1,p2)函数，格式化日期的月份差
-- period_diffdrop 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;
复制代码
后续再添加
划线
评论
复制
发布于: 2022-12-15阅读数: 19
版权声明: 本文为 InfoQ 作者【@下一站】的原创文章。
原文链接:【http://xie.infoq.cn/article/f371dd72a25c7553987bb3d1f】。
本文遵守【CC-BY 4.0】协议，转载请保留原文出处及本版权声明。
@下一站
关注
懒人 2020-11-22 加入
都是黄泉预约客，何必难为每一天，执念太强，无法豁然。
评论