Oracle 動態交叉表生成：從靜態 SQL 到 Java 動態構建完整方案

業務背景：範式化數據的報表呈現困境

第三範式 (3NF) 設計消除冗餘，但產生「垂直表」：學生-學期-科目-成績 四欄。報表需「橫向展示」：每科一列。動態交叉表 解決「列不固定、隨數據變化」的核心需求。

靜態 SQL 實現 (已知科目固定)

SELECT jbxx.xh, jbxx.xm,
       (SELECT cj FROM cj WHERE xh=jbxx.xh AND xq=1 AND km=1) AS km1,
       (SELECT cj FROM cj WHERE xh=jbxx.xh AND xq=1 AND km=2) AS km2,
       (SELECT cj FROM cj WHERE xh=jbxx.xh AND xq=1 AND km=3) AS km3
FROM jbxx
WHERE <班級條件>
ORDER BY jbxx.xh;

缺點：新增科目需改 SQL，違反開閉原則。

Java 動態構建完整方案 (JDBC + 動態 PIVOT)

import java.sql.*;
import java.util.*;

public class DynamicCrossTab {
    public static void main(String[] args) throws Exception {
        // 1. 連線配置
        String url = "jdbc:oracle:thin:@server:1521:orcl";
        String user = "scott", pass = "tiger";
        
        try (Connection conn = DriverManager.getConnection(url, user, pass);
             Statement stmt = conn.createStatement();
             Statement stmtCnt = conn.createStatement()) {
            
            // 2. 取得所有科目 (動態列來源)
            ResultSet rsKm = stmt.executeQuery("SELECT km, kmmc FROM km ORDER BY km");
            List kmList = new ArrayList<>();
            List kmNameList = new ArrayList<>();
            while (rsKm.next()) {
                kmList.add(rsKm.getString("km"));
                kmNameList.add(rsKm.getString("kmmc"));
            }
            rsKm.close();
            
            // 3. 可選：過濾無數據科目 (統計該班該科是否有成績)
            String classFilter = "AND banji='202401'";  // 實際參數化
            List validIdx = new ArrayList<>();
            StringBuilder sqlSelect = new StringBuilder("SELECT jbxx.xh, jbxx.xm");
            for (int i = 0; i < kmList.size(); i++) {
                String km = kmList.get(i);
                // 檢查是否有數據
                ResultSet rsCnt = stmtCnt.executeQuery(
                    "SELECT COUNT(*) FROM cj WHERE xq=1 AND km=" + km + classFilter);
                if (rsCnt.next() && rsCnt.getInt(1) > 0) {
                    validIdx.add(i);
                    sqlSelect.append(", (SELECT cj FROM cj WHERE xh=jbxx.xh AND xq=1 AND km=")
                             .append(km).append(") AS km_").append(km);
                }
                rsCnt.close();
            }
            
            // 4. 組裝最終 SQL
            String sql = sqlSelect.append(" FROM jbxx WHERE 1=1 ")
                                  .append(classFilter)
                                  .append(" ORDER BY jbxx.xh").toString();
            
            // 5. 執行並動態輸出
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData meta = rs.getMetaData();
            int cols = meta.getColumnCount();
            
            // 列標題
            System.out.print("學號\t姓名");
            for (int idx : validIdx) System.out.print("\t" + kmNameList.get(idx));
            System.out.println();
            
            // 數據行
            while (rs.next()) {
                for (int i = 1; i <= cols; i++) {
                    System.out.print(rs.getString(i) + (i==cols?"\n":"\t"));
                }
            }
            System.out.println("數據列印完成！");
        }
    }
}

純 PL/SQL 動態游標方案 (無中間層)

CREATE OR REPLACE PROCEDURE gen_crosstab(
    p_class   IN VARCHAR2,
    p_cursor  OUT SYS_REFCURSOR
) AS
    v_sql   VARCHAR2(32767);
    v_cols  VARCHAR2(32767);
BEGIN
    -- 1. 動態收集有效科目列
    SELECT LISTAGG('(SELECT cj FROM cj WHERE xh=j.xh AND xq=1 AND km='||km||') AS km_'||km, ',')
           WITHIN GROUP (ORDER BY km)
    INTO v_cols
    FROM (
        SELECT DISTINCT km FROM cj 
        WHERE xq=1 AND banji=p_class  -- 僅有數據的科目
    );
    
    -- 2. 組裝動態 SQL
    v_sql := 'SELECT j.xh, j.xm, ' || v_cols || 
             ' FROM jbxx j WHERE j.banji = :cls ORDER BY j.xh';
    
    -- 3. 開啟游標 (綁定參數防注入)
    OPEN p_cursor FOR v_sql USING p_class;
END;

Oracle 11g+ 原生 PIVOT XML 動態列 (進階)

SELECT * FROM (
    SELECT xh, xm, km, cj FROM cj JOIN jbxx USING(xh) WHERE xq=1 AND banji='202401'
)
PIVOT XML (
    MAX(cj) FOR km IN (SELECT DISTINCT km FROM cj WHERE xq=1 ORDER BY km)
);

返回 XMLTYPE，需應用層解析 XML 序列化為表格，適合報表工具整合。

效能優化關鍵點

1. 索引覆蓋：CREATE INDEX idx_cj_xq_km_xh ON cj(xq, km, xh) INCLUDE (cj) 使子查詢/PIVOT 走 Index Fast Full Scan。
2. 物化視圖預計算：每日刷新 MV_CROSSTAB_DAILY，報表直接查 MV。
3. 結果快取：SELECT /*+ RESULT_CACHE */ ... 適用於低頻變更的歷史報表。
4. 分頁處理：大數據量必須加 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY (12c+) 或 ROWNUM 分頁。

架構選擇建議