Java 呼叫 Oracle 預存程序完整實戰：從建立到 JDBC 整合

實作步驟概覽

1. 撰寫 Oracle 預存程序
2. 建立資料庫連線工具類
3. 編寫 Java 應用程式呼叫預存程序

1. Oracle 預存程序範例

建立測試表

create table test(
    id varchar2(32),
    name varchar2(32)
);

新增資料預存程序

CREATE OR REPLACE PROCEDURE insert_procedure(
    PARA1 IN VARCHAR2,
    PARA2 IN VARCHAR2
) AS
BEGIN
    INSERT INTO test (id, name) VALUES (PARA1, PARA2);
END insert_procedure;

查詢回傳游標預存程序

CREATE OR REPLACE PROCEDURE select_procedure(
    para_id IN VARCHAR2,
    name OUT sys_refcursor
) AS
BEGIN
    OPEN name FOR SELECT * FROM test WHERE id = para_id;
END;

重點：sys_refcursor 定義在 SYS.STANDARD 套件中，用於回傳結果集。

2. JDBC 連線工具類

public class DBUtil {
    public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    public static final String URL = "jdbc:oracle:thin:@localhost:1521/orcl";
    public static final String USERNAME = "pfm";
    public static final String PASSWORD = "pfm";

    static {
        try { Class.forName(DRIVER); }
        catch (ClassNotFoundException e) { e.printStackTrace(); }
    }

    public static Connection getConnection() {
        try { return DriverManager.getConnection(URL, USERNAME, PASSWORD); }
        catch (SQLException e) { e.printStackTrace(); return null; }
    }

    // 關閉資源的完整方法組：closeResultSet、closeStatement、closeConnection、closeAll
}

3. Java 呼叫預存程序

呼叫新增程序

public static void insert_call() {
    Connection conn = DBUtil.getConnection();
    CallableStatement proc = null;
    try {
        proc = conn.prepareCall("{ call insert_procedure(?,?) }");
        proc.setString(1, "1");
        proc.setString(2, "hello call");
        proc.execute();
    } catch (SQLException e) { e.printStackTrace(); }
    finally {
        try { proc.close(); DBUtil.closeAll(null, null, conn); }
        catch (Exception e) { e.printStackTrace(); }
    }
}

呼叫查詢程序（回傳游標）

public static void select_call() {
    Connection conn = DBUtil.getConnection();
    CallableStatement stmt = null;
    try {
        stmt = conn.prepareCall("{ call select_procedure(?, ?) }");
        stmt.setString(1, "1");
        stmt.registerOutParameter(2, OracleTypes.CURSOR);
        stmt.execute();
        ResultSet rs = (ResultSet) stmt.getObject(2);
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    } catch (SQLException e) { e.printStackTrace(); }
    finally { DBUtil.closeConnection(conn); }
}

關鍵點總結

• 使用 CallableStatement 執行預存程序
• 輸入參數用 setXxx()，輸出參數用 registerOutParameter() 指定型別
• 游標類型需引用 oracle.jdbc.driver.OracleTypes.CURSOR
• 務必在 finally 區塊關閉所有資源，避免洩漏