SQL*Loader(sqlldr)是 Oracle 官方提供的高速批量資料載入工具,支援多種平面檔案格式(CSV、固定寬度、定界符等)快速匯入資料庫。本文從基本操作、四種載入模式、控制檔寫法、日期/函數處理、LOB 匯入、換行符處理等面向全面解析。
CREATE TABLE ftest (id NUMBER, username VARCHAR2(20), password VARCHAR2(20), sj DATE);
1 f f 2010-8-19 2 f1 f1 2010-8-19 3 f2 f2 2010-8-19 4 f3 f3 2010-8-19 5 f4 f4 2010-8-19
LOAD DATA INFILE 'c:\data.txt' INSERT INTO TABLE ftest FIELDS TERMINATED BY " " (id, username, password, sj)
INSERT:目標表必須為空,否則報錯(預設模式)。APPEND:表有資料時追加。REPLACE / TRUNCATE:先清空表再載入(TRUNCATE 使用 TRUNCATE TABLE 指令,速度較快)。sqlldr userid/password control=c:\cont.ctl data=c:\data.txt
LOAD DATA INFILE * INTO TABLE dept FIELDS TERMINATED BY ',' (deptno, dname, loc) BEGINDATA 10,Sales,Virginia 20,Accounting,Virginia ...
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
支援雙引號包裹欄位,雙引號內可含逗號;雙引號本身用兩個雙引號跳脫。
(deptno POSITION(1:2), dname POSITION(3:16), loc POSITION(17:29))
亦可用相對位置 POSITION(*:16) 或長度 CHAR(14)。
last_updated DATE 'DD/MM/YYYY'
dname "UPPER(:dname)", loc "UPPER(:loc)"
REPLACE(:col, '\n', CHR(10))。INFILE 'file.dat' "FIX 80"(固定長度記錄)、"VAR 3"(前 3 位元組為長度)、"STR x'7C0D0A'"(自訂記錄分隔符)。LOAD DATA INFILE demo.dat "STR x'7C0D0A'" INTO TABLE dept REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (deptno, dname, loc, comments CHAR(1000000))
CHAR(1000000) 擴大預設 255 字元限制。
LOAD DATA INFILE * INTO TABLE lob_demo REPLACE (owner POSITION(17:25), time_stamp POSITION(44:55) DATE "Mon DD HH24:MI", filename POSITION(57:100), data LOBFILE(filename) TERMINATED BY EOF) BEGINDATA -rw-r--r-- 1 tkyte tkyte 1220342 jun 17 15:26 classes12.zip
LOAD DATA
INFILE *
INTO TABLE image_load REPLACE
FIELDS TERMINATED BY ','
(id, name, file_name FILLER,
image COLUMN OBJECT (
source COLUMN OBJECT (
localdata LOBFILE(file_name) TERMINATED BY EOF NULLIF file_name='none'
)
)
)
BEGINDATA
1,icons,icons.gif
CHARACTERSET 'ZHS16GBK' 或 'UTF8' 解決中文亂碼。TERMINATED BY WHITESPACE。SQL*Loader 功能強大,掌握控制檔語法(INFILE、FIELDS、POSITION、DATE、LOBFILE、COLUMN OBJECT 等)即可應對大多數批量匯入需求。實務上建議先以小量測試驗證控制檔,再正式大量載入。