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

Oracle 預存程序實戰全解析：從基礎語法到游標封裝應用

編輯：Oracle 資料庫基礎

前言

預存程序是 Oracle 資料庫開發的核心技能，能將業務邏輯封裝在資料庫層，提升效能、安全性與可維護性。本文透過完整代碼範例，循序漸進講解從基本建立、參數傳遞、OUT 參數返回值，到套件封裝 REF CURSOR 實現多條件查詢的實戰技巧。

環境準備：建立測試表

CREATE TABLE stuInfo (
    stuID   INT PRIMARY KEY,
    stuName VARCHAR2(20)
);

進階一：無參數預存程序

最簡單的形式，執行固定邏輯（如插入測試資料）。

CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
    INSERT INTO stuInfo VALUES (1, 'liheng');
END;
/
-- 執行：EXEC proc1; 或 BEGIN proc1; END;

進階二：IN 參數預存程序

接收外部輸入值，實現動態資料操作。記得提交事務！

CREATE OR REPLACE PROCEDURE proc2 (
    v_ID   INT,
    v_Name VARCHAR2
) IS
BEGIN
    INSERT INTO stuInfo VALUES (v_ID, v_Name);
    COMMIT; -- 關鍵：若無 COMMIT，資料僅在會話中可見
END;
/
-- 執行：EXEC proc2(2, 'wangwu');

進階三：OUT 參數返回單值

利用 OUT 模式將查詢結果回傳給呼叫端。

CREATE OR REPLACE PROCEDURE proc3 (
    v_ID    IN  INT,
    v_Name  OUT VARCHAR2
) IS
    varName stuInfo.stuName%TYPE; -- 使用 %TYPE 錨定型別，提升維護性
BEGIN
    SELECT stuName INTO varName FROM stuInfo WHERE stuID = v_ID;
    v_Name := varName; -- 賦值給 OUT 參數
EXCEPTION
    WHEN NO_DATA_FOUND THEN v_Name := '查無此人';
END;
/
-- 執行需使用區塊：
-- VARIABLE v_name VARCHAR2(20);
-- EXEC proc3(1, :v_name);
-- PRINT v_name;

進階四：套件封裝 REF CURSOR 返回結果集

套件將規範與實作分離，REF CURSOR 讓程式能動態返回結果集，是應用層（Java/.NET/Python）呼叫的標準方式。

1. 返回所有記錄

CREATE OR REPLACE PACKAGE PKG_STUINFO IS
    TYPE stuInfoCursorType IS REF CURSOR;
    PROCEDURE getStuInfo(stuInfoCursor OUT stuInfoCursorType);
END PKG_STUINFO;
/

CREATE OR REPLACE PACKAGE BODY PKG_STUINFO IS
    PROCEDURE getStuInfo(stuInfoCursor OUT stuInfoCursorType) IS
        var_cursor stuInfoCursorType;
    BEGIN
        OPEN var_cursor FOR SELECT * FROM stuInfo;
        stuInfoCursor := var_cursor;
    END;
END PKG_STUINFO;
/

2. 依編號查詢（支援 0 代表全查）

CREATE OR REPLACE PACKAGE PKG_STUINFO IS
    TYPE stuInfoCursorType IS REF CURSOR;
    PROCEDURE getStuInfo(v_ID INT, stuInfoCursor OUT stuInfoCursorType);
END PKG_STUINFO;
/

CREATE OR REPLACE PACKAGE BODY PKG_STUINFO IS
    PROCEDURE getStuInfo(v_ID INT, stuInfoCursor OUT stuInfoCursorType) IS
        var_cursor stuInfoCursorType;
    BEGIN
        IF v_ID = 0 THEN
            OPEN var_cursor FOR SELECT * FROM stuInfo;
        ELSE
            OPEN var_cursor FOR SELECT * FROM stuInfo WHERE stuID = v_ID;
        END IF;
        stuInfoCursor := var_cursor;
    END;
END PKG_STUINFO;
/

3. 依姓名模糊查詢

CREATE OR REPLACE PACKAGE PKG_STUINFO IS
    TYPE stuInfoCursorType IS REF CURSOR;
    PROCEDURE getStuInfo(v_Name VARCHAR2, stuInfoCursor OUT stuInfoCursorType);
END PKG_STUINFO;
/

CREATE OR REPLACE PACKAGE BODY PKG_STUINFO IS
    PROCEDURE getStuInfo(v_Name VARCHAR2, stuInfoCursor OUT stuInfoCursorType) IS
        var_cursor stuInfoCursorType;
    BEGIN
        IF v_Name IS NULL OR v_Name = ' ' THEN
            OPEN var_cursor FOR SELECT * FROM stuInfo;
        ELSE
            OPEN var_cursor FOR SELECT * FROM stuInfo WHERE stuName LIKE '%' || v_Name || '%';
        END IF;
        stuInfoCursor := var_cursor;
    END;
END PKG_STUINFO;
/