Oracle CLOB 欄位完整處理指南：INSERT/UPDATE/SELECT 最佳實踐、DISTINCT 限制與繞過方案、JDBC 事務邊界控制

為何用 CLOB

VARCHAR2 僅 4000 位元組；CLOB 支援至 8GB，適合長文本存儲。

核心難點：特殊字元與 JDBC 流式處理

直接 SQL 寫入不可行：腳本長度限制、換行/引號等特殊字元處理麻煩。

標準 INSERT 流程（三步驟）

1. 插入空 CLOB：INSERT INTO T_TOPIC(TOPIC_ID, TOPIC_CONTENT) VALUES('0000001', EMPTY_CLOB());
2. SELECT ... FOR UPDATE 鎖定行取得可寫入的 LOB 定位器：

   ResultSet rs = pstmt.executeQuery();
   if (rs.next()) {
     oracle.sql.CLOB lob = (CLOB) rs.getClob(1);
     Writer os = lob.getCharacterOutputStream();
     os.write(content);
     os.flush();
   }

3. 必須在同一事務中完成：conn.setAutoCommit(false);

UPDATE 流程

同樣採 SELECT ... FOR UPDATE + 事務邊界控制。

SELECT 讀取流程

CLOB clob = (CLOB) rs.getClob("FLD_CONTENT");
Reader reader = clob.getCharacterStream();
StringBuffer sb = new StringBuffer();
char[] buf = new char[1024];
for (int len = reader.read(buf); len > 0; len = reader.read(buf)) {
  sb.append(buf, 0, len);
}

DISTINCT 限制與兩種繞過方案

含 CLOB 的表不可直接使用 DISTINCT（CLOB 無法 LIKE 比較，導致去重失敗）。

1. SQL 中轉 VARCHAR2 再 DISTINCT（受 4000 位元組限制）
2. 改寫查詢：先查無 CLOB 欄位集合，外層用 EXISTS 或 IN 過濾

完整 JDBC 更新封裝方法

public boolean updateClob(String sql, String col, String buf) {
  Connection conn = dp.getConnection();
  conn.setAutoCommit(false);
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(sql);
  if (rs.next()) {
    CLOB clob = (CLOB) rs.getClob(col);
    Method m = clob.getClass().getMethod("getCharacterOutputStream");
    Writer wr = (Writer) m.invoke(clob);
    BufferedWriter bw = new BufferedWriter(wr);
    bw.write(buf);
    bw.flush(); bw.close();
    conn.commit(); conn.close();
    return true;
  }
  // 例外處理回滾...
}