Oracle 存儲過程進階實戰：從基礎增刪改到 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');
END proc1;
/

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 proc2;
/

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;
END proc3;
/

4. 封裝包返回結果集：REF CURSOR 模式

規格部：

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 getStuInfo;
END PKG_STUINFO;
/

5. 條件查詢：按 ID 或返回全部

CREATE OR REPLACE PACKAGE BODY PKG_STUINFO IS
  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 getStuInfo;
END PKG_STUINFO;
/

6. 模糊查詢：按姓名 LIKE 匹配

CREATE OR REPLACE PACKAGE BODY PKG_STUINFO IS
  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 getStuInfo;
END PKG_STUINFO;
/

調用驗證

-- SQL*Plus / PL/SQL Developer
VAR rc REFCURSOR
EXEC PKG_STUINFO.getStuInfo(0, :rc);
PRINT rc

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

最佳實踐總結

• 使用 %TYPE 錨定類型，表結構變更自動適配。
• REF CURSOR 實現結果集跨邊界傳遞（Java/.NET/Python 皆可消費）。
• 包規格與實現分離，便於版本管理與依賴解耦。
• 動態 SQL 需求時改用 DBMS_SQL 或 OPEN ... FOR USING。