Oracle 文本文件導入導出實戰：SQL*Loader 與 SPOOL 深度解析

導出篇：基於 SQL*Plus SPOOL 的文本生成

核心原理

利用 SQL*Plus 的報表格式化功能，通過設置列分隔符、頁面大小、抬頭抑制等參數，將查詢結果導向文本文件。需注意 SPOOL 輸出可能包含空行，後續需清理。

完整導出腳本範例

-- 測試表構建
CREATE TABLE TESTINOUT(ID NUMBER(10), USERNAME VARCHAR2(10), DATE1 DATE, MONEY NUMBER(6,2));
INSERT INTO TESTINOUT VALUES(1,'will',TO_DATE('29-12-2001','dd-mm-yyyy'),33.33);
INSERT INTO TESTINOUT VALUES(2,'ddxxkk',TO_DATE('29-12-2002','dd-mm-yyyy'),443.33);
INSERT INTO TESTINOUT VALUES(1234567890,'thisistest',TO_DATE('29-12-2002','dd-mm-yyyy'),4443.33);

-- 導出腳本 testinout.sql
SET COLSEP ''           -- 列分隔符設為空
SET TRIMSPOOL ON        -- 去除尾部空格
SET LINESIZE 120        -- 行寬
SET PAGESIZE 2000       -- 頁大小，防止分頁換行
SET NEWPAGE 1           -- 新頁控制
SET HEADING OFF         -- 隱藏列標題
SET TERM OFF            -- 關閉終端顯示
COLUMN ID FORMAT A10 TRUNCATE
COLUMN MONEY FORMAT A6 TRUNCATE
SPOOL d:\iii.lst
SELECT TO_CHAR(ID,'0000000009S') AS ID,
       USERNAME,
       TO_CHAR(DATE1,'yyyymmdd') AS DATE1,
       TO_CHAR(MONEY*100,'000000S') AS MONEY
FROM TESTINOUT;
SPOOL OFF
EXIT;

關鍵 SET 命令對照表

導入篇：SQL*Loader 高效載入

數據文件格式

• 定長記錄：每行長度固定，通過位置解析
• 變長記錄：分隔符分隔（如逗號、豎線）

系統自動跳過空行。

控制文件編寫範例

LOAD DATA
INFILE 'd:\iii.lst'
APPEND
INTO TABLE TESTINOUT
(
  ID       POSITION(01:10) INTEGER EXTERNAL,
  USERNAME POSITION(11:20) CHAR,
  DATE1    POSITION(21:28) DATE 'YYYYMMDD',
  MONEY    POSITION(29:34) ZONED(6,2)
)

執行與日誌分析

D:\>sqlldr user1/user1@my8i control='d:\in.ctl'

• LOG 文件：記錄載入統計、錯誤信息，可用 LOG= 指定路徑
• BAD 文件：存放格式錯誤的數據行，可用 BAD= 指定路徑
• DISCARD 文件：不滿足 WHEN 條件的數據（可選）

最佳實踐建議

1. 導出優先用 OCIULDR：大數據量場景下性能優於 SPOOL 數量級
2. 導入用 SQL*Loader 直接路徑：加 DIRECT=TRUE 繞過 SQL 引擎，直寫數據塊
3. 字符集一致性：導出導入端 NLS_LANG 必須一致，避免亂碼
4. 預建表結構：導入前建好目標表、索引、約束，用 APPEND 模式僅載入數據
5. 並行載入：大表可拆分多個數據文件 + 多個 SQL*Loader 會話並行