Oracle 存儲過程實戰代碼大全：建表、參數模式、OUT 回傳、REF CURSOR 多模式查詢、套件封裝

以學生資訊表 stuInfo 為例，展示從建表、基本過程、IN/OUT 參數、REF CURSOR 回傳結果集，到 **套件封裝三種查詢模式 (全表、依編號、依姓名模糊)** 的完整代碼，可直接複製套用。

基礎建表

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

1. 無參數過程：固定新增一筆

CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
  INSERT INTO stuInfo VALUES (1, 'liheng');
  COMMIT;
END;
/

2. IN 參數過程：動態新增

CREATE OR REPLACE PROCEDURE proc2 (
  v_ID   IN INT,
  v_Name IN VARCHAR2
) IS
BEGIN
  INSERT INTO stuInfo VALUES (v_ID, v_Name);
  COMMIT;
END;
/

3. OUT 參數過程：依編號查姓名

CREATE OR REPLACE PROCEDURE proc3 (
  v_ID    IN  INT,
  v_Name  OUT VARCHAR2
) IS
  varName stuInfo.stuName%TYPE;
BEGIN
  SELECT stuName INTO varName FROM stuInfo WHERE stuID = v_ID;
  v_Name := varName;
EXCEPTION
  WHEN NO_DATA_FOUND THEN v_Name := 'NOT FOUND';
END;
/

4. 套件封裝：REF CURSOR 回傳結果集 (企業級標準)

Package Spec

CREATE OR REPLACE PACKAGE PKG_STUINFO IS
  TYPE stuInfoCursorType IS REF CURSOR;  -- 弱型游標

  -- 全表查詢
  PROCEDURE getStuInfo (stuInfoCursor OUT stuInfoCursorType);

  -- 依編號查詢 (0 表全表)
  PROCEDURE getStuInfo (v_ID IN INT, stuInfoCursor OUT stuInfoCursorType);

  -- 依姓名模糊查詢 (空白表全表)
  PROCEDURE getStuInfo (v_Name IN VARCHAR2, stuInfoCursor OUT stuInfoCursorType);
END PKG_STUINFO;
/

Package Body

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;

  -- 依編號
  PROCEDURE getStuInfo (v_ID IN 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;

  -- 依姓名模糊
  PROCEDURE getStuInfo (v_Name IN VARCHAR2, stuInfoCursor OUT stuInfoCursorType) IS
    var_cursor stuInfoCursorType;
  BEGIN
    IF 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;
/

呼叫範例

-- SQL*Plus
VARIABLE rc REFCURSOR;
EXEC PKG_STUINFO.getStuInfo(:rc);
PRINT rc;

EXEC PKG_STUINFO.getStuInfo(1, :rc);
PRINT rc;

EXEC PKG_STUINFO.getStuInfo('li', :rc);
PRINT rc;

-- Java JDBC
CallableStatement cs = conn.prepareCall("{call PKG_STUINFO.getStuInfo(?, ?)}");
cs.setInt(1, 1);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);

關鍵最佳實務

• 套件多載：同名程序不同參數清單，呼叫端更直觀。
• %TYPE 錨定型別：varName stuInfo.stuName%TYPE，DDL 變更自動適應。
• 例外處理：NO_DATA_FOUND 回傳預設值，避免未處理例外中斷呼叫端。
• COMMIT 策略：查詢類不提交；DML 類由呼叫端控制交易邊界 (更靈活)。
• 游標關閉：呼叫端遍歷完畢 **必須關閉** rs.close() / CLOSE cursor，否則洩漏。

擴展：強型游標 (Strong Ref Cursor)

TYPE strong_cur IS REF CURSOR RETURN stuInfo%ROWTYPE;  -- 編譯期檢查欄位
PROCEDURE getStuInfoStrong (p_rc OUT strong_cur);

強型游標可在編譯期發現欄位不符，適合核心穩定查詢；弱型適合動態/通用查詢。

這套「建表 → 參數模式 → REF CURSOR → 套件多載」代碼骨架，涵蓋 90% 企業級 PL/SQL 介面設計需求，建立專案範本庫即可高效複用。