一：sql loader使用例子

car.csv要匯入的資料格式如下

姓名性別身份證號碼人員類別未登帳時間

張宏華男 432401511011201 有工作（區） 2011.04-2012.03

胡祥勝男有工作（41市 2011.04-2012.03

楊長保男 430702195111221011 有工作（區） 2011.04-2012.03

劉新翁男 432401511125001 有工作（41市2011.04-2012.03

周家勝男 432401511123001 有工作（(市）2011.04-2012.03

顧春茂男 432401510210301 有工作（市） 2011.04-2012.03

王躍志男 432401195102200015 有工作（41區2011.04-2012.03

潘伯倫男 432401195112237516 老市“4050” 2011.04-2012.03

a.建立表(將上面的資料匯入到這個表中)

create table jy_20120308

(

username varchar2(40),

sex varchar2(10),

idcard varchar2(20),

rylb varchar2(50),

sj varchar2(50)

)

a.建立SQL*Loader輸入資料所需要的檔案,均儲存到C:\，用記事本編輯：

控制檔案：input.ctl，內容如下：

　　 load data　　　　　　　　　　 --1、控制檔案標識

　　 infile 'D:\sqlldr\tx.csv' --2、要輸入的資料檔名為car.csv

　　 append into table jy_20120308 --3、向表test中追加記錄

　　 fields terminated by "," --4、欄位以逗號分隔

　　 (username,sex,idcard,rylb,sj) -----定義列對應順序

a、insert，為預設方式，在資料裝載開始時要求表為空

b、append，在表中追加新記錄

c、replace，刪除舊記錄，替換成新裝載的記錄

d、truncate，同上

在DOS視窗下使用SQL*Loader命令實現資料的輸入

C:\>sqlldr userid=system/manager@orcl control=input.ctl

預設日誌檔名為：input.log

預設壞記錄檔案為：input.bad

也可以使用下面形式

建立SQL*Loader輸入資料所需要的檔案,均儲存到C:\，用記事本編輯：

控制檔案：input.ctl，內容如下：

　　 load data　　　　　　　　　　 --1、控制檔案標識

　　 infile * --2、可以在控制檔案內或在執行命令時指定 --資料內容或資料檔名

　　 append into table jy_20120308 --3、向表test中追加記錄

　　 fields terminated by "," --4、欄位以逗號分隔

　　 (username,sex,idcard,rylb,sj) -----定義列對應順序

sqlldr 'DBUser/Password control=/export/home/imp.ctl'

data= D:\sqlldr\tx.csv' log=port.log

C:\>sqlldr userid=system/manager@orcl control=input.ctl
data=/export/home/port.txt log=port.log

b）在控制檔案中直接匯入資料

1、控制檔案test.ctl的內容

LOAD DATA

INFILE *

BADFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.BAD'

DISCARDFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.DSC'

INSERT INTO TABLE EMCCOUNTRY

Fields terminated by ";" Optionally enclosed by '"'

(

COUNTRYID NULLIF (COUNTRYID="NULL"),

COUNTRYCODE,

COUNTRYNAME,

CONTINENTID NULLIF (CONTINENTID="NULL"),

MAPID NULLIF (MAPID="NULL"),

CREATETIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (CREATETIME="NULL"),

LASTMODIFIEDTIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (LASTMODIFIEDTIME="NULL")

)

BEGINDATA

1;"JP";"Japan";1;9;"09/16/2004 16:31:32";NULL

2;"CN";"China";1;10;"09/16/2004 16:31:32";NULL

3;"IN";"India";1;11;"09/16/2004 16:31:32";NULL

4;"AU";"Australia";6;12;"09/16/2004 16:31:32";NULL

5;"CA";"Canada";4;13;"09/16/2004 16:31:32";NULL

6;"US";"United States";4;14;"09/16/2004 16:31:32";NULL

7;"MX";"Mexico";4;15;"09/16/2004 16:31:32";NULL

8;"GB";"United Kingdom";3;16;"09/16/2004 16:31:32";NULL

9;"DE";"Germany";3;17;"09/16/2004 16:31:32";NULL

10;"FR";"France";3;18;"09/16/2004 16:31:32";NULL

11;"IT";"Italy";3;19;"09/16/2004 16:31:32";NULL

12;"ES";"Spain";3;20;"09/16/2004 16:31:32";NULL

13;"FI";"Finland";3;21;"09/16/2004 16:31:32";NULL

14;"SE";"Sweden";3;22;"09/16/2004 16:31:32";NULL

15;"IE";"Ireland";3;23;"09/16/2004 16:31:32";NULL

16;"NL";"Netherlands";3;24;"09/16/2004 16:31:32";NULL

17;"DK";"Denmark";3;25;"09/16/2004 16:31:32";NULL

18;"BR";"Brazil";5;85;"09/30/2004 11:25:43";NULL

19;"KR";"Korea, Republic of";1;88;"09/30/2004 11:25:43";NULL

20;"NZ";"New Zealand";6;89;"09/30/2004 11:25:43";NULL

21;"BE";"Belgium";3;79;"09/30/2004 11:25:43";NULL

22;"AT";"Austria";3;78;"09/30/2004 11:25:43";NULL

23;"NO";"Norway";3;82;"09/30/2004 11:25:43";NULL

24;"LU";"Luxembourg";3;81;"09/30/2004 11:25:43";NULL

25;"PT";"Portugal";3;83;"09/30/2004 11:25:43";NULL

26;"GR";"Greece";3;80;"09/30/2004 11:25:43";NULL

27;"IL";"Israel";1;86;"09/30/2004 11:25:43";NULL

28;"CH";"Switzerland";3;84;"09/30/2004 11:25:43";NULL

29;"A1";"Anonymous Proxy";0;0;"09/30/2004 11:25:43";NULL

30;"A2";"Satellite Provider";0;0;"09/30/2004 11:25:43";NULL

31;"AD";"Andorra";3;0;"09/30/2004 11:25:43";NULL

32;"AE";"United Arab Emirates";1;0;"09/30/2004 11:25:43";NULL

33;"AF";"Afghanistan";1;0;"09/30/2004 11:25:43";NULL

34;"AG";"Antigua and Barbuda";7;0;"09/30/2004 11:25:43";NULL

35;"AI";"Anguilla";7;0;"09/30/2004 11:25:43";NULL

36;"AL";"Albania";3;0;"09/30/2004 11:25:43";NULL

37;"AM";"Armenia";3;0;"09/30/2004 11:25:43";NULL

38;"AN";"Netherlands Antilles";3;0;"09/30/2004 11:25:43";NULL

39;"AO";"Angola";2;0;"09/30/2004 11:25:43";NULL

40;"AP";"Asia/Pacific Region";2;0;"09/30/2004 11:25:43";NULL

41;"AQ";"Antarctica";8;0;"09/30/2004 11:25:43";NULL

42;"AR";"Argentina";5;0;"09/30/2004 11:25:43";NULL

43;"AS";"American Samoa";6;0;"09/30/2004 11:25:43";NULL

44;"AW";"Aruba";5;0;"09/30/2004 11:25:43";NULL

45;"AZ";"Azerbaijan";1;0;"09/30/2004 11:25:43";NULL

46;"BA";"Bosnia and Herzegovina";3;0;"09/30/2004 11:25:43";NULL

47;"BB";"Barbados";5;0;"09/30/2004 11:25:43";NULL

48;"BD";"Bangladesh";1;0;"09/30/2004 11:25:43";NULL

49;"BF";"Burkina Faso";2;0;"09/30/2004 11:25:43";NULL

50;"BG";"Bulgaria";3;0;"09/30/2004 11:25:43";NULL

51;"BH";"Bahrain";1;0;"09/30/2004 11:25:43";NULL

52;"BI";"Burundi";2;0;"09/30/2004 11:25:43";NULL

53;"BJ";"Benin";2;0;"09/30/2004 11:25:43";NULL

54;"BM";"Bermuda";4;0;"09/30/2004 11:25:43";NULL

55;"BN";"Brunei Darussalam";1;0;"09/30/2004 11:25:43";NULL

56;"BO";"Bolivia";5;0;"09/30/2004 11:25:43";NULL

57;"BS";"Bahamas";7;0;"09/30/2004 11:25:43";NULL

58;"BT";"Bhutan";1;0;"09/30/2004 11:25:43";NULL

59;"BV";"Bouvet Island";5;0;"09/30/2004 11:25:43";NULL

60;"BW";"Botswana";2;0;"09/30/2004 11:25:43";NULL

61;"BY";"Belarus";3;0;"09/30/2004 11:25:43";NULL

2、執行匯入命令

C:\>sqlldr userid=system/manager control=test.ctl

c）複雜格式的匯入

Sqlldr

sql loader可以把一些以文字格式存放的資料順利的匯入到oracle資料庫中，

是一種在不同資料庫之間進行資料遷移的非常方便而且通用的工具。

缺點就速度比較慢，另外對blob等型別的資料就有點麻煩了。

用法: SQLLDR keyword=value [,keyword=value,...]

有效的關鍵字:

userid -- ORACLE username/password

control - 控制檔案

log - 記錄的日誌檔案

bad - 壞資料檔案

data - 資料檔案

discard - 丟棄的資料檔案

discardmax - 允許丟棄資料的最大值 (全部預設)

skip -- Number of logical records to skip (預設0)

load -- Number of logical records to load (全部預設)

errors - 允許的錯誤記錄數 (預設50)

rows -- Number of rows in conventional path bind array or between direct path data saves

（每次提交的記錄數，預設: 常規路徑 64, 所有直接路徑）

bindsize -- Size of conventional path bind array in bytes(預設256000)

每次提交記錄的緩衝區的大小(位元組為單位，預設256000)

silent --禁止輸出資訊 (header,feedback,errors,discards,partitions)

direct - 使用直通路徑方式匯入 (預設FALSE) 某日讀了一篇文章，說是如果選擇匯入方式為true或是y，則會繞過了SGA，直接寫datafile，而且是直接在HWM以上寫。這就導致了HWM會被不斷抬高，這樣即使後來delete掉了這批資料，HWM依然沒有被降下來。HWM不斷被抬高的直接表現就是在做全表掃描的時候會越來越慢

parfile -- parameter file: name of file that contains parameter specifications

parallel -- 並行匯入 (預設FALSE)

file -- File to allocate extents from

與bindsize成對使用，其中較小者會自動調整到較大者

sqlldr先計算單條記錄長度，乘以rows，如小於bindsize，不會試圖擴張rows以填充bindsize；如超出，則以bindsize為準。

external_table

-- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(預設NOT_USED)

columnarrayrows

-- Number of rows for direct path column array(預設5000)

streamsize -- Size of direct path stream buffer in bytes(預設256000)

multithreading

-- use multithreading in direct path

resumable -- enable or disable resumable for current session(預設FALSE)

resumable_name

-- text string to help identify resumable statement

resumable_timeout

-- wait time (in seconds) for RESUMABLE(預設7200)

date_cache -- size (in entries) of date conversion cache(預設1000)

注意：有兩種方式可以指定命令列引數：透過位置或者透過關鍵字。前者的例子：'sqlldr scott/tiger foo'；

後者的例子：'sqlldr control=foo userid=scott/tiger'；

不能前面使用關鍵字指定後面透過位置制定的混合方式；

比如：'sqlldr scott/tiger control=foo logfile=log' 是允許的，

但'sqlldr scott/tiger control=foo log'不允許。

為清楚起見最好所有命令列引數都用關鍵字指定。

控制檔案：

一個控制命令的指令碼檔案，通常以ctl結尾，內容如下：

LOAD DATA

INFILE 't.dat' 要匯入的檔案

// INFILE 'tt.date' 匯入多個檔案

// INFILE * 表示要匯入的內容就在control檔案裡下面的BEGINDATA後面就是匯入的內容

INTO TABLE table_name 指定裝入的表

BADFILE 'c:\bad.txt' 可選，指定壞檔案地址,預設在當前目錄下生成與原檔名一致的.bad檔案

************* 以下是4種裝入表的方式

APPEND 原先的表有資料就加在後面

INSERT 裝載空表如果原先的表有資料 sqlloader會停止預設值

REPLACE 原先的表有資料原先的資料會全部刪除

TRUNCATE 指定的內容和replace的相同會用truncate語句刪除現存資料

************* 指定分隔符

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

// TERMINATED BY WRITESPACE 以空白分割

TRAILING NULLCOLS 表的欄位沒有對應的值時允許為空

************* 下面是表的欄位

(

col_1 , col_2 ,col_filler FILLER // FILLER 關鍵字此列的數值不會被裝載

// 如: lg,lg,not 結果 lg lg

)

如果沒宣告FIELDS TERMINATED BY ',' 時，可以用下面兩種方式實現同樣功能：

1．為每一列指定分隔符

(

col_1 [interger external] TERMINATED BY ',' ,

col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,

col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'

)

2．用位置告訴欄位裝載資料

(

col_1 position(1:2),

col_2 position(3:10),

col_3 position(*:16), // 這個欄位的開始位置在前一欄位的結束位置

col_4 position(1:16),

col_5 position(3:10) char(8) // 指定欄位的型別

)

BEGINDATA 對應開始的 INFILE * 要匯入的內容就在control檔案裡

10,Sql,what

20,lg,show

使用oracle sqlldr匯入文字資料的例子

相關文章