GBase8s数据库自定义函数SPL例程撰写

示例：
date_add()函数：

DELIMITER |
create dba function gbasedbt.date_add(date_exp date, interval_int int, int_type varchar(25)) 
 returns date;
 define r_date date;
 if upper(int_type)='YEAR' then
   select  (date_exp+interval_int units YEAR)  into r_date from systables where tabid=1;
 elif upper(int_type)='QUARTER' then
   select  (date_exp+interval_int*3 units MONTH)  into r_date from systables where tabid=1;
 elif upper(int_type)='MONTH' then
   select  (date_exp+interval_int units MONTH)  into r_date from systables where tabid=1;
 elif upper(int_type)='WEEK' then
   select  (date_exp+interval_int*7 units DAY)  into r_date from systables where tabid=1;
 elif upper(int_type)='DAY' then
   select  (date_exp+interval_int units DAY)  into r_date from systables where tabid=1;
 else
   select int_type into r_date from systables where tabid=1;
 end if;
 return r_date;
 end function;                                                                                                                                                                        
|

datetime_add()函数：

DELIMITER |
create dba function gbasedbt.datetime_add(date_exp datetime year to second, interval_int int, int_type varchar(25)) 
 returns datetime year to second;
 define r_date datetime year to second;
if upper(int_type)='YEAR' then
   select  (date_exp+interval_int units year)  into r_date from systables where tabid=1;
 elif upper(int_type)='QUARTER' then
   select  (date_exp+interval_int*3 units MONTH)  into r_date from systables where tabid=1;
 elif upper(int_type)='MONTH' then
   select  (date_exp+interval_int units MONTH)  into r_date from systables where tabid=1;
 elif upper(int_type)='WEEK' then
   select  (date_exp+interval_int*7 units DAY)  into r_date from systables where tabid=1;
 elif upper(int_type)='DAY' then
   select  (date_exp+interval_int units DAY)  into r_date from systables where tabid=1;
 elif upper(int_type)='HOUR' then
   select  (date_exp+interval_int units HOUR)  into r_date from systables where tabid=1;
 elif upper(int_type)='MINUTE' then
   select  (date_exp+interval_int units MINUTE)  into r_date from systables where tabid=1;
 elif upper(int_type)='SECOND' then
   select  (date_exp+interval_int units SECOND)  into r_date from systables where tabid=1;
 else
   select int_type into r_date from systables where tabid=1;
 end if;
 return r_date;
 end function;                                                                                                                                                                                                                                                   
|

dbaccess db1 - < udr.sql
注意：事先保证数据库db1存在

测试：
一、DATE_ADD()和DATETIME_ADD()函数测试如下：

1.函数说明：

date_add函数可以实现一个时间点加减年、月、日、季度、星期的功能；
datetime_add函数可以实现一个时间点加减时、分、秒的功能；

date_add(Ymd date,interval_int int,mode varchar)函数有三个参数，
Ymd代表一个时间格式为'%Y-%m-%d',如'2020-07-09'；
interval_int代表需要加上或者减去的时间间隔，正数代表加上，负数代表减去
mode代表需要加上或者减去的单位，有'YEAR'、'MONTH'、'QUARTER'、'DAY'、'QUARTER'、'WEEK'。

datetime_add(YmdHMS date,interval_int int,mode varchar)函数有三个参数，
YmdHMS代表一个时间格式为'%Y-%m-%d %H:%M:%S',如'2020-07-09 12:00:00'；
interval_int代表需要加上或者减去的时间间隔，正数代表加上，负数代表减去
mode代表需要加上或者减去的单位，有'HOUR'、'MINUTE'、'SECOND'。

2.以下分别为测试年、月、日、季度、星期、时、分、秒用例

execute function date_add('2020-07-09', -2, 'YEAR');
 
execute function date_add('2020-07-09', -13, 'MONTH');
 
execute function date_add('2020-07-09', -366, 'DAY');
 
execute function date_add('2020-07-09', +2, 'QUARTER');
 
execute function date_add('2020-07-09', +6, 'WEEK');
 
execute function datetime_add('2020-07-09 12:00:00', +25, 'HOUR');
 
execute function datetime_add('2020-07-09 12:00:00', -10, 'MINUTE');
 
execute function datetime_add('2020-07-09 12:00:00', 61, 'SECOND');

推荐阅读

late

目录