SqlLoader如何使用

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

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

1、假設要插入數據的表ftest，字段是（id,username,password,sj）

2、導入表的數據 以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 內容如下：

 load data　　　　　　　　　　infile 'c:\data.txt'　　　　　　 insert into table ftest　　　　fields terminated by " "(id,username,password,sj)

注：如果表中沒有數據就用insert，有數據就用append，刪除舊數據插入新的數據用replace或truncate

4 在cmd命令窗口中執行

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

5 在plsql中查看表ftest

查看已成功插入。

重新學習sqlldr

sqlldr導入數據的一個最簡單例子：

load datainfile * --告訴sqlldr要加載的數據就包含在控制文件本身into table dept --加載到哪個表fields terminated by ',' --數據加載形式應該是逗號分隔的值(deptno,dname,loc) --所要加載的列begindata      --告訴sqlldr后面的行市要加載到dept表的數據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導入的四種加載方式：

APPEND ：原先的表有數據 就加在后面 INSERT：裝載空表 如果原先的表有數據 sqlloader會停止 默認值 REPLACE ：原先的表有數據 原先的數據會全部刪除 TRUNCATE ：指定的內容和replace的相同 會用truncate語句刪除現存數據

用SQLLDR加載數據的FAQ

1、如何加載定界數據

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

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

它指定用逗號分隔數據字段，每個字段可以用雙引號括起。如果把這個控制文件的最后部分修改如下:

  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)另一種常用的格式是制表符定界數據。有兩種方法使用terminated by子句來加載這種數據：

terminated by X'09' --使用十六進制格式的制表符；若用ASCII，制表符應該是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，因為在第一個制表符和第二個制表符之間沒有數據

3）sqlldr的filler關鍵字使用

如跳過制表符

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、如何加載固定格式數據

要加載定寬的固定位置數據，將會在控制文件中使用position關鍵字。

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 字段從哪里開始，到哪里結束。對于position，我們可以使用重疊的位置，可以在記錄中來回反復。如下修改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時，可以使用相對偏移量，也可以使用絕對偏移量。前面的例子使用了絕對偏移量，明確指定字段從哪開始，從哪結束，也可以將前面的控制文件改寫如下：

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

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

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

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

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數據類型，并指定要使用的日期掩碼。這個日期掩碼與數據庫中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、如何使用函數加載數據

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

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

如下控制文件加載數據無法導入

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的使用：一般默認用的好

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

這種情況下，增加TRAILING NULLCOLS會導致綁定變量: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中的使用

假設輸入文件中有以下格式的日期：HH24:MI:SS:只有一個時間；日期時間默認為sysdateDD/MM/YYYY:只有一個日期，時間默認為午夜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、如何加載有內嵌換行符的數據

1）用非換行符的其它字符來表示換行符，并在加載時使用一個sql函數用一個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

注：調用中必須用\\n來表示替換符，而不是\n

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

--控制文件load datainfile demo.dat "fix 80" --指定了輸入數據文件demo.dat，這個文件中每個記錄80字節into table deptreplacefields terminated by ','trailing nullcols(deptno, dname "upper(:dname)", loc "upper(:loc)", comments)--數據文件10,Sales,Virginia,this is the sales\noffice in Virginia            20,，,Sales,Virginia,this is the sales\noffice in Virginia

注：

在unix上，行結束標記是\n即CHR(10),而windows nt平臺的行結束標記是\r\n即CHR(13)||CHR(10)； 可以在控制文件中使用trim內置sql函數來完成截斷尾部的空白符

select * from dept;

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

--控制文件load datainfile demo.dat "var 3" --表明了前三個字節用于記錄每一行的字節數into table deptreplacefields terminated by ','trailing nullcols(deptno, dname "upper(:dname)", loc "upper(:loc)", comments)--數據文件05410,Sales,Virginia,this is the sales office in Virginia

注：在unix上換行符只算一個字節，在windows nt上算兩個字節

select * from dept;

4）在infile指令上使用STR屬性，加載一個變寬文件，其中用某個字符序列來表示行結束符，而不是用換行符表示 STR屬性以十六進制指定，要得到十六進制串，最容易的辦法就是使用sql和utl_raw來生成十六進制串。如在unix平臺，行結束標記是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)--數據文件10,Sales,Virginia,this is the salesoffice in Virginia|select * from dept;

6、加載lob數據

1）加載內聯的lob數據。這些lob數據通常內嵌有換行符和其他特殊字符

--修改表depttruncate table dept;alter table dept drop column comments;alter table dept add comments clob;--數據文件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默認輸入的字段都是char(255)。char(1000000)表示允許輸入多達1000000個字符)select * from dept;

2)加載外聯的lob數據。

需要把包含有一些文件名的數據文件加載在lob中，而不是讓lob數據與結構化數據混在一起。這樣就不必使用上述的4種方法之一來避開輸入數據中的內嵌換行符問題，而這種情況在大量的文本或二進制數據中頻繁出現。sqlldr稱這種額外的數據文件為lobfile。 sqlldr還可以支持加載結構化數據文件。可以告訴sqlldr如何從另外一個文件解析lob數據，這樣就可以加載其中的一部分作為結構化數據中的每一行。sqlldr稱這種外部引用的文件為復雜二級數據文件。

lobfile數據采用以下某種格式:

定長字段（從lobfile加載字節100到10000）； 定界字段（以某個字符結束，或用某個字符括起）；--最常見，以一個文件結束符（EOF）結束 長度/值對，這是一個邊長字段

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

一般用于加載圖像

 create table image_load( id number, name varchar2(255), image ordsys.ordimage) --首先要了解ordsys.ordimage類型

加載這種數據的控制文件如下所示：

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

--繼續編輯加載進來數據的屬性begin for c in (select * from image_load) loop  c.image.setproperties;--setproperties是ordsys.ordimage類型提供的方法，處理圖像本身，并用適當的值更新對象的其余屬性 end loop;end;

額外介紹：

使用plsql加載lob數據

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;

注：

創建的目錄默認為大寫DIR1，如果目錄寫成dir1就會提示錯誤，如果要想使用混有大小寫的目錄名，在創建這樣的目錄時應該帶引號的標識符，如下所示：

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

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