Oracle 函數分為單行函數(逐列運算,回傳單值)與群組函數(聚合多列,回傳單值)兩大類。本文深度解析單行函數核心家族,重點剖析 NULL 語意對函數行為的影響,並補充群組函數進階用法。
單行函數核心特性
- 可用於
SELECT、WHERE、ORDER BY、SET、VALUES 等子句。
- 支援巢狀呼叫:
UPPER(TRIM(ename))。
- NULL 傳播規則:多數函數引數為 NULL 時回傳 NULL,例外:
NVL、NVL2、COALESCE、NULLIF、CONCAT、DECODE、REPLACE、DUMP。
NULL 與單行函數:關鍵實戰案例
-- emp 表:sal (薪資), comm (獎金) 可能為 NULL
-- 錯誤:sal + comm 只要 comm 為 NULL,結果即為 NULL
UPDATE emp SET total = (sal + comm) * 1.1; -- 導致 total 變 NULL
-- 正確:使用 NVL/COALESCE 排除 NULL 影響
UPDATE emp SET total = (sal + NVL(comm, 0)) * 1.1;
-- 或
UPDATE emp SET total = (sal + COALESCE(comm, 0)) * 1.1;
單行字串函數精選
| 函數 | 語法 | 說明 | 範例結果 |
ASCII | ASCII('A') | 首字元 ASCII 碼 | 65 |
CHR | CHR(65) | ASCII 碼轉字元 | 'A' |
CONCAT | CONCAT('a', 'b') | 串接,NULL 視為空字串 | 'ab' |
INITCAP | INITCAP('hello world') | 每字首字大寫 | 'Hello World' |
INSTR | INSTR(str, sub, pos, nth) | 搜尋位置,支援負數反向 | INSTR('a.b.c', '.', -1) → 4 |
LENGTH | LENGTH('中文') | 字元長度(非位元組) | 2 |
LOWER/UPPER | LOWER('ABC') | 大小寫轉換 | 'abc' |
LPAD/RPAD | LPAD('7', 3, '0') | 填充至指定長度 | '007' |
LTRIM/RTRIM/TRIM | TRIM(' x ') | 去除空白或指定字元 | 'x' |
REPLACE | REPLACE(str, old, new) | 全域取代 | REPLACE('a_b', '_', '-') → 'a-b' |
SUBSTR | SUBSTR(str, pos, len) | 擷取子字串 | SUBSTR('abcde', 2, 2) → 'bc' |
TRANSLATE | TRANSLATE(str, from, to) | 字元級一對一映射取代 | TRANSLATE('123', '12', 'ab') → 'ab3' |
群組函數(聚合函數)進階
| 函數 | 特性 | 進階用法 |
COUNT | 忽略 NULL;COUNT(*) 計列 | COUNT(DISTINCT col)、COUNT(CASE WHEN cond THEN 1 END) 條件計數 |
SUM/AVG | 忽略 NULL | SUM(CASE WHEN type='A' THEN amt END) 條件加總 |
MAX/MIN | 忽略 NULL;適用字串/日期 | MAX(hire_date) KEEP (DENSE_RANK FIRST ORDER BY sal) 先進階分析函數 |
STDDEV/VARIANCE | 統計分佈 | 搭配 OVER() 成為視窗函數 |
LISTAGG | 11g+ 字串聚合 | LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) |
MEDIAN | 中位數 | 等同 PERCENTILE_CONT(0.5) |
GROUP BY 進階
ROLLUP、CUBE、GROUPING SETS 產生多層次小計/總計。
GROUPING(col) 識別該列是否為彙總列(1=彙總,0=明細)。
HAVING 過濾群組結果,可使用群組函數。
實戰模式:條件聚合替代多次掃描
-- 傳統:3 次掃描
SELECT COUNT(*) FROM emp WHERE deptno=10;
SELECT COUNT(*) FROM emp WHERE deptno=20;
SELECT COUNT(*) FROM emp WHERE deptno=30;
-- 條件聚合:1 次掃描
SELECT
COUNT(CASE WHEN deptno=10 THEN 1 END) AS dept10_cnt,
COUNT(CASE WHEN deptno=20 THEN 1 END) AS dept20_cnt,
COUNT(CASE WHEN deptno=30 THEN 1 END) AS dept30_cnt
FROM emp;
精通單行函數的 NULL 語意與群組函數的進階聚合技巧,能寫出更簡潔、高效、正確的 SQL。
copyright ©
萬盛學電腦網 all rights reserved