oracle改造至mysql

DECODE(exp,when,then,…)

Mysql语法：CASE WHEN
简单函数：
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
搜索函数：
CASE WHEN [expr] THEN [result1]…ELSE [default] END
案例：

SELECT t.*,
  CASE WHEN t.controlflag =  Y  THEN  C 
       WHEN t.controlflag =  A  THEN  N  ELSE t.controlflag END  checkcontrolflag ,
  CASE WHEN t.controlflag =  Y  THEN  A 
       WHEN t.controlflag =  C  THEN  N  ELSE t.controlflag END  authcontrolflag 
FROM
  BASE_TRANCODE t 
ORDER BY t.TRANCODE

ROWINDEX BETWEEN分页

Mysql语法：LIMIT #{start#}, #{limit}
如果limit后面是一个参数，就是检索前多少行。如果limit后面是2个参数，就是从offset+1行开始，检索rows行记录。

1.SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;
2.SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000,1) LIMIT 10;
3.SELECT * FROM table ORDER BY id LIMIT 1000000, 10;

其中1和2的效率都高于3。
如sql中涉及返回总条数
COUNT(1) OVER() AS TOTAL
将统计总数逻辑单独生成统计sql
Select COUNT(1) FROM TABLENAME WHERE XXX....
在代码层“..Action.java”中将
datals.get(0)).getCount()
改成直接单独查询拆分出来的统计方法
XXXService.getXXXXCount(param);

TRUNC(XXX)日期格式化改造

针对原sql使用trunc（Date）进行去时分秒格式化的
转mysql后使用：
DATE(XXXX)
或者
STR_TO_DATE(date_format(XXXX, %Y%m%d ), %Y%m%d )

日期格式（DATE）相加减运算改造

由原来的直接date+1:
#dateMax# + 1
改成使用mysql自带运算函数：
TIMESTAMPADD(SQL_TSI_DAY,1,#dateMax#)

NVL(exp1,exp2)改造

Mysql语法：IFNULL(expression, alt_value)结合IF(expr1,expr2,expr3)，或者直接使用IF(expr1,expr2,expr3)
oracle 插入 null 和”，取出来都是null，可以通过nvl直接转换。mysql插入null和”，取出来分别是null和”，而ifnull只能判断null，不能判断”，因此如果需要将null和”都作为“空”转换时，不能直接使用ifnull。
select ifnull(name , 1) from dual;

Oracle中left join表关联简写改造

由原来的oracle简写“（+）”进行关联：

select 
         T.*
from
    CORP_RICH_ORDERTRADENIGHT T,
    CORP_RICH_CUSTPAYAMOUNTHIS H
where
    T.SERIALNO = H.TRADENO(+)

改为用left join进行替换：

select 
         T.*
from
    CORP_RICH_ORDERTRADENIGHT T  left join 
    CORP_RICH_CUSTPAYAMOUNTHIS H
    ON T.SERIALNO = H.TRADENO
WHERE T.ISSUEID = #issueid#

LOCK TABLE语法改造

涉及锁表语句由oracle语法：
LOCK TABLE CORP_RICH_ORDERTRADENIGHT IN SHARE MODE
改为mysql语法：
LOCK TABLE CORP_RICH_ORDERTRADENIGHT write
并且在锁表后的操作完成后执行手动解锁语句
unlock tables

多表关联多字段UPDATE改造

多表关联更新由原来oracle的语法

UPDATE FINANCIAL_SALEBANK FS
SET (FS.FREEZEAMOUNT, FS.OPERATORDATE) =
 (SELECT T.FREEZEAMOUNT，#operatorDate# opDATE  
FROM  CORP_RICH_ORDERTRADE T  WHERE T.TRANCODE = 0  AND XXXXX)
WHERE EXISTS (SELECT 1 FROM CORP_RICH_ORDERTRADE T 
WHERE T.TRANCODE = 0 AND XXXXX)

改为mysql语法：

UPDATE FINANCIAL_SALEBANK FF,(SELECT T.FREEZEAMOUNT amount,
 #operatorDate# opDATE FROM CORP_RICH_ORDERTRADE T   
WHERE T.TRANCODE = 0 AND XXXXX) bb
 set ff.FREEZEAMOUNT = bb.amount, ff.OPERATORDATE = bb.opDATE where FF.XXXX = BB.XXXXX

分组统计函数SUM(XXXX) OVER (PARTITION BY XXXXX)改造

分窗统计由原来的oracle OVER()语句：
SUM(XXXX) OVER (PARTITION BY XXXXX)
改为，将需要统计的值，单独生成分组统计后的临时表，然后用主表left join进行关联查出。

REGEXP_INSTR函数改造

 正则表达式函数由原来的：

REGEXP_INSTR(SUBSTR(B.OLDCONTENT, 1, INSTR(B.OLDCONTENT, ： , 1, 2)), ： ||#param3#|| )
修改为mysql自带函数：
SUBSTR(B.OLDCONTENT, 1, LOCATE( ： , B.OLDCONTENT, INSTR(B.OLDCONTENT, ： ) + 1)) regexp CONCAT( ： ,#param3#, )

TO_CHAR函数改造

转字符型由原来的oracle函数：
to_char(HOLIDAYDATE, yyyy-MM-dd )
改为mysql自带函数：
date_format(HOLIDAYDATE, %Y-%m-%d )

TO_NUMBER()函数改造

转数值型由原来的oracle函数：
TO_NUMBER(T.SYSVALUE)
改为mysql自带函数：
CAST(T.SYSVALUE as SIGNED)

MERGE INTO语法改造

insert与update两个独立语句，应用程序检查是否存在数据，再确认是否执行insert与update。

SUBSTR函数改造

字符截取由原来的oracle函数：
SUBSTR(CBT.TELLNOS,0,7)
改为如下：mysql中类似的SUBSTR语句需要将0开始改为1开始
SUBSTR(CBT.TELLNOS,1,7)

TO_NUMBER(TRUNC(DATE1)-TRUNC(DATE2))函数改造

两个日期相减由原来的oracle函数：
TO_NUMBER(TRUNC(C.PAYMENTDATE)-TRUNC(#nowdt#))
改为mysql自带函数：
datediff(DATE(C.PAYMENTDATE), DATE(#nowdt#))

MONTHS_BETWEEN()函数改造

计算两个日期中间相差月份的数值由原来oracle的函数：
months_between(A.MATURITYDATE,A.STARTDATE)
改为mysql函数：
ABS(TIMESTAMPDIFF(MONTH, date1 , date ))

Rtrim(string1,string2)函数改造

string1去除前面与string2字符集匹配的由原来的oracle函数：
Rtrim(string1,string2)
改为mysql函数：
reverse(replace(REVERSE( string1 ), string2 , ))

‘||’拼接符改造

字符拼接由原来的oracle用法：
SELECT #serialno:VARCHAR# || #xpadcoSeq# AS SERIALNO FROM DUAL
改为mysql自带行数：
SELECT CONCAT(#serialno#,#xpadcoSeq#) AS SERIALNO FROM DUAL

ROW_NUMBER() OVER(PARTITION BY)获取第一条数据改造

分组获取第一条记录由原来的oracle用法：
row_number() over(partition by c.issueid order by c.levelid desc) rn
改为mysql自带函数用法：
SUBSTRING_INDEX(group_concat(B.AREABANKID ORDER BY B.AREABANKID DESC), , ,1) AS ROWINDEX