下面的是關于sql*loader 的使用的一點總結 有些是來自itpub上的一些網友的總結
大部分是Oracle專家高級編程上的實例 只是我實踐以后寫的結果
sqlldr userid=lgone/tiger control=a.ctl
LOAD DATA
INFILE 't.dat' // 要導入的文件
// INFILE 'tt.dat' // 導入多個文件
// INFILE * // 要導入的內容就在control文件里 下面的BEGINDATA后面就是導入的內容
INTO TABLE table_name // 指定裝入的表
// into table t_name partition (p_1) 分區的載入
BADFILE 'c:/bad.txt' // 指定壞文件地址
************* 以下是4種裝入表的方式
APPEND // 原先的表有數據 就加在后面
// INSERT // 裝載空表 假如原先的表有數據 sqlloader會停止 默認值
// REPLACE // 原先的表有數據 原先的數據會全部刪除
// TRUNCATE // 指定的內容和replace的相同 會用truncate語句刪除現存數據
SKip 5 可以用 "SKIP n" 要害字來指定導入時可以跳過多少行數據
************* 指定的TERMINATED可以在表的開頭 也可在表的內部字段部分
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// 裝載這種數據: 10,lg,"""lg""","lg,lg"
// 在表中結果: 10 lg "lg" lg,lg
// TERMINATED BY X '09' // 以十六進制格式 '09' 表示的
// TERMINATED BY WRITESPACE // 裝載這種數據: 10 lg lg
TRAILING NULLCOLS ************* 表的字段沒有對應的值時答應為空
************* 下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER 要害字 此列的數值不會被裝載
// 如: lg,lg,not 結果 lg lg
)
// 當沒聲明FIELDS TERMINATED BY ',' 時
// (
// col_1 [interger external] TERMINATED BY ',' ,
// col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
// col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
// )
// 當沒聲明FIELDS TERMINATED BY ','用位置告訴字段裝載數據
// (
// col_1 position(1:2),
// col_2 position(3:10),
// col_3 position(*:16), // 這個字段的開始位置在前一字段的結束位置
// col_4 position(1:16),
// col_5 position(3:10) char(8) // 指定字段的類型
// )
BEGINDATA // 對應開始的 INFILE * 要導入的內容就在control文件里
10,Sql,what
20,lg,show
=====================================================================================
//////////// 注重begindata后的數值前面不能有空格
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) 來分隔數據
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( DEPTNO,
FILLER_1 FILLER, // 下面的 "Something Not To Be Loaded" 將不會被裝載
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), // 這個字段的開始位置在前一字段的結束位置
LOC position(*:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA
5 ***** 使用函數 日期的一種表達 TRAILING NULLCOLS的使用
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS // 其實下面的ENTIRE_LINE在BEGINDATA后面的數據中是沒有直接對應
// 的列的值的 假如第一行改為 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME "upper(:dname)", // 使用函數
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy', // 日期的一種表達方式 還有'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 ***** 使用自定義的函數 // 解決的時間問題
使用函數這僅適合于常規導入，并不適合 direct導入方式 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 )" // 使用自定義的函數
)
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 // 通過要害字concatenate 把幾行的記錄看成一行記錄
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales, // 其實這3行看成一行 10,Sales,Virginia,1/5/2000
Virginia,
1/5/2000
// 這列子用 continueif list="," 也可以
告訴sqlldr在每行的末尾找逗號 找到逗號就把下一行附加到上一行
LOAD DATA
INFILE *
continueif this(1:1) = '-' // 找每行的開始是否有連接字符 - 有就把下一行連接為一行
// 如 -10,Sales,Virginia,
// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
// 其中1:1 表示從第一行開始 并在第一行結束 還有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
================================ 用別的語言幫助解決的方法
txt文件中的每2行作為一個記錄插入到數據庫中的一條記錄,文件是定長的
1 2 3 4 5
6 7
插入數據記錄是
1 2 3 4 5 6 7
-------------
可以把換行符作為一個分隔符來處理
-------------
1、到www.activeperl.com去下載一個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、在命令窗口下執行 perl test.pl
6、得到一個新的文本文件：test.new,內容如下：
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 ***** 載入每行的行號
load data
infile *
into table t
replace
( seqno RECNUM //載入每行的行號
text Position(1:1024))
BEGINDATA
fsdfasj //自動分配一行號給載入 表t 的seqno字段 此行為 1
fasdjfasdfl // 此行為 2 ...
9 ***** 載入有換行符的數據
注重: unix 和 windows 不同 //n & /n
還可以用 dbms_lob 和 bfile 看一個文件的回車 換行 等其他非凡字符
< 1 > 使用一個非換行符的字符
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 的使用幫助轉換換行符
)
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
// 這樣裝載會把換行符裝入數據庫 下面的方法就不會 但要求數據的格式不同
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 告訴每個記錄的前3個字節表示記錄的長度 如第一個記錄的 071 表示此記錄有 71 個字節
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屬性
// 最靈活的一中 可定義一個新的行結尾符 win 回車換行 : chr(13)chr(10)
此列中記錄是以 a/r/n 結束的
select utl_raw.cast_to_raw(''chr(13)chr(10)) from dual;
結果 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 ***** 將數據導入多個表
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 ***** 轉載 RAW 數據 或 轉載長字段
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 ***** 轉載 LOB 數據
用 dbms_lob dbms_lob 轉載的數據要在服務器上 通過網絡的不行
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 要大寫
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數據在同一個數據文件中
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數據在單獨的文件中
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是從這里來的
text LOBFILE(filename) TERMINATED BY EOF
)
BEGINDATA
04/14/2001 12:36p 1,697 BUILTIN/Administrators demo10.log
// 這是 windows 下面的情況 上面的數據是用 dir /q/n 看見的情況 *******
///// unix 下的情況
用 ls -l 得到上面數據的情況
控制文件就改下時間的格式
------------------------------
lob 到對象列
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 ***** 轉載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
==============================================================================
象這樣的數據 用 nullif 子句
10-jan-200002350Flipper seemed unusually hungry today.
10510-jan-200009945Spread over three meals.
id position(1:3) nullif id=blanks // 這里可以是blanks 或者別的表達式
// 下面是另一個列子 第一行的 1 在數據庫中將成為 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
------------------------------------------------------------
假如是英文的日志 格式，可能需要修改環境變量 nls_lang or nls_date_format
