声明
本人是刚入行的小白,进入公司后被安排把MySQL数据库改为Oracle数据库,主要负责修改建表语句和Mapper文件。由于之前未接触过Oracle,以下内容有错误的地方,请各位大佬给予指正和修改
概述
关于建表语句
-
Oracle中不能使用着重号:`
-
当建表的时候 表名 或 字段名 被双引号**““包括时,在被使用的时候要严格按照双引号内的字母拼写,否则Oracle找不到该表或该字段;所以在建表的时候不要使用””**,不使用双引号的情况下,无论单词是大写还是小写,oracle都默认为大写
关于DML语句
-
Oracle中表的别名不能使用关键字AS,但字段的别名可以使用AS
-
虽然Oracle中也可以使用CONCAT()函数,但是当拼接两个以上的字符串时,需要改为||,或使用多个CONCAT()函数嵌套,例:
--mysql concat('ac','eg','gg') --oracle 'ac' || 'eg' || 'gg' 或 concat(concat('ac','eg'),'gg')
-
Oracle中使用NVL直接替代IFNULL
关于JSON相关函数
参考博客:https://dragonpeng.blog.csdn.net/article/details/89479698?spm=1001.2014.3001.5506
具体修改需要结合项目灵活替换。
各种函数的替换,将如下进行详细分析
函数替换
limit
MySQL用法
主要用于分页,不再赘述
Oracle替换
使用ROWNUM进行替换
例子
不含order by
-- mysql
SELECT * FROM 表名 LIMIT 1
-- oracle
SELECT * FROM 表名 WHERE ROWNUM <= 1
-- mysql
SELECT * FROM 表名 LIMIT 2,5
-- oracle
SELECT * FROM (SELECT t.*,ROWNUM rn FROM 表名 t) nn
WHERE nn.rn > 2 AND nn.rn <= (2+5)
含有order by
-- mysql
SELECT * FROM 表名 ORDER BY 字段名 DESC LIMIT 1
--oracle
SELECT * FROM (SELECT * FROM 表名 ORDER BY 字段名 DESC) WHERE ROWNUM <= 1
-- mysql
SELECT * FROM 表名 ORDER BY 字段名 DESC LIMIT 2,5
--oracle
SELECT * FROM (
SELECT A.*, rownum r FROM (
SELECT * FROM 表名 ORDER BY 字段名 DESC
) A WHERE rownum < (2+5)
) B WHERE r >= 2
substring()
MySQL用法
截取字符串:从特定位置开始的字符串返回一个给定长度的子字符串
SUBSTRING(字符串,position);//获得字符串的position位置之后的所有字符
-- 如果position为0,则返回一个空串
SUBSTRING(string,position,length);//获得字符串的position位置之后的length个字符
Oracle替换
使用substr(string,start,[length])
例子
--mysql
SELECT substring('I LIKE MYSQL', 3) FROM dual;//LIKE MYSQL
SELECT substring('I LIKE MYSQL', 0) FROM dual;//
SELECT substring('I LIKE MYSQL', -3) FROM dual;//SQL
SELECT substring('I LIKE MYSQL',3,6) FROM dual;//LIKE M
--oracle
SELECT SUBSTR('I LIKE MYSQL', 1) FROM dual --截取所有字符串,返回'I LIKE MYSQL'
SELECT SUBSTR('I LIKE MYSQL', 2) FROM dual --从第2个字符开始,截取到末尾。返回' LIKE MYSQL'
SELECT SUBSTR('I LIKE MYSQL', -4) FROM dual --从倒数第4个字符开始,截取到末尾。返回'YSQL'
SELECT SUBSTR('I LIKE MYSQL', 3, 6) FROM dual --从第3个字符开始,截取6个字符。返回'LIKE M'
SELECT SUBSTR('I LIKE MYSQL', -4, 3) FROM dual--从倒数第4个字符开始,截取3个字符。返回'YSQ'
substring_index()
MySQL用法
substring_index(“待截取有用部分的字符串”,“截取数据依据的字符”,截取字符的位置N)
Oracle替换
substr()、instr()
简单介绍一下Oracle中**instr()**函数的用法:
instr()函数的格式 (俗称:字符查找函数)
格式一:instr(string1,string2 ) // instr(源字符串, 目标字符串)
格式二:instr(string1,string2 [,start_position [,nth_appearance]])//instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析:string2 的值要在string1中查找,是从start_position给出的数值(即:位置)开始在string1检索,检索第nth_appearance(几)次出现string2。
注:在Oracle/PLSQL中,instr函数返回要截取的字符串在源字符串中的位置。只检索一次,也就是说从字符的开始到字符的结尾就结束。
例子
格式一:
select instr('helloworld','l') from dual; --返回结果:3 默认第一次出现“l”的位置
select instr('helloworld','lo') from dual; --返回结果:4 即第一个“lO”出现的位置
select instr('helloworld','wo') from dual; --返回结果:6 即“wo”同时出现,第一个字母“w”出现的位置
格式二:
select instr('helloworld','l',2,2) from dual;
--返回结果:4 也就是说:在"helloworld"的第2(e)个位置开始,查找第二次出现“l”的位置
select instr('helloworld','l',3,2) from dual;
--返回结果:4 也就是说:在"helloworld"的第3(l)个位置开始,查找第二次出现的“l”的位置
select instr('helloworld','l',4,2) from dual;
--返回结果:9 也就是说:在"helloworld"的第4(l)个位置开始,查找第二次出现的“l”的位置
select instr('helloworld','l',-1,1) from dual;
--返回结果:9 也就是说:在"helloworld"的倒数第1(d)个位置开始,往回查找第一次出现的“l”的位置
select instr('helloworld','l',-2,2) from dual;
--返回结果:4 也就是说:在"helloworld"的倒数第2(l)个位置开始,往回查找第二次出现的“l”的位置
select instr('helloworld','l',2,3) from dual;
--返回结果:9 也就是说:在"helloworld"的第2(e)个位置开始,查找第三次出现的“l”的位置
select instr('helloworld','l',-2,3) from dual;
--返回结果:3 也就是说:在"helloworld"的倒数第2(l)个位置开始,往回查找第三次出现的“l”的位置
例子
--mysql
SELECT SUBSTRING_INDEX('192-168-8-203','-',1); //192 (取第一个'-'前面的字符串)
SELECT SUBSTRING_INDEX('192-168-8-203','-',-1); //203(取倒数第一个'-'后面的字符串)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('192-168-8-203','-',2),'-',-1); //168
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('192-168-8-203','-',-2),'-',1); //8
--oracle
SELECT SUBSTR('192-168-8-203',1,INSTR('192-168-8-203','-',1,1)-1) FROM DUAL //192
SELECT SUBSTR('192-168-8-203',INSTR('192-168-8-203','-',-1,1)+1) FROM DUAL //203
SELECT SUBSTR('192-168-8-203',INSTR('192-168-8-203','-',1,1)+1,(INSTR('192-168-8-203','-',1,2)-(INSTR('192-168-8-203','-',1,1)+1))) FROM DUAL; //168
SELECT SUBSTR('192-168-8-203',INSTR('192-168-8-203','-',1,2)+1,(INSTR('192-168-8-203','-',1,3)-(INSTR('192-168-8-203','-',1,2)+1))) FROM DUAL; //8
locate()
MySQL中的locate()函数用法
- locate(字符串1,字符串2)
从字符串2的第一个位置开始找,找到并返回 字符串1在字符串2中第一次出现的位置
- locate(字符串1,字符串2,pos)
从字符串2的pos位置开始找,找到并返回字符串1在字符串2中第一次出现的位置
如果找不到则返回0
Oracle替代locate()函数的用法
instr()
例子:
--mysql
locate('bar', 'foobarbar') //4
locate('x','abxz') //3
locate('a','bxz') //0
--oracle
instr('foobarbar','bar') //4
instr('bxz','a') //0
replace()
MySQL用法
REPLACE(str,old_string,new_string); //将str中的old_string替换为new_string字符串
Oracle
与MySQ类似,不再赘述
datediff()
MySQL用法
datediff函数:返回两个日期之间的天数。
datediff(date1,date2);//date1-date2
date1 和 date2 参数是合法的日期或日期/时间表达式。
注释:只有值的日期部分参与计算。
SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate //1
SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate //-1
Oracle替换
遇到datediff函数时,可以用以下方法在oracle中实现该函数的功能:
天:
ROUND(TO_NUMBER(END_DATE - START_DATE))
小时:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
分钟:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
秒:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
毫秒:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 60)
例子
--mysql
DATEDIFF(dat1.DT_ACCOUNTING, dat2.DT_ACCOUNTING);
--Oracle
ROUND(TO_NUMBER(TO_DATE(dat1.DT_ACCOUNTING)-TO_DATE(dat2.DT_ACCOUNTING)));
--mysql
DATEDIFF('2022-09-09', '2022-09-08');
--oracle
ROUND(TO_NUMBER(TO_DATE('2022-09-09','yyyy-MM-dd HH24:mi:ss') - TO_DATE('2022-09-08','yyyy-MM-dd HH24:mi:ss')))
date_format()
MySQL用法
date_format():用于以不同的格式显示日期/时间数据
DATE_FORMAT(date,format)
例子
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') //Dec 29 2008 11:45 PM
DATE_FORMAT(NOW(),'%m-%d-%Y') //12-29-2008
DATE_FORMAT(NOW(),'%d %b %y') //29 Dec 08
DATE_FORMAT(NOW(),'%d %b %Y %T:%f') //29 Dec 2008 16:25:46.635
Oracle替换
to_char(date,format)
例子
tochar(sysdate,'YYYY-MM-DD') //2022-09-10
str_to_date()
MySQL用法
STR_TO_DATE(str,fmt); //根据fmt格式字符串将str字符串转换为日期值
Oracle替换
to_date(str,fmt);
char_length()
MySQL用法
返回以字符来测量字符串str的长度。一个多字节字符算作一个字符。这意味着,对于包含五个两字节字符的字符串,那么 length() 返回10,而 char_length() 返回5。
select char_length("text"); //4
oracle替换
lengthb()
例子
--mysql
SELECT LENGTH('abc我') FROM dual;//4
SELECT CHAR_LENGTH('abc我') FROM dual;//6
--Oracle
SELECT LENGTH('abc我') FROM dual;//4
SELECT LENGTHB('abc我') FROM dual;//6
参数为 null 或 空字符串'',返回null
不传参,抛出异常
curdate()
MySQL用法
相当于current_date()
返回当前日期 (以"YYYYMMDD"字符串 或 YYYYMMDD数字格式显示)
NOW() | CURDATE() | CURTIME() |
---|---|---|
2022-09-10 8:59:23 | 2022-09-10 | 8:59:23 |
Oracle替换
sysdate()
date_sub()/subdate()
MySQL用法
DATE_SUB(date,INTERVAL expr type); //从日期减去指定的时间间隔
DATE_SUB("2022-09-10", INTERVAL 3 DAY);//2022-09-07
SUBDATE(date, INTERVAL expr unit);//从日期减去指定的时间间隔
SUBDATE("2022-09-10", INTERVAL 3 DAY);//2022-09-07
Oracle替换
select SYSDATE + INTERVAL '1' DAY, SYSDATE - INTERVAL '2' DAY from dual;
例子
--mysql
date_sub('2022-09-10 13:13:20',interval 2 day);//2022-09-08 13:13:20
--oracle
select TO_DATE('2022-09-10 13:13:20','yyyy-MM-dd HH24:mi:ss') - interval '2' day FROM DUAL;//2022-09-08 13:13:20
或
SELECT TO_DATE('2022-09-10 13:13:20','yyyy-MM-dd HH24:mi:ss') - 2 FROM DUAL;
Mapper.xml中
--mysql
SELECT DATE_SUB(CURDATE(), INTERVAL #{dtInterval}-1 DAY) FROM DUAL;
--oracle
SELECT SYSDATE - INTERVAL #{dtInterval}-'1' DAY FROM DUAL;
year()/month()/day()/quarter
MySQL用法
--year()
获取一个日期的年份值,范围为1000到9999,如果日期为零,YEAR()函数返回0
SELECT year("2022-09-10") FROM DUAL; //2022
--month()
获取一个日期的月份值
SELECT month("2022-09-10") FROM DUAL; //09
--day()
获取一个日期的日期值
SELECT day("2022-09-10") FROM DUAL; //10
--quarter()
获取一个日期所在第几个季度
SELECT quarter("2018-01-07 10:17:12"); // 1
SELECT quarter("2018-12-07"); // 4
Oracle替换
--oracle
---年
SELECT to_number(to_char(to_date('2022-09-10','yyyy-MM-dd'),'yyyy')) FROM DUAL;//2022
或
SELECT EXTRACT(year from to_date('2022-09-10','yyyy-MM-dd')) FROM DUAL;//2022
---月
SELECT to_number(to_char(to_date('2022-09-10','yyyy-MM-dd'),'mm')) FROM DUAL;//9
或
SELECT EXTRACT(month from to_date('2022-09-10','yyyy-MM-dd')) FROM DUAL;//9
---日
SELECT to_number(to_char(to_date('2022-09-10','yyyy-MM-dd'),'dd')) FROM DUAL;//10
或
SELECT EXTRACT(day from to_date('2022-09-10','yyyy-MM-dd')) FROM DUAL;//10
---季度
SELECT TO_CHAR (to_date('2022-09-10','yyyy-MM-dd'), 'q') FROM dual; //3
SELECT TO_CHAR (SYSDATE, 'q') FROM dual; //获取当前季度
find_in_set()
MySQL用法
FIND_IN_SET(str,strlist)
str:要查询的字符串
strlist:字段名参数以","分隔如(1,2,6,8,10,22)
查询字段(strlist)中包含(str)的结果,返回结果为null或记录
- 如果str 在由N个子链组成的字符串列表strlist 中,则返回值的范围在1到N之间。
一个字符串列表就是一个由一些被","符号分开的子链组成的字符串。如果第一个参数是一个常数字符串, 而第二个是 type SET列,则FIND_IN_SET函数被优化,使用比特计算。
- 如果str不在strlist或strlist为空字符串,则返回值为0。
如任意一个参数为NULL,则返回值为NULL。这个函数在第一个参数包含一个逗号时将无法正常运行。
例子:
SELECT FIND_IN_SET('y','x,y,z'); //2
SELECT FIND_IN_SET('a','x,y,z'); //0 不存在返回零
SELECT FIND_IN_SET(NULL,'x,y,z'); //null 有参数为空返回null
SELECT FIND_IN_SET('a',NULL); //null 有参数为空返回null
Oracle替换
暂时没有找到能够替换的函数,所以自己自定义一个与MySQL功能一样的该函数
参考网上大佬的自定义函数语句如下:
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;
JSON函数
具体函数功能参考博客:https://dragonpeng.blog.csdn.net/article/details/89479698?spm=1001.2014.3001.5506
这里偷点懒,只给出修改前后的SQL语句
json_quote/unquote
MySQL作用
--json_quote 把value值用双引号""包起来
SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');// "null" , ""null""
--json_unquote 把value值的双引号""删掉
Oracle的字段不需要双引号
json_extract
-- 改前
JSON_UNQUOTE(JSON_EXTRACT(apifa.ACCT_REMARK, '$.repaymentPlanSyncStatus')) repaymentPlanSyncStatus
-- 改后
JSON_VALUE(apifa.ACCT_REMARK, '$.repaymentPlanSyncStatus') repaymentPlanSyncStatus
json_search
-- 改前
JSON_SEARCH(aa.TSK_HIS_HDR_IDS,'one',#{userId}) IS NOT NULL
-- 改后
INSTR(aa.TSK_HIS_HDR_IDS,#{userId}) > 0 (可以有其他改法)
->
-- 改前
a.BASE_INFO -> '$.settle'
-- 改后
JSON_VALUE(a.BASE_INFO,'$.settle')
-- 改前
JSON_UNQUOTE(aa.BASE_INFO -> "$.cunm") AS 'cunm'
-- 改后
JSON_VALUE(a.BASE_INFO,'$.cunm') AS cunm
json_length
-- 改前
(JSON_LENGTH(v_wf_app.TSK_HIS_HDR_IDS) IS NULL
OR JSON_LENGTH(v_wf_app.TSK_HIS_HDR_IDS) = 0
OR v_wf_app.TSK_HDR_ID != REPLACE(JSON_EXTRACT(v_wf_app.TSK_HIS_HDR_IDS, CONCAT(CONCAT('$[', JSON_LENGTH(v_wf_app.TSK_HIS_HDR_IDS) - 1), ']')), '"', ''))
-- 改后
( LENGTH( v_wf_app.TSK_HIS_HDR_IDS ) IS NULL
OR v_wf_app.TSK_HDR_ID != GET_ARRAY_KEY_VALUE(v_wf_app.TSK_HIS_HDR_IDS, CASE SPLITARRAYLENGTH(v_wf_app.TSK_HIS_HDR_IDS,',') -1 WHEN -1 THEN 0 ELSE SPLITARRAYLENGTH(v_wf_app.TSK_HIS_HDR_IDS,',')-1 END) )
用到了两个自定义函数GET_ARRAY_KEY_VALUE,SPLITARRAYLENGTH
自定义函数代码如下:
/* 自定义函数获取数组下标地址 */
CREATE OR REPLACE
FUNCTION splitArrayLength ( v_source IN VARCHAR2, v_delimiter IN VARCHAR2 ) RETURN INT IS j INT := 0;
i INT := 1;
len INT := 0;
delim_len INT := 0;
cnt INT := 0;
BEGIN
len := length( v_source );
delim_len := length( v_delimiter );
WHILE
j < len
LOOP
j := instr( v_source, v_delimiter, i );
IF
j = 0 THEN
j := len;
cnt := cnt + 1;
IF
i >= len THEN
EXIT;
END IF;
ELSE i := j + delim_len;
cnt := cnt + 1;
END IF;
END LOOP;
RETURN cnt;
END splitArrayLength;
/* 自定义函数,通过下标获取数据 */
CREATE OR REPLACE
FUNCTION GET_ARRAY_KEY_VALUE(P_JSON_DOC VARCHAR2, P_KEY VARCHAR2) RETURN VARCHAR2
AS
V_RESULT VARCHAR2(200);
BEGIN
-- routine body goes here, e.g.
-- DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');
EXECUTE IMMEDIATE 'select JSON_VALUE(:1,''$[' || P_KEY || ']'') from dual' into V_RESULT using P_JSON_DOC;
RETURN V_RESULT;
END;
group_concat()
MySQL用法
将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
group_concat函数首先根据group by指定的列进行分组,将同一组的列显示出来,并且用分隔符分隔。
由函数参数(字段名)决定要返回的列
例子:
create table emp(
emp_id int primary key auto_increment comment '编号',
emp_name char(20) not null default '' comment '姓名',
salary decimal(10,2) not null default 0 comment '工资',
department char(20) not null default '' comment '部门'
);
insert into emp(emp_name,salary,department)
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),('刘云鹏',7800,'销售部');
select group_concat(emp_name) from emp;
//张晶晶,王飞飞,赵刚,刘小贝,王大鹏,张小斐,刘云云,刘云鹏,刘云鹏
select department,group_concat(emp_name) from emp group by department;
| 人事部 | 刘小贝,王大鹏,张小斐
| 财务部 | 张晶晶,王飞飞,赵刚
| 销售部 | 刘云云,刘云鹏,刘云鹏
group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
(1)使用distinct可以排除重复值;
(2)如果需要对结果中的值进行排序,可以使用order by子句;
(3)separator是一个字符串值,默认为逗号
Oracle替换
使用LISTAGG函数替换,但是group_concat中必须含有order by语句
例:
-- 改前
JSON_UNQUOTE(JSON_EXTRACT(CONCAT('[',GROUP_CONCAT(CONCAT('"',case when
cs_cif_rating.MT_RATING_TYP_CD='CS_CRR' then cs_cif_rating.GRADE else null end,'"') ORDER BY cs_cif_rating.DT_CREATED DESC),']'),'$[0]')) GRADE
--改后
JSON_VALUE(CONCAT(CONCAT('[',(LISTAGG(case when
cs_cif_rating.MT_RATING_TYP_CD='CS_CRR' then cs_cif_rating.GRADE else null end,',') WITNIN GROUP(ORDER BY cs_cif_rating.DT_CREATED DESC))),']'),'$[0]') GRADE
更新中。。。