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

Oracle 預存程序基本執行：PL/SQL 與 Java 呼叫範例

編輯：Oracle 資料庫基礎

前言

預存程序是資料庫端封裝業務邏輯的核心機制。本文以最簡單的「查詢學生分數」範例，示範 PL/SQL 區塊與 Java (JDBC CallableStatement) 兩種呼叫方式的完整語法與注意事項。

一、建立預存程序

CREATE OR REPLACE PROCEDURE pro_stu (
    in_name  IN  VARCHAR2,
    out_val  OUT VARCHAR2
) AS
    r_stu student%ROWTYPE;
BEGIN
    SELECT * INTO r_stu FROM student s WHERE s.name = in_name;
    out_val := r_stu.value;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        out_val := 'NOT_FOUND';
    WHEN TOO_MANY_ROWS THEN
        out_val := 'DUPLICATE';
END pro_stu;
/

改良點： 加入例外處理避免未捕捉例外導致呼叫端收到 ORA-06550。

二、PL/SQL 區塊呼叫 (SQL*Plus / SQL Developer)

SET SERVEROUTPUT ON
DECLARE
    v_name  VARCHAR2(20) := 'John';
    v_val   VARCHAR2(20);
BEGIN
    pro_stu(v_name, v_val);
    DBMS_OUTPUT.PUT_LINE('Return value: ' || v_val);
END;
/

或使用 EXEC / CALL (僅支援 IN/OUT 標量，不支援複雜型別)：

VARIABLE v_val VARCHAR2(20)
EXEC pro_stu('John', :v_val)
PRINT v_val

三、Java (JDBC) 呼叫

完整可執行範例

package com.example.jdbc;

import java.sql.*;

public class CallStoredProc {
    private static final String DRIVER = "oracle.jdbc.OracleDriver";
    private static final String URL    = "jdbc:oracle:thin:@localhost:1521:orcl";
    private static final String USER   = "scott";
    private static final String PWD    = "tiger";

    public static void main(String[] args) {
        String sql = "{ call pro_stu(?, ?) }";  // JDBC Escape 語法
        
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD);
             CallableStatement cs = conn.prepareCall(sql)) {
            
            Class.forName(DRIVER);
            
            // 1. 設定 IN 參數
            cs.setString(1, "John");
            
            // 2. 註冊 OUT 參數型別 (索引對應預存程序參數順序)
            cs.registerOutParameter(2, Types.VARCHAR);
            
            // 3. 執行
            cs.execute();
            
            // 4. 取得 OUT 參數值
            String result = cs.getString(2);
            System.out.println("返回結果：" + result);
            
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

四、關鍵細節總結

項目	說明
JDBC Escape 語法	`{ call proc_name(?, ?) }` 大括號、call 關鍵字、參數佔位符 ? 均為標準寫法
參數索引	從 1 開始，對應預存程序宣告順序 (非 SQL 呼叫順序)
OUT 參數註冊	必須在 `execute()` 前呼叫 `registerOutParameter(index, sqlType)`
型別對應	Oracle VARCHAR2 → `Types.VARCHAR`，NUMBER → `Types.NUMERIC/INTEGER`，DATE → `Types.TIMESTAMP`，REF CURSOR → `OracleTypes.CURSOR`
取值方法	`getString()`, `getInt()`, `getDate()`, `getObject()` (需轉型)
資源管理	使用 try-with-resources 自動關閉 Connection/CallableStatement

五、常見錯誤與排查

ORA-06550: PLS-00306：參數數量/型別不符 → 檢查 Java 端 setXxx/registerOutParameter 數量與 PL/SQL 宣告一致。
ORA-01403: no data found：PL/SQL 內 SELECT INTO 無資料 → 在預存程序加入 WHEN NO_DATA_FOUND 例外處理，設定預設 OUT 值。
java.sql.SQLException: Invalid column index：getString(2) 索引超出已註冊參數範圍 → 確認 registerOutParameter 索引正確。
驅動類別找不到：Classpath 缺少 ojdbc8.jar / ojdbc11.jar → Maven 引入 com.oracle.database.jdbc:ojdbc11。

六、進階：呼叫 Function (函數)

-- Oracle Function
CREATE OR REPLACE FUNCTION func_get_val (p_name VARCHAR2) RETURN VARCHAR2 AS
    v_val VARCHAR2(20);
BEGIN
    SELECT value INTO v_val FROM student WHERE name = p_name;
    RETURN v_val;
END;
/

-- Java 呼叫 Function (使用 ? = call ...)
String sql = "{ ? = call func_get_val(?) }";
try (CallableStatement cs = conn.prepareCall(sql)) {
    cs.registerOutParameter(1, Types.VARCHAR); // 回傳值佔位符索引為 1
    cs.setString(2, "John");
    cs.execute();
    String result = cs.getString(1);
}

建議：生產環境預存程序命名採用動詞前綴 (proc_, p_, sp_)，參數採用 in_/out_/inout_ 前綴，提升可讀性。所有呼叫端統一使用連線池與 try-with-resources 管理資源。