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

Oracle 儲存過程與自定義函數完整指南：建立、參數模式、Java 調用、游標回傳

編輯：Oracle 資料庫相關知識

PL/SQL 儲存過程與函數是資料庫端業務邏輯封裝的核心機制。本文系統講解建立語法、IN/OUT/IN OUT 參數、Java JDBC 調用、套件封裝游標回傳全棧實戰。

核心差異

特性	儲存過程	儲存函數
RETURN 子句	無	強制
回傳值	透過 OUT 參數	RETURN 單一值
SQL 中調用	不可	可（SELECT func() FROM dual）
多值回傳	多個 OUT 參數	需用集合/記錄型別

1. 無參儲存過程

CREATE OR REPLACE PROCEDURE say_hello_world AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('hello world');
END say_hello_world;

調用：EXEC say_hello_world; 或 BEGIN say_hello_world; END;

2. 帶參儲存過程（IN/OUT/IN OUT）

CREATE OR REPLACE PROCEDURE add_salary(
  p_staff_name IN xgj_test.username%TYPE
) AS
  v_old_sal xgj_test.sal%TYPE;
BEGIN
  SELECT sal INTO v_old_sal FROM xgj_test WHERE username = p_staff_name;
  UPDATE xgj_test SET sal = sal + 1000 WHERE username = p_staff_name;
  DBMS_OUTPUT.PUT_LINE('調整前薪資:' || v_old_sal || ' 調整後:' || (v_old_sal + 1000));
  -- 注意：未 COMMIT，由呼叫端控制交易
END add_salary;

調用：

BEGIN
  add_salary('xiao');
  add_salary('gong');
  COMMIT;
END;

3. 儲存函數

CREATE OR REPLACE FUNCTION query_income(
  p_staff_name IN VARCHAR2
) RETURN NUMBER AS
  v_sal xgj_test.sal%TYPE;
  v_comm xgj_test.comm%TYPE;
BEGIN
  SELECT sal, comm INTO v_sal, v_comm FROM xgj_test WHERE username = p_staff_name;
  RETURN v_sal * 12 + NVL(v_comm, 0);  -- 必須處理 NULL
END query_income;

調用：SELECT query_income('gong') FROM dual;

4. OUT 參數多值回傳

CREATE OR REPLACE PROCEDURE query_staff_info(
  p_name   IN  xgj_test.username%TYPE,
  p_sal    OUT NUMBER,
  p_comm   OUT xgj_test.comm%TYPE,
  p_job    OUT xgj_test.job%TYPE
) IS
BEGIN
  SELECT sal, comm, job INTO p_sal, p_comm, p_job
  FROM xgj_test WHERE username = p_name;
END;

5. 套件封裝游標回傳（解決多行多欄回傳）

Package Spec

CREATE OR REPLACE PACKAGE my_pkg IS
  TYPE staff_cursor IS REF CURSOR;
  PROCEDURE query_by_job(
    p_job        IN  xgj_test.job%TYPE,
    p_result_set OUT staff_cursor
  );
END my_pkg;

Package Body

CREATE OR REPLACE PACKAGE BODY my_pkg IS
  PROCEDURE query_by_job(
    p_job        IN  xgj_test.job%TYPE,
    p_result_set OUT staff_cursor
  ) AS
  BEGIN
    OPEN p_result_set FOR
      SELECT * FROM xgj_test WHERE job = p_job;
  END;
END my_pkg;

6. Java JDBC 調用完整範例

工具類

public class DBUtils {
  private static final String URL = "jdbc:oracle:thin:@ip:port:sid";
  private static final String USER = "xxx";
  private static final String PASS = "xxx";
  static { Class.forName("oracle.jdbc.driver.OracleDriver"); }
  public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(URL, USER, PASS);
  }
  public static void close(Connection c, Statement s, ResultSet rs) { ... }
}

調用儲存過程

CallableStatement cs = conn.prepareCall("{call query_staff_info(?,?,?,?)}");
cs.setString(1, "xiao");
cs.registerOutParameter(2, OracleTypes.NUMBER);
cs.registerOutParameter(3, OracleTypes.NUMBER);
cs.registerOutParameter(4, OracleTypes.VARCHAR);
cs.execute();
int sal = cs.getInt(2);
int comm = cs.getInt(3);
String job = cs.getString(4);

調用儲存函數

CallableStatement cs = conn.prepareCall("{?=call query_income(?)}");
cs.registerOutParameter(1, OracleTypes.NUMBER);
cs.setString(2, "gong");
cs.execute();
double income = cs.getDouble(1);

調用套件游標

CallableStatement cs = conn.prepareCall("{call my_pkg.query_by_job(?,?)}");
cs.setString(1, "Staff");
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = ((OracleCallableStatement)cs).getCursor(2);
while (rs.next()) {
  System.out.println(rs.getString("username") + "\t" + rs.getDouble("sal"));
}