Oracle 常用函數大全：110 個核心函數詳解與實戰範例

Oracle 常用函數分類速覽

Oracle 資料庫提供了豐富的內建函數，涵蓋字串處理、數值運算、日期時間、轉換、聚合等多個領域。本文精選 110 個高頻函數，附帶語法說明與實戰 SQL 範例，助你快速掌握核心用法。

一、字串函數

1. ASCII / CHR

取得字元的 ASCII 碼值，或由 ASCII 碼反推字元。

SELECT ASCII('A') AS A_code, ASCII('a') AS a_code, ASCII('0') AS zero_code, ASCII(' ') AS space_code FROM dual;
-- 結果: 65, 97, 48, 32

SELECT CHR(54740) AS chinese_char, CHR(65) AS english_char FROM dual;
-- 結果: 趙, A

2. CONCAT / || 串接

兩字串相連，Oracle 也支援 || 運算子。

SELECT CONCAT('010-', '88888888') || '轉23' AS phone FROM dual;
-- 結果: 010-88888888轉23

3. INITCAP / LOWER / UPPER

首字大寫、全小寫、全大寫轉換。

SELECT INITCAP('smith') AS initcap, LOWER('AaBb') AS lower, UPPER('AaBb') AS upper FROM dual;
-- 結果: Smith, aabb, AABB

4. INSTR / SUBSTR / LENGTH

搜尋子字串位置、截取子字串、取得長度。

SELECT INSTR('oracle training', 'ra', 1, 2) AS pos FROM dual;  -- 9
SELECT SUBSTR('13088888888', 3, 8) AS sub FROM dual;           -- 08888888
SELECT LENGTH('北京') AS len FROM dual;                          -- 2 (字元數)

5. LPAD / RPAD / LTRIM / RTRIM / TRIM

填充、去除左右空白或指定字元。

SELECT LPAD(RPAD('gao', 10, '*'), 17, '*') AS padded FROM dual;  -- *******gao*******
SELECT LTRIM(RTRIM(' gao qian jing ', ' '), ' ') AS trimmed FROM dual; -- gao qian jing

6. REPLACE / TRANSLATE / SOUNDEX

字串替換、字元對應替換、同音比對。

SELECT REPLACE('he love you', 'he', 'i') AS replaced FROM dual;  -- i love you
SELECT SOUNDEX('weather') AS s1, SOUNDEX('wether') AS s2 FROM dual; -- 同音值相同

二、數值函數

基本運算

ABS(-100)      -- 100 絕對值
CEIL(3.14)     -- 4  向上取整
FLOOR(3.14)    -- 3  向下取整
ROUND(55.5)    -- 56 四捨五入
TRUNC(55.5)    -- 55 截斷
MOD(10,3)      -- 1  餘數
POWER(2,10)    -- 1024 次方
SQRT(64)       -- 8  平方根
SIGN(-10)      -- -1 正負號

三角與對數

SIN(1.57079)   -- 1
COS(-3.14159)  -- -1
TAN(0.785398)  -- 1
ASIN(0.5)      -- 0.52359878
ACOS(-1)       -- 3.1415927
ATAN(1)        -- 0.78539816
LN(2.7182818)  -- 1  自然對數
LOG(2, 4)      -- 2  以 2 為底對數
EXP(1)         -- 2.7182818  e 的 n 次方

雙曲函數

SINH(20)  -- 242582598
COSH(20)  -- 242582598
TANH(20)  -- 1

三、日期時間函數

-- 當前日期時間
SYSDATE

-- 月份運算
ADD_MONTHS(TO_DATE('199912','YYYYMM'), 2)   -- 2000-02
ADD_MONTHS(TO_DATE('199912','YYYYMM'), -2)  -- 1999-10

-- 月底日期
LAST_DAY(SYSDATE)

-- 兩日期月份差
MONTHS_BETWEEN('19-DEC-1999', '19-MAR-1999')  -- 9

-- 時區轉換
NEW_TIME(SYSDATE, 'PDT', 'GMT')

-- 下一個指定週幾
NEXT_DAY('18-MAY-2001', 'FRIDAY')  -- 25-MAY-2001

-- 日期格式化
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
TO_DATE('2024-01-15', 'YYYY-MM-DD')

四、轉換函數

TO_CHAR(number|date, fmt)   -- 轉字串
TO_NUMBER(char, fmt)        -- 轉數值
TO_DATE(char, fmt)          -- 轉日期
CAST(expr AS type)          -- ANSI 標準轉型

五、聚合與分析函數

COUNT(*) / COUNT(col)       -- 計數
SUM(col) / AVG(col)         -- 總和/平均
MAX(col) / MIN(col)         -- 極值
STDDEV(col) / VARIANCE(col) -- 標準差/變異數

-- 視窗函數範例
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC) AS rn
RANK()        OVER (ORDER BY sal DESC) AS rk
DENSE_RANK()  OVER (ORDER BY sal DESC) AS drk
LAG(sal)      OVER (ORDER BY hiredate) AS prev_sal
LEAD(sal)     OVER (ORDER BY hiredate) AS next_sal

六、條件與其他實用函數

NVL(expr1, expr2)           -- expr1 為 null 回傳 expr2
NVL2(expr1, expr2, expr3)   -- expr1 不為 null 回傳 expr2，否則 expr3
COALESCE(expr1, expr2, ...) -- 回傳第一個非 null
DECODE(val, if1, then1, if2, then2, ..., else) -- 類似 switch
CASE WHEN cond THEN result ... ELSE other END   -- ANSI 標準
GREATEST(1,5,3)  -- 5 最大值
LEAST(1,5,3)     -- 1 最小值
VSIZE(expr)      -- 內部儲存大小 (bytes)
DUMP(expr, fmt)  -- 內部表示詳細資訊

七、實戰技巧：快速查詢所有使用者表欄位資訊

SELECT table_name, column_name, data_type, data_length, nullable
FROM user_tab_columns
WHERE table_name IN ('EMP', 'DEPT')
ORDER BY table_name, column_id;

掌握這 110 個核心函數，足以應對 90% 以上的日常開發與維護場景。建議收藏備查，實戰中靈活組合，事半功倍。