技法一：|| 操作符 (標準、通用、推薦)
-- 基礎拼接
SELECT '工號為' || fnumber || '的員工姓名為' || fname
FROM t_employee
WHERE fname IS NOT NULL;
-- 與字面量混合
SELECT 'ID:' || id || ', Name:' || name FROM users;
-- NULL處理: 任一操作數為NULL結果為NULL
SELECT 'A' || NULL || 'C' FROM dual; -- 結果: NULL
-- 解決: NVL/COALESCE
SELECT 'A' || NVL(col, '') || 'C' FROM dual;
技法二：CONCAT 函數 (僅兩參數、標準SQL)
-- 兩參數拼接
SELECT CONCAT('工號:', fnumber) FROM t_employee;
-- 非字符串隱式轉換
SELECT CONCAT('年齡:', fage) FROM t_employee; -- NUMBER自動TO_CHAR
-- ❌ 錯誤: 超過兩參數
SELECT CONCAT('工號為', fnumber, '的員工姓名為', fname) FROM t_employee;
-- ORA-00909: invalid number of arguments
-- ✅ 正確: 巢狀嵌套
SELECT CONCAT(CONCAT(CONCAT('工號為', fnumber), '的員工姓名為'), fname)
FROM t_employee
WHERE fname IS NOT NULL;
技法三：|| 連接 NULL 語義陷阱與規避
| 表達式 | 結果 | 說明 |
|---|
'A' || 'B' | AB | 正常 |
'A' || NULL | NULL | Oracle標準: NULL傳染 |
CONCAT('A', NULL) | A | CONCAT視NULL為空串 |
'A' || NVL(col, '') | A+值 | 推薦寫法 |
COALESCE(col1, col2, 'def') | 首個非NULL | 多列兜底 |
性能對比與選型建議
| 維度 | || | CONCAT |
|---|
| 可讀性 | ⭐⭐⭐⭐⭐ 直觀 | ⭐⭐ 巢狀難讀 |
| 參數數量 | 無限制 | 固定2個 |
| NULL語義 | 傳染NULL | 視為空串 |
| 標準兼容 | Oracle/PostgreSQL/MySQL(||) | ANSI SQL標準 |
| 解析性能 | 略優(原生操作符) | 函數調用開銷 |
最佳實踐決策樹
IF 拼接片段 ≤ 2 且 需ANSI兼容 或 希望NULL視為空串
THEN 使用 CONCAT(a, b)
ELSIF 片段 > 2 或 需保持NULL語義
THEN 使用 a || b || c ...
-- 並配合 NVL/COALESCE 處理可空列
ELSE
-- 複雜格式化場景
考慮使用:
1. TO_CHAR 格式化數字/日期後再拼接
2. LISTAGG (11g+) 多行轉單行拼接
3. XMLAGG/XMLELEMENT (舊版本) 多行聚合
END IF;
進階聚合拼接 (11g+)
-- 部門員工姓名逗號分隔
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
-- 指定分隔符、截長補短
SELECT deptno, LISTAGG(ename, '; ') WITHIN GROUP (ORDER BY hiredate) AS employees
FROM emp
GROUP BY deptno;
核心建議：
• 99%場景首選 || 操作符，配合NVL(col, '')處理可空列。
• 僅在需嚴格ANSI SQL移植性、或明確希望CONCAT的NULL空串語義時使用CONCAT。
• 多行聚合拼接強制使用 LISTAGG，禁用PL/SQL循環拼接(性能差、易超長)。
• 動態SQL拼接時嚴禁直接拼接用戶輸入，必須使用綁定變量防注入。
• 12c+ JSON_OBJECT/JSON_ARRAYAGG構建結構化輸出優於手工拼接字符串。
