MySql语句转Oracle的坑啊

1.模糊查询

• Mysql

SELECT * 
FROM USER
WHERE username like CONCAT('%','王','%')

• Oracle

SELECT * 
FROM USER
WHERE username like  '%'||'王'||'%'

Oracle里面也有concat函数，如果要使用CONCAT函数的话，我们需要使用两个CONCAT函数，对模糊查询进行拼接,也是可以的。这里的话，我就不演示了。

image.png

2.按照时间查询

• Mysql

SELECT * 
FROM USER
WHERE create_time between ''2019-12-10 12:22:29" and "2019-12-11 11:22:29"

• Oracle

SELECT * 
FROM USER
WHERE create_time between TO_DATE(''2019-12-10 12:22:29" , 'yyyy-mm-dd hh24:mi:ss' ) and TO_DATE("2019-12-11 11:22:29", 'yyyy-mm-dd hh24:mi:ss' ) 

3.分页查询

• MySQL：

    SELECT
        sheet.* 
    FROM
        sheetA act,
        sheetB sheet,
        sheetC actop 
    WHERE
        AND sheetB.ACT_INST_ID = sheetA.ACTIVITYINSTID 
        AND sheetA.REPOID = sheetC.REPO_ID 
        Limit 0,10

• Oracle：
  单表分页

    SELECT
    * 
FROM
    sheetA
WHERE
    ROWID NOT IN ( SELECT ROWID FROM sheetA WHERE ROWNUM <= 0 ) 
AND ROWNUM <= 10

关联查询分页

SELECT
    sheets.* 
FROM
    (
    SELECT
        sheet.* ,
        ROWNUM "rownum" 
    FROM
        sheetA act,
        sheetB sheet,
        sheetC actop 
    WHERE
        AND sheetB.ACT_INST_ID = sheetA.ACTIVITYINSTID 
        AND sheetA.REPOID = sheetC.REPO_ID 
    )  sheets
WHERE
    sheets."rownum" >= 0
    AND sheets."rownum" <= 5

后续遇到在更新.....