最近工作上需要做 Microsoft SQL 遷移到 Oracle SQL
本人進行了上百支ＳＱＬ語法的改寫，整理一些小心得
希望能夠幫助到需要的人
別名
MSSQL
Select * from ( select user_id as id ,user_name as name from users ) as A
Oracle
Select * from ( select user_id as id ,user_name as name from users ) A
相異點:
oracle 跟 mssql 的 column 別名都能使用 as，但是 oracle的 table 別名不能使用 as !
串接字串
mssql 有兩種串接字串的方法
CONCAT 函數 或者是 字串+字串
select concat('x','y','z') as coll1 , 'a'+'b'+'c' as col2
撈取結果
| col1 | col2 |
|---|---|
| xyz | abc |
Oracle 也有CONCAT函數，但是input最多只能串接"兩個"字串，超過兩個會報錯
"ORA-00909: 引數數目無效"
串接兩個以上的字串必須使用 '||'
select 'x'||'y'||'z' as col1 FROM DUAL
虛擬表
如果我想測試一個功能，但沒有特地想撈哪張表，要怎麼做呢?
MSSQL 中可以這樣寫，注意: 不需要加入 'from '關鍵字
select 1+1 as count
在 ORACLE 中，必須加入DUAL這張虛擬表
否則會報出 ORA-00923: 在應出現的位置找不到 FROM 關鍵字
select 1+1 as count FROM DUAL
日期處理:
取得當下時間
Mssql
GETDATE(), 預設格式範例: 2021-09-02 14:34:12
select GETDATE() as now
Oracle
不考慮時區問題的話，常用 SYSTIMESTAMP 與 SYSDATE
SYSTIMESTAMP 精度較高
預設格式範例 : 02-9月 -21
select SYSDATE as now FROM DUAL
日期計算:
MSSQL
取得現在的後一天日期
select DATEADD(day, 1 ,GETDATE())
取得現在前一天日期
select DATEADD(day, -1 ,GETDATE())
其中day 可以換成 year,month,minute,second 等...
Oracle
如果是單純的日期加減，可以使用+- 完成
select SYSDATE+1 FROM DUAL
那如果是小時呢?
select SYSDATE+(1/24) FROM DUAL
其他分鐘..秒鐘..依此類推
月份跟年 這種有大小月，閏年就要用到函數
ADD_MONTHS
select ADD_MONTHS (sysdate,1) FROM DUAL 加一個月
select ADD_MONTHS (sysdate,12) FROM DUAL 加一年
字串切割
mssql:
select substring('ABCDEFG',0,4)
意思，從索引為0的地方，往後切四個位元
結果: 'ABC'
可能有人覺得為啥長度只有3，是不是在話唬爛，其實是因為A的索引值為1，而不是0。
oracle:
select substr('ABCDEFG',0,4) FROM DUAL
邏輯同mssql，substring >> substr
結果: 'ABCD'
為什麼結果會比mssql的結果多了一個位元，其實是因為在oracle預設字串索引0的話，會自動從1開始
可以嘗試使用 select substr('ABCDEFG',1,4) FROM DUAL
會發現結果也是 'ABCD'
型態轉型
MSSQL , ORACLE 共同具有 CAST 函數
MSSQL
SELECT CAST(GETDATE() AS VARCHAR )
RESULT :"09 2 2021 4:30PM" 型態為VARCHAR
ORACLE
SELECT CAST(sysdate AS VARCHAR(30) ) FROM DUAL
RESULT :"02-9月 -21" 型態為VARCHAR
注意: ORACLE 使用 CAST 函數時 VARCHAR,CHAR.etc 等
需指定長度，否則會報錯；mssql 則不用
CAST 雖然為SQL共同規範的函數，但在兩個SQL系統中的靈活性都很差
所以各推薦以下用法:
MSSQL:convert函數
SELECT CONVERT(VARCHAR, GETDATE() , 120 ) AS col1
其中 120 為'樣式編號'，為mssql 的特有用法
譬如 120 的樣式邊號為 'yyyy-MM-dd hh:MM:ss'
112 為 'yyyyMMdd'
種類繁多讀者可自行google
這函數的好處為，在轉型的同時也能指定樣式
Oracle : to_char 函數
SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
Oracle 支援 format，靈活性高的多
注意事項: 因為oracle會將所有的字串都轉成大寫
所以在oracle的月(months)是用 MM表示，分(minutes)則是用MI表示，不~可~混~用~。而HH24則是24小時制的意思，如想用12小時制，HH即可。
以下補充其他語法邏輯差不多，不需要特別處理的部分
| 用處 | MSsql | oracle |
|---|---|---|
| 判空 | ISNULL | NVL |
| 字串長度(字元) | LEN | LENGTH |
| 轉型數字 | NUMERIC | Text |
| 資料長度(byte) | DATALENGTH | LENGTHB |
| 補齊字元 | REPLICATE | RPAD |
之後想到再補充...
