SqlLoader怎么使用
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)前平面文件最常見(jiàn)的數(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 字段從哪里開(kāi)始，到哪里結(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ì)偏移量，明確指定字段從哪開(kāi)始，從哪結(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è)字符開(kāi)始，可以使用（*+2:16），即相當(dāng)于（5:16）.
position子句中的結(jié)束位置必須是數(shù)據(jù)結(jié)束的絕對(duì)列位置。有時(shí)，可能指定每個(gè)字段的長(zhǎng)度更為容易，特別是如果這些字段是連續(xù)的。采用這種
方式，只需告訴sqlldr：記錄從第一個(gè)字節(jié)開(kāi)始，然后指定每個(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;--可見(jiàn)在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)避開(kā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è)字符括起）；--最常見(jiàn)，以一個(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)容是小編給大家分享的關(guān)于SqlLoader怎么使用的相關(guān)資料，希望大家喜歡。
相關(guān)文章
SQL?獲取SQL?Server中兩個(gè)日期之間的所有日期(三種方法)
這篇文章主要介紹了SQL獲取SQL?Server中兩個(gè)日期之間的所有日期,通過(guò)示例代碼展示了如何使用CTE和日期函數(shù)獲取從”2022-01-01″到”2022-01-10″之間的所有日期,需要的朋友可以參考下2024-06-06
解析SQL?Server?CDC配合Kafka?Connect監(jiān)聽(tīng)數(shù)據(jù)變化的問(wèn)題
這篇文章主要介紹了SQL?Server?CDC配合Kafka?Connect監(jiān)聽(tīng)數(shù)據(jù)變化,除了數(shù)據(jù)庫(kù)開(kāi)啟CDC支持以外，主要還是要將變更的數(shù)據(jù)通過(guò)Kafka?Connect傳輸數(shù)據(jù)，Debezium是目前官方推薦的連接器，本文給大家分享實(shí)現(xiàn)步驟，感興趣的朋友跟隨小編一起看看吧2021-12-12
這篇文章主要給大家介紹了關(guān)于SQL?Server啟用sa賬戶的簡(jiǎn)單圖文教程,因?yàn)榘惭b數(shù)據(jù)的時(shí)候沒(méi)有啟用sa賬戶,這里記錄一下如何啟用,需要的朋友可以參考下2023-09-09
可以用擴(kuò)展存儲(chǔ)過(guò)程xp_dirtree獲取文件列表，用openrowset倒入數(shù)據(jù)到二進(jìn)制字段。2009-08-08
為了大家更容易理解我舉出的SQL語(yǔ)句，本文假定已經(jīng)建立了一個(gè)學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)，全文均以學(xué)生成績(jī)的管理為例來(lái)描述。2008-12-12
你想獲取某一年份第一周開(kāi)始的日期，Insus.NET給你的答案，可以參考使用下面這個(gè)函數(shù)2012-01-01
這篇文章主要為大家詳細(xì)介紹了Win10 64位安裝個(gè)人版SQL2000圖文教程，感興趣的小伙伴們可以參考一下2016-07-07
sql server獲得新記錄標(biāo)識(shí)列值的二種方法
有時(shí)開(kāi)發(fā)需要我們需把新插入的記錄之后立即返回插入記錄的ID哦，下面給大家介紹mssqlserver返回新添加的記錄的標(biāo)識(shí)列的值sql語(yǔ)句2013-11-11
Sql Server事務(wù)語(yǔ)法及使用方法實(shí)例分析
這篇文章主要介紹了Sql Server事務(wù)語(yǔ)法及使用方法,結(jié)合實(shí)例形式分析了Sql Server事務(wù)的概念、原理及相關(guān)使用技巧,需要的朋友可以參考下2019-02-02
SQL Server2012數(shù)據(jù)庫(kù)備份和還原的教程
這篇文章主要為大家詳細(xì)介紹了SQL Server2012數(shù)據(jù)庫(kù)備份和還原的教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-03-03