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

原文链接:【http://xie.infoq.cn/article/f371dd72a25c7553987bb3d1f】。

@下一站

关注

懒人 2020-11-22 加入

都是黄泉预约客，何必难为每一天，执念太强，无法豁然。

发布

暂无评论

创作场景

mysql 转国产数据库 Gbase 8s 常见函数脚本

@下一站

评论