Oracle 存儲過程讀寫檔案實戰：UTL_FILE 實現表與檔案雙向同步

前置作業：OS 權限與 DIRECTORY 註冊

-- OS 層面（Linux 範例）
mkdir -p /home/zxin10/file
chmod g+w /home/zxin10/file

-- 資料庫層面
CREATE OR REPLACE DIRECTORY bbb AS '/home/zxin10/file';
GRANT READ, WRITE ON DIRECTORY bbb TO app_user;

案例 1：表資料導出至檔案（逐列寫入）

CREATE OR REPLACE PROCEDURE v3_sub_fetch_test_2 (
  v_temp    IN  VARCHAR2,  -- 目錄路徑參數
  v_retvalue OUT NUMBER    -- 1 成功、0 失敗
) AS
  v_file_handle UTL_FILE.FILE_TYPE;
  v_filepath    VARCHAR2(500);
  v_filename    VARCHAR2(500);
  v_results     VARCHAR2(500);
  v_pid         VARCHAR2(1000);
  v_cname       VARCHAR2(500);
  v_sql         VARCHAR2(1000);
  c_cursor      SYS_REFCURSOR;
BEGIN
  v_filepath := NVL(v_temp, '/home/zxin10/file3');
  v_filename := 'free_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI') || '.all';

  v_sql := 'SELECT cpid, cpcnshortname FROM zxdbm_ismp.scp_basic';
  OPEN c_cursor FOR v_sql;
  v_file_handle := UTL_FILE.FOPEN(UPPER('bbb'), v_filename, 'A');  -- 目錄名需大寫

  LOOP
    FETCH c_cursor INTO v_pid, v_cname;
    EXIT WHEN c_cursor%NOTFOUND;
    v_results := v_pid || '|' || v_cname;
    UTL_FILE.PUT_LINE(v_file_handle, v_results);
  END LOOP;

  CLOSE c_cursor;
  UTL_FILE.FCLOSE(v_file_handle);
  v_retvalue := 1;
EXCEPTION WHEN OTHERS THEN
  v_retvalue := 0;
  IF UTL_FILE.IS_OPEN(v_file_handle) THEN UTL_FILE.FCLOSE(v_file_handle); END IF;
END;
/

案例 2：檔案資料導入表（解析分隔符）

檔案 zte.apsuic 格式：1|22|cheng（三欄，| 分隔）。

CREATE OR REPLACE PROCEDURE v3_sub_fetch_test_3 (
  v_temp    IN  VARCHAR2,
  v_retvalue OUT NUMBER
) AS
  v_file_handle UTL_FILE.FILE_TYPE;
  v_filepath    VARCHAR2(500);
  v_filename    VARCHAR2(500) := 'zte.apsuic';
  v_str         VARCHAR2(1000);
  v_id, v_age, v_name VARCHAR2(1000);
  v_pos1, v_pos2, v_idx NUMBER;
BEGIN
  v_filepath := NVL(v_temp, '/home/zxin10/file');
  v_file_handle := UTL_FILE.FOPEN(UPPER('bbb'), v_filename, 'R');

  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_file_handle, v_str);
    EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END;

    v_pos1 := 1; v_idx := 1;
    FOR i IN 1..3 LOOP
      v_pos2 := INSTR(v_str, '|', v_pos1);
      IF v_pos2 != 0 THEN
        CASE v_idx
          WHEN 1 THEN v_id   := SUBSTR(v_str, v_pos1, v_pos2 - v_pos1);
          WHEN 2 THEN v_age  := SUBSTR(v_str, v_pos1, v_pos2 - v_pos1);
          WHEN 3 THEN v_name := SUBSTR(v_str, v_pos1, v_pos2 - v_pos1);
        END CASE;
        v_pos1 := v_pos2 + 1;
      ELSE
        CASE v_idx
          WHEN 1 THEN v_id   := SUBSTR(v_str, v_pos1);
          WHEN 2 THEN v_age  := SUBSTR(v_str, v_pos1);
          WHEN 3 THEN v_name := SUBSTR(v_str, v_pos1);
        END CASE;
      END IF;
      v_idx := v_idx + 1;
    END LOOP;

    INSERT INTO zxdbm_ismp.loaddata VALUES (v_id, v_age, v_name);
  END LOOP;

  UTL_FILE.FCLOSE(v_file_handle);
  v_retvalue := 1;
EXCEPTION WHEN OTHERS THEN
  v_retvalue := 0;
  IF UTL_FILE.IS_OPEN(v_file_handle) THEN UTL_FILE.FCLOSE(v_file_handle); END IF;
END;
/

關鍵最佳實踐

• 目錄名大寫：UTL_FILE.FOPEN('BBB', ...)，否則 ORA-29280。
• 例外處理：務必在 EXCEPTION 區關閉檔案控制代碼，避免洩漏。
• 大量資料：建議使用 BULK COLLECT + FORALL 批次寫入，或改用外部表/ SQL*Loader。
• 編碼一致：資料庫字元集與檔案編碼需一致（如 AL32UTF8），避免亂碼。

結語

UTL_FILE 是 PL/SQL 與 OS 檔案橋樑，配合 DIRECTORY 物件實現安全、可控的讀寫。生產環境建議封裝為通用套件，統一錯誤碼、日誌記錄與重試機制。