您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle PL/SQL 核心基礎：塊結構、控制流、變數、游標、程序函數與包完整指南

編輯：Oracle 資料庫基礎

SQL 是宣告式的「說什麼」，PL/SQL 是過程式的「怎麼做」。Oracle PL/SQL 將 SQL 嵌入程式邏輯，賦予開發者實現複雜業務規則、批量處理、事件驅動的能力。本文系統梳理 PL/SQL 核心知識體系：程式結構、控制流、變數與資料型別、SQL 整合、程序/函數、游標、包與觸發器，為進階開發鋪路。

一、程式基本單位：Block（塊）

所有 PL/SQL 程式碼皆以 Block 為單位，三段式結構：

DECLARE    -- 宣告區 (可選)
  變數、游標、例外、子程式...
BEGIN      -- 執行區 (必須)
  可執行陳述式...
EXCEPTION  -- 例外處理區 (可選)
  WHEN exception_name THEN ...
END;

巢狀 Block 可構建模組化邏輯，內層 Block 可捕獲外層未處理的例外，實現細粒度錯誤控制。

二、控制結構

1. 條件：IF-THEN-ELSIF-ELSE-END IF

支援多條件分支，無 Switch/Case 陳述式（可用 CASE 運算式在 SQL/賦值中實現）。

2. 迴圈：三種形式

基本迴圈： LOOP ... EXIT WHEN ... END LOOP; 至少執行一次。
WHILE 迴圈： WHILE condition LOOP ... END LOOP; 先判斷。
FOR 迴圈： FOR i IN [REVERSE] low..high LOOP ... END LOOP; 計數器隱式宣告、自動遞增/遞減、不可在迴圈中修改計數器。

3. 循序結構：GOTO

支援 GOTO label，但強烈不建議使用，結構化程式設計原則下，迴圈/條件/模組化足以替代，濫用導致義大利麵代碼。

三、變數宣告與賦值

語法： variable_name datatype [NOT NULL := default_value];

方向： 變數名在左，型別在右（與 C/Java 相反），如 v_id NUMBER;。
賦值： 使用 :=，非 =（= 僅用於 SQL 比較或預設值）。例：v_id := 5;。
常用型別： NUMBER(p,s)、VARCHAR2(size)、BOOLEAN、DATE、%TYPE、%ROWTYPE、RECORD、TABLE/VARRAY（集合）。

四、SQL 在 PL/SQL 中的無縫整合

PL/SQL 區塊可直接嵌入 DML (INSERT/UPDATE/DELETE)、SELECT INTO（單行）、COMMIT/ROLLBACK/SAVEPOINT (TCL)。DDL 需用動態 SQL (EXECUTE IMMEDIATE / DBMS_SQL)。

隱式游標屬性： SQL%ROWCOUNT、SQL%FOUND、SQL%NOTFOUND、SQL%ISOPEN 即時反映最近一條 DML/SELECT INTO 影響。

五、程序與函數

特性	Procedure	Function
返回值	無 (可用 OUT/IN OUT 參數回傳多值)	必須有 RETURN 型別，單一值
呼叫方式	獨立陳述式 `proc(...);`	表達式一部分 `v := func(...);`
SQL 中呼叫	不可直接用於 SQL	可用於 SQL (需 DETERMINISTIC/無副作用)
建立語法	`CREATE OR REPLACE PROCEDURE name(params) AS ...`	`CREATE OR REPLACE FUNCTION name(params) RETURN type AS ...`

參數模式：IN (預設,唯讀)、OUT (唯寫,回傳)、IN OUT (雙向)。預設值僅限 IN 參數。

六、游標 —— 處理多行查詢結果的核心

1. 隱式游標

SELECT INTO、DML 自動開啟，僅支援單行結果，多行拋 TOO_MANY_ROWS。

2. 顯式游標

CURSOR c_emp IS SELECT * FROM emp WHERE deptno = p_deptno;
-- 參數化游標
CURSOR c_emp(p_deptno NUMBER) IS SELECT * FROM emp WHERE deptno = p_deptno;

生命週期： OPEN → FETCH INTO (迴圈) → CLOSE。
屬性： %FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN。
FOR 迴圈游標： FOR rec IN c_emp LOOP ... END LOOP; 自動 OPEN/FETCH/CLOSE，最簡潔推薦。

3. REF CURSOR (動態/弱類型游標)

用於程式間傳遞結果集、動態構建查詢、回傳給客戶端 (JDBC/ODP.NET)。
TYPE t_refcur IS REF CURSOR; (強類型) 或 SYS_REFCURSOR (弱類型)。

七、包 —— 模組化、封裝、效能三合一

包規格 (Spec) ： 公開介面（程序/函數/變數/游標/型別/例外宣告）。
包主體 (Body) ： 實作細節、私有元素、初始化區塊。
核心價值：

命名空間隔離： 避免命名衝突，類似 OO 的 Namespace。
狀態保持： 包級變數在 Session 級持久化，跨呼叫共享狀態（如快取、計數器）。
整體載入： 首次引用任一元素即將整包載入 Shared Pool，減少磁碟 I/O、Hard Parse。
權限授予： GRANT EXECUTE ON pkg TO user; 粒度精細。

八、觸發器 —— 事件驅動的自動化

類型： DML (BEFORE/AFTER INSERT/UPDATE/DELETE)、DDL、Database Event (STARTUP/SHUTDOWN/LOGON/LOGOFF)、INSTEAD OF (View)。
時機： ROW LEVEL (每列) vs STATEMENT LEVEL (整句)。
用途： 稽核、衍生欄位維護、跨表同步、複雜約束、安全審計。
警示： 觸發器隱式執行、難除錯、易造成遞迴/連鎖效能災難，能用約束/應用層/包程序解決，盡量不用觸發器。