数据库迁移（MySQL函数转化为Oracle函数）

本文链接：https://blog.csdn.net/weixin_51019125/article/details/127090655

声明

本人是刚入行的小白，进入公司后被安排把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

更新中。。。