- 格式化时间函数DATE_FORMAT
create or replace FUNCTION DATE_FORMAT(date1 varchar2, yangshi varchar2)
RETURN varchar(30)
AS
format1 varchar(30);
format2 varchar(30);
format3 varchar(30);
format4 varchar(30);
format5 varchar(30);
format6 varchar(30);
format7 varchar(30);
format8 varchar(30);
format9 varchar(30);
format10 varchar(30);
BEGIN
-- 如果时间是NULL 则返回NULL
IF date1 IS NULL THEN
RETURN NULL;
END IF;
format1:='%Y-%m-%d %H:%i:%s';
format2:='%Y-%m-%d %H:%i';
format3:='%Y-%m-%d %H';
format4:='%Y-%m-%d';
format5:='%Y-%m';
format6:='%Y';
format7:='%m-%d %H:%i:%s';
format8:='%m-%d %H:%i';
format9:='%m-%d %H';
format10:='%m-%d';
-- 如果格式化样式为NULL 则返回日期本身
IF yangshi IS NULL THEN
RETURN date1;
END IF;
-- 如果时间以及格式化样式不为NULL 根据请求样式返回格式化后的日期
IF yangshi = format1 THEN
RETURN to_char(date1,'YYYY-MM-DD hh24:mi:ss');
END IF;
IF yangshi = format2 THEN
RETURN to_char(date1,'YYYY-MM-DD hh24:mi');
END IF;
IF yangshi = format3 THEN
RETURN to_char(date1,'YYYY-MM-DD hh24');
END IF;
IF yangshi = format4 THEN
RETURN to_char(date1,'YYYY-MM-DD');
END IF;
IF yangshi = format5 THEN
RETURN to_char(date1,'YYYY-MM');
END IF;
IF yangshi = format6 THEN
RETURN to_char(date1,'YYYY');
END IF;
IF yangshi = format7 THEN
RETURN to_char(date1,'MM-DD hh24:mi:ss');
END IF;
IF yangshi = format8 THEN
RETURN to_char(date1,'MM-DD hh24:mi');
END IF;
IF yangshi = format9 THEN
RETURN to_char(date1,'MM-DD hh24');
END IF;
IF yangshi = format10 THEN
RETURN to_char(date1,'MM-DD');
END IF;
END DATE_FORMAT;
- FIND_IN_SET函数
CREATE
OR REPLACE FUNCTION FIND_IN_SET
(
piv_str1 varchar2,
piv_str2 varchar2,
p_sep varchar2 := ','
)
RETURN NUMBER
IS
l_idx number:=0; -- 用于计算piv_str2中分隔符的位置
str varchar2(500); -- 根据分隔符截取的子字符串
piv_str varchar2(500) := piv_str2; -- 将piv_str2赋值给piv_str
res number :=0; -- 返回结果
loopIndex number :=0;
BEGIN
-- 如果piv_str中没有分割符,直接判断piv_str1和piv_str是否相等,相等 res=1
IF instr( piv_str, p_sep, 1 ) = 0 THEN
IF piv_str = piv_str1 THEN
res := 1;
END IF;
ELSE
-- 循环按分隔符截取piv_str
LOOP
l_idx := instr( piv_str, p_sep );
loopIndex := loopIndex + 1;
-- 当piv_str中还有分隔符时
IF l_idx > 0 THEN
-- 截取第一个分隔符前的字段str
str := substr( piv_str, 1, l_idx - 1 );
-- 判断 str 和piv_str1 是否相等,相等 res=1 并结束循环判断
IF str = piv_str1 THEN
res := loopIndex;
EXIT;
END IF;
piv_str := substr(piv_str, l_idx + length( p_sep ));
ELSE
-- 当截取后的piv_str 中不存在分割符时,判断piv_str和piv_str1是否相等,相等 res=1
IF piv_str = piv_str1 THEN
res := loopIndex;
END IF;
-- 无论最后是否相等,都跳出循环
EXIT;
END IF;
END LOOP;
-- 结束循环
END IF;
-- 返回res
RETURN res;
END FIND_IN_SET;
COMMIT;
- GROUP_CONCAT函数
改为 wm_concat() 或者 LISTAGG(name,’,’)
*注:自定义函数使用性能不确定,建议使用数据库自带函数
其他不同项:
-
创建表的时候,不支持在列的后面直接加 comment 注释,使用 COMMENT ON IS 代替,如:
COMMENT ON TABLE xxx IS xxx
COMMENT ON COLUMN xxx IS xxx -
不支持 date_sub 函数,使用 dateadd(datepart,n,date) 代替,
其中,datepart可以为:year(yy,yyyy),quarter(qq,q),month(mm,m),dayofyear(dy,y),day(dd,d),week(wk,ww),weekday(dw),hour(hh), minute(mi,n), second(ss,s), millisecond(ms)
例子:
select dateadd(month, -6, now());
select dateadd(month, 2, now()); -
不支持 date_format 函数,它有三种代替方法:
a: 使用 datepart 代替:语法:datepart(datepart, date),返回代表日期的指定部分的整数,
datepart可以为:year(yy,yyyy),quarter(qq,q),month(mm,m),dayofyear(dy,y),day(dd,d),week(wk,ww),weekday(dw),hour(hh), minute(mi,n),second(ss,s), millisecond(ms) 例子: select datepart(year, '2018-12-13 08:45:00'); --2018 select datepart(month, '2018-12-13 08:45:00'); --12
b: 使用 date_part 代替,功能和 datepart 一样,写法不同,参数顺序颠倒,且都要加引号,
例子: select date_part('2018-12-13 08:45:00', 'year');--2018 select date_part('2018-12-13 08:45:00', 'mm'); -- 12
c: 使用 extract 代替,语法:extract(dtfield from date),从日期类型date中抽取dtfield对应的值
dtfield 可以是 year,month,day,hour,minute,second
例子:
select extract(year from ‘2018-12-13 08:45:00’); --2018
select extract(month from ‘2018-12-13 08:45:00’); --12 -
不支持 substring_index 函数, 使用 substr / substring 代替,
语法:
substr(char[,m[,n]])
substring(char[from m[ for n]]) -
不支持 group_concat 函数,使用 wm_concat 代替,
例子:
select wm_concat(id) as idstr from persion ORDER BY id ; -
不支持 from_unixtime 函数,使用 round 代替
语法:round(date[,format])
-
不支持 case-when-then-else ,
例如:
select case when id = 2 then “aaa” when id = 3 then “bbb” else “ccc” end as test
from (select id from person) tt; -
current_timestamp 的返回值带有时区,
例子:
select current_timestamp();
2018-12-17 14:34:18.433839 +08:00 -
convert(type, value) 函数,
与 mysql 的 convert 一样,但是参数是反过来的,mysql 是 convert(value, type)
-
不支持 on duplicate key update,
使用 merge into 代替
-
不支持 ignore,即 insert ignore into
-
不支持 replace into,
使用 merge into 代替
-
不支持 if。
-
不支持 auto_increment, 使用 identity 代替
如: identity(1, 1),从 1 开始,每次增 1
-
不支持 longtext 类型,
可用 CLOB 代替。