Oracle项目转Mysql的sql语句改造总结

最近公司有个项目需要将之前的Oracle版本转换为Mysql版本，除了需要修改配置外，最主要的就是sql语句的修改，现将改造过程中修改过的sql函数记录如下：

1.日期转换：
to_char --> date_format(a.order_date,'%Y-%m-%d %H:%i:%s')
to_date  --> str_to_date(#{orderDate},'%Y-%m-%d %H:%i:%s')

2.获取当前日期 sysdate --> sysdate()

3.字符串拼接 || --> concat()

4.格式化数据 TRUNC() 
①截取日期 trunc(sysdate,'yyyy') --> date_format(sysdate(),'%Y')
②取整TRUNC() --> FLOOR()

5.条件判断 decode()  --> case when then end
(case id when '0' then '1' else '0' end) treeLeaf

6.为空判断 nvl() --> ifnull()

7.行转列 wm_concat() --> group_concat()

8.时间为空校验 confirmedDate != ‘ ’,如果拿传入的时间类型参数与空字符串''进行对比判断则会引发异常. 只保  confirmedDate != null

9.年龄计算  
(SELECT TRUNC((to_char(sysdate, 'yyyyMMdd') - to_char(birth_date, 'yyyyMMdd')) /10000) as age   
-->
(SELECT date_format(sysdate(), '%Y-%m-%d') - date_format(birth_date, '%Y-%m-%d') as age

10.本级及下级区域查询
方法一：
START WITH id = #{areaCode} CONNECT BY PRIOR id = PARENT_ID   
-->  
(id = #{areaCode} or PARENT_IDS like concat('%',#{areaCode},'%') )
方法二：
START WITH id = #{areaCode} CONNECT BY PRIOR id = PARENT_ID   
--> 
FIND_IN_SET(a.id, getSOChildList(0))
新增mysql函数 getSOChildList(param)

DELIMITER //
CREATE DEFINER=`root`@`%` FUNCTION getSOChildList(rootId VARCHAR(10000)) RETURNS varchar(10000) CHARSET utf8
BEGIN 
       DECLARE pTemp VARCHAR(10000);  
       DECLARE cTemp VARCHAR(10000);  
      
       SET pTemp = '$';  
       SET cTemp =cast(rootId as CHAR); 
      
       WHILE cTemp is not null DO  
         SET pTemp = concat(pTemp,',',cTemp); 
         SELECT group_concat(ID) INTO cTemp FROM SYS_OFFICE   
         WHERE FIND_IN_SET(PARENT_ID,cTemp)>0;
       END WHILE;  
       RETURN pTemp;  
     END
//

11.前一天  
sysdate-1 --> date_sub(sysdate(),interval 1 day)

12.获取行号 ROWNUM 关键字
SELECT
(@rowNum:=@rowNum+1) as  ROWNUM,
a.* from(
SELECT
    *
FROM
    DRUG_INVENTORY_OUT_SUB
)a,(Select (@rowNum :=0) ) b

13.获取两个日期之间的月份间隔:
month_between（date1，date2）--> timestampdiff(month,date1,date2)

14. 日期月份累加(示例为在原日期上加3个月)：
add_months（date1,3）--> DATE_ADD(date1,INTERVAL 3)
获取2天后的日期：
sysdate+2 --> date_add(sysdate(),interval 2 day)

15.nextval自增序列实现：
<1>.--创建Sequence管理表 sequence
DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
name VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment INT NOT NULL DEFAULT 1,
PRIMARY KEY (name)
) ENGINE=InnoDB;
--创建好表之后插入值定义序列名、初始值、自增幅度
INSERT INTO sequence VALUES ('testSeq', 0, 1);
<2>--创建获取当前值的函数 currval()

DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END
$
DELIMITER ;
--创建取下一个值的函数 nextval()

DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
UPDATE sequence
SET current_value = current_value + increment
WHERE name = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER;

<3>使用时通过调用nextval()并传入序列名参数即可返回下一个自增值：
示例:
SELECT NEXTVAL('testSeq') as NEXTVAL;

16.to_number('123') --> cast('123' as SIGNED INTEGER)