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

Oracle 存儲過程進階：參數模式、動態遊標、Package 封裝與多條件查詢實戰

編輯：Oracle 資料庫基礎

從基礎到進階：參數模式與數據類型錨定

1. IN / OUT / IN OUT 參數模式

-- 僅輸入
CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
  INSERT INTO stu_info VALUES (1, 'liheng');
END;

-- 輸入參數
CREATE OR REPLACE PROCEDURE proc2 (
  v_id   IN INT,
  v_name IN VARCHAR2
) IS
BEGIN
  INSERT INTO stu_info VALUES (v_id, v_name);
  COMMIT;
END;

-- 輸出參數：使用 %TYPE 錚定表列類型，避免硬編碼
CREATE OR REPLACE PROCEDURE proc3 (
  v_id   IN  INT,
  v_name OUT VARCHAR2
) IS
  v_var_name stu_info.stu_name%TYPE;
BEGIN
  SELECT stu_name INTO v_var_name FROM stu_info WHERE stu_id = v_id;
  v_name := v_var_name;
END;

%TYPE：錚定列類型，DDL 變更自動適配。
SELECT ... INTO：單行查詢，需處理 NO_DATA_FOUND、TOO_MANY_ROWS。

2. 返回結果集：SYS_REFCURSOR + Package 封裝

2.1 返回所有記錄

CREATE OR REPLACE PACKAGE pkg_stuinfo IS
  TYPE stu_cur_type IS REF CURSOR;
  PROCEDURE get_stu_info (p_cur OUT stu_cur_type);
END pkg_stuinfo;
/

CREATE OR REPLACE PACKAGE BODY pkg_stuinfo IS
  PROCEDURE get_stu_info (p_cur OUT stu_cur_type) IS
    v_cur stu_cur_type;
  BEGIN
    OPEN v_cur FOR SELECT * FROM stu_info;
    p_cur := v_cur;
  END;
END pkg_stuinfo;
/

2.2 按 ID 查詢（ID=0 表示查所有）

CREATE OR REPLACE PACKAGE pkg_stuinfo IS
  TYPE stu_cur_type IS REF CURSOR;
  PROCEDURE get_stu_info (p_id IN INT, p_cur OUT stu_cur_type);
END pkg_stuinfo;
/

CREATE OR REPLACE PACKAGE BODY pkg_stuinfo IS
  PROCEDURE get_stu_info (p_id IN INT, p_cur OUT stu_cur_type) IS
    v_cur stu_cur_type;
  BEGIN
    IF p_id = 0 THEN
      OPEN v_cur FOR SELECT * FROM stu_info;
    ELSE
      OPEN v_cur FOR SELECT * FROM stu_info WHERE stu_id = p_id;
    END IF;
    p_cur := v_cur;
  END;
END pkg_stuinfo;
/

2.3 按姓名模糊查詢（空串查所有）

CREATE OR REPLACE PACKAGE pkg_stuinfo IS
  TYPE stu_cur_type IS REF CURSOR;
  PROCEDURE get_stu_info (p_name IN VARCHAR2, p_cur OUT stu_cur_type);
END pkg_stuinfo;
/

CREATE OR REPLACE PACKAGE BODY pkg_stuinfo IS
  PROCEDURE get_stu_info (p_name IN VARCHAR2, p_cur OUT stu_cur_type) IS
    v_cur stu_cur_type;
  BEGIN
    IF p_name IS NULL OR p_name = ' ' THEN
      OPEN v_cur FOR SELECT * FROM stu_info;
    ELSE
      OPEN v_cur FOR SELECT * FROM stu_info WHERE stu_name LIKE '%' || p_name || '%';
    END IF;
    p_cur := v_cur;
  END;
END pkg_stuinfo;
/

3. 調用示例

-- 匿名塊調用
DECLARE
  c SYS_REFCURSOR;
  v_id   stu_info.stu_id%TYPE;
  v_name stu_info.stu_name%TYPE;
BEGIN
  pkg_stuinfo.get_stu_info('li', c);
  LOOP
    FETCH c INTO v_id, v_name;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
  END LOOP;
  CLOSE c;
END;
/

-- Java/JDBC 調用（概念）
-- CallableStatement cs = conn.prepareCall("{call pkg_stuinfo.get_stu_info(?, ?)}");
-- cs.setString(1, "li");
-- cs.registerOutParameter(2, OracleTypes.CURSOR);
-- cs.execute();
-- ResultSet rs = (ResultSet) cs.getObject(2);

4. 核心最佳實踐總結

維度	建議
參數類型	優先使用 `%TYPE` / `%ROWTYPE` 錚定，減少硬編碼維護成本
結果集返回	統一使用 `SYS_REFCURSOR`（弱類型遊標），Package 定義強類型 `REF CURSOR` 供內部使用
動態查詢條件	在 PL/SQL 中構建動態 SQL（`OPEN cur FOR 'SELECT ...' USING ...`）或條件分支 `IF/ELSE` 開啟不同遊標
異常處理	`SELECT INTO` 必須包含 `EXCEPTION WHEN NO_DATA_FOUND THEN ... WHEN TOO_MANY_ROWS THEN ...`
命名規範	參數名不可與表列名相同（如 `p_id` vs `id`），否則引用歧義導致邏輯錯誤
事務控制	Package 過程不 `COMMIT`，由調用方（Service 層）統一管理事務邊界