Oracle SQL / MSSQL 常用語法比較


Posted by c9103205 on 2021-09-03

最近工作上需要做 Microsoft SQL 遷移到 Oracle SQL 

本人進行了上百支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

之後想到再補充...


#SQL #MSSQL #oracleSQL







Related Posts

[Day 06]: Docker Network-2

[Day 06]: Docker Network-2

[Day 04] 樣板模式,合成模式,狀態模式,代理模式

[Day 04] 樣板模式,合成模式,狀態模式,代理模式

Node.js 部署到 heroku dynos

Node.js 部署到 heroku dynos


Comments