PL/SQL 是 Oracle 對標準 SQL 的程序性擴充,整合於資料庫核心,提供高效能、交易型、可重用的伺服器端程式設計能力。本文系統梳理語法基礎,適合初學者建立正確觀念與資深開發者快速查閱。
PL/SQL 核心優勢
- 高效能:批次處理減少網路往返,
FORALL/BULK COLLECT 最小化引擎切換。
- 完整 SQL 整合:支援所有 SQL 資料型別、函數、DDL(動態 SQL)、物件型別。
- 模組化與重用:Procedure/Function/Package/Trigger 存儲於資料庫,可跨工具/應用呼叫。
- 安全性:
GRANT EXECUTE 精細控制存取,AUTHID DEFINER/CURRENT_USER 切換權限模型。
- 跨平台:純文字原始碼,任何支援 Oracle 的 OS 均可編輯執行。
PL/SQL 區塊結構
[DECLARE] -- 宣告區:變數、常數、游標、型別、例外
-- 宣告陳述式
BEGIN -- 執行區:業務邏輯、SQL、控制流程
-- 可執行陳述式
[EXCEPTION] -- 例外處理區:捕捉錯誤
-- 例外處理陳述式
END; -- 結束標記
- 每句以分號
; 結尾,SQL 可跨行。
- 註解:
-- 單行 或 /* 多行 */。
- 巢狀區塊:
BEGIN ... END; 可在執行區再嵌套。
變數與常數宣告
v_name VARCHAR2(30) := 'Scott'; -- 變數含初始值
c_pi CONSTANT NUMBER := 3.14159; -- 常數不可變更
v_sal emp.sal%TYPE; -- 錨定型別:跟隨欄位定義
v_row emp%ROWTYPE; -- 記錄型別:整列結構
v_arr DBMS_SQL.VARCHAR2_TABLE; -- 關聯陣列/集合
控制結構
| 結構 | 語法要點 |
| IF-THEN-ELSIF-ELSE | IF cond THEN ... ELSIF cond THEN ... ELSE ... END IF; |
| CASE | CASE expr WHEN val THEN ... ELSE ... END CASE; (表達式) 或 CASE WHEN cond THEN ... END CASE; (搜尋式) |
| LOOP | LOOP ... EXIT WHEN cond; ... END LOOP; |
| WHILE | WHILE cond LOOP ... END LOOP; |
| FOR | FOR i IN [REVERSE] low..high LOOP ... END LOOP; (隱式游標) 或 FOR rec IN (SELECT ...) LOOP ... END LOOP; |
例外處理
BEGIN
-- 業務邏輯
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- 處理 SELECT INTO 無資料
WHEN TOO_MANY_ROWS THEN
-- 處理 SELECT INTO 多筆
WHEN OTHERS THEN
-- 捕捉所有其他例外
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
RAISE; -- 重新拋出或記錄後決定是否吞掉
END;
- 預先定義例外:
NO_DATA_FOUND、TOO_MANY_ROWS、ZERO_DIVIDE、DUP_VAL_ON_INDEX 等。
- 自訂例外:
DECLARE e_custom EXCEPTION; PRAGMA EXCEPTION_INIT(e_custom, -20001); 然後 RAISE e_custom;。
游標
- 隱式游標:
SELECT INTO、DML 陳述式自動管理,屬性 SQL%ROWCOUNT、SQL%FOUND、SQL%NOTFOUND。
- 顯式游標:
DECLARE CURSOR c IS SELECT ...; OPEN c; FETCH c INTO ...; CLOSE c;。
- 游標 FOR 迴圈:
FOR rec IN c LOOP ... END LOOP; 自動開啟/擷取/關閉。
- 參數化游標:
CURSOR c(p_deptno NUMBER) IS SELECT ... WHERE deptno = p_deptno;。
- REF CURSOR:弱/強型別游標變數,跨程式單元傳遞結果集。
程式單元類型
| 類型 | 特點 | 用途 |
| Anonymous Block | 無名、不存儲、即時執行 | 測試、腳本、一次性任務 |
| Procedure | 有名、可存儲、無回傳值、支援 IN/OUT/IN OUT 參數 | 業務流程、批次作業 |
| Function | 有名、可存儲、必須回傳值、可用於 SQL | 計算、轉換、查詢封裝 |
| Package | 規格+主體、封裝相關程式、狀態保持(Session 級) | 模組化、API 設計、效能 |
| Trigger | 事件驅動、隱式執行 | 稽核、衍生欄位、複雜約束 |
| Type/Object | 物件導向:型別、方法、繼承 | 複雜領域模型 |
執行與除錯
- SQL*Plus / SQL Developer / VS Code:
EXEC proc_name(...); 或匿名區塊。
SET SERVEROUTPUT ON 啟用 DBMS_OUTPUT。
DBMS_DEBUG、PL/SQL Debugger(SQL Developer、VS Code 擴充)支援中斷點、單步、變數檢視。
- 編譯錯誤:
SHOW ERRORS、查詢 USER_ERRORS。
夯實 PL/SQL 基礎,是邁向高階資料庫開發、效能調優、架構設計的必經之路。
copyright ©
萬盛學電腦網 all rights reserved