Oracle PL/SQL 呼叫 Java 存儲過程完整教學

架構概覽

Oracle 資料庫內建 JVM (Aurora)，允許將 Java 類別載入資料庫，並透過 Callable Specification (Call Spec) 發佈為 PL/SQL 可呼叫的存儲過程/函數。流程三步驟：

1. 建立並編譯 Java Source (LoadJava / CREATE JAVA SOURCE)。
2. 建立 PL/SQL Wrapper (Stored Procedure/Function) 映射 Java 靜態方法。
3. 在 PL/SQL 中呼叫 Wrapper。

步驟 1：建立 Java Source

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "TransMetaData" AS
import java.io.*;
import java.sql.*;
import java.util.Properties;
import oracle.jdbc.driver.OracleDriver;

public class TransMetaData {
    public static void donow(String str)
        throws FileNotFoundException, IOException, SQLException, UnsupportedEncodingException {
        str = "_" + str;
        Properties props = new Properties();
        props.load(new FileInputStream("c:/tables.properties"));

        String srcUrl = props.getProperty("DATABAST_SOURCE_URL" + str);
        String srcUser = props.getProperty("DATABAST_SOURCE_USERNAME" + str);
        String srcPwd = props.getProperty("DATABAST_SOURCE_PASSWORD" + str);
        String tgtUrl = props.getProperty("DATABAST_TARGET_URL" + str);
        String tgtUser = props.getProperty("DATABAST_TARGET_USERNAME" + str);
        String tgtPwd = props.getProperty("DATABAST_TARGET_PASSWORD" + str);
        String srcTable = props.getProperty("SQL_SOURCE_TABLENAME" + str);
        String tgtTable = props.getProperty("SQL_TARGET_TABLENAME" + str);
        String fields = props.getProperty("SQL_TABLE_FIELD" + str);

        Connection srcConn = null, tgtConn = null;
        DriverManager.registerDriver(new OracleDriver());
        srcConn = DriverManager.getConnection(srcUrl, srcUser, srcPwd);
        tgtConn = DriverManager.getConnection(tgtUrl, tgtUser, tgtPwd);

        if (!srcConn.isClosed() && !tgtConn.isClosed()) {
            System.out.println("**源與目的資料庫連線成功**");
            srcConn.setAutoCommit(false);
            tgtConn.setAutoCommit(false);
        } else {
            throw new Exception("資料庫連線失敗，請檢查！");
        }

        PreparedStatement pstmt = srcConn.prepareStatement("SELECT " + fields + " FROM " + srcTable);
        ResultSet rs = pstmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int cols = rsmd.getColumnCount();
        String[] data = new String[cols];
        String placeholders = "";

        PreparedStatement delStmt = tgtConn.prepareStatement("DELETE FROM " + tgtTable);
        delStmt.executeUpdate();
        delStmt.close();

        while (rs.next()) {
            placeholders = "";
            for (int i = 0; i < cols; i++) {
                String val = rs.getString(i + 1);
                data[i] = (val != null) ? new String(val.trim().getBytes("ISO-8859-1"), "GBK") : "";
                placeholders += (i != cols - 1) ? "?," : "?";
            }
            String insSql = "INSERT INTO " + tgtTable + " (" + fields + ") VALUES (" + placeholders + ")";
            PreparedStatement insStmt = tgtConn.prepareStatement(insSql);
            for (int i = 0; i < cols; i++) insStmt.setString(i + 1, data[i]);
            insStmt.executeUpdate();
            insStmt.close();
        }

        srcConn.commit();
        tgtConn.commit();
        System.out.println("**資料遷移完成**");
    }
}
/

限制：方法必須為 static；不寫套件名；參數/回傳型別需與 PL/SQL 映射對應。

步驟 2：建立 PL/SQL Wrapper

無參數、無回傳

CREATE OR REPLACE PROCEDURE TransMetaData
AS LANGUAGE JAVA
NAME 'TransMetaData.donow()';
/

帶參數

CREATE OR REPLACE PROCEDURE TransMetaData(p_str IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'TransMetaData.donow(java.lang.String)';
/

帶回傳值

CREATE OR REPLACE FUNCTION TransMetaData(p_str IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'TransMetaData.donow(java.lang.String) return double';
/

步驟 3：呼叫執行

-- 無參數
EXEC TransMetaData;

-- 帶參數
EXEC TransMetaData('1');

-- 函數呼叫
DECLARE v_result NUMBER;
BEGIN
    v_result := TransMetaData('1');
    DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

簡易範例：取得隨機數

CREATE OR REPLACE JAVA SOURCE NAMED JS_Rand AS
public class clsRand {
    public static double Rand() { return java.lang.Math.random(); }
}
/

CREATE OR REPLACE FUNCTION rand RETURN NUMBER
AS LANGUAGE JAVA
NAME 'clsRand.Rand() return double';
/

BEGIN
    DBMS_OUTPUT.PUT_LINE(rand);
END;
/

常見坑點與提示

• Java 程式碼中的檔案路徑 (如 c:/tables.properties) 是資料庫伺服器端路徑，非客戶端。
• 編碼轉換 (ISO-8859-1 → GBK) 需視實際字元集調整。
• 資料庫 JVM 權限：需 GRANT JAVASYSPRIV、檔案讀寫權限 (SYS.DBMS_JAVA.GRANT_PERMISSION)。
• 除錯：System.out.println 輸出導向資料庫 Trace 檔，可用 DBMS_JAVA.SET_OUTPUT 導向 PL/SQL 緩衝區。

結語

PL/SQL 呼叫 Java 能將複雜邏輯（檔案處理、網路通訊、加密演算等）移至 Java 實作，再透過資料庫原生介面整合，發揮兩者優勢。