萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle PL/SQL 單行函數與群組函數深度解析:從字串操作到 NULL 語意陷阱

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;

單行字串函數精選

函數語法說明範例結果
ASCIIASCII('A')首字元 ASCII 碼65
CHRCHR(65)ASCII 碼轉字元'A'
CONCATCONCAT('a', 'b')串接,NULL 視為空字串'ab'
INITCAPINITCAP('hello world')每字首字大寫'Hello World'
INSTRINSTR(str, sub, pos, nth)搜尋位置,支援負數反向INSTR('a.b.c', '.', -1) → 4
LENGTHLENGTH('中文')字元長度(非位元組)2
LOWER/UPPERLOWER('ABC')大小寫轉換'abc'
LPAD/RPADLPAD('7', 3, '0')填充至指定長度'007'
LTRIM/RTRIM/TRIMTRIM(' x ')去除空白或指定字元'x'
REPLACEREPLACE(str, old, new)全域取代REPLACE('a_b', '_', '-') → 'a-b'
SUBSTRSUBSTR(str, pos, len)擷取子字串SUBSTR('abcde', 2, 2) → 'bc'
TRANSLATETRANSLATE(str, from, to)字元級一對一映射取代TRANSLATE('123', '12', 'ab') → 'ab3'

群組函數(聚合函數)進階

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
函數特性進階用法
COUNT忽略 NULL;COUNT(*) 計列COUNT(DISTINCT col)、COUNT(CASE WHEN cond THEN 1 END) 條件計數
SUM/AVG忽略 NULLSUM(CASE WHEN type='A' THEN amt END) 條件加總
MAX/MIN忽略 NULL;適用字串/日期MAX(hire_date) KEEP (DENSE_RANK FIRST ORDER BY sal) 先進階分析函數
STDDEV/VARIANCE統計分佈搭配 OVER() 成為視窗函數
LISTAGG11g+ 字串聚合LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename)
MEDIAN中位數等同 PERCENTILE_CONT(0.5)