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

Oracle PL/SQL 程式設計入門：區塊結構、控制流與核心物件

編輯：Oracle 資料庫基礎

為什麼需要 PL/SQL

SQL 是非程序化的 4GL（宣告式），擅長集合操作但無法表達複雜流程控制。PL/SQL 是 Oracle 的程序化擴展（3GL），提供變數、迴圈、例外處理、模組化等能力，讓業務邏輯贴近資料執行，減少網路往返，提升效能與封裝性。

PL/SQL 區塊結構

[DECLARE]   -- 宣告區（變數、游標、型別、例外）
BEGIN       -- 執行區（必須）
  -- 程式邏輯
[EXCEPTION] -- 例外處理區（可選）
  WHEN exception_name THEN ...
END;
/

完整範例

DECLARE
  v_id   INTEGER;
  v_name VARCHAR2(20);
  CURSOR c_emp IS SELECT emp_id, emp_name FROM employee WHERE emp_id = 3;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_id, v_name;
    EXIT WHEN c_emp%NOTFOUND;
  END LOOP;
  CLOSE c_emp;
  DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('查無資料');
END;
/

控制結構

條件判斷

IF condition THEN stmt1; ELSIF condition2 THEN stmt2; ELSE stmt3; END IF;
CASE expr WHEN val1 THEN stmt1 WHEN val2 THEN stmt2 ELSE stmt3 END CASE;

迴圈三種形式

-- 1. 基本迴圈（至少執行一次）
LOOP ... EXIT WHEN cond; ... END LOOP;

-- 2. WHILE 迴圈（先判斷）
WHILE condition LOOP ... END LOOP;

-- 3. FOR 迴圈（數值範圍/游標/集合）
FOR i IN 1..10 LOOP ... END LOOP;
FOR rec IN cursor_name(param) LOOP ... END LOOP;
FOR idx IN coll.FIRST .. coll.LAST LOOP ... END LOOP;

順序控制

GOTO label 合法但不建議使用，結構化程式設計可完全替代。

變數宣告與賦值

語法：變數名型別 [NOT NULL] [:= 初始值];
賦值運算子：:=（非 =）。
錨定型別：v_sal emp.sal%TYPE、v_row emp%ROWTYPE，結構變更自動適配。

常用資料型別

類別	型別	說明
數值	NUMBER(p,s)	p=精度(1~38), s=小數位; 整數用 PLS_INTEGER/BINARY_INTEGER 效能最佳
字串	VARCHAR2(n)	變長字串，最大 32767（PL/SQL）或 4000（SQL）
日期	DATE / TIMESTAMP	TIMESTAMP 含小數秒與時區
布林	BOOLEAN	僅 PL/SQL 可用，SQL 不支援
大物件	CLOB/BLOB/BFILE	非結構化大資料

SQL 指令在 PL/SQL 中的使用

DML（SELECT/INSERT/UPDATE/DELETE/MERGE）直接嵌入執行區。
DDL（CREATE/DROP/ALTER）需用 EXECUTE IMMEDIATE 動態 SQL。
DCL（COMMIT/ROLLBACK/SAVEPOINT）直接使用。
SELECT INTO 只能回傳單行，多行需用游標或 BULK COLLECT。

程序與函數

-- 程序（無回傳值）
CREATE OR REPLACE PROCEDURE proc_name (p_in IN TYPE, p_out OUT TYPE) AS
BEGIN ... END;

-- 函數（有回傳值）
CREATE OR REPLACE FUNCTION func_name (p_in IN TYPE) RETURN TYPE AS
BEGIN ... RETURN value; END;

範例：寫入兩筆資料

CREATE OR REPLACE PROCEDURE test_proc AS
  v_f11 NUMBER := 1; v_f21 VARCHAR2(20) := 'first';
  v_f12 NUMBER := 2; v_f22 VARCHAR2(20) := 'second';
BEGIN
  INSERT INTO t1 VALUES (v_f11, v_f21);
  INSERT INTO t1 VALUES (v_f12, v_f22);
END test_proc;
/

游標核心觀念

游標 = 查詢結果集的指標。宣告 → 開啟 → 擷取 → 關閉。CURSOR FOR LOOP 自動化四步驟，為首選寫法。

進階模組化：套件

-- 規格（公開介面）
CREATE OR REPLACE PACKAGE pkg_emp AS
  FUNCTION get_sal(p_empno NUMBER) RETURN NUMBER;
  PROCEDURE raise_sal(p_empno NUMBER, p_pct NUMBER);
END pkg_emp;

-- 主體（實作細節）
CREATE OR REPLACE PACKAGE BODY pkg_emp AS
  FUNCTION get_sal(p_empno NUMBER) RETURN NUMBER IS
    v_sal NUMBER;
  BEGIN
    SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
    RETURN v_sal;
  END;
  PROCEDURE raise_sal(p_empno NUMBER, p_pct NUMBER) IS
  BEGIN
    UPDATE emp SET sal = sal * (1 + p_pct/100) WHERE empno = p_empno;
  END;
END pkg_emp;

套件提供命名空間、狀態保持（套件變數）、過載、封裝等優勢，是大型 PL/SQL 專案的標準架構單元。