整理 Oracle 基礎語法與常用函數，對照 SQL Server 寫法差異，適合從 SQL Server 轉型或初學 Oracle 的開發者快速上手。
一、基礎查詢特殊符號
| 功能 | Oracle | SQL Server |
|---|
| 字串串接 | || | + |
| 欄位別名 | col AS alias 或 col alias（無 AS） | col AS alias 或 col alias |
| 表別名 | table alias（**不可加 AS**） | table AS alias 或 table alias |
| 去重 | DISTINCT | DISTINCT |
二、單行函數分類速查
1. 字元函數
| 函數 | 說明 | 範例 | SQL Server 對應 |
|---|
LOWER/UPPER/INITCAP | 小寫/大寫/首字母大寫 | INITCAP('sql course') → 'Sql Course' | LOWER/UPPER（無 INITCAP） |
CONCAT(s1,s2) | 兩字串串接 | CONCAT('Hello','World') | s1 + s2 或 CONCAT |
SUBSTR(str,pos,len) | 子字串（位置從 1 起，負數從右） | SUBSTR('HelloWorld',-5,5) → 'World' | SUBSTRING(str,pos,len)、LEFT/RIGHT |
LENGTH | 字串長度 | LENGTH('HelloWorld') → 10 | LEN/DATALENGTH |
INSTR(str,sub,pos,occur) | 尋找子字串位置，支援第 N 次出現 | INSTR('HelloWorld','w') → 6 | CHARINDEX(sub,str,pos)（無第 N 次） |
LPAD/RPAD(str,n,pad) | 左/右填充至長度 n | LPAD('hello',10,'*') → '*****hello' | REPLICATE + CONCAT |
TRIM([LEADING/TRAILING/BOTH] c FROM str) | 修剪字元 | TRIM('H' FROM 'HelloWorld') → 'elloWorld' | LTRIM/RTRIM/TRIM |
2. 數值函數
| 函數 | 說明 | 範例 | SQL Server 對應 |
|---|
ROUND(n,m) | 四捨五入至 m 位小數 | ROUND(45.926,2) → 45.93 | ROUND |
TRUNC(n,m) | 截斷至 m 位小數 | TRUNC(45.926,2) → 45.92 | CAST(n AS INT) 或 ROUND(n,m,1) |
MOD(n,d) | 餘數 | MOD(16,3) → 1 | n % d |
POWER(n,m) | n 的 m 次方 | POWER(4,2) → 16 | POWER/SQUARE |
SQRT(n) | 平方根 | SQRT(4) → 2 | SQRT |
EXP/LN/LOG(b,n) | 指數/自然對數/以 b 為底對數 | LOG(10,100) → 2 | EXP/LOG/LOG10 |
SIGN(n) | 正負號 | SIGN(-8) → -1 | SIGN |
DBMS_RANDOM.VALUE | 隨機數 | DBMS_RANDOM.VALUE(0,1) | RAND() |
3. 日期函數
| 函數 | 說明 | 範例 | SQL Server 對應 |
|---|
SYSDATE | 當前日期時間 | SELECT SYSDATE FROM dual | GETDATE() |
MONTHS_BETWEEN(d1,d2) | 月份差 | MONTHS_BETWEEN(SYSDATE, DATE '2010-11-29') | DATEDIFF(MONTH,...) |
ADD_MONTHS(d,n) | 加減月份 | ADD_MONTHS(SYSDATE,1) | DATEADD(MONTH,n,d) |
NEXT_DAY(d,'FRIDAY') | 下一個指定星期 | NEXT_DAY(DATE '2011-11-01','FRIDAY') | 無直譯，需自寫 |
LAST_DAY(d) | 該月最後一天 | LAST_DAY(DATE '2011-11-01') → 2011-11-30 | EOMONTH (2012+) |
TO_CHAR(d,'fmt') | 日期轉字串 | TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') | CONVERT/FORMAT |
4. 轉換函數
| 轉換方向 | Oracle | SQL Server |
|---|
| 字串 → 數值 | TO_NUMBER('123.45') | CAST/CONVERT |
| 字串 → 日期 | TO_DATE('2011-11-01','YYYY-MM-DD') | CAST/CONVERT 或直接隱式轉換 |
| 數值 → 字串 | TO_CHAR(123.45,'FM999.00') | STR/CAST/CONVERT |
| 日期 → 字串 | TO_CHAR(SYSDATE,'YYYY-MM-DD') | CONVERT/VARCHAR/FORMAT |
5. 通用函數（NULL 處理）
| 函數 | 說明 | SQL Server 對應 |
|---|
NVL(expr1,expr2) | expr1 為 NULL 回傳 expr2 | ISNULL(expr1,expr2) |
NVL2(expr1,expr2,expr3) | expr1 不為 NULL 回傳 expr2，否則 expr3 | CASE WHEN expr1 IS NULL THEN expr3 ELSE expr2 END |
NULLIF(expr1,expr2) | 相等回傳 NULL，否則 expr1 | NULLIF |
COALESCE(expr1,expr2,...) | 回傳第一個非 NULL | COALESCE |
三、多表連接語法對照
-- 內連接
SELECT * FROM a INNER JOIN b ON a.id = b.parent_id;
-- 等價舊語法
SELECT * FROM a, b WHERE a.id = b.parent_id;
-- 左外連接
SELECT * FROM a LEFT JOIN b ON a.id = b.parent_id;
-- 等價舊語法（Oracle 專有）
SELECT * FROM a, b WHERE a.id = b.parent_id(+);
-- 右外連接
SELECT * FROM a RIGHT JOIN b ON a.id = b.parent_id;
-- 等價舊語法
SELECT * FROM a, b WHERE a.id(+) = b.parent_id;
-- 完全外連接
SELECT * FROM a FULL JOIN b ON a.id = b.parent_id;
-- Oracle 舊語法無對應，需用 UNION 模擬
-- 交叉連接（笛卡爾積）
SELECT * FROM a CROSS JOIN b;
四、函數嵌套與實用技巧
-- 嵌套範例
SELECT LENGTH(SUBSTR('HelloWorld',1,5)) FROM dual; -- 5
-- 取得星期幾
SELECT TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=ENGLISH') FROM dual; -- FRIDAY
-- 日期格式化模板
TO_CHAR(date_col, 'FMYYYY-MM-DD HH24:MI:SS') -- FM 移除前導空白
TO_CHAR(num_col, 'FM999,999.00')
Oracle 函數命名偏向動詞（TO_CHAR、ADD_MONTHS），SQL Server 偏向名詞（CONVERT、DATEADD）。建立「函數對照表」並貼在螢幕邊，轉型期效率倍增。
