萬盛學電腦網

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

Oracle SQL*Loader 完整使用指南:從入門到進階

Oracle SQL*Loader 完整使用指南:從入門到進階

SQL*Loader(sqlldr)是 Oracle 官方提供的高速批量資料載入工具,支援多種平面檔案格式(CSV、固定寬度、定界符等)快速匯入資料庫。本文從基本操作、四種載入模式、控制檔寫法、日期/函數處理、LOB 匯入、換行符處理等面向全面解析。

一、最簡單的入門範例

1. 目標資料表

CREATE TABLE ftest (id NUMBER, username VARCHAR2(20), password VARCHAR2(20), sj DATE);

2. 資料檔 data.txt

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

3. 控制檔 cont.ctl

LOAD DATA
INFILE 'c:\data.txt'
INSERT INTO TABLE ftest
FIELDS TERMINATED BY " "
(id, username, password, sj)
  • INSERT:目標表必須為空,否則報錯(預設模式)。
  • APPEND:表有資料時追加。
  • REPLACE / TRUNCATE:先清空表再載入(TRUNCATE 使用 TRUNCATE TABLE 指令,速度較快)。

4. 執行指令

sqlldr userid/password control=c:\cont.ctl data=c:\data.txt

二、控制檔進階寫法

1. 內建資料

LOAD DATA
INFILE *
INTO TABLE dept
FIELDS TERMINATED BY ','
(deptno, dname, loc)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
...

2. 定界檔案(CSV)

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

支援雙引號包裹欄位,雙引號內可含逗號;雙引號本身用兩個雙引號跳脫。

3. 固定寬度(POSITION)

(deptno POSITION(1:2), dname POSITION(3:16), loc POSITION(17:29))

亦可用相對位置 POSITION(*:16) 或長度 CHAR(14)。

4. 日期格式

last_updated DATE 'DD/MM/YYYY'

5. 函數轉換

dname "UPPER(:dname)", loc "UPPER(:loc)"

6. 內嵌換行符處理

  • 以特殊標記代替換行,載入時 REPLACE(:col, '\n', CHR(10))。
  • 或使用 INFILE 'file.dat' "FIX 80"(固定長度記錄)、"VAR 3"(前 3 位元組為長度)、"STR x'7C0D0A'"(自訂記錄分隔符)。

三、LOB 大物件載入

1. 內聯 LOB(資料與結構混在同一檔)

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 字元限制。

2. 外部 LOBFILE

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

3. 物件型別(如 ORDIMAGE)

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

四、常見 FAQ

  • TRAILING NULLCOLS:缺少尾欄位時自動補 NULL,避免「欄位不足」錯誤。
  • CASE 條件轉換:針對多種日期格式動態轉換。
  • 字元集:控制檔加 CHARACTERSET 'ZHS16GBK' 或 'UTF8' 解決中文亂碼。
  • 數值欄位換行符問題:最後一欄為 NUMBER 且檔案為 CRLF 時,加 TERMINATED BY WHITESPACE。

小結

SQL*Loader 功能強大,掌握控制檔語法(INFILE、FIELDS、POSITION、DATE、LOBFILE、COLUMN OBJECT 等)即可應對大多數批量匯入需求。實務上建議先以小量測試驗證控制檔,再正式大量載入。

copyright © 萬盛學電腦網 all rights reserved