萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle LOB 大物件完整實作:BFILE、BLOB、CLOB 存取、載入與最佳實踐

LOB(Large Object)支援高達 4GB-128TB 的非結構化資料儲存。Oracle 提供 BLOB(二進位)、CLOB(字元)、NCLOB(Unicode 字元)、BFILE(外部檔案)四種類型。本文以實作範例涵蓋目錄建立、檔案載入、讀寫、暫時 LOB 等完整生命週期。

BFILE:唯讀外部檔案參考

資料實際存放於作業系統檔案系統,資料庫僅存儲指標(Directory Object + 檔名)。

建立 Directory 物件

CREATE OR REPLACE DIRECTORY book_text AS '/u01/app/oracle/book_files';
GRANT READ ON DIRECTORY book_text TO app_user;

插入 BFILE 範例

CREATE OR REPLACE PROCEDURE insert_book(p_filename VARCHAR2) AS
  v_bfile  BFILE;
  v_exists BOOLEAN;
BEGIN
  v_bfile := BFILENAME('BOOK_TEXT', p_filename);
  v_exists := (DBMS_LOB.FILEEXISTS(v_bfile) = 1);

  IF v_exists THEN
    INSERT INTO my_book_files (id, file_ref)
    VALUES ((SELECT NVL(MAX(id),0)+1 FROM my_book_files), v_bfile);
    DBMS_OUTPUT.PUT_LINE('Inserted: ' || p_filename);
  ELSE
    DBMS_OUTPUT.PUT_LINE('File not found: ' || p_filename);
  END IF;
EXCEPTION
  WHEN DBMS_LOB.NOEXIST_DIRECTORY THEN DBMS_OUTPUT.PUT_LINE('Directory 不存在');
  WHEN DBMS_LOB.INVALID_DIRECTORY THEN DBMS_OUTPUT.PUT_LINE('Directory 無效');
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
-- 呼叫
EXEC insert_book('chapter01.pdf');

BLOB/CLOB:資料庫內部儲存

資料實際存放於資料庫區塊中,支援讀寫、索引、備份/恢復。

建立表與載入 BLOB(PDF 檔案)

CREATE TABLE pdf_table (id NUMBER PRIMARY KEY, pdf_content BLOB);

CREATE OR REPLACE PROCEDURE insert_pdf(p_filename VARCHAR2) AS
  v_bfile    BFILE;
  v_blob     BLOB;
  v_len      INTEGER;
  v_id       NUMBER;
BEGIN
  v_bfile := BFILENAME('PDF_DIR', p_filename);
  IF DBMS_LOB.FILEEXISTS(v_bfile) = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Not found: ' || p_filename); RETURN;
  END IF;

  v_len := DBMS_LOB.GETLENGTH(v_bfile);
  DBMS_OUTPUT.PUT_LINE('File size: ' || v_len);

  SELECT NVL(MAX(id),0)+1 INTO v_id FROM pdf_table;
  INSERT INTO pdf_table (id, pdf_content) VALUES (v_id, EMPTY_BLOB())
  RETURNING pdf_content INTO v_blob;

  DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.LOADFROMFILE(v_blob, v_bfile, v_len);
  DBMS_LOB.FILECLOSE(v_bfile);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); ROLLBACK;
END;
/
EXEC insert_pdf('manual.pdf');

CLOB 大量文字寫入(>32KB 需分塊)

DECLARE
  v_clob CLOB;
  v_text VARCHAR2(32767) := RPAD('大量文字...', 32000, 'x');
BEGIN
  INSERT INTO doc_table (id, content) VALUES (1, EMPTY_CLOB())
  RETURNING content INTO v_clob;

  -- 分塊寫入
  FOR i IN 1..10 LOOP
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_text), v_text);
  END LOOP;
  COMMIT;
END;

關鍵 DBMS_LOB API 速查

操作BLOBCLOBBFILE
開啟DBMS_LOB.OPEN(lob, DBMS_LOB.LOB_READWRITE)同左DBMS_LOB.FILEOPEN(bf, DBMS_LOB.FILE_READONLY)
讀取DBMS_LOB.READ(lob, amt, offset, buffer)同左同左
寫入DBMS_LOB.WRITE(lob, amt, offset, buffer)同左不支援
追加DBMS_LOB.WRITEAPPEND(lob, amt, buffer)同左不支援
從檔案載入DBMS_LOB.LOADFROMFILE(dest_lob, src_bf, amt)同左來源
比較DBMS_LOB.COMPARE(lob1, lob2, amt, off1, off2)同左同左
取得長度DBMS_LOB.GETLENGTH(lob)同左同左
關閉DBMS_LOB.CLOSE(lob)同左DBMS_LOB.FILECLOSE(bf)

最佳實踐

  1. 啟用 SecureFiles (11g+):STORE AS SECUREFILE,自動壓縮、去重、加密、智慧型快取。
  2. 大量載入用 SQL*Loader / Data Pump / External Table,避免 PL/SQL 迴圈。
  3. 暫時 LOB:DBMS_LOB.CREATETEMPORARY 用於中間運算,用畢 FREETEMPORARY 釋放。
  4. 索引:CTXSYS.CONTEXT 全文索引 CLOB,FUNCTION-BASED INDEX 針對 BLOB 元資料。
  5. 備份:RMAN 原生支援 LOB,SecureFiles 可重複資料刪除大幅縮減備份窗口。

掌握 LOB 完整生命週期操作,能安全高效管理企業級非結構化資料資產。

copyright © 萬盛學電腦網 all rights reserved