PL/SQL 單行函數與群組函數深度解析

函數概述

在 Oracle 資料庫中，函數是一種接受零個或多個參數並返回單一值的程式單元。內建函數可直接在 SQL 或 PL/SQL 陳述式中呼叫，主要分為兩大類：單行函數與群組函數（又稱集合函數）。單行函數逐行操作資料，群組函數則對多行資料彙總計算。

單行函數詳解

核心特性

單行函數涵蓋字元、數字、日期、轉換等多種類型，可用於 SELECT、WHERE、ORDER BY 等子句。例如：

SELECT ename, 
       TO_CHAR(hiredate, 'Day, DD-Mon-YYYY') 
FROM   emp 
WHERE  UPPER(ename) LIKE 'AL%' 
ORDER BY SOUNDEX(ename);

此外，單行函數亦可用於 UPDATE 的 SET 子句、INSERT 的 VALUES 子句及 DELETE 的 WHERE 子句中。

NULL 值處理

NULL 代表未知或空值，任何算術運算遇到 NULL 結果皆為 NULL，大多數函數亦同。例外函數包括 CONCAT、DECODE、DUMP、NVL、REPLACE，它們在參數為 NULL 時仍可返回非 NULL 值。其中 NVL(expr1, expr2) 最為關鍵：若 expr1 為 NULL 則返回 expr2，否則返回 expr1。

實例：計算員工總薪資（薪資 + 獎金），若獎金為 NULL 需用 NVL 排除影響：

UPDATE emp 
SET    salary = (salary + NVL(bonus, 0)) * 1.1;

常用單行字元函數

單行數字函數

操作數值資料，執行數學運算。所有三角函數參數與結果均為弧度。常用函數包括：

• ABS(n)：絕對值
• CEIL/FLOOR(n)：大於等於/小於等於 n 的最小/最大整數
• ROUND/TRUNC(n, m)：四捨五入/截斷至 m 位小數（m 可為負數）
• POWER(n1, n2)：n1 的 n2 次方
• MOD(n1, n2)：餘數
• SQRT(n)：平方根
• 三角函數：SIN、COS、TAN 及其反函數、雙曲函數
• LN/LOG/EXP：對數與指數運算

單行日期函數

操作 DATE 型別資料，多數回傳 DATE 值：

• ADD_MONTHS(d, n)：日期加減月份
• LAST_DAY(d)：該月最後一天
• MONTHS_BETWEEN(d1, d2)：兩日期相差月數
• NEXT_DAY(d, '星期')：日期之後指定星期的第一天
• ROUND/TRUNC(d, fmt)：日期四捨五入/截斷
• SYSDATE：目前系統日期時間
• NEW_TIME：時區轉換

單行轉換函數

實現資料型別間轉換：

• TO_CHAR(x, fmt, nls)：數字/日期轉字串
• TO_DATE(c, fmt, nls)：字串轉日期
• TO_NUMBER(c, fmt, nls)：字串轉數字
• HEXTORAW/RAWTOHEX：十六進位與 RAW 互轉
• CHARTOROWID/ROWIDTOCHAR：ROWID 與 CHAR 互轉
• CONVERT：字元集轉換
• TO_MULTI_BYTE/TO_SINGLE_BYTE：單/多位元組字元互轉

其他單行函數

• DECODE(expr, search, result [, ...] [, default])：條件判斷，類似 IF-THEN-ELSE
• GREATEST/LEAST(expr_list)：回傳最大/最小值
• DUMP(expr, fmt, start, length)：回傳內部表示資訊
• VSIZE(expr)：回傳內部表示位元組數
• USER/UID/USERENV：會話資訊函數
• EMPTY_BLOB/CLOB/BFILENAME：LOB 初始化函數

群組函數（多行函數）

核心差異

群組函數對多行資料彙總返回單一結果，常與 GROUP BY 搭配使用，可用於 SELECT 或 HAVING 子句，不可用於 WHERE 子句。

常用群組函數

GROUP BY 分組查詢

使用群組函數時，SELECT 清單中未納入群組函數的欄位必須出現在 GROUP BY 子句中。

SELECT state, COUNT(*) zip_count
FROM   zip_codes
GROUP BY state
ORDER BY COUNT(*) DESC;

HAVING 子句過濾分組

WHERE 不可使用群組函數，需用 HAVING 過濾分組後的結果：

SELECT sales_clerk, SUM(sale_amount)
FROM   gross_sales
WHERE  sales_dept = 'OUTSIDE'
GROUP BY sales_clerk
HAVING SUM(sale_amount) > 10000;

巢狀函數

函數可巢狀呼叫，遵循「由內而外、由左至右」執行順序。常見於 DECODE 邏輯判斷、群組函數與單行函數混合嵌套：

SELECT deptno,
       GREATEST(COUNT(DISTINCT job), COUNT(DISTINCT mgr)) cnt,
       COUNT(DISTINCT job) jobs,
       COUNT(DISTINCT mgr) mgrs
FROM   emp
GROUP BY deptno;

1. 上一篇文章： Oracle PL/SQL 語言基礎完全指南：架構、語法、程式單元與實戰腳本庫
2. 下一篇文章： Oracle 10g 實體備份資料庫開啟讀寫模式實戰

Oracle 資料庫基礎

知識管理系統（KMS）資料庫平台選型指南：關係型與文檔型的抉擇與融合

前言：KMS 的雙重資料挑戰 知識管理系統（KMS）需處理兩

圖解 SQL Server 2005 連結 Oracle 11g 完整步驟：從 Client 安裝到 Linked Server 測試

架構概覽 SQL Server 透過 Linked Serv

Oracle 10g 資料庫啟動與關閉完整指南

前言 本文詳細記錄 Oracle 10g 資料庫的啟動與關閉

Oracle 分區表降低磁碟 I/O 衝突：三大分區模式選型指南

背景痛點 單表 500 萬筆，所有資料放在同一顆實體磁碟，併