Oracle Java Stored Procedure 完整開發指南：三種部署方式、JDBC 內部連線與 loadjava 實戰

Oracle Database 內建 **JVM (Aurora)**，支援在資料庫內直接運行 Java 程式碼，封裝為 **Java Stored Procedure (JSP)** 供 SQL/PL/SQL 呼叫。本文系統整理 **三種部署路徑**、**內部 JDBC 連線模式**、`loadjava` 工具鏈實戰與權限模型，助您打通 Java 生態與 Oracle 核心的最後一公里。

核心架構概念

• Java Source / Class / Resource：資料庫物件類型，對應 `.java`、`.class`、屬性檔/圖片等資源。
• Resolver：解析器，負責解析 Java 類別間的引用關係 (如 `import`、繼承)。
• Call Spec (呼叫規格)：PL/SQL 包裝器，定義 Java 方法對外暴露的介面 (參數、返回值)，映射 SQL/PL/SQL 呼叫約定。
• 內部 JDBC Driver (KPRB)：運行於資料庫 JVM 內部，**零網路開銷**、直接存取當前 Session 的事務上下文，連線字串固定為 jdbc:default:connection:。

三種部署方式完整實戰

方式一：直接嵌入 Java Source 碼 (適合簡單邏輯、快速原型)

-- 1. 編譯並載入 Java Source
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "JavaDemo1" AS
import java.sql.*;
public class JavaDemo1 {
  public static void main(String[] argv) {
    System.out.println("Hello, Java Stored Procedure!");
  }
}
/
-- 查詢編譯錯誤
SELECT * FROM USER_ERRORS WHERE TYPE = 'JAVA SOURCE' AND NAME = 'JavaDemo1';

-- 2. 建立 Call Spec (PL/SQL 包裝器)
CREATE OR REPLACE PROCEDURE javademo1 AS
  LANGUAGE JAVA NAME 'JavaDemo1.main(java.lang.String[])';
/
-- 3. 執行
SET SERVEROUTPUT ON SIZE 5000
CALL DBMS_JAVA.SET_OUTPUT(5000); -- 重導向 System.out 到 SQL*Plus
CALL javademo1();
-- 輸出: Hello, Java Stored Procedure!

方式二：載入外部 .class 檔案 (適合獨立編譯、版本控管)

-- 1. 建立 Directory Object 指向 .class 檔所在目錄 (需 DBA 權限)
CREATE OR REPLACE DIRECTORY java_classes AS 'D:\oracle\java_classes';
GRANT READ ON DIRECTORY java_classes TO scott;

-- 2. 使用 BFILE 載入 Class
CREATE OR REPLACE JAVA CLASS USING BFILE (java_classes, 'OracleJavaProc.class');
/

-- 3. 建立 Call Spec
CREATE OR REPLACE PROCEDURE test_javaproc AS
  LANGUAGE JAVA NAME 'OracleJavaProc.main(java.lang.String[])';
/

-- 4. 執行
SET SERVEROUTPUT ON SIZE 5000
CALL DBMS_JAVA.SET_OUTPUT(5000);
CALL test_javaproc();

方式三：loadjava 命令列工具 (企業級標準：支援 JAR、依賴解析、遠程部署)

# 1. 編寫完整 Java 類別 (含業務邏輯 + 內部 JDBC)
# OracleJavaProc.java
import java.sql.*;
import oracle.jdbc.*;
public class OracleJavaProc {
  public static void addSalGrade(int grade, int losal, int hisal) throws SQLException {
    System.out.println("Creating new salgrade for EMPLOYEE...");
    try (Connection conn = DriverManager.getConnection("jdbc:default:connection:");
         PreparedStatement pstmt = conn.prepareStatement(
           "INSERT INTO salgrade (GRADE, LOSAL, HISAL) VALUES (?, ?, ?)")) {
      pstmt.setInt(1, grade);
      pstmt.setInt(2, losal);
      pstmt.setInt(3, hisal);
      pstmt.executeUpdate();
    } catch (SQLException e) {
      System.err.println("ERROR! Adding Salgrade: " + e.getMessage());
      throw e;
    }
  }
}

# 2. 編譯
javac OracleJavaProc.java

# 3. 遠程載入、編譯、解析 (一條龍)
loadjava -u scott/tiger@//host:1521/orclpdb -v -resolve OracleJavaProc.class
# -v: 詳細輸出; -resolve: 立即解析依賴; -genmissing: 自動生成缺失類別存根

# 4. 驗證載入狀態
SELECT object_name, object_type, status 
FROM user_objects 
WHERE object_type LIKE 'JAVA%';
-- 輸出: JAVA SOURCE VALID, JAVA CLASS VALID

# 5. 建立 Call Spec
CREATE OR REPLACE PROCEDURE add_salgrade(
  p_grade NUMBER, p_losal NUMBER, p_hisal NUMBER
) AS LANGUAGE JAVA NAME 'OracleJavaProc.addSalGrade(int, int, int)';
/

# 6. 執行驗證
SET SERVEROUTPUT ON SIZE 2000
CALL DBMS_JAVA.SET_OUTPUT(2000);
EXEC add_salgrade(6, 10000, 15000);
SELECT * FROM salgrade WHERE grade = 6;
-- 輸出: GRADE=6, LOSAL=10000, HISAL=15000

關鍵技術深度解析

內部 JDBC 連線 (KPRB Driver)

• 連線字串： jdbc:default:connection: (固定，不可變)
• 特性：零網路延遲、共用當前 Session 事務 (無需/不可 COMMIT/ROLLBACK)、直接存取 PL/SQL 變數 (透過 `OracleCallableStatement`)。
• 限制：無法連接**其他**資料庫；DDL 會隱式提交當前事務 (慎用)。

Java 權限模型 (DBMS_JAVA.GRANT_PERMISSION)

-- 授予檔案系統讀寫權限
EXEC DBMS_JAVA.GRANT_PERMISSION('SCOTT', 'SYS:java.io.FilePermission', '/tmp/*', 'read,write');

-- 授予網路連線權限 (連外部 API)
EXEC DBMS_JAVA.GRANT_PERMISSION('SCOTT', 'SYS:java.net.SocketPermission', 'api.example.com:443', 'connect,resolve');

-- 查詢權限
SELECT * FROM USER_JAVA_POLICY WHERE GRANTEE = 'SCOTT';

原則：最小權限、審計追蹤、生產環境嚴格管控 `SYS:java.security.AllPermission`。

資料類型映射表 (Java ↔ SQL/PL/SQL)

進階實戰：回傳 REF CURSOR 給 PL/SQL

-- Java 端
public static void getEmpsByDept(int deptNo, java.sql.ResultSet[] rset) throws SQLException {
  try (Connection conn = DriverManager.getConnection("jdbc:default:connection:");
       PreparedStatement pstmt = conn.prepareStatement(
         "SELECT empno, ename, sal FROM emp WHERE deptno = ?")) {
    pstmt.setInt(1, deptNo);
    rset[0] = pstmt.executeQuery(); // 陣列包裝模擬 OUT 參數
  }
}

-- Call Spec (OUT 參數用 ResultSet[])
CREATE OR REPLACE PROCEDURE get_emps_by_dept(
  p_deptno IN NUMBER, p_cursor OUT SYS_REFCURSOR
) AS LANGUAGE JAVA NAME 'OracleJavaProc.getEmpsByDept(int, java.sql.ResultSet[])';
/

效能與避坑指南

總結：何時選擇 Java Stored Procedure？

1. **複雜演算/邏輯**：正則解析、加密/解密、圖形運算、機器學習推論 (載入 ONNX 模型)。
2. **外部系統整合**：HTTP/REST 呼叫、消息隊列發送、檔案系統操作 (需權限)。
3. **程式碼復用**：現有成熟 Java Library (Apache Commons, Jackson, POI 等) 直接部署，避免重寫 PL/SQL。
4. **跨平台邏輯一致性**：同一 Java 類別同時部署於應用層與資料庫層，保證計算結果絕對一致。

掌握 `loadjava` 部署流程、內部 JDBC 模式與權限模型，讓 Java 成為 Oracle 資料庫的**強力擴充引擎**，而非單純的外部應用語言。