Oracle數據導出TXT文件實戰：SQL*Plus SPOOL技術詳解

Oracle數據導出TXT文件實戰：SQL*Plus SPOOL技術詳解

使用SQL*Plus的SPOOL功能可高效將查詢結果導出為格式化文本文件。本文通過實例演示完整導出流程，涵蓋環境設置、腳本編寫、執行驗證。

核心思路

1. 編寫SQL腳本設置顯示格式
2. 使用SPOOL命令重定向輸出到文件
3. 執行查詢生成目標數據
4. 關閉SPOOL完成導出

完整導出腳本 (d:\czrk.sql)

SET ECHO OFF
SET FEEDBACK OFF
SET NEWPAGE NONE
SET PAGESIZE 50000
SET LINESIZE 20000
SET VERIFY OFF
SET PAGESIZE 0
SET TERM OFF
SET TRIMS ON
SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET TIMING OFF
SET VERIFY OFF
SET COLSEP '|'

SPOOL d:\czrk.txt

SELECT sfzh || ',' || xm || ',' || xb || ',' 
     || csrq || ',' || mz || ',' || xzqh || ',' || jzdz
FROM m_czrk
WHERE ROWNUM <= 10000;

SPOOL OFF

關鍵設置解析

執行步驟

-- 1. 連接數據庫
sqlplus dc/dc@mydb

-- 2. 執行腳本
SQL> @d:\czrk.sql

-- 3. 檢查生成文件
-- d:\czrk.txt 應包含10000行，每行格式：身份證,姓名,性別,出生日期,民族,行政區劃,戶籍地址

進階場景與優化

1. 大數據量分批導出

-- 使用游標循環分批，避免單次查詢內存壓力
DECLARE
  v_file UTL_FILE.FILE_TYPE;
  CURSOR c_data IS SELECT ...;
BEGIN
  v_file := UTL_FILE.FOPEN('EXPORT_DIR', 'data.txt', 'W');
  FOR r IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file, r.col1 || ',' || r.col2 || ...);
    IF MOD(c_data%ROWCOUNT, 10000) = 0 THEN
      UTL_FILE.FFLUSH(v_file);
    END IF;
  END LOOP;
  UTL_FILE.FCLOSE(v_file);
END;

2. CSV標準格式（處理逗號、引號轉義）

SELECT '"' || REPLACE(col1, '"', '""') || '","' || 
       REPLACE(col2, '"', '""') || '"' ...
FROM table;

3. 並行導出（多文件合併）

使用DBMS_PARALLEL_EXECUTE或手動分範圍啟動多會話並行SPOOL，最後合併文件。

常見問題排查

• 文件為空：檢查查詢條件、SPOOL路徑寫入權限
• 亂碼：確保NLS_LANG設置正確（如AMERICAN_AMERICA.ZHS16GBK）
• 行截斷：增大LINESIZE（最大32767）
• 性能慢：關閉TERM、FEEDBACK、ECHO；適度增大ARRAYSIZE

總結

SQL*Plus SPOOL是輕量級數據導出的利器。掌握核心SET命令組合、腳本化操作、編碼設置、大數據量分批策略，能滿足絕多數文本導出需求。生產環境建議封裝為標準化導出框架，支持參數化查詢、多格式輸出、日志審計。