這篇文章給大家分享的是SQL*Loader是什么，具體的使用是怎樣。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，文中的介紹得很詳細(xì)，而要易于理解和學(xué)習(xí)，有需要的朋友可以參考，接下來(lái)就跟隨小編一起了解看看吧。

SQL*Loader（SQLLDR）是Oracle的高速批量數(shù)據(jù)加載工具。這是一個(gè)非常有用的工具，可用于多種平面文件格式向Oralce數(shù)據(jù)庫(kù)中加載數(shù)據(jù)。今天看了申請(qǐng)了*loader的使用，自己小試了下，記錄在這

1、假設(shè)要插入數(shù)據(jù)的表ftest，字段是（id,username,password,sj）

2、導(dǎo)入表的數(shù)據(jù) 以txt格式存儲(chǔ)，名為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)

注：如果表中沒(méi)有數(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ù)的一個(gè)最簡(jiǎn)單例子：

load data
infile * --告訴sqlldr要加載的數(shù)據(jù)就包含在控制文件本身
into table dept --加載到哪個(gè)表
fields terminated by ',' --數(shù)據(jù)加載形式應(yīng)該是逗號(hào)分隔的值
(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會(huì)停止 默認(rèn)值
REPLACE ：原先的表有數(shù)據(jù) 原先的數(shù)據(jù)會(huì)全部刪除
TRUNCATE ：指定的內(nèi)容和replace的相同 會(huì)用truncate語(yǔ)句刪除現(xiàn)存數(shù)據(jù)

用SQLLDR加載數(shù)據(jù)的FAQ

1、如何加載定界數(shù)據(jù)

    1)定界數(shù)據(jù)即用某個(gè)特殊字符分隔的數(shù)據(jù)，可能用引號(hào)括起，這是當(dāng)前平面文件最常見的數(shù)據(jù)格式。
    對(duì)于定界數(shù)據(jù)，最常用的格式是逗號(hào)分隔值格式。采用這種文件格式，數(shù)據(jù)中的每個(gè)字段與下一個(gè)字段用一個(gè)逗號(hào)分隔。文本串可以用引號(hào)括起，這樣就串本身包含逗號(hào)。如果串還必須包含引號(hào)，一般約定是使用兩個(gè)引號(hào)。加載定界數(shù)據(jù)，相應(yīng)的典型控制文件與前面例子相似，但是fields terminated by子句通常如下指定：

 fields terminated by ',' optionally enclose by '"'

  它指定用逗號(hào)分隔數(shù)據(jù)字段，每個(gè)字段可以用雙引號(hào)括起。如果把這個(gè)控制文件的最后部分修改如下:

  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子句來(lái)加載這種數(shù)據(jù)：

   terminated by X'09' --使用十六進(jìn)制格式的制表符；若用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 --因?yàn)橐坏┯龅揭粋€(gè)制表符就會(huì)輸出一個(gè)值。

因此，將10賦給deptno，dname得到了null，因?yàn)樵诘谝粋€(gè)制表符和第二個(gè)制表符之間沒(méi)有數(shù)據(jù)

3）sqlldr的filler關(guān)鍵字使用

如跳過(guò)制表符

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ù)，將會(huì)在控制文件中使用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

    這個(gè)控制文件沒(méi)有使用terminated by子句；而是使用了position來(lái)告訴sqlldr 字段從哪里開始，到哪里結(jié)束。
對(duì)于position，我們可以使用重疊的位置，可以在記錄中來(lái)回反復(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時(shí)，可以使用相對(duì)偏移量，也可以使用絕對(duì)偏移量。前面的例子使用了絕對(duì)偏移量，明確指定字段從哪開始，從哪結(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

    *指示控制文件得出上一個(gè)字段在哪里結(jié)束。因此，在這種情況下，(*:16)與(3:16)是一樣的。注意，控制文件可以混合使用相對(duì)位置和絕對(duì)位置。
另外，使用*表示法時(shí)，可以把它與偏移量相加。例如dname從deptno結(jié)束之后的；兩個(gè)字符開始，可以使用（*+2:16），即相當(dāng)于（5:16）.

    position子句中的結(jié)束位置必須是數(shù)據(jù)結(jié)束的絕對(duì)列位置。有時(shí)，可能指定每個(gè)字段的長(zhǎng)度更為容易，特別是如果這些字段是連續(xù)的。采用這種

  方式，只需告訴sqlldr：記錄從第一個(gè)字節(jié)開始，然后指定每個(gè)字段的長(zhǎng)度。如下：

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ù)類型，并指定要使用的日期掩碼。這個(gè)日期掩碼與數(shù)據(jù)庫(kù)中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ù)無(wú)法導(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的使用：一般默認(rèn)用的好

    解決方法，就是使用TRAILING NULLCOLS。這樣，如果輸入記錄中不存在某一列的數(shù)據(jù)，sqlldr就會(huì)為該列綁定一個(gè)null值。

這種情況下，增加TRAILING NULLCOLS會(huì)導(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:只有一個(gè)時(shí)間；日期時(shí)間默認(rèn)為sysdate
DD/MM/YYYY:只有一個(gè)日期，時(shí)間默認(rèn)為午夜0點(diǎn)
HH24:MI:SS DD/MM/YYYY:日期時(shí)間都顯式提供

可用如下的控制文件

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）用非換行符的其它字符來(lái)表示換行符，并在加載時(shí)使用一個(gè)sql函數(shù)用一個(gè)CHR(10)替換該文本。

alter table dept add comments varchar2(4000);
--使用下列來(lái)加載文本
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)這個(gè)代替
)
begindata
10,Sales,Virginia,this is the sales\noffice in Virginia

注：調(diào)用中必須用\\n來(lái)表示替換符，而不是\n

2）在infile指令上使用FIX屬性，加載一個(gè)定長(zhǎng)平面文件。
    使用該方法，輸入數(shù)據(jù)必須出現(xiàn)在定長(zhǎng)記錄中。對(duì)于固定位置的數(shù)據(jù)，使用FIX屬性就特別合適，這些文件一般為定長(zhǎng)文件。
    另外使用該方法時(shí)，數(shù)據(jù)必須在外部存儲(chǔ)，不能存儲(chǔ)在控制文件本身。

--控制文件
load data
infile demo.dat "fix 80" --指定了輸入數(shù)據(jù)文件demo.dat，這個(gè)文件中每個(gè)記錄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é)束標(biāo)記是\n即CHR(10),而windows nt平臺(tái)的行結(jié)束標(biāo)記是\r\n即CHR(13)||CHR(10)；
  可以在控制文件中使用trim內(nèi)置sql函數(shù)來(lái)完成截?cái)辔膊康目瞻追?br>

select * from dept;

3)在infile指令在、上使用VAR屬性，加載一個(gè)變寬文件，在該文件使用的格式中，每一行前幾個(gè)字節(jié)指定了這一行的長(zhǎng)度

--控制文件
load data
infile demo.dat "var 3" --表明了前三個(gè)字節(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上換行符只算一個(gè)字節(jié)，在windows nt上算兩個(gè)字節(jié)

select * from dept;

4）在infile指令上使用STR屬性，加載一個(gè)變寬文件，其中用某個(gè)字符序列來(lái)表示行結(jié)束符，而不是用換行符表示
    STR屬性以十六進(jìn)制指定，要得到十六進(jìn)制串，最容易的辦法就是使用sql和utl_raw來(lái)生成十六進(jìn)制串。如在unix平臺(tái)，行結(jié)束標(biāo)記是CHR(10)，我們的特殊字符是一個(gè)管道符號(hào)（|），則可以寫成：

 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默認(rèn)輸入的字段都是char(255)。char(1000000)表示允許輸入多達(dá)1000000個(gè)字符
)
select * from dept;

2)加載外聯(lián)的lob數(shù)據(jù)。

    需要把包含有一些文件名的數(shù)據(jù)文件加載在lob中，而不是讓lob數(shù)據(jù)與結(jié)構(gòu)化數(shù)據(jù)混在一起。這樣就不必使用上述的4種方法之一來(lái)避開輸入數(shù)據(jù)中
的內(nèi)嵌換行符問(wèn)題，而這種情況在大量的文本或二進(jìn)制數(shù)據(jù)中頻繁出現(xiàn)。sqlldr稱這種額外的數(shù)據(jù)文件為lobfile。
    sqlldr還可以支持加載結(jié)構(gòu)化數(shù)據(jù)文件?？梢愿嬖Vsqlldr如何從另外一個(gè)文件解析lob數(shù)據(jù)，這樣就可以加載其中的一部分作為結(jié)構(gòu)化數(shù)據(jù)中的每一行。
sqlldr稱這種外部引用的文件為復(fù)雜二級(jí)數(shù)據(jù)文件。

    lobfile數(shù)據(jù)采用以下某種格式:

    定長(zhǎng)字段（從lobfile加載字節(jié)100到10000）；
    定界字段（以某個(gè)字符結(jié)束，或用某個(gè)字符括起）；--最常見，以一個(gè)文件結(jié)束符（EOF）結(jié)束
    長(zhǎng)度/值對(duì)，這是一個(gè)邊長(zhǎng)字段

--加載數(shù)據(jù)的表
create table lob_demo
(owner varchar2(255),
time_stamp date,
filename varchar2(255),
data blob)
--假設(shè)有一目錄，其中包含想要加載到數(shù)據(jù)庫(kù)中的文件。以下為想要加載文件的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ù)加載到對(duì)象列

一般用于加載圖像

 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這不是一個(gè)列名，而是列名的一部分。

使用的列名是image.source.localdata

select * from image_load

--繼續(xù)編輯加載進(jìn)來(lái)數(shù)據(jù)的屬性
begin
 for c in (select * from image_load) loop
  c.image.setproperties;--setproperties是ordsys.ordimage類型提供的方法，處理圖像本身，并用適當(dāng)?shù)闹蹈聦?duì)象的其余屬性
 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)建的目錄默認(rèn)為大寫DIR1，如果目錄寫成dir1就會(huì)提示錯(cuò)誤，如果要想使用混有大小寫的目錄名，在創(chuàng)建這樣的目錄時(shí)應(yīng)該帶引號(hào)的標(biāo)識(shí)符，如下所示：

create or replace directory "dir2" as 'D:\oracle';

上述內(nèi)容具有一定的借鑒價(jià)值，感興趣的朋友可以參考，希望能對(duì)大家有幫助，想要了解更多"SQL*Loader是什么，具體的使用是怎樣"的內(nèi)容，大家可以關(guān)注群英網(wǎng)絡(luò)的其它相關(guān)文章。

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：[email protected]進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，查實(shí)之后，將立刻刪除涉嫌侵權(quán)內(nèi)容。

猜你喜歡

• 查詢符合條件的記錄總數(shù)的SQL語(yǔ)句如何寫？

  查詢符合條件的記錄總數(shù)的SQL語(yǔ)句如何寫？在SQL Server中，我們想要查詢記錄的總數(shù)是比較簡(jiǎn)單的，用count()函數(shù)就可以實(shí)現(xiàn)，下面我們要實(shí)現(xiàn)的需求是查詢符合條件的記錄總數(shù)，那么這要如何寫SQL語(yǔ)句呢？

• 數(shù)據(jù)庫(kù)觸發(fā)器DB2與SQL Server分別怎么，有什么不同

  大部分?jǐn)?shù)據(jù)庫(kù)語(yǔ)句的基本語(yǔ)法是相同的，但具體到的每一種數(shù)據(jù)庫(kù)，又有些不一樣，例如觸發(fā)器，DB2和SQL Server兩種很大的不同。對(duì)數(shù)據(jù)庫(kù)觸發(fā)器DB2和SqlServer有哪些區(qū)別感興趣的朋友一起看看本文吧

• SQL Server數(shù)據(jù)庫(kù)中備份與還原的操作是什么

  這篇文章主要介紹了SqlServer數(shù)據(jù)庫(kù)備份與還原的實(shí)現(xiàn)步驟，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

• SQL數(shù)據(jù)庫(kù)備份怎樣做？數(shù)據(jù)庫(kù)備份代碼詳解

  備份對(duì)于保護(hù)文件，數(shù)據(jù)免受丟失或者損壞等意外情況起重要作用，因此數(shù)據(jù)做好備份很重要。那么SQL SERVER數(shù)據(jù)庫(kù)備份要怎么實(shí)現(xiàn)呢？下面給大家分享一個(gè)實(shí)例，供大家參考學(xué)習(xí)。

• sql編譯與重編是什么意思？

  這篇文章主要介紹sql編譯與重編，很多朋友對(duì)于sql編譯與重編可能不是很了解，對(duì)此下面小編就給大家來(lái)介紹一下sql編譯與重編是什么，以及應(yīng)用和要注意的方面，希望大家閱讀完這篇文章能有所收獲，接下來(lái)跟隨小編一起了解看看吧。