SqlLoader怎麼使用

SQL*Loader（SQLLDR）是Oracle的高速批量數據加載工具。這是一個非常有用的工具，可用於多種平面文件格式向Oralce數據庫中加載數據，本文給大家分享sqlloader怎麼使用的相關資料，感興趣的朋友一起學習吧

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

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

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

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

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,Virginia
select * from dept
1  10  Sales  Virginia,USA
2  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 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，因為在第一個制表符和第二個制表符之間沒有數據

3）sqlldr的filler關鍵字使用

如跳過制表符

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

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

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

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

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

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

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

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

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

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

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

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。這樣，如果輸入記錄中不存在某一列的數據，sqlldr就會為該列綁定一個null值。

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

假設輸入文件中有以下格式的日期：
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、如何加載有內嵌換行符的數據

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

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

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

--控制文件
load data
infile demo.dat "fix 80" --指定了輸入數據文件demo.dat，這個文件中每個記錄80字節
into table dept
replace
fields 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 data
infile demo.dat "var 3" --表明了前三個字節用於記錄每一行的字節數
into table dept
replace
fields 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 data
infile demo.dat "str x'7C0D0A'" 
into table dept
replace
fields terminated by ','
trailing nullcols
(deptno,
 dname "upper(:dname)",
 loc "upper(:loc)",
 comments
)
--數據文件
10,Sales,Virginia,this is the sales
office in Virginia|
select * from dept;

6、加載lob數據

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

--修改表dept
truncate table dept;
alter table dept drop column comments;
alter table dept add comments clob;
--數據文件
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)加載外聯的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 *
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數據加載到對象列

一般用於加載圖像

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

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

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

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.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;

注：

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

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

以上內容是小編給大家分享的關於SqlLoader怎麼使用的相關資料，希望大家喜歡。