Oracle 數據庫存儲圖片兩大方案：BFILE+DBMS_LOB 與 SQL*Loader 批量導入

方案對比概覽

方案一：PL/SQL + BFILE + DBMS_LOB（單文件/動態上傳）

前置準備

-- 1. 授權創建目錄對象權限
GRANT CREATE ANY DIRECTORY TO app_user;
GRANT CREATE ANY LIBRARY TO app_user;  -- 部分版本需要

-- 2. 創建目錄對象映射 OS 路徑
CREATE OR REPLACE DIRECTORY photo_dir AS 'E:\photo';

-- 3. 目標表
CREATE TABLE photos (blob_column BLOB);

核心 PL/SQL 塊

DECLARE
  l_blob  BLOB;
  l_bfile BFILE := BFILENAME('PHOTO_DIR', 'flower.jpg');
BEGIN
  -- 1. 插入空 BLOB 並獲取 LOB 定位器
  INSERT INTO photos VALUES (EMPTY_BLOB())
  RETURNING blob_column INTO l_blob;

  -- 2. 打開 BFILE
  DBMS_LOB.FILEOPEN(l_bfile, DBMS_LOB.FILE_READONLY);

  -- 3. 從文件載入到 BLOB（自動處理分塊）
  DBMS_LOB.LOADFROMFILE(
    dest_lob    => l_blob,
    src_lob     => l_bfile,
    amount      => DBMS_LOB.GETLENGTH(l_bfile)  -- 全量
  );

  -- 4. 關閉 BFILE
  DBMS_LOB.FILECLOSE(l_bfile);

  COMMIT;
END;
/

-- 驗證
SELECT DBMS_LOB.GETLENGTH(blob_column) FROM photos;  -- 1212681

關鍵點提示

• `BFILENAME` 只建立邏輯指針，**不驗證文件是否存在**；`FILEOPEN` 時才會報錯。
• 目錄對象權限：`GRANT READ ON DIRECTORY photo_dir TO app_user;`
• 大文件（>2GB）需使用 `DBMS_LOB.LOADBLOBFROMFILE` 分塊讀寫，避免 PGA 爆滿。
• Oracle 12c+ 可用 `DBMS_LOB.COPY_DBFS_LINK` 等新特性對接 DBFS/Cloud Storage。

方案二：SQL*Loader LOBFILE 批量導入（海量遷移）

控制文件

LOAD DATA
INFILE 'sample.dat'
APPEND
INTO TABLE test
FIELDS TERMINATED BY ','
(
  product_id   INTEGER EXTERNAL(6),
  file_name    FILLER CHAR(30),          -- 不載入表列，僅供 LOBFILE 引用
  ad_graphic   LOBFILE(file_name) TERMINATED BY EOF
)

數據文件

1,1.jpg,
2,2.jpg,
3,3.jpg,
4,4.jpg,
5,5.jpg,

執行命令

sqlldr userid=app_user/pwd control=test.ctl log=test.log direct=true

• `direct=true`：直接路徑載入，高性能，需表無觸發器、無外鍵、無聚簇索引。
• `TERMINATED BY EOF`：讀取文件至末尾，適用二進制圖片。
• 文件路徑：`file_name` 可為相對路徑（相對控制文件目錄）或絕對路徑。

進階選項：SecureFiles LOB (11g+)

CREATE TABLE photos (
  id          NUMBER PRIMARY KEY,
  img         BLOB
) LOB (img) STORE AS SECUREFILE (
  ENABLE STORAGE IN ROW,      -- 小 LOB 存行內
  COMPRESS HIGH,              -- 透明壓縮
  DEDUPLICATE,                -- 重複刪除
  ENCRYPT USING 'AES256'      -- 透明加密
);

SecureFiles 同時提供**更高讀寫性能、壓縮、去重、加密**，是現代 Oracle 存儲非結構化數據的首選。

總結選型

• **應用上傳、單文件、需事務** → PL/SQL + DBMS_LOB。
• **數據遷移、初始化、定期批量、TB 級** → SQL*Loader 直接路徑 + LOBFILE。
• **新建系統** → 必用 SecureFiles LOB，享受壓縮/去重/加密紅利。