Oracle 核心基本功大練兵：SQL 進階查詢、PL/SQL 程式設計與效能優化實戰精選

扎實的基本功是 DBA 與開發者進階的基石。本文精選 Scott 模式下的 30+ 實戰範例，涵蓋多表連接、子查詢、群組函數嵌套、視圖簡化、ROWNUM 分頁、約束條件、PL/SQL 區塊結構、集合與記錄型別、游標四大循環模式、異常處理、錯誤日誌、存儲過程/函數/觸發器、遞歸樹狀顯示等核心技能，是 Oracle 從業者的「每日一練」最佳教材。

一、 SQL 進階查詢精選

1. 多表連接與子查詢：各部門最高薪員工

SELECT e.ename, e.sal
FROM   emp e
JOIN   (SELECT MAX(sal) max_sal, deptno FROM emp GROUP BY deptno) t
  ON   (e.sal = t.max_sal AND e.deptno = t.deptno);

2. 群組函數嵌套：平均薪資最高部門名稱

SELECT d.dname
FROM   dept d
WHERE  d.deptno = (
  SELECT deptno FROM (
    SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno
  ) WHERE avg_sal = (
    SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno
  ));

3. ROWNUM 分頁：薪資第 6-10 名

SELECT ename, sal FROM (
  SELECT ename, sal, ROWNUM r FROM (
    SELECT ename, sal FROM emp ORDER BY sal DESC
  )
) WHERE r BETWEEN 6 AND 10;

4. 視圖封裝複雜查詢

CREATE VIEW v$_dept_avg_sal_info AS
SELECT deptno, AVG(sal) avg_sal, grade
FROM   emp
JOIN   salgrade s ON AVG(sal) BETWEEN s.losal AND s.hisal
GROUP BY deptno;

-- 後續查詢大幅簡化
SELECT t1.deptno, t1.avg_sal, t1.grade, d.dname
FROM   v$_dept_avg_sal_info t1
JOIN   dept d ON t1.deptno = d.deptno
WHERE  t1.grade = (SELECT MIN(grade) FROM v$_dept_avg_sal_info);

二、 DDL 約束條件實戰

CREATE TABLE stu (
  id        NUMBER(2),
  name      VARCHAR2(20) CONSTRAINT stu_name_nn NOT NULL,
  sex       NUMBER(2),
  age       NUMBER(3),
  sdate     DATE,
  grade     NUMBER(3) DEFAULT 1,
  class     NUMBER(3),
  email     VARCHAR2(50),
  CONSTRAINT stu_name_email_uin UNIQUE (name, email)
);

三、 PL/SQL 核心程式設計

1. 區塊結構與異常處理

SET SERVEROUTPUT ON;
DECLARE
  v_num NUMBER := 0;
BEGIN
  v_num := 2 / v_num;
  DBMS_OUTPUT.PUT_LINE(v_num);
EXCEPTION
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('error');
END;

2. 錨定型別 %TYPE / %ROWTYPE

v_empno emp.empno%TYPE;
v_emp   emp%ROWTYPE;

3. 集合型別：關聯陣列、巢狀表、VARRAY

-- 關聯陣列 (Index-by Table)
TYPE t_empno_tab IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
v_empnos t_empno_tab;
v_empnos(-1) := 8989;  -- 支援負索引

4. 記錄型別 RECORD

TYPE t_dept_rec IS RECORD (
  deptno dept.deptno%TYPE,
  dname  dept.dname%TYPE,
  loc    dept.loc%TYPE
);
v_dept t_dept_rec;
v_dept.dname := 'Research';

5. SQL 操作與 SQL%ROWCOUNT

UPDATE emp2 SET sal = sal/2 WHERE deptno = 10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' 行受影響');

6. 動態 SQL EXECUTE IMMEDIATE

EXECUTE IMMEDIATE 'CREATE TABLE t (col VARCHAR2(20) DEFAULT ''test'')';

7. 控制結構：IF / LOOP / WHILE / FOR

-- FOR 迴圈 (正向/反向)
FOR k IN 1..10 LOOP ... END LOOP;
FOR k IN REVERSE 1..10 LOOP ... END LOOP;

四、 游標四大循環模式

帶參數游標

CURSOR c(p_deptno emp.deptno%TYPE, p_job emp.job%TYPE) IS
  SELECT ename, sal FROM emp WHERE deptno = p_deptno AND job = p_job;

FOR r IN c(30, 'CLERK') LOOP ... END LOOP;

五、 存儲過程、函數、觸發器

存儲過程 (含參數模式 IN/OUT/IN OUT)

CREATE OR REPLACE PROCEDURE max_num(
  v_a IN NUMBER, v_b IN NUMBER,
  v_ret OUT NUMBER, v_temp IN OUT NUMBER) IS
BEGIN
  IF v_a > v_b THEN v_ret := v_a; ELSE v_ret := v_b; END IF;
  v_temp := v_temp + 1;
END;

函數 (必須 RETURN)

CREATE OR REPLACE FUNCTION sal_tax(v_sal NUMBER) RETURN NUMBER IS
BEGIN
  IF v_sal < 2000 THEN RETURN 0.10;
  ELSIF v_sal < 2750 THEN RETURN 0.15;
  ELSE RETURN 0.20; END IF;
END;

-- 呼叫方式同內建函數
SELECT ename, sal, sal_tax(sal) FROM emp;

觸發器：審計日誌與級聯更新

-- DML 審計觸發器
CREATE OR REPLACE TRIGGER trig_audit
AFTER INSERT OR UPDATE OR DELETE ON emp2
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO emp2_log VALUES (USER, 'INSERT', SYSDATE);
  ELSIF UPDATING THEN
    INSERT INTO emp2_log VALUES (USER, 'UPDATE', SYSDATE);
  ELSIF DELETING THEN
    INSERT INTO emp2_log VALUES (USER, 'DELETE', SYSDATE);
  END IF;
END;

-- 級聯更新觸發器 (部門號變更同步到員工表)
CREATE OR REPLACE TRIGGER trip_cascade
AFTER UPDATE OF deptno ON dept2
FOR EACH ROW
BEGIN
  UPDATE emp2 SET deptno = :NEW.deptno WHERE deptno = :OLD.deptno;
END;

六、 遞歸樹狀結構顯示

CREATE OR REPLACE PROCEDURE p_tree(p_pid article.pid%TYPE, p_level BINARY_INTEGER) IS
  v_prefix VARCHAR2(1024);
  CURSOR c IS SELECT * FROM article WHERE pid = p_pid;
BEGIN
  FOR i IN 1..p_level LOOP v_prefix := v_prefix || '****'; END LOOP;
  FOR r IN c LOOP
    DBMS_OUTPUT.PUT_LINE(v_prefix || r.cont);
    IF r.isleaf = 0 THEN p_tree(r.id, p_level + 1); END IF;
  END LOOP;
END;

EXEC p_tree(0, 0);

七、 錯誤日誌最佳實踐

CREATE TABLE errorlog (
  id     NUMBER PRIMARY KEY,
  errcode NUMBER,
  errmsg  VARCHAR2(1024),
  errdate DATE
);
CREATE SEQUENCE seq_errorlog_id;

DECLARE
  v_errcode NUMBER; v_errmsg VARCHAR2(1024);
BEGIN
  DELETE FROM dept WHERE deptno = 10;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    v_errcode := SQLCODE;
    v_errmsg  := SQLERRM;
    INSERT INTO errorlog VALUES (seq_errorlog_id.NEXTVAL, v_errcode, v_errmsg, SYSDATE);
    COMMIT;
END;

總結

這 30+ 範例涵蓋了 Oracle 開發與維護 80% 的日常場景。建議：

1. 建立個人程式碼片段庫，分類儲存
2. 每週挑選 1-2 個重寫優化
3. 實戰專案中有意識應用進階特性 (集合、動態 SQL、觸發器)
4. 參考《Oracle PL/SQL Programming》系統深造

基本功不分年資，唯有持續精進，方能從容應對生產環境的複雜挑戰。