玩轉 Oracle 學習第七天：PL/SQL 進階、視圖與觸發器實戰
第七天課程聚焦 PL/SQL 進階控制結構、例外處理機制、視圖 與 觸發器 四大核心主題。學習目標：能撰寫分頁程序、下單邏輯等實務模組；熟練處理常見例外；靈活設計各類觸發器；深度理解視圖優勢與限制並善用於安全與簡化查詢。
一、流程控制結構速查
1. 條件分支
-- 單分支
IF condition THEN statements; END IF;
-- 雙分支
IF condition THEN statements; ELSE statements; END IF;
-- 多分支
IF cond1 THEN stmt1;
ELSIF cond2 THEN stmt2;
ELSE stmt3;
END IF;
實務範例：依薪資調整薪資
CREATE OR REPLACE PROCEDURE adj_sal_by_name (p_ename VARCHAR2) IS
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE ename = p_ename;
IF v_sal < 2000 THEN
UPDATE emp SET sal = sal * 1.1 WHERE ename = p_ename;
END IF;
END;
/
實務範例：依職稱差異調薪
CREATE OR REPLACE PROCEDURE adj_sal_by_job (p_empno NUMBER) IS
v_job emp.job%TYPE;
BEGIN
SELECT job INTO v_job FROM emp WHERE empno = p_empno;
IF v_job = 'PRESIDENT' THEN
UPDATE emp SET sal = sal + 500 WHERE empno = p_empno;
ELSIF v_job = 'MANAGER' THEN
UPDATE emp SET sal = sal + 300 WHERE empno = p_empno;
ELSE
UPDATE emp SET sal = sal + 100 WHERE empno = p_empno;
END IF;
END;
2. 迴圈結構
| 類型 | 語法骨架 | 適用場景 |
|---|
| 基本 LOOP | LOOP ... EXIT WHEN cond; ... END LOOP; | 至少執行一次、條件在迴圈中產生 |
| WHILE LOOP | WHILE cond LOOP ... END LOOP; | 先判斷再執行、次數不定 |
| FOR LOOP | FOR i IN [REVERSE] start..end LOOP ... END LOOP; | 次數固定、隱式計數器 |
-- 基本 LOOP 範例：批次新增 10 筆使用者
CREATE OR REPLACE PROCEDURE batch_ins_user (p_name VARCHAR2) IS
v_num NUMBER := 1;
BEGIN
LOOP
INSERT INTO users VALUES (v_num, p_name);
EXIT WHEN v_num = 10;
v_num := v_num + 1;
END LOOP;
END;
-- WHILE 範例：續寫 11~20
v_num := 11;
WHILE v_num <= 20 LOOP
INSERT INTO users VALUES (v_num, p_name);
v_num := v_num + 1;
END LOOP;
-- FOR 範例：反向 10..1
BEGIN
FOR i IN REVERSE 1..10 LOOP
INSERT INTO users VALUES (i, '世陽');
END LOOP;
END;
3. GOTO 與 NULL 語句
GOTO label; 跳轉至 <<label>>，限制：不可從外層跳入內層區塊/迴圈/IF。NULL; 為合法空陳述式，常用於 ELSE NULL; 明確表達「不處理」。
DECLARE i INT := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('i=' || i);
IF i = 10 THEN GOTO end_loop; END IF;
i := i + 1;
END LOOP;
<<end_loop>>
NULL;
END;
二、實務級存儲程序開發：參數模式、Java 呼叫、分頁程序
1. 參數模式：IN / OUT / IN OUT
IN（預設）：唯讀輸入OUT：回傳值，呼叫端需註冊IN OUT：可讀可寫
2. 無回傳值程序 + Java JDBC 呼叫
-- PL/SQL
CREATE OR REPLACE PROCEDURE add_book (
p_bid IN NUMBER,
p_bname IN VARCHAR2,
p_pub IN VARCHAR2
) IS
BEGIN
INSERT INTO book VALUES (p_bid, p_bname, p_pub);
END;
// Java
CallableStatement cs = conn.prepareCall("{call add_book(?,?,?)}");
cs.setInt(1, 10);
cs.setString(2, "笑傲江湖");
cs.setString(3, "人民出版社");
cs.execute();
3. 有回傳值程序（非游標）+ Java
-- PL/SQL
CREATE OR REPLACE PROCEDURE get_emp (
p_empno IN NUMBER,
p_ename OUT VARCHAR2,
p_sal OUT NUMBER,
p_job OUT VARCHAR2
) IS
BEGIN
SELECT ename, sal, job INTO p_ename, p_sal, p_job
FROM emp WHERE empno = p_empno;
END;
// Java
CallableStatement cs = conn.prepareCall("{call get_emp(?,?,?,?)}");
cs.setInt(1, 7788);
cs.registerOutParameter(2, OracleTypes.VARCHAR);
cs.registerOutParameter(3, OracleTypes.DOUBLE);
cs.registerOutParameter(4, OracleTypes.VARCHAR);
cs.execute();
String name = cs.getString(2);
double sal = cs.getDouble(3);
String job = cs.getString(4);
4. 回傳結果集：SYS_REFCURSOR + 套件定義
-- 步驟 1：建立套件定義游標型別
CREATE OR REPLACE PACKAGE pkg_cursor AS
TYPE t_cursor IS REF CURSOR;
END pkg_cursor;
-- 步驟 2：程序輸出游標
CREATE OR REPLACE PROCEDURE get_dept_emps (
p_deptno IN NUMBER,
p_rc OUT pkg_cursor.t_cursor
) IS
BEGIN
OPEN p_rc FOR SELECT * FROM emp WHERE deptno = p_deptno;
END;
// Java
CallableStatement cs = conn.prepareCall("{call get_dept_emps(?,?)}");
cs.setInt(1, 10);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);
while (rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getString(2));
}
5. 通用分頁程序（動態 SQL + 計算總筆數/總頁數）
CREATE OR REPLACE PROCEDURE proc_page (
p_tname IN VARCHAR2, -- 表名
p_pagesize IN NUMBER, -- 每頁筆數
p_pagenow IN NUMBER, -- 當前頁碼
p_totalrows OUT NUMBER, -- 總筆數
p_totalpages OUT NUMBER, -- 總頁數
p_rc OUT pkg_cursor.t_cursor
) IS
v_sql VARCHAR2(1024);
v_start NUMBER := (p_pagenow - 1) * p_pagesize + 1;
v_end NUMBER := p_pagenow * p_pagesize;
BEGIN
-- 1. 分頁查詢（Oracle 經典 ROWNUM 兩層寫法）
v_sql := 'SELECT * FROM (SELECT t1.*, ROWNUM rn FROM (SELECT * FROM '
|| p_tname || ') t1 WHERE ROWNUM <= ' || v_end
|| ') WHERE rn >= ' || v_start;
OPEN p_rc FOR v_sql;
-- 2. 計算總筆數
v_sql := 'SELECT COUNT(*) FROM ' || p_tname;
EXECUTE IMMEDIATE v_sql INTO p_totalrows;
-- 3. 計算總頁數
IF MOD(p_totalrows, p_pagesize) = 0 THEN
p_totalpages := p_totalrows / p_pagesize;
ELSE
p_totalpages := p_totalrows / p_pagesize + 1;
END IF;
END;
Java 驗證分頁
CallableStatement cs = conn.prepareCall("{call proc_page(?,?,?,?,?,?)}");
cs.setString(1, "emp");
cs.setInt(2, 5);
cs.setInt(3, 1);
cs.registerOutParameter(4, OracleTypes.INTEGER);
cs.registerOutParameter(5, OracleTypes.INTEGER);
cs.registerOutParameter(6, OracleTypes.CURSOR);
cs.execute();
int totalRows = cs.getInt(4);
int totalPages = cs.getInt(5);
ResultSet rs = (ResultSet) cs.getObject(6);
三、PL/SQL 例外處理機制
1. 例外分類
- 預定義例外：
NO_DATA_FOUND、TOO_MANY_ROWS、ZERO_DIVIDE、DUP_VAL_ON_INDEX、INVALID_NUMBER、VALUE_ERROR 等 20 種。
- 非預定義例外：Oracle 錯誤碼未命名，可用
PRAGMA EXCEPTION_INIT 關聯。
- 使用者自訂例外：
DECLARE my_ex EXCEPTION; 配合 RAISE my_ex;。
2. 例外傳播規則
- 未捕捉的例外會向外層區塊傳播，直到最外層或被宿主環境（SQL*Plus、Java、Python）捕獲。
RAISE; 在例外區重拋原例外；RAISE new_ex; 轉拋新例外。
3. 實戰範例
-- 捕捉預定義例外
DECLARE
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename FROM emp WHERE empno = &gno;
DBMS_OUTPUT.PUT_LINE(v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('查無此員工編號');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('資料異常：單一編號對應多筆');
END;
-- 自訂例外：更新不存在員工視為業務錯誤
CREATE OR REPLACE PROCEDURE raise_sal (p_empno NUMBER) IS
e_no_update EXCEPTION;
BEGIN
UPDATE emp SET sal = sal + 1000 WHERE empno = p_empno;
IF SQL%NOTFOUND THEN
RAISE e_no_update;
END IF;
EXCEPTION
WHEN e_no_update THEN
DBMS_OUTPUT.PUT_LINE('未更新任何員工，編號可能不存在');
END;
四、視圖：虛擬表的威力與限制
核心差異
| 特性 | 表 | 視圖 |
|---|
| 儲存空間 | 佔用段/區塊 | 不儲存資料，僅存查詢定義 |
| 索引 | 可建立 | 無（12c+ 可建立基於視圖的物化視圖索引） |
| 查詢速度 | 快 | 每次執行底層查詢，相對較慢 |
| 安全性 | 需授權表權限 | 可僅授權視圖，隱藏敏感欄/列 |
| 簡化複雜查詢 | 否 | 是，封裝 JOIN/子查詢/聚合 |
建立視圖語法
-- 1. 簡單視圖：薪資 < 1000 員工
CREATE VIEW v_low_sal AS
SELECT * FROM emp WHERE sal < 1000;
-- 2. 複雜視圖：員工+部門名稱，唯讀
CREATE VIEW v_emp_dept AS
SELECT e.empno, e.ename, d.dname
FROM emp e JOIN dept d ON e.deptno = d.deptno
WITH READ ONLY;
-- 3. 條件限制視圖：僅允許插入符合條件的列
CREATE VIEW v_clerk AS
SELECT * FROM emp WHERE job = 'CLERK'
WITH CHECK OPTION; -- DML 必須符合 WHERE 條件
修改與刪除
-- 修改視圖定義（OR REPLACE）
CREATE OR REPLACE VIEW v_low_sal AS
SELECT empno, ename, sal FROM emp WHERE sal < 1500;
-- 刪除視圖
DROP VIEW v_low_sal;
可更新視圖限制
- 單一基礎表、無聚合/GROUP BY/DISTINCT/集合運算/ROWMID/子查詢。
- 若視圖含
WITH CHECK OPTION，DML 必須符合視圖 WHERE 條件。
- 複雜視圖需配合
INSTEAD OF 觸發器實現 DML。
五、觸發器：隱式執行的業務守門員
觸發器類型
| 分類 | 觸發時機 | FOR EACH ROW | 典型用途 |
|---|
| DML 列級 | BEFORE / AFTER INSERT/UPDATE/DELETE | 是 | 衍生欄位計算、稽核、資料轉換 |
| DML 陳述式級 | BEFORE / AFTER INSERT/UPDATE/DELETE | 否 | 跨列驗證、記錄影響筆數 |
| DDL/系統事件 | CREATE/ALTER/DROP、LOGON/LOGOFF、SHUTDOWN/STARTUP | 否 | 結構變更稽核、安全登入審計 |
| INSTEAD OF | 針對視圖的 DML | 是 | 使不可更新視圖可寫入 |
語法範本
CREATE OR REPLACE TRIGGER trg_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW]
[WHEN (condition)]
DECLARE
-- 宣告區
BEGIN
-- 執行區
-- :NEW.col 新值、:OLD.col 舊值（列級觸發器可用）
EXCEPTION
-- 例外區
END;
實務範例：自動維護修改時間戳
CREATE OR REPLACE TRIGGER trg_emp_upd_time
BEFORE UPDATE ON emp
FOR EACH ROW
BEGIN
:NEW.updated_at := SYSDATE;
:NEW.updated_by := USER;
END;
實務範例：防止週末異動核心表
CREATE OR REPLACE TRIGGER trg_no_weekend_dml
BEFORE INSERT OR UPDATE OR DELETE ON emp
DECLARE
v_day VARCHAR2(3);
BEGIN
v_day := TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN');
IF v_day IN ('SAT','SUN') THEN
RAISE_APPLICATION_ERROR(-20001, '週末禁止異動 EMP 表');
END IF;
END;
六、本日重點回顧
- 熟練
IF/ELSIF/ELSE、三種迴圈、GOTO/NULL 控制流程。
- 掌握
IN/OUT/IN OUT 參數模式與 Java CallableStatement 呼叫全流程。
- 會撰寫動態 SQL 分頁程序，並正確計算總筆數/總頁數。
- 理解例外傳播、預定義/自訂例外處理模式。
- 視圖：簡化查詢、資料隱藏、
WITH READ ONLY/CHECK OPTION、INSTEAD OF 觸發器。
- 觸發器：四大類型、
:NEW/:OLD、條件觸發 WHEN、自訂錯誤 RAISE_APPLICATION_ERROR。
第七天內容紮實且實務導向，建議動手完成分頁程序、自訂例外、視圖與觸發器的完整建立與測試，為後續套件開發、大型專案架構打下堅實基礎。
