Oracle SQL 進階查詢技巧：日期、集合運算、分析函數與層次查詢

前言

掌握進階 SQL 查詢技巧，能將複雜業務邏輯推至資料庫層執行，大幅降低應用層資料傳輸與處理負擔。本文涵蓋偽欄位、日期時間函數、集合運算、字串/轉換函數、空值處理、ROLLUP/CUBE 多維彙總、分析函數與層次化查詢，附完整範例。

一、偽欄位與基本 DDL 操作

1.1 偽欄位

• ROWID：資料列物理位址，最快單列存取路徑。
• ROWNUM：查詢結果集順序號（自 1 起），常用於分頁 WHERE ROWNUM <= 10。

1.2 表結構修改

-- 修改欄位長度
ALTER TABLE mytable MODIFY (address VARCHAR2(50));

-- 新增欄位
ALTER TABLE mytable ADD (phone VARCHAR2(20));

-- 刪除欄位
ALTER TABLE mytable DROP COLUMN phone;

-- 截斷表（保留結構，高速清空，不可回滾）
TRUNCATE TABLE mytable;

-- 查看表結構
DESC mytable;

-- 以現有表建立新表（含資料）
CREATE TABLE new_mytable AS SELECT * FROM mytable;

-- 僅複製結構無資料
CREATE TABLE new_mytable AS SELECT * FROM mytable WHERE 1=2;

二、日期時間處理大全

2.1 日期格式模型對照

• YYYY/MM/DD 年/月/日
• HH24/MI/SS 24時/分/秒
• MONTH/MON 月份全寫/縮寫
• DAY/DY 週幾全寫/縮寫

三、交易控制：SAVEPOINT / ROLLBACK / COMMIT

INSERT INTO mytable VALUES (7,'dick','tianjin','dick@sdi.com',DATE '2007-10-10');
SAVEPOINT p1;

UPDATE mytable SET name='abck' WHERE id=1;
SAVEPOINT p2;

INSERT INTO mytable VALUES (8,'qing','tianjin','dick@sdi.com',DATE '2008-10-10');
SAVEPOINT p3;

UPDATE mytable SET name='ddddd' WHERE id=3;
SAVEPOINT p4;

-- 回滾至 p2（撤銷 p3、p4 操作）
ROLLBACK TO SAVEPOINT p2;
-- 繼續或 COMMIT

四、集合運算子

-- 範例：欄位名稱/型別需相容
SELECT product_id, product_type_id, name FROM products
UNION ALL
SELECT prd_id, prd_type_id, name FROM more_products;

SELECT product_id, product_type_id, name FROM products
UNION
SELECT prd_id, prd_type_id, name FROM more_products;

SELECT product_id, product_type_id, name FROM products
INTERSECT
SELECT prd_id, prd_type_id, name FROM more_products;

SELECT product_id, product_type_id, name FROM products
MINUS
SELECT prd_id, prd_type_id, name FROM more_products;

五、字串操作與轉換函數

-- 連接運算子
SELECT '商品名稱為:' || name AS info FROM products;

-- TRANSLATE 字元級替換
SELECT name, TRANSLATE(name, 'spring', 'SPRING') FROM products;

-- LPAD 左填充 (字串, 總長度, 填充字元)
SELECT LPAD('function', 10, '=') FROM dual; -- ==function

-- RPAD 右填充
SELECT RPAD('function', 10, '=') FROM dual; -- function==

六、DECODE 與條件邏輯

DECODE(expr, search1, result1, ..., default) 類似 CASE WHEN 的簡潔寫法。

-- 基本用法
SELECT DECODE(1, 1, 2, 3) FROM dual; -- 回傳 2

-- 多條件
SELECT prd_id, available,
       DECODE(available, 'Y', '產品可用', 'N', '產品不可用', '未知狀態')
  FROM more_products;

-- 多搜尋值
SELECT product_id, product_type_id,
       DECODE(product_type_id,
              1, '書本',
              2, '視頻',
              3, 'DVD',
              4, 'CD',
              '雜志')
  FROM products;

七、空值處理三劍客

八、多維彙總：ROLLUP 與 CUBE

-- 基本群組
SELECT division_id, SUM(salary) FROM employees2 GROUP BY division_id;

-- ROLLUP：單欄位產生小計 + 總計
SELECT division_id, SUM(salary)
  FROM employees2
 GROUP BY ROLLUP(division_id);

-- ROLLUP：多欄位階層小計
SELECT division_id, job_id, SUM(salary)
  FROM employees2
 GROUP BY ROLLUP(division_id, job_id);

-- CUBE：所有維度組合小計 + 總計
SELECT division_id, job_id, SUM(salary)
  FROM employees2
 GROUP BY CUBE(division_id, job_id);

差異：ROLLUP 產生 n+1 層級（階層），CUBE 產生 2^n 組合（全交叉）。

九、分析函數：窗口計算利器

語法：函數() OVER (PARTITION BY 分組欄位 ORDER BY 排序欄位)

9.1 ROW_NUMBER：唯一遞增序號

-- 全表薪資排名（相同薪資仍給不同序號）
SELECT employee_id, division_id, job_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees2;

-- 依職務分組排名
SELECT employee_id, division_id, job_id, salary,
       ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY salary DESC) AS rn
  FROM employees2;

9.2 RANK：相同值同排名，跳過下一名

SELECT employee_id, division_id, job_id, salary,
       RANK() OVER (PARTITION BY division_id ORDER BY salary DESC) AS rk
  FROM employees2;

9.3 DENSE_RANK：相同值同排名，不跳過

SELECT d.dname, e.ename, e.sal,
       DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS dense_rk
  FROM emp e, dept d
 WHERE e.deptno = d.deptno;

十、層次化查詢：組織架構/物料清單

Oracle 專有 START WITH ... CONNECT BY PRIOR 語法支援遞迴查詢。

10.1 基本語法

SELECT employee_id, manager_id, empname, title, salary
  FROM more_employees
 START WITH employee_id = 1
 CONNECT BY PRIOR employee_id = manager_id;

• START WITH：指定根節點條件。
• CONNECT BY PRIOR：定義父子關聯，PRIOR 修飾父欄位。

10.2 LEVEL 偽欄位：顯示層級

SELECT LEVEL, employee_id, manager_id, empname, title, salary
  FROM more_employees
 START WITH employee_id = 1
 CONNECT BY PRIOR employee_id = manager_id
 ORDER BY LEVEL;

LEVEL：根節點=1，子節點=2，以此類推。

10.3 格式化輸出：縮排顯示階層

SELECT LEVEL,
       LPAD(' ', 2*(LEVEL-1)) || empname AS employee_name
  FROM more_employees
 START WITH employee_id = 1
 CONNECT BY PRIOR employee_id = manager_id;

十一、總結

• 善用 TO_CHAR/TO_DATE/ADD_MONTHS 處理日期，避免隱性轉換導致索引失效。
• 集合運算子處理多來源資料整合，注意欄位順序/型別一致。
• NVL/NVL2/NULLIF 精準控制空值語意，減少應用層判斷。
• 報表彙總首選 ROLLUP/CUBE，避免多次掃描表。
• 分析函數替代自連接/子查詢計算排名/累計/移動平均，效能提升數量級。
• 層次查詢解決樹狀結構遞迴，配合 LEVEL 與 SYS_CONNECT_BY_PATH 可輸出完整路徑。