萬盛學電腦網

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

Oracle SPOOL 導出文本文件完整指南:參數優化、CSV 格式化與自動化腳本

SQL*Plus SPOOL 命令是將查詢結果導出為文本/CSV 文件的最輕量、無依賴方式。本文系統講解 SPOOL 核心參數、CSV 格式化最佳配置、大數據量導出效能優化、Windows/Linux 自動化腳本模板。

SPOOL 核心命令與參數

-- 基礎語法
SPOOL [file_name[.ext]] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT

-- 關鍵會話設置 (必須在 SPOOL 前設置)
SET NEWPAGE 0          -- 無分頁換頁符
SET LINESIZE 32767     -- 最大行寬 (防止換行截斷)
SET PAGESIZE 0         -- 抑制標題重複、分頁
SET HEADING OFF        -- 不輸出列標題 (或 ON 輸出一次)
SET FEEDBACK OFF       -- 不顯示 "N rows selected"
SET VERIFY OFF         -- 不顯示變量替換前後對比
SET TRIMSPOOL ON       -- 移除行尾空格 (關鍵!否則定寬列尾部大量空格)
SET TAB OFF            -- 不使用 TAB 分隔 (配合 COLSEP)
SET COLSEP ','         -- 列分隔符 (CSV 用逗號,TSV 用 TAB)
SET TERMOUT OFF        -- 關閉終端輸出 (大幅提速)
SET ECHO OFF           -- 不回顯命令
SET TIMING OFF         -- 不顯示計時
SET TRIMOUT ON         -- 移除輸出行尾空格
SET WRAP OFF           -- 不換行 (配合大 LINESIZE)

標準 CSV 導出腳本模板

-- export_employees_csv.sql
-- 使用方式: sqlplus -s user/pass@db @export_employees_csv.sql

SET NEWPAGE 0
SET LINESIZE 32767
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TRIMSPOOL ON
SET TAB OFF
SET COLSEP ','
SET TERMOUT OFF
SET ECHO OFF
SET TIMING OFF
SET TRIMOUT ON
SET WRAP OFF
SET NUMFORMAT 999999999999999.99  -- 數值格式,避免科學記數法

-- 動態生成文件名 (含日期時間)
COLUMN spool_file NEW_VALUE spool_file
SELECT 'employees_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.csv' AS spool_file FROM dual;

SPOOL &spool_file

-- 可選:輸出列標題 (手動構造)
-- SELECT 'EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,SALARY' FROM dual;

SELECT 
  employee_id,
  first_name,
  last_name,
  email,
  TO_CHAR(hire_date, 'YYYY-MM-DD') hire_date,
  salary
FROM employees
WHERE department_id = 80
ORDER BY employee_id;

SPOOL OFF
EXIT

大數據量導出效能優化

優化點設置/做法效果
關閉終端輸出SET TERMOUT OFF消除網絡/終端渲染瓶頸,提速 10-50x
Array Fetch SizeSET ARRAYSIZE 5000 (默認 15)減少網絡往返,每次獲取 5000 行
LOB 處理SET LONG 2000000000 SET LONGCHUNKSIZE 32767完整導出 CLOB/BLOB,避免截斷
並行導出分區表按分區並行 SPOOL 多文件,最後合併利用多 CPU 核心,線性加速
直接路徑不可直接用於 SPOOL,改用 UTL_FILE 或 DBMS_CLOUD繞過 SQL*Plus 格式化層,原始塊輸出

自動化部署腳本

Windows 批處理

@echo off
SET ORACLE_SID=ORCL
SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8
sqlplus -s user/pass@ORCL @export_employees_csv.sql
if %errorlevel% neq 0 (
  echo Export failed at %date% %time% >> export_error.log
  exit /b 1
)
echo Export completed: employees_%date:~-4,4%%date:~-10,2%%date:~-7,2%_*.csv

Linux Shell + Cron 定時任務

#!/bin/bash
# /opt/scripts/export_daily.sh
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=ORCL
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=$ORACLE_HOME/bin:$PATH

LOGFILE="/var/log/oracle_export_$(date +%Y%m%d).log"
SQLSCRIPT="/opt/scripts/export_employees_csv.sql"

sqlplus -s /nolog <> $LOGFILE 2>&1
CONNECT user/pass@ORCL
@$SQLSCRIPT
EXIT
EOF

if [ $? -eq 0 ]; then
  echo "$(date) Export SUCCESS" >> $LOGFILE
  # 可選:壓縮、傳輸至 S3/HDFS、發送通知
else
  echo "$(date) Export FAILED" >> $LOGFILE
  mailx -s "Oracle Export Failed" dba@company.com < $LOGFILE
fi

特殊場景處理

  • 含換行符的文本欄位:使用 REPLACE(col, CHR(10), ' ') 或 REPLACE(col, CHR(13)||CHR(10), ' ') 替換換行,或改用 JSON/XML 導出。
  • 超長行 (>32KB):SQL*Plus LINESIZE 最大 32767,超長需用 UTL_FILE.PUT_LINE 分塊寫入或 Python/cx_Oracle 導出。
  • 編碼問題:確保 NLS_LANG=.AL32UTF8,文件編碼為 UTF-8,Excel 打開需加 BOM 或用 Power Query 導入指定編碼。
  • 日期/時間格式統一:ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; 統一輸出格式。

替代方案對比

copyright © 萬盛學電腦網 all rights reserved
工具/方法優點缺點適用場景
SQL*Plus SPOOL零依賴、隨裝隨用、腳本化簡單格式化開銷大、無並行、LOB 受限中小表 (<100萬行)、臨時導出、自動化腳本
SQLcl (現代 CLI)支援 JSON/CSV/TSV 格式化、更快、更好編碼需額外安裝常規導出首選
Python cx_Oracle + csv完全可控、流式寫入、並行、類型轉換靈活需部署 Python 環境大數據量、ETL 流程、雲上傳
Data Pump (EXPDP)極快、並行、含元數據、跨版本二進制格式、需 IMPDP 還原、非人類可讀備份/遷移/同步、非文本交換
Oracle APEX / SQL DeveloperGUI 導出、多格式支援手動操作、難自動化一次性導出、業務用戶自助