SqlLoader如何使用-創(chuàng)新互聯(lián)

這篇文章將為大家詳細(xì)講解有關(guān)SqlLoader如何使用，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

創(chuàng)新互聯(lián)致力于成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè),成都網(wǎng)站設(shè)計,集團(tuán)網(wǎng)站建設(shè)等服務(wù)標(biāo)準(zhǔn)化，推過標(biāo)準(zhǔn)化降低中小企業(yè)的建站的成本，并持續(xù)提升建站的定制化服務(wù)水平進(jìn)行質(zhì)量交付，讓企業(yè)網(wǎng)站從市場競爭中脫穎而出。 選擇創(chuàng)新互聯(lián)，就選擇了安全、穩(wěn)定、美觀的網(wǎng)站建設(shè)服務(wù)！

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-192 f1 f1 2010-8-193 f2 f2 2010-8-194 f3 f3 2010-8-195 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 datainfile * --告訴sqlldr要加載的數(shù)據(jù)就包含在控制文件本身into table dept --加載到哪個表fields terminated by ',' --數(shù)據(jù)加載形式應(yīng)該是逗號分隔的值(deptno,dname,loc) --所要加載的列begindata      --告訴sqlldr后面的行市要加載到dept表的數(shù)據(jù)10,Sales,Virginia20,Accounting,Virginia30,Consulting,Virginia40,Finance,Virginiacreate table dept(deptno number(2) constraint dept_pk primary key,dname varchar2(14),loc varchar2(13))sqlldr userid=gwm/gwm@fgisdb control=c:\demol.ctlselect * from dept;1  10  Sales  Virginia2  20  Accounting  Virginia3  30  Consulting  Virginia4  40  Finance  Virginia

sqlldr導(dǎo)入的四種加載方式：

APPEND ：原先的表有數(shù)據(jù) 就加在后面 INSERT：裝載空表 如果原先的表有數(shù)據(jù) sqlloader會停止 默認(rèn)值 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ù)，可能用引號括起，這是當(dāng)前平面文件最常見的數(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,Virginiaselect * from dept1  10  Sales  Virginia,USA2  20  Accounting  Va,"USA"3  30  Consulting  Virginia 4  40  Finance  Virginia

2)另一種常用的格式是制表符定界數(shù)據(jù)。有兩種方法使用terminated by子句來加載這種數(shù)據(jù)：

terminated by X'09' --使用十六進(jìn)制格式的制表符；若用ASCII，制表符應(yīng)該是9

 terminated by whitespace--使用terminated by whitespaceload datainfile *into table deptreplacefields terminated by whitespace(deptno,dname,loc) begindata     10 Sales Virginia select * from dept;1  10  Sales  Virginia--使用terminated by X'09'load datainfile *into table deptreplacefields terminated by X'09'(deptno,dname,loc) begindata     10        Sales        Virginiaselect * from dept;1  10

Sales --因為一旦遇到一個制表符就會輸出一個值。

因此，將10賦給deptno，dname得到了null，因為在第一個制表符和第二個制表符之間沒有數(shù)據(jù)

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

如跳過制表符

load datainfile *into table deptreplacefields terminated by X'09'(deptno,dummy1 filler,dname,dummy2 filler,loc) begindata     10        Sales        Virginiaselect * from dept;1  10  Sales  Virginia

2、如何加載固定格式數(shù)據(jù)

要加載定寬的固定位置數(shù)據(jù)，將會在控制文件中使用position關(guān)鍵字。

load datainfile *into table deptreplace(deptno position(1:2), dname position(3:16), loc position(17:29) ) begindata     10Accounting   Virginia,USAselect * from dept;1  10  Accounting   Virginia,USA

這個控制文件沒有使用terminated by子句；而是使用了position來告訴sqlldr 字段從哪里開始，到哪里結(jié)束。對于position，我們可以使用重疊的位置，可以在記錄中來回反復(fù)。如下修改dept表：

alter table dept add entire_line varchar(29);

并使用如下控制文件：

load datainfile *into table deptreplace(deptno position(1:2), dname position(3:16), loc position(17:29), entire_line position(1:29) ) begindata     10Accounting   Virginia,USAselect * from dept;1  10  Accounting   Virginia,USA  10Accounting  Virginia,USA

使用position時，可以使用相對偏移量，也可以使用絕對偏移量。前面的例子使用了絕對偏移量，明確指定字段從哪開始，從哪結(jié)束，也可以將前面的控制文件改寫如下：

load datainfile *into table deptreplace(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），即相當(dāng)于（5:16）.

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

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

load datainfile *into table deptreplace(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 datainfile *into table deptreplacefields terminated by ','(deptno, dname, loc, last_updated date 'dd/mm/yyyy' ) begindata     10,Accounting,Virginia,1/5/2000select * from dept;1  10  Accounting   Virginia    2000-5-1

4、如何使用函數(shù)加載數(shù)據(jù)

如果想確保加載的數(shù)據(jù)是大寫的，可以改寫控制文件如下：

load datainfile *into table deptreplacefields terminated by ','(deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated date 'dd/mm/yyyy' ) begindata     10,Accounting,Virginia,1/5/2000select * from dept;1  10   ACCOUNTING   VIRGINIA    2000-5-1

如下控制文件加載數(shù)據(jù)無法導(dǎo)入

load datainfile *into table deptreplacefields 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就會為該列綁定一個null值。

這種情況下，增加TRAILING NULLCOLS會導(dǎo)致綁定變量:entire_line成為null。

load datainfile *into table deptreplacefields 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/2000select * from dept;1  10  ACCOUNTING  VIRGINIA  10AccountingVirginia1/5/2000  2000-5-1

2）case在sqlldr中的使用

假設(shè)輸入文件中有以下格式的日期：HH24:MI:SS:只有一個時間；日期時間默認(rèn)為sysdateDD/MM/YYYY:只有一個日期，時間默認(rèn)為午夜0點HH24:MI:SS DD/MM/YYYY:日期時間都顯式提供

可用如下的控制文件

load datainfile *into table deptreplacefields terminated by ','TRAILING NULLCOLS(deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated "case  when length(:last_updated)>9 then to_date(:last_updated,'hh34:mi:ss dd/mm/yyyy') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh34:mi:ss') else to_date(:last_updated,'dd/mm/yyyy') end" ) begindata10,Sales,Virginia,12:03:03 17/10/200520,Accounting,Virginia,02:23:5430,Consulting,Virginia,01:24:00 21/10/200640,Finance,Virginia,17/8/2005alter session set nls_date_format='dd-mon-yyyy hh34:mi:ss';select * from dept;

5、如何加載有內(nèi)嵌換行符的數(shù)據(jù)

1）用非換行符的其它字符來表示換行符，并在加載時使用一個sql函數(shù)用一個CHR(10)替換該文本。

alter table dept add comments varchar2(4000);--使用下列來加載文本load datainfile *into table deptreplacefields terminated by ','trailing nullcols(deptno, dname "upper(:dname)", loc "upper(:loc)", comments "replace(:comments,'\\n',chr(10))" --'\\n'換行符用chr(10)這個代替)begindata10,Sales,Virginia,this is the sales\noffice in Virginia

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

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

--控制文件load datainfile demo.dat "fix 80" --指定了輸入數(shù)據(jù)文件demo.dat，這個文件中每個記錄80字節(jié)into table deptreplacefields 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平臺的行結(jié)束標(biāo)記是\r\n即CHR(13)||CHR(10)； 可以在控制文件中使用trim內(nèi)置sql函數(shù)來完成截斷尾部的空白符

select * from dept;

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

--控制文件load datainfile demo.dat "var 3" --表明了前三個字節(jié)用于記錄每一行的字節(jié)數(shù)into table deptreplacefields 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é)

select * from dept;

4）在infile指令上使用STR屬性，加載一個變寬文件，其中用某個字符序列來表示行結(jié)束符，而不是用換行符表示 STR屬性以十六進(jìn)制指定，要得到十六進(jìn)制串，最容易的辦法就是使用sql和utl_raw來生成十六進(jìn)制串。如在unix平臺，行結(jié)束標(biāo)記是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 datainfile demo.dat "str x'7C0D0A'" into table deptreplacefields terminated by ','trailing nullcols(deptno, dname "upper(:dname)", loc "upper(:loc)", comments)--數(shù)據(jù)文件10,Sales,Virginia,this is the salesoffice in Virginia|select * from dept;

6、加載lob數(shù)據(jù)

1）加載內(nèi)聯(lián)的lob數(shù)據(jù)。這些lob數(shù)據(jù)通常內(nèi)嵌有換行符和其他特殊字符

--修改表depttruncate table dept;alter table dept drop column comments;alter table dept add comments clob;--數(shù)據(jù)文件10,Sales,Virginia,this is the salesoffice in Virginia|20,Accounting,Virginia,this is the Accountingoffice in Virginia|30,Consuling,Virginia,this is the Consulingoffice in Virginia|40,Finance,Virginia,"this is the Financeoffice in Virginia,it has embedded commas and ismuch longer than the other comments filed.If youfeel the need to add double quotes text in here likethis:""you will need to double up those quotes!""topreserve them in the string. This field keeps going for up to1000000 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 datainfile demo.dat "str x'7C0D0A'" into table deptreplacefields 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個字符)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)嵌換行符問題，而這種情況在大量的文本或二進(jìn)制數(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 *replaceinto 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.zipselect 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 datainfile *into table image_loadreplacefields terminated by ','(id,name,file_name filler,image column object( source column object ( localdata lobfile(file_name) terminated by EOF     nullif file_name='none' )))begindata1,icons,icons.gif

注：column object告訴sqlldr這不是一個列名，而是列名的一部分。

使用的列名是image.source.localdata

select * from image_load

--繼續(xù)編輯加載進(jìn)來數(shù)據(jù)的屬性begin for c in (select * from image_load) loop  c.image.setproperties;--setproperties是ordsys.ordimage類型提供的方法，處理圖像本身，并用適當(dāng)?shù)闹蹈聦ο蟮钠溆鄬傩?nbsp;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.txtdeclare 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就會提示錯誤，如果要想使用混有大小寫的目錄名，在創(chuàng)建這樣的目錄時應(yīng)該帶引號的標(biāo)識符，如下所示：

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

關(guān)于“SqlLoader如何使用”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。

當(dāng)前題目：SqlLoader如何使用-創(chuàng)新互聯(lián)
分享網(wǎng)址：http://bm7419.com/article28/dpdicp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、網(wǎng)站維護(hù)、搜索引擎優(yōu)化、商城網(wǎng)站、靜態(tài)網(wǎng)站、營銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)