SQL*Loader（SQLLDR）是Oracle的高速批量數(shù)據(jù)加載工具。這是一個非常有用的工具，可用于多種平面文件格式向Oralce數(shù)據(jù)庫中加載數(shù)據(jù)。今天看了申請了*loader的使用，自己小試了下，記錄在這
1、假設(shè)要插入數(shù)據(jù)的表ftest，字段是（id,username,password,sj）
2、導(dǎo)入表的數(shù)據(jù) 以txt格式存儲，名為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、寫控制文件，格式為ctl，命名為cont.ctl 內(nèi)容如下：
load data
infile 'c:\data.txt'
insert into table ftest
fields terminated by " "
(id,username,password,sj)
注：如果表中沒有數(shù)據(jù)就用insert，有數(shù)據(jù)就用append，刪除舊數(shù)據(jù)插入新的數(shù)據(jù)用replace或truncate
4 在cmd命令窗口中執(zhí)行
sqlldr fyzh/fyzh control=c:\cont.ctl data=c:\data.txt
5 在plsql中查看表ftest
查看已成功插入。
重新學(xué)習(xí)sqlldr
sqlldr導(dǎo)入數(shù)據(jù)的一個最簡單例子：
load data
infile * --告訴sqlldr要加載的數(shù)據(jù)就包含在控制文件本身
into table dept --加載到哪個表
fields terminated by ',' --數(shù)據(jù)加載形式應(yīng)該是逗號分隔的值
(deptno,dname,loc) --所要加載的列
begindata --告訴sqlldr后面的行市要加載到dept表的數(shù)據(jù)
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
create table dept
(deptno number(2) constraint dept_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
sqlldr userid=gwm/gwm@fgisdb control=c:\demol.ctl
select * from dept;
1 10 Sales Virginia
2 20 Accounting Virginia
3 30 Consulting Virginia
4 40 Finance Virginia
sqlldr導(dǎo)入的四種加載方式：
APPEND ：原先的表有數(shù)據(jù) 就加在后面
INSERT：裝載空表 如果原先的表有數(shù)據(jù) sqlloader會停止 默認值
REPLACE ：原先的表有數(shù)據(jù) 原先的數(shù)據(jù)會全部刪除
TRUNCATE ：指定的內(nèi)容和replace的相同 會用truncate語句刪除現(xiàn)存數(shù)據(jù)
用SQLLDR加載數(shù)據(jù)的FAQ
1、如何加載定界數(shù)據(jù)
1)定界數(shù)據(jù)即用某個特殊字符分隔的數(shù)據(jù)，可能用引號括起，這是當前平面文件最常見的數(shù)據(jù)格式。
對于定界數(shù)據(jù)，最常用的格式是逗號分隔值格式。采用這種文件格式，數(shù)據(jù)中的每個字段與下一個字段用一個逗號分隔。文本串可以用引號括起，這樣就串本身包含逗號。如果串還必須包含引號，一般約定是使用兩個引號。加載定界數(shù)據(jù)，相應(yīng)的典型控制文件與前面例子相似，但是fields terminated by子句通常如下指定：
fields terminated by ',' optionally enclose by '"'
它指定用逗號分隔數(shù)據(jù)字段，每個字段可以用雙引號括起。如果把這個控制文件的最后部分修改如下:
fields terminated by ',' optionally enclosed by '"'
(deptno,dname,loc)
begindata
10,Sales,"Virginia,USA"
20,Accounting,"Va,""USA"""
30,Consulting,Virginia
40,Finance,Virginia
select * from dept
1 10 Sales Virginia,USA
2 20 Accounting Va,"USA"
3 30 Consulting Virginia
4 40 Finance Virginia
2)另一種常用的格式是制表符定界數(shù)據(jù)。有兩種方法使用terminated by子句來加載這種數(shù)據(jù)：
terminated by X'09' --使用十六進制格式的制表符；若用ASCII，制表符應(yīng)該是9
terminated by whitespace
--使用terminated by whitespace
load data
infile *
into table dept
replace
fields terminated by whitespace
(deptno,dname,loc)
begindata
10 Sales Virginia
select * from dept;
1 10 Sales Virginia
--使用terminated by X'09'
load data
infile *
into table dept
replace
fields terminated by X'09'
(deptno,dname,loc)
begindata
10 Sales Virginia
select * from dept;
1 10
Sales --因為一旦遇到一個制表符就會輸出一個值。
因此，將10賦給deptno，dname得到了null，因為在第一個制表符和第二個制表符之間沒有數(shù)據(jù)
3）sqlldr的filler關(guān)鍵字使用
如跳過制表符
load data
infile *
into table dept
replace
fields terminated by X'09'
(deptno,dummy1 filler,dname,dummy2 filler,loc)
begindata
10 Sales Virginia
select * from dept;
1 10 Sales Virginia
2、如何加載固定格式數(shù)據(jù)
要加載定寬的固定位置數(shù)據(jù)，將會在控制文件中使用position關(guān)鍵字。
load data
infile *
into table dept
replace
(deptno position(1:2),
dname position(3:16),
loc position(17:29)
)
begindata
10Accounting Virginia,USA
select * from dept;
1 10 Accounting Virginia,USA
這個控制文件沒有使用terminated by子句；而是使用了position來告訴sqlldr 字段從哪里開始，到哪里結(jié)束。
對于position，我們可以使用重疊的位置，可以在記錄中來回反復(fù)。如下修改dept表：
alter table dept add entire_line varchar(29);
并使用如下控制文件：
load data
infile *
into table dept
replace
(deptno position(1:2),
dname position(3:16),
loc position(17:29),
entire_line position(1:29)
)
begindata
10Accounting Virginia,USA
select * from dept;
1 10 Accounting Virginia,USA 10Accounting
Virginia,USA
使用position時，可以使用相對偏移量，也可以使用絕對偏移量。前面的例子使用了絕對偏移量，明確指定字段從哪開始，從哪結(jié)束，也可以將
前面的控制文件改寫如下：
load data
infile *
into table dept
replace
(deptno position(1:2),
dname position(*:16),
loc position(*:29),
entire_line position(1:29)
)
begindata
10Accounting
Virginia,USA
*指示控制文件得出上一個字段在哪里結(jié)束。因此，在這種情況下，(*:16)與(3:16)是一樣的。注意，控制文件可以混合使用相對位置和絕對位置。
另外，使用*表示法時，可以把它與偏移量相加。例如dname從deptno結(jié)束之后的；兩個字符開始，可以使用（*+2:16），即相當于（5:16）.
position子句中的結(jié)束位置必須是數(shù)據(jù)結(jié)束的絕對列位置。有時，可能指定每個字段的長度更為容易，特別是如果這些字段是連續(xù)的。采用這種
方式，只需告訴sqlldr：記錄從第一個字節(jié)開始，然后指定每個字段的長度。如下：
load data
infile *
into table dept
replace
(deptno position(1) char(2),
dname position(*) char(14),
loc position(*) char(13),
entire_line position(1) char(29)
)
begindata
10Accounting Virginia,USA
select * from dept;
3、如何加載日期
使用sqlldr加載日期只需在控制文件中date數(shù)據(jù)類型，并指定要使用的日期掩碼。這個日期掩碼與數(shù)據(jù)庫中to_char和to_date中使用的日期掩碼一樣。
如修改dept表如下：
alter table dept add last_updated date;
load data
infile *
into table dept
replace
fields terminated by ','
(deptno,
dname,
loc,
last_updated date 'dd/mm/yyyy'
)
begindata
10,Accounting,Virginia,1/5/2000
select * from dept;
1 10
Accounting
Virginia
2000-5-1
4、如何使用函數(shù)加載數(shù)據(jù)
如果想確保加載的數(shù)據(jù)是大寫的，可以改寫控制文件如下：
load data
infile *
into table dept
replace
fields terminated by ','
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
last_updated date 'dd/mm/yyyy'
)
begindata
10,Accounting,Virginia,1/5/2000
select * from dept;
1 10
ACCOUNTING
VIRGINIA
2000-5-1
如下控制文件加載數(shù)據(jù)無法導(dǎo)入
load data
infile *
into table dept
replace
fields terminated by ','
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
last_updated date 'dd/mm/yyyy',
entire_line ":deptno||:dname||:loc||:last_updated"
)
begindata
10,Accounting,Virginia,1/5/2000
1）TRAILING NULLCOLS的使用：一般默認用的好
解決方法，就是使用TRAILING NULLCOLS。這樣，如果輸入記錄中不存在某一列的數(shù)據(jù)，sqlldr就會為該列綁定一個null值。
這種情況下，增加TRAILING NULLCOLS會導(dǎo)致綁定變量:entire_line成為null。
load data
infile *
into table dept
replace
fields terminated by ','
TRAILING NULLCOLS
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
last_updated date 'dd/mm/yyyy',
entire_line ":deptno||:dname||:loc||:last_updated"
)
begindata
10,Accounting,Virginia,1/5/2000
select * from dept;
1 10 ACCOUNTING VIRGINIA 10AccountingVirginia1/5/2000 2000-5-1
2）case在sqlldr中的使用
假設(shè)輸入文件中有以下格式的日期：
HH24:MI:SS:只有一個時間；日期時間默認為sysdate
DD/MM/YYYY:只有一個日期，時間默認為午夜0點
HH24:MI:SS DD/MM/YYYY:日期時間都顯式提供
可用如下的控制文件
load data
infile *
into table dept
replace
fields terminated by ','
TRAILING NULLCOLS
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
last_updated
"case
when length(:last_updated)>9
then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'dd/mm/yyyy')
end"
)
begindata
10,Sales,Virginia,12:03:03 17/10/2005
20,Accounting,Virginia,02:23:54
30,Consulting,Virginia,01:24:00 21/10/2006
40,Finance,Virginia,17/8/2005
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select * from dept;
5、如何加載有內(nèi)嵌換行符的數(shù)據(jù)
1）用非換行符的其它字符來表示換行符，并在加載時使用一個sql函數(shù)用一個CHR(10)替換該文本。
alter table dept add comments varchar2(4000);
--使用下列來加載文本
load data
infile *
into table dept
replace
fields terminated by ','
trailing nullcols
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
comments "replace(:comments,'\\n',chr(10))" --'\\n'換行符用chr(10)這個代替
)
begindata
10,Sales,Virginia,this is the sales\noffice in Virginia
注：調(diào)用中必須用\\n來表示替換符，而不是\n
2）在infile指令上使用FIX屬性，加載一個定長平面文件。
使用該方法，輸入數(shù)據(jù)必須出現(xiàn)在定長記錄中。對于固定位置的數(shù)據(jù)，使用FIX屬性就特別合適，這些文件一般為定長文件。
另外使用該方法時，數(shù)據(jù)必須在外部存儲，不能存儲在控制文件本身。
--控制文件
load data
infile demo.dat "fix 80" --指定了輸入數(shù)據(jù)文件demo.dat，這個文件中每個記錄80字節(jié)
into table dept
replace
fields terminated by ','
trailing nullcols
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
comments
)
--數(shù)據(jù)文件
10,Sales,Virginia,this is the sales\noffice in Virginia
20,，,Sales,Virginia,this is the sales\noffice in Virginia
注：
在unix上，行結(jié)束標記是\n即CHR(10),而windows nt平臺的行結(jié)束標記是\r\n即CHR(13)||CHR(10)；
可以在控制文件中使用trim內(nèi)置sql函數(shù)來完成截斷尾部的空白符
3)在infile指令在、上使用VAR屬性，加載一個變寬文件，在該文件使用的格式中，每一行前幾個字節(jié)指定了這一行的長度
--控制文件
load data
infile demo.dat "var 3" --表明了前三個字節(jié)用于記錄每一行的字節(jié)數(shù)
into table dept
replace
fields terminated by ','
trailing nullcols
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
comments
)
--數(shù)據(jù)文件
05410,Sales,Virginia,this is the sales office in Virginia
注：在unix上換行符只算一個字節(jié)，在windows nt上算兩個字節(jié)
4）在infile指令上使用STR屬性，加載一個變寬文件，其中用某個字符序列來表示行結(jié)束符，而不是用換行符表示
STR屬性以十六進制指定，要得到十六進制串，最容易的辦法就是使用sql和utl_raw來生成十六進制串。如在unix平臺，行結(jié)束標記是CHR(10)，我們的特殊字符是一個管道符號（|），則可以寫成：
select utl_raw.cast_to_raw('|'||chr(10)) from dual;--可見在unix上為x'7C0A'
在windows上用
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;--為x'7C0D0A'
--控制文件
load data
infile demo.dat "str x'7C0D0A'"
into table dept
replace
fields terminated by ','
trailing nullcols
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
comments
)
--數(shù)據(jù)文件
10,Sales,Virginia,this is the sales
office in Virginia|
select * from dept;
6、加載lob數(shù)據(jù)
1）加載內(nèi)聯(lián)的lob數(shù)據(jù)。這些lob數(shù)據(jù)通常內(nèi)嵌有換行符和其他特殊字符
--修改表dept
truncate table dept;
alter table dept drop column comments;
alter table dept add comments clob;
--數(shù)據(jù)文件
10,Sales,Virginia,this is the sales
office in Virginia|
20,Accounting,Virginia,this is the Accounting
office in Virginia|
30,Consuling,Virginia,this is the Consuling
office in Virginia|
40,Finance,Virginia,"this is the Finance
office in Virginia,it has embedded commas and is
much longer than the other comments filed.If you
feel the need to add double quotes text in here like
this:""you will need to double up those quotes!""to
preserve them in the string. This field keeps going for up to
1000000 bytes (because of the control file definition I used)
or until we hit the magic and of record marker,
the | followed by an end of line - it is right here ->"|
--控制文件
load data
infile demo.dat "str x'7C0D0A'"
into table dept
replace
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
comments char(1000000) --sqlldr默認輸入的字段都是char(255)。char(1000000)表示允許輸入多達1000000個字符
)
select * from dept;
2)加載外聯(lián)的lob數(shù)據(jù)。
需要把包含有一些文件名的數(shù)據(jù)文件加載在lob中，而不是讓lob數(shù)據(jù)與結(jié)構(gòu)化數(shù)據(jù)混在一起。這樣就不必使用上述的4種方法之一來避開輸入數(shù)據(jù)中
的內(nèi)嵌換行符問題，而這種情況在大量的文本或二進制數(shù)據(jù)中頻繁出現(xiàn)。sqlldr稱這種額外的數(shù)據(jù)文件為lobfile。
sqlldr還可以支持加載結(jié)構(gòu)化數(shù)據(jù)文件?？梢愿嬖Vsqlldr如何從另外一個文件解析lob數(shù)據(jù)，這樣就可以加載其中的一部分作為結(jié)構(gòu)化數(shù)據(jù)中的每一行。
sqlldr稱這種外部引用的文件為復(fù)雜二級數(shù)據(jù)文件。
lobfile數(shù)據(jù)采用以下某種格式:
定長字段（從lobfile加載字節(jié)100到10000）；
定界字段（以某個字符結(jié)束，或用某個字符括起）；--最常見，以一個文件結(jié)束符（EOF）結(jié)束
長度/值對，這是一個邊長字段
--加載數(shù)據(jù)的表
create table lob_demo
(owner varchar2(255),
time_stamp date,
filename varchar2(255),
data blob)
--假設(shè)有一目錄，其中包含想要加載到數(shù)據(jù)庫中的文件。以下為想要加載文件的owner，time_stamp，文件名及文件本身
load data
infile *
replace
into table lob_demo
(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
select owner,time_stamp,filename,dbms_lob.getlength(data) from lob_demo;
3）將lob數(shù)據(jù)加載到對象列
一般用于加載圖像
create table image_load(
id number,
name varchar2(255),
image ordsys.ordimage) --首先要了解ordsys.ordimage類型
加載這種數(shù)據(jù)的控制文件如下所示：
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
注：column object告訴sqlldr這不是一個列名，而是列名的一部分。
使用的列名是image.source.localdata
--繼續(xù)編輯加載進來數(shù)據(jù)的屬性
begin
for c in (select * from image_load) loop
c.image.setproperties;--setproperties是ordsys.ordimage類型提供的方法，處理圖像本身，并用適當?shù)闹蹈聦ο蟮钠溆鄬傩?
end loop;
end;
額外介紹：
使用plsql加載lob數(shù)據(jù)
create table demo (id int primary key,theclob clob)
create or replace directory dir1 as 'D:\oracle';
SQL> host echo 'hello world!' >d:/oracle/test.txt
declare
l_clob clob;
l_bfile bfile;
begin
insert into demo values (1, empty_clob()) returning theclob into l_clob;
l_bfile := bfilename('DIR1', 'test.txt');
dbms_lob.fileopen(l_bfile);
dbms_lob.loadfromfile(l_clob, l_bfile, dbms_lob.getlength(l_bfile));
dbms_lob.fileclose(l_bfile);
end;
select dbms_lob.getlength(theclob),theclob from demo;
注：
創(chuàng)建的目錄默認為大寫DIR1，如果目錄寫成dir1就會提示錯誤，如果要想使用混有大小寫的目錄名，在創(chuàng)建這樣的目錄時應(yīng)該帶引號的標識符，如下所示：
create or replace directory "dir2" as 'D:\oracle';
以上內(nèi)容是小編給大家分享的關(guān)于SqlLoader怎么使用的相關(guān)資料，希望大家喜歡。
您可能感興趣的文章:- Oracle 高速批量數(shù)據(jù)加載工具sql*loader使用說明
- [Oracle] 常用工具集之SQL*Loader的用法
- SQL LOADER錯誤小結(jié)
