下面的是關(guān)于sql*loader 的使用的一點(diǎn)總結(jié) 有些是來(lái)自itpub上的一些網(wǎng)友的總結(jié)
大部分是Oracle專家高級(jí)編程上的實(shí)例 只是我實(shí)踐以后寫(xiě)的結(jié)果
sqlldr userid=lgone/tiger control=a.ctl
LOAD DATA
INFILE 't.dat' // 要導(dǎo)入的文件
// INFILE 'tt.dat' // 導(dǎo)入多個(gè)文件
// INFILE * // 要導(dǎo)入的內(nèi)容就在control文件里 下面的BEGINDATA后面就是導(dǎo)入的內(nèi)容
INTO TABLE table_name // 指定裝入的表
// into table t_name partition (p_1) 分區(qū)的載入
BADFILE 'c:/bad.txt' // 指定壞文件地址
************* 以下是4種裝入表的方式
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ù)
SKip 5 可以用 "SKIP n" 要害字來(lái)指定導(dǎo)入時(shí)可以跳過(guò)多少行數(shù)據(jù)
************* 指定的TERMINATED可以在表的開(kāi)頭 也可在表的內(nèi)部字段部分
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// 裝載這種數(shù)據(jù): 10,lg,"""lg""","lg,lg"
// 在表中結(jié)果: 10 lg "lg" lg,lg
// TERMINATED BY X '09' // 以十六進(jìn)制格式 '09' 表示的
// TERMINATED BY WRITESPACE // 裝載這種數(shù)據(jù): 10 lg lg
TRAILING NULLCOLS ************* 表的字段沒(méi)有對(duì)應(yīng)的值時(shí)答應(yīng)為空
************* 下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER 要害字 此列的數(shù)值不會(huì)被裝載
// 如: lg,lg,not 結(jié)果 lg lg
)
// 當(dāng)沒(méi)聲明FIELDS TERMINATED BY ',' 時(shí)
// (
// col_1 [interger external] TERMINATED BY ',' ,
// col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
// col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
// )
// 當(dāng)沒(méi)聲明FIELDS TERMINATED BY ','用位置告訴字段裝載數(shù)據(jù)
// (
// col_1 position(1:2),
// col_2 position(3:10),
// col_3 position(*:16), // 這個(gè)字段的開(kāi)始位置在前一字段的結(jié)束位置
// col_4 position(1:16),
// col_5 position(3:10) char(8) // 指定字段的類型
// )
BEGINDATA // 對(duì)應(yīng)開(kāi)始的 INFILE * 要導(dǎo)入的內(nèi)容就在control文件里
10,Sql,what
20,lg,show
=====================================================================================
//////////// 注重begindata后的數(shù)值前面不能有空格
1 ***** 普通裝載
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10,Sales,"""USA"""
20,Accounting,"Virginia,USA"
30,Consulting,Virginia
40,Finance,Virginia
50,"Finance","",Virginia // loc 列將為空
60,"Finance",,Virginia // loc 列將為空
2 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情況
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
-- FIELDS TERMINATED BY x'09'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
3 ***** 指定不裝載那一列 還可用 POSTION(x:y) 來(lái)分隔數(shù)據(jù)
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( DEPTNO,
FILLER_1 FILLER, // 下面的 "Something Not To Be Loaded" 將不會(huì)被裝載
DNAME,
LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,"Virginia,USA"
4 ***** position的列子
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(*:16), // 這個(gè)字段的開(kāi)始位置在前一字段的結(jié)束位置
LOC position(*:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA
5 ***** 使用函數(shù) 日期的一種表達(dá) TRAILING NULLCOLS的使用
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS // 其實(shí)下面的ENTIRE_LINE在BEGINDATA后面的數(shù)據(jù)中是沒(méi)有直接對(duì)應(yīng)
// 的列的值的 假如第一行改為 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME "upper(:dname)", // 使用函數(shù)
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy', // 日期的一種表達(dá)方式 還有'dd-mon-yyyy' 等
ENTIRE_LINE ":deptno:dname:loc:last_updated"
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finance,Virginia,15/3/2001
6 ***** 使用自定義的函數(shù) // 解決的時(shí)間問(wèn)題
使用函數(shù)這僅適合于常規(guī)導(dǎo)入，并不適合 direct導(dǎo)入方式 9i可能可以
create or replace
function my_to_date( p_string in varchar2 ) return date
as
type fmtArray is table of varchar2(25);
l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',
'dd/mm/yyyy',
'dd/mm/yyyy hh24:mi:ss' );
l_return date;
begin
for i in 1 .. l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
when others then null;
end;
EXIT when l_return is not null;
end loop;
if ( l_return is null )
then
l_return :=
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
p_string, 'GMT', 'EST' );
end if;
return l_return;
end;
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )" // 使用自定義的函數(shù)
)
BEGINDATA
10,Sales,Virginia,01-aPRil-2001
20,Accounting,Virginia,13/04/2001
30,Consulting,Virginia,14/04/2001 12:02:02
40,Finance,Virginia,987268297
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Not a date
7 ***** 合并多行記錄為一行記錄
LOAD DATA
INFILE *
concatenate 3 // 通過(guò)要害字concatenate 把幾行的記錄看成一行記錄
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales, // 其實(shí)這3行看成一行 10,Sales,Virginia,1/5/2000
Virginia,
1/5/2000
// 這列子用 continueif list="," 也可以
告訴sqlldr在每行的末尾找逗號(hào) 找到逗號(hào)就把下一行附加到上一行
LOAD DATA
INFILE *
continueif this(1:1) = '-' // 找每行的開(kāi)始是否有連接字符 - 有就把下一行連接為一行
// 如 -10,Sales,Virginia,
// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
// 其中1:1 表示從第一行開(kāi)始 并在第一行結(jié)束 還有continueif next 但continueif list最理想
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA // 但是好象不能象右面的那樣使用
-10,Sales,Virginia, -10,Sales,Virginia,
1/5/2000 1/5/2000
-40, 40,Finance,Virginia,13/04/2001
Finance,Virginia,13/04/2001
================================ 用別的語(yǔ)言幫助解決的方法
txt文件中的每2行作為一個(gè)記錄插入到數(shù)據(jù)庫(kù)中的一條記錄,文件是定長(zhǎng)的
1 2 3 4 5
6 7
插入數(shù)據(jù)記錄是
1 2 3 4 5 6 7
-------------
可以把換行符作為一個(gè)分隔符來(lái)處理
-------------
1、到www.activeperl.com去下載一個(gè)activeperl5.6 MSI
2、安裝 PERL
3、你的文本文件示例：test.old
1 2 3 4 5
6 7
a b c d e
f g
4、我的PERL程序：test.pl
$mycount=2;
open(FILE_OLD","TEST.OLD");
open(FILE_NEW",">TEST.NEW");
while(<FILE_OLD>)
{
chomp;
if ($mycount%2 == 0)
{print FILE_NEW $_." ";}
else
{print FILE_NEW $_."/n";}
$mycount++;
}
5、在命令窗口下執(zhí)行 perl test.pl
6、得到一個(gè)新的文本文件：test.new,內(nèi)容如下：
1 2 3 4 5 6 7
a b c d e f g
---------------
load data
infile 'test.txt'
concatenate(2)
into table aa
fields terminated by whitespace
(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7)
==============================================================
8 ***** 載入每行的行號(hào)
load data
infile *
into table t
replace
( seqno RECNUM //載入每行的行號(hào)
text Position(1:1024))
BEGINDATA
fsdfasj //自動(dòng)分配一行號(hào)給載入 表t 的seqno字段 此行為 1
fasdjfasdfl // 此行為 2 ...
9 ***** 載入有換行符的數(shù)據(jù)
注重: unix 和 windows 不同 //n & /n
還可以用 dbms_lob 和 bfile 看一個(gè)文件的回車(chē) 換行 等其他非凡字符
< 1 > 使用一個(gè)非換行符的字符
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS "replace(:comments,'/n',chr(10))" // replace 的使用幫助轉(zhuǎn)換換行符
)
BEGINDATA
10,Sales,Virginia,01-april-2001,This is the Sales/nOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting/nOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting/nOffice in Virginia
40,Finance,Virginia,987268297,This is the Finance/nOffice in Virginia
< 2 > 使用fix屬性
LOAD DATA
INFILE demo17.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo17.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia
// 這樣裝載會(huì)把換行符裝入數(shù)據(jù)庫(kù) 下面的方法就不會(huì) 但要求數(shù)據(jù)的格式不同
LOAD DATA
INFILE demo18.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo18.dat
10,Sales,Virginia,01-april-2001,"This is the Sales
Office in Virginia"
20,Accounting,Virginia,13/04/2001,"This is the Accounting
Office in Virginia"
30,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting
Office in Virginia"
40,Finance,Virginia,987268297,"This is the Finance
Office in Virginia"
< 3 > 使用var屬性
LOAD DATA
INFILE demo19.dat "var 3"
// 3 告訴每個(gè)記錄的前3個(gè)字節(jié)表示記錄的長(zhǎng)度 如第一個(gè)記錄的 071 表示此記錄有 71 個(gè)字節(jié)
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo19.dat
07110,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
07820,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
07140,Finance,Virginia,987268297,This is the Finance
Office in Virginia
< 4 > 使用str屬性
// 最靈活的一中 可定義一個(gè)新的行結(jié)尾符 win 回車(chē)換行 : chr(13)chr(10)
此列中記錄是以 a/r/n 結(jié)束的
select utl_raw.cast_to_raw(''chr(13)chr(10)) from dual;
結(jié)果 7C0D0A
LOAD DATA
INFILE demo20.dat "str X'7C0D0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo20.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia
10 ***** 將數(shù)據(jù)導(dǎo)入多個(gè)表
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp WHEN empno !
= ' '
(
empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj WHEN projno != ' '
(
projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)
11 ***** 轉(zhuǎn)載 RAW 數(shù)據(jù) 或 轉(zhuǎn)載長(zhǎng)字段
options(bindsize=1075700,rows=1)
load data
infile my.data "fix 53760" // 53760 * 20=1075700 1075700是小于64K 的最大因子
concatenate 20
preserve blanks
into table foo
append
(id constant 1,bigdata raw(1075700))
12 ***** 轉(zhuǎn)載 LOB 數(shù)據(jù)
用 dbms_lob dbms_lob 轉(zhuǎn)載的數(shù)據(jù)要在服務(wù)器上 通過(guò)網(wǎng)絡(luò)的不行
drop table demo;
create or replace Directory dir1 as 'c:/temp/';
create or replace directory "dir2" as 'c:/temp/';
create table demo
( id int primary key,
theClob clob
)
/
host echo 'Hello World/!' > c:/temp/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' ); -- DIR1 要大寫(xiě)
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
/
----------------------------------
用 sqlldr 在同一行的LOB lob數(shù)據(jù)在同一個(gè)數(shù)據(jù)文件中
LOAD DATA
INFILE demo21.dat "str X'7C0D0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS char(1000000)
)
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
40,Finance,Virginia,987268297,"This is the Finance
Office in Virginia, it has embedded commas and is
mUCh longer then the other comments field. If you
feel the need to add double quoted text in here like
this: ""You will need to double up those quotes!
"" to
preserve them in the string. This field keeps going for upto
1,000,000 bytes or until we hit the magic end of record marker,
the followed by a end of line -- it is right here ->"
------------------------------------------------------
用 sqlldr 不在同一行的LOB 就是lob數(shù)據(jù)在單獨(dú)的文件中
create table lob_demo
( owner varchar2(255),
timestamp date,
filename varchar2(255),
text clob
)
/
LOAD DATA /////////// window 的
INFILE *
REPLACE
INTO TABLE LOB_DEMO
( owner position(40:61),
timestamp position(1:18) "to_date(:timestamp'm','mm/dd/yyyy hh:miam')",
filename position(63:80), -- 下面的LOB的filename是從這里來(lái)的
text LOBFILE(filename) TERMINATED BY EOF
)
BEGINDATA
04/14/2001 12:36p 1,697 BUILTIN/Administrators demo10.log
// 這是 windows 下面的情況 上面的數(shù)據(jù)是用 dir /q/n 看見(jiàn)的情況 *******
///// unix 下的情況
用 ls -l 得到上面數(shù)據(jù)的情況
控制文件就改下時(shí)間的格式
------------------------------
lob 到對(duì)象列
create table image_load( id number, name varchar2(255),
image ordsys.ordimage )
/
desc ordsys.ordimage
desc ordsys.ordsource
LOAD DATA
INFILE *
INTO TABLE T
replace
fields terminated by ","
(
id,
name,
fiel_name filler,
image column object
(
source column object
(
localdatalobfile(file_name) terminated by bof
nullif file_name='NONE'
)
)
)
begindata
1,icons,icons.gif
13 ***** 轉(zhuǎn)載varrays /嵌套表
create type myArrayType
as varray(10) of number(12,2)
/
create table t
( x int primary key, y myArrayType )
/
LOAD DATA
INFILE *
INTO TABLE T
replace
fields terminated by ","
(
x,
y_cnt FILLER,
y varray count (y_cnt)
(
y
)
)
BEGINDATA
1,2,3,4
2,10,1,2,3,4,5,6,7,8,9,10
3,5,5,4,3,2,1
------------------------------------
create or replace type myTableType
as table of number(12,2)
/
create table t
( x int primary key, y myTableType )
nested table y store as y_tab
/
LOAD DATA
INFILE *
INTO TABLE T
replace
fields terminated by ","
(
x,
y nested table count (CONSTANT 5)
(
y
)
)
BEGINDATA
1,100,200,300,400,500
2,123,243,542,123,432
==============================================================================
象這樣的數(shù)據(jù) 用 nullif 子句
10-jan-200002350Flipper seemed unusually hungry today.
10510-jan-200009945Spread over three meals.
id position(1:3) nullif id=blanks // 這里可以是blanks 或者別的表達(dá)式
// 下面是另一個(gè)列子 第一行的 1 在數(shù)據(jù)庫(kù)中將成為 null
LOAD DATA
INFILE *
INTO TABLE T
REPLACE
(n position(1:2) integer external nullif n='1',
v position(3:8)
)
BEGINDATA
1 10
20lg
------------------------------------------------------------
假如是英文的日志 格式，可能需要修改環(huán)境變量 nls_lang or nls_date_format
