使用 SQLJ 撰寫 Oracle 預存程序：從 PL/SQL 遷移的完整實戰指南

背景與動機

SQLJ (SQL Embedded in Java) 為 **靜態 SQL 嵌入 Java** 的標準 (ISO/ANSI)，提供：**編譯期 SQL 語法/型別檢查**、**效能優於動態 JDBC** (預先綁定存取路徑)、**程式碼可讀性優於 PL/SQL** (Java 生態、OOP、豐富函式庫)。Oracle 8i/9i/10g/11g/12c/19c/21c 均支援 SQLJ (資料庫內 JVM - Aurora/JServer)。

案例：DeleteClassification 批次刪除邏輯

業務需求：依期間刪除 Classification 子表中屬於該期間 ValidTransaction 父表的所有明細列 (百萬列級)，分批提交、統計刪除比例、超過閾值自動重建索引。

五步驟部署流程

步驟 1：撰寫 SQLJ 來源檔

// PipelineReset.sqlj
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import oracle.jdbc.driver.OracleConnection;
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ExecutionContext;

// 迭代器宣告 (對應 PL/SQL CURSOR)
#sql iterator StartEndDate (Timestamp startDate, Timestamp endDate);
#sql iterator ValidTransactions (Long transactionId);
#sql iterator ClassificationCount (long);

public class PipelineReset {
    public static void DeleteClassification(
        String periodName,
        int commitBatchSize,
        int percentageDeleted
    ) throws SQLException {
        // 1. 取得期間起迄
        StartEndDate dateIter;
        #sql dateIter = { SELECT startDate, endDate FROM Period WHERE description = :periodName };
        Timestamp startDate = null, endDate = null;
        if (dateIter.next()) { startDate = dateIter.startDate(); endDate = dateIter.endDate(); }
        dateIter.close();

        // 2. 取得該期間所有交易 ID
        ValidTransactions vtIter;
        #sql vtIter = {
            SELECT T.TransactionId
            FROM ValidTransaction T
            WHERE T.TransactionDate >= :startDate
              AND T.TransactionDate <= :endDate
              AND EXISTS (SELECT 1 FROM Classification C WHERE C.TransactionId = T.TransactionId)
        };
        ArrayList vtIdList = new ArrayList<>();
        while (vtIter.next()) vtIdList.add(vtIter.transactionId());
        vtIter.close();

        // 3. 批次刪除
        ExecutionContext ec = DefaultContext.getDefaultContext().getExecutionContext();
        ClassificationCount countIter;
        long vDeletedRows = 0, vTotalRows = 0;
        int vCount = 0;

        if (!vtIdList.isEmpty()) {
            #sql countIter = { SELECT COUNT(*) FROM Classification };
            #sql { FETCH :countIter INTO :vTotalRows };
            countIter.close();

            Iterator itr = vtIdList.iterator();
            while (itr.hasNext()) {
                vCount++;
                #sql [ec] { DELETE FROM Classification WHERE TransactionId = :(itr.next()) };
                vDeletedRows += ec.getUpdateCount();
                if (vCount % commitBatchSize == 0) {
                    #sql [ec] { COMMIT };
                }
            }
            #sql [ec] { COMMIT };
        }

        // 4. 判斷是否重建索引
        if ((((float) vDeletedRows / (float) vTotalRows) * 100) >= percentageDeleted) {
            #sql [ec] { CALL RebuildIndex('CLASSIFICATION_PK_IND', 'parallel nologging compute statistics') };
        }
    }
}

步驟 2：翻譯編譯 → 產生 .class + .ser (Profile)

# sqlj.properties (連線/選項集中管理)
sqlj.user=scott
sqlj.password=tiger
sqlj.driver=oracle.jdbc.driver.OracleDriver
sqlj.url=jdbc:oracle:oci8:@ORCL
sqlj.warn=verbose
sqlj.linemap=true

# 翻譯 + 編譯 + Profile 客製化
sqlj -props=sqlj.properties -explain -ser2class -status PipelineReset.sqlj
# 產生：PipelineReset.class, StartEndDate.class, ValidTransactions.class, ClassificationCount.class, PipelineReset_SJProfile*.class

步驟 3：打包 JAR → loadjava 載入資料庫

jar cvf0 PipelineReset.jar *.class
loadjava -user scott/tiger@ORCL -resolve PipelineReset.jar
# 或 Thin Driver: loadjava -thin -user scott/tiger@host:1521:ORCL -resolve PipelineReset.jar

驗證：SELECT object_name, status FROM user_objects WHERE object_type = 'JAVA CLASS' ORDER BY object_name; 全部 VALID 即成功。

步驟 4：建立 PL/SQL Wrapper (發布 Java 方法)

CREATE OR REPLACE PACKAGE PipelineResetFull AS
    PROCEDURE JDeleteClassification (
        periodName       IN VARCHAR2,
        commitBatchSize  IN NUMBER,
        percentDeleted   IN NUMBER
    );
END PipelineResetFull;
/

CREATE OR REPLACE PACKAGE BODY PipelineResetFull AS
    PROCEDURE JDeleteClassification (
        periodName       IN VARCHAR2,
        commitBatchSize  IN NUMBER,
        percentDeleted   IN NUMBER
    ) AS
    LANGUAGE JAVA
    NAME 'PipelineReset.DeleteClassification(java.lang.String, int, int)';  -- 必須完整簽名
    END JDeleteClassification;
END PipelineResetFull;
/

關鍵坑：`NAME` 子句**必須使用完整 Java 型別名稱** (`java.lang.String`)，不可寫 `String`，否則運行時拋 ORA-29531: no method ... in class ...。

步驟 5：測試呼叫

BEGIN
    PipelineResetFull.JDeleteClassification('January 1999', 10000, 10);
END;
/
-- 等同 PL/SQL 版本呼叫，完全整合，呼叫端無感差異

SQLJ vs PL/SQL vs JDBC 三方對比

遷移建議

• 新專案：優先考慮 **應用層 JDBC/JPA/Hibernate + PL/SQL 核心邏輯**，SQLJ 部署複雜度較高、社群較小。
• 既有 PL/SQL 重構**：將**計算密集、演算法複雜、需 Java 函式庫 (加密、解析、科學計算)** 的邏輯移植為 SQLJ，保留資料密集操作 (BULK COLLECT/FORALL) 於 PL/SQL。
• Oracle 12c/19c/21c**：支援 **Java 8/11** (JDK 版本依資料庫版本)，可使用 Lambda、Stream、新日期時間 API 豐富 SQLJ 邏輯。

結語

SQLJ 以 **編譯期安全、高效能、Java 生態** 三大優勢，成為 PL/SQL 之外的強力選項，特別適合**演算法複雜、需跨平台邏輯重用、團隊 Java 技能強** 的場景。掌握「翻譯→編譯→JAR→loadjava→Wrapper」五步驟，即可在 Oracle 資料庫內無縫運行 Java 邏輯，享受兩全其美的開發體驗。