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

PL/SQL 區塊、預存程序與套件基礎入門

編輯：Oracle 資料庫基礎

前言

PL/SQL (Procedural Language/SQL) 是 Oracle 擴充 SQL 的程序性語言。核心程式單元為「區塊」，進階封裝單元為「預存程序」與「套件」。本文從最基本的區塊結構出發，逐步建立預存程序、套件，並示範 Java 呼叫。

一、PL/SQL 區塊結構

DECLARE       -- 宣告區 (選填) : 變數、常數、游標、例外、型別
BEGIN         -- 執行區 (必填) : 程序性邏輯、SQL 語句
EXCEPTION     -- 例外處理區 (選填) : 錯誤捕捉與處理
END;          -- 結束標記

1. 最簡單區塊 (僅執行區)

SET SERVEROUTPUT ON
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello Oracle');
END;
/

2. 含宣告區與查詢

DECLARE
    v_ename VARCHAR2(20);
    v_sal   NUMBER(7,2);
BEGIN
    SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = &emp_no;
    DBMS_OUTPUT.PUT_LINE('員工：' || v_ename || ' 薪資：' || v_sal);
END;
/

&emp_no 為替換變數，執行時提示輸入。

3. 含例外處理 (健壯性)

DECLARE
    v_ename VARCHAR2(20);
    v_sal   NUMBER(7,2);
BEGIN
    SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = &emp_no;
    DBMS_OUTPUT.PUT_LINE('員工：' || v_ename || ' 薪資：' || v_sal);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('查無此員工編號！');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('發生錯誤：' || SQLERRM);
END;
/

二、預存程序

建立語法

CREATE [OR REPLACE] PROCEDURE proc_name (
    param1 IN/OUT/IN OUT datatype,
    param2 IN datatype DEFAULT value
) IS | AS
    -- 區域變數宣告 (位於 IS/AS 與 BEGIN 之間)
BEGIN
    -- 程序邏輯
    NULL;
EXCEPTION
    -- 例外處理
END [proc_name];
/

範例：修改員工薪資

CREATE OR REPLACE PROCEDURE update_emp_sal (
    p_ename  IN VARCHAR2,
    p_newsal IN NUMBER
) IS
BEGIN
    UPDATE emp SET sal = p_newsal WHERE ename = p_ename;
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '員工 ' || p_ename || ' 不存在');
    END IF;
    COMMIT; -- 或由呼叫端控制交易
END update_emp_sal;
/

呼叫方式

SQL*Plus： EXEC update_emp_sal('SMITH', 100); 或 CALL update_emp_sal('SMITH', 100);
PL/SQL 區塊： BEGIN update_emp_sal('SMITH', 100); END;
Java (JDBC)：

String sql = "{ call update_emp_sal(?, ?) }";
try (CallableStatement cs = conn.prepareCall(sql)) {
    cs.setString(1, "SMITH");
    cs.setInt(2, 100);
    cs.execute();
}

三、套件

套件由「規格」與「主體」兩部分組成，實現封裝、命名空間、效能優化 (整體載入共享池)。

1. 套件規格 (介面)

CREATE OR REPLACE PACKAGE emp_pkg AS
    -- 公用常數、變數、游標、型別
    c_max_sal CONSTANT NUMBER := 50000;
    
    -- 公用程序/函數原型
    PROCEDURE update_sal(p_ename VARCHAR2, p_newsal NUMBER);
    FUNCTION get_sal(p_ename VARCHAR2) RETURN NUMBER;
    
    -- 公開游標
    CURSOR c_emp_dept(p_deptno NUMBER) IS
        SELECT * FROM emp WHERE deptno = p_deptno;
END emp_pkg;
/

2. 套件主體 (實作)

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
    -- 私用變數/程序 (外部不可見)
    v_call_count NUMBER := 0;
    
    PROCEDURE update_sal(p_ename VARCHAR2, p_newsal NUMBER) IS
    BEGIN
        v_call_count := v_call_count + 1;
        UPDATE emp SET sal = p_newsal WHERE ename = p_ename;
    END;
    
    FUNCTION get_sal(p_ename VARCHAR2) RETURN NUMBER IS
        v_sal NUMBER;
    BEGIN
        SELECT sal INTO v_sal FROM emp WHERE ename = p_ename;
        RETURN v_sal;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN RETURN -1;
    END;
    
    -- 初始化區塊 (套件首次被引用時執行一次)
    BEGIN
        DBMS_OUTPUT.PUT_LINE('EMP_PKG initialized');
    END;
END emp_pkg;
/

3. 使用套件元素

-- 呼叫程序
BEGIN
    emp_pkg.update_sal('SMITH', 200);
END;
/

-- 呼叫函數
DECLARE
    v_sal NUMBER;
BEGIN
    v_sal := emp_pkg.get_sal('SMITH');
    DBMS_OUTPUT.PUT_LINE('SMITH 薪資：' || v_sal);
END;
/

-- 使用公開游標
DECLARE
    v_rec emp%ROWTYPE;
BEGIN
    OPEN emp_pkg.c_emp_dept(10);
    LOOP
        FETCH emp_pkg.c_emp_dept INTO v_rec;
        EXIT WHEN emp_pkg.c_emp_dept%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_rec.ename);
    END LOOP;
    CLOSE emp_pkg.c_emp_dept;
END;
/

四、Java 呼叫套件程序

String sql = "{ call emp_pkg.update_sal(?, ?) }";
try (CallableStatement cs = conn.prepareCall(sql)) {
    cs.setString(1, "SMITH");
    cs.setInt(2, 200);
    cs.execute();
}

五、區塊 vs 程序 vs 套件比較

特性	匿名區塊	預存程序	套件
儲存位置	不儲存 (客戶端執行)	資料庫伺服器	資料庫伺服器
可重用性	無	高	最高 (模組化)
效能	每次編譯執行	共享池快取	整體載入共享池，減少磁碟 I/O
封裝性	無	單一程序	相關程序/函數/型別/游標分組
權限管理	依當前使用者	GRANT EXECUTE ON proc	GRANT EXECUTE ON pkg
狀態保持	無	無 (預設)	套件層級變數可跨會話保持 (Session State)

六、最佳實務建議

業務邏輯下沉：複雜計算、多表交易、資料驗證盡量在 PL/SQL 完成，減少網路往返。
套件化管理：相關功能歸屬同一套件 (如 order_pkg, hr_pkg)，避免散落的獨立程序。
例外處理標準化：使用 RAISE_APPLICATION_ERROR(-20xxx, 'msg') 回傳自訂錯誤碼，Java 端捕捉 SQLException.getErrorCode() 判斷業務錯誤。
避免套件狀態依賴：套件變數在 RAC/連線池環境下可能導致非預期行為，改用資料表或 Redis 保存共享狀態。
版本控管：PL/SQL 代碼納入 Git，使用 CREATE OR REPLACE 部署，生產環境嚴禁直接修改。

延伸閱讀：Oracle PL/SQL Language Reference、Steven Feuerstein《Oracle PL/SQL Programming》。掌握集合、大量綁定 (FORALL/BULK COLLECT)、動態 SQL (EXECUTE IMMEDIATE) 可進一步提升 PL/SQL 效能。