sql语句中，MySQL函数替换成Oracle函数

sql语句中，MySQL函数替换成Oracle函数

转载：数据库迁移（MySQL函数转化为Oracle函数）_mysql语句转oracle语句在线工具_呓语肿弟的博客-CSDN博客

非常实用的文章。

ifnull()函数–Oracle使用nvl()替换

-- mysql
IFNULL(t.name, '');

-- oracle
nvl(t.name, '');

concat()函数

oracle也可以使用这个函数，但是建议超过两个以上使用函数嵌套来拼接字符串

-- mysql
concat('ac','eg','gg')

-- oracle
'ac' || 'eg' || 'gg'

-- 或者
concat(concat('ac','eg'),'gg')

substring()–Oracle使用substr()替换

截取字符串：从特定位置开始的字符串返回一个给定长度的子字符串
SUBSTRING(字符串,position);//获得字符串的position位置之后的所有字符
-- 如果position为0，则返回一个空串 

SUBSTRING(string,position,length);//获得字符串的position位置之后的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()–Oracle使用substr()、instr()替换

substring_index（“待截取有用部分的字符串”，“截取数据依据的字符”，截取字符的位置N）

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()–Oracle使用instr()替换

mysql locate()函数介绍

- locate(字符串1，字符串2)
从字符串2的第一个位置开始找，找到并返回 字符串1在字符串2中第一次出现的位置

- locate(字符串1，字符串2，pos)
从字符串2的pos位置开始找，找到并返回字符串1在字符串2中第一次出现的位置

如果找不到则返回0

例子

--mysql
	locate('bar', 'foobarbar') //4
	locate('x','abxz') //3
	locate('a','bxz') //0
--oracle
	instr('foobarbar','bar') //4
   	instr('bxz','a') //0

replace()

Oracle同样也是这个函数

datediff()–Oracle使用ROUND(TO_NUMBER())替换

mysql datediff()函数介绍

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

例子

遇到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()–Oracle使用to_char()替换

例子

-- mysql
date_format(sysdate(), '%Y-%m-%d')

-- oracle
to_char(sysdate,'YYYY-MM-DD')

str_to_date()–Oracle使用to_date()替换

例子

-- mysql
str_to_date('2023-01-01', '%Y-%m-%d')

-- oracle
to_date('2023-01-01','YYYY-MM-DD')

char_length()–Oracle使用lengthb()替换

mysql char_length()介绍

返回以字符来测量字符串str的长度。一个多字节字符算作一个字符。
这意味着，对于包含五个两字节字符的字符串，那么 length() 返回10，而 char_length() 返回5。
    
select char_length("text"); //4

例子

-- 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()，NOW()，CURTIME()–Oracle使用sysdate()替换

select sysdate();

date_sub()/subdate()

mysql date_sub()/subdate()介绍

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可以直接使用INTERVAL来计算时间

-- 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()/month()/day()/quarter介绍

--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
  -- 年
	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()介绍

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。这个函数在第一个参数包含一个逗号时将无法正常运行。

oracle 没有对应的函数，可以使用自己编写的函数

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;  

group_concat()–Oracle使用listagg()替换

例子

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是一个字符串值，默认为逗号

-- 改前
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

未完待续…