SQLLDR应用举例【转】
|
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 列将为空
注：BEGINDATA后的数值前面不能有空格
2、TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情况
LOAD DATA
INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY WHITESPACE --FIELDS TERMINATED BY x'20' (DEPTNO, DNAME, LOC ) BEGINDATA 10 Sales Virginia
注：x'20'表示字符ASCII码的16进制数值
3、指定不装载那一列
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
结果：10 | Accounting Vir | ginia,USA | 10Accounting Virginia,USA
5、使用函数日期的一种表达TRAILING NULLCOLS的使用
LOAD DATA
INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS
--这句的意思是将没有对应值的列都置为null
--如果第一行改为 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
注：可以通过:dname类型调用函数，特别注意date函数的使用。
6、 合并多行记录为一行记录 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, Virginia, 1/5/2000
注：例如有些文本文件以N行为一循环记录数据，则可以这样导入。
7、使用continueif来合并记录行
上例可直接使用continueif last= ','来告诉Oracle如果前一个数据以','结尾，则这个附加到上一行
LOAD DATA
INFILE * continueif last= ',' INTO TABLE DEPT replace FIELDS TERMINATED BY ',' (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED date 'dd/mm/yyyy' ) BEGINDATA 10,Sales, Virginia, 1/5/2000
注：ContinueIf还可以使用this或next选项，具体操作见文档
8、载入每行的行号
LOAD DATA INFILE * INTO TABLE DEPT
replace
(DEPTNORECNUM //载入每行的行号 ENTIRE_LINEPosition(1:1024)
)
BEGINDATA fsdfasj --自动分配行号到DEPTNO字段，此行为1 fasdjfasdfl --自动递增，此行为2 9、载入有换行符的数据 ①使用一个非换行符的字符
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,'%%',chr(10))" --用replace函数转换成换行符 ) BEGINDATA 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
注：换行的特殊字符如果使用'\n'，则会在Windows编译过程中直接换成换行符，导致无法转换
②使用fix属性 Load DATA INFILE demo1.dat "fix 68" INTO TABLE t1_a REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED Date 'dd/mm/yyyy', ENTIRE_LINE ) demo1.dat 10,aaaab,Virginia,01/05/2001,This is the aaaab Office in Virginia 20,aaaac,Virginia,13/04/2001,This is the aaaac Office in Virginia 30,aaaad,Virginia,14/04/2001,This is the aaaad Office in Virginia 40,aaaae,Virginia,16/02/2001,This is the aaaae Office in Virginia
注：fix只能加在外部文件数据导入时，另外需要每行数据长度都相等。
--下面这种方法也一样 Load DATA INFILE demo17.dat "fix 70" INTO TABLE t1_a REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED Date 'dd/mm/yyyy', ENTIRE_LINE ) demo2.dat 10,aaaab,Virginia,01/05/2001,"This is the aaaab Office in Virginia" 20,aaaac,Virginia,13/04/2001,"This is the aaaac Office in Virginia" 30,aaaad,Virginia,14/04/2001,"This is the aaaad Office in Virginia" 40,aaaae,Virginia,16/02/2001,"This is the aaaae Office in Virginia" ③ 使用var属性 Load DATA INFILE demo17.dat "var 3" INTO TABLE t1_a REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED Date 'dd/mm/yyyy', ENTIRE_LINE ) demo17.dat 03510,Sales,Virginia,01/01/2001,This 03920,Accounting,Virginia,13/04/2001,Thi 04530,Consulting,Virginia,14/04/2001,This is t 07140,Finance,Virginia,14/04/2001,This is the Finance Office in Virginia
注：var 3 表示前三位用于说明该条记录的长度 (但是谁告诉我长度怎么数的？-_-|||)
④使用str属性 可使用str来定义一个行结尾符 计算以|\r\n 结束的值： select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual; 结果 7C0D0A Load DATA INFILE demo17.dat "str X'7C0D0A'" INTO TABLE t1_a REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED Date 'dd/mm/yyyy', ENTIRE_LINE ) demo17.dat 10,Sales,Virginia,01/01/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,This is the Consulting Office in Virginia| 40,Finance,Virginia,14/04/2002,This is the Finance Office in Virginia
注意：同样需要在外部文件数据导入中使用，且最后一个不用加；另外注意不要有空格
10、nullif导入
LOAD DATA
INFILE * INTO TABLE t1_a
REPLACE
(DEPTNO position(1:2) integer external nullif DEPTNO='1',
--当导入deotno的值为'1'时，则该条记录不导入
DNAME position(3:8) ) BEGINDATA 1 10 20lg
注：需要注意的是在前面指定的数据类型以及后面的引号！
==========================================================================================================
Oracle SQL*Loader 使用指南（转载）
如何使用 SQL*Loader 工具 我们可以用Oracle的sqlldr工具来导入数据。例如: sqlldr scott/tiger control=loader.ctl 控制文件(loader.ctl)将加载一个外部数据文件(含分隔符)
loader.ctl如下:
load data infile 'c:\data\mydata.csv' into table emp fields terminated by "," optionally enclosed by '"' (empno, empname, sal, deptno)
mydata.csv 如下:
10001,"Scott Tiger", 1000, 40 10002,"Frank Naude", 500, 20 下面是一个指定记录长度的示例控制文件。“*” 代表数据文件与此文件同名，即在后面使用BEGINDATA段来标识数据。 load data infile * replace into table departments ( dept position (02:05) char(4), deptname position (08:27) char(20) ) begindata COSC COMPUTER SCIENCE ENGL ENGLISH LITERATURE MATH MATHEMATICS POLY POLITICAL SCIENCE
Unloader这样的工具
Oracle没有提供将数据导出到一个文件的工具。但是我们可以用SQL*Plus的select及 format 数据来输出到一个文件： set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on spool oradata.txt select col1 || ',' || col2 || ',' || col3 from tab1 where col2 = 'XYZ'; spool off
另外，也可以使用使用 UTL_FILE PL/SQL 包处理:
rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter declare fp utl_file.file_type; begin fp := utl_file.fopen('c:\oradata','tab1.txt','w'); utl_file.putf(fp, '%s, %s\n', 'TextField', 55); utl_file.fclose(fp); end; /
当然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。
加载可变长度或指定长度的记录
LOAD DATA
INFILE * INTO TABLE load_delimited_data FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (data1, data2 ) BEGINDATA 11111,AAAAAAAAAA 22222,"A,B,C,D,"
下面是导入固定位置（固定长度）数据示例：
LOAD DATA INFILE * INTO TABLE load_positional_data (data1 POSITION(1:5), data2 POSITION(6:15) ) BEGINDATA 11111AAAAAAAAAA 22222BBBBBBBBBB
跳过数据行：
可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如：
LOAD DATA
INFILE * INTO TABLE load_positional_data SKIP 5 --似乎不行？需要在DOS层级下操作才有效 (data1 POSITION(1:5), data2 POSITION(6:15) ) BEGINDATA 11111AAAAAAAAAA 22222BBBBBBBBBB
导入数据时修改数据：
在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.如： LOAD DATA INFILE * INTO TABLE modified_data (rec_no "my_db_sequence.nextval", region CONSTANT '31', time_loaded "to_char(SYSDATE, 'HH24:MI')", data1 POSITION(1:5) ":data1/100", data2 POSITION(6:15) "upper(:data2)", data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" ) BEGINDATA 11111AAAAAAAAAA991201 22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt' BADFILE 'bad_orders.txt' APPEND INTO TABLE mailing_list FIELDS TERMINATED BY "," (addr, city, state, zipcode, mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)", mailing_city "decode(:mailing_city, null, :city, :mailing_city)", mailing_state )
将数据导入多个表：
如: 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 )
导入选定的记录：
如下例： (01) 代表第一个字符, (30:37) 代表30到37之间的字符: LOAD DATA INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis' APPEND INTO TABLE my_selective_table WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217' ( region CONSTANT '31', service_key POSITION(01:11) INTEGER EXTERNAL, call_b_no POSITION(12:29) CHAR )
导入时跳过某些字段：
可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER 字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如： LOAD DATA TRUNCATE INTO TABLE T1 FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 )
导入多行记录：
可以使用下面两个选项之一来实现将多行数据导入为一个记录:
CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
SQL*Loader 数据的提交：
一般情况下是在导入数据文件数据后提交的。 也可以通过指定 ROWS= 参数来指定每次提交记录数。
提高 SQL*Loader 的性能：
1) 一个简单而容易忽略的问题是，没有对导入的表使用任何索引和/或约束(主键)。如果这样做，甚至在使用ROWS=参数时，会很明显降低数据库导入性能。 2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。 3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。 4) 可以同时运行多个导入任务.
常规导入与direct导入方式的区别：
常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数
============================================================================
控制根据数据不同插入不同的表 处理定长记录 处理变长记录(从access到oracle) FIELDS TERMINATED BY x’09′ （制表符） LOAD DATA 使用filler 跳过 在导入数据文本中不想进行导入的列 demo u can see upper functions so like SVRMGR> host sqlldr scott/scott control=control.txt TRAILING NULLCOLS my compute just down i lose some article yet not been submit LOAD DATA let’s goon sqlldr with function is powerful magic power function CASE When…..END relation discard file sometimes customer give our data.txt date format are too different so we have ablility to create own functions i copy a good fucniton from TOM use of it if other format it would in bad file we kan reload it 使用sqlldr 应该注意的问题 1 不能选择使用哪个回滚段 你必须保证任何一个回滚段有足够的长度来容纳delete 或者使用truncate 选项 由于insert并没有产生过多的回滚,因为它只写 rowid into redo 理解没有问题吧?? 2truncate选项 为truncate table t reuse storage SQLLDR默认输入流为数据类型为CHAR 长度为 255 所以当 begindata section里面的 输入流 长于255 的时候会报错的 sqlldr希望你输入小于255字节或更少字节的数据,而获得的比这要多,解决的方法是在控制文件中只是单纯地使用 char(N) n是可以包括你输入列地最大长度 比方说load long类型 地时候 可以 输入 char(10000) ^_^ 就到这里 大家如果发现有什么bug 请贴出来 thx LOAD DATA 例如你想 装入 1.3.5列 跳过2.4列 可以利用filler |
本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/1086083，如需转载请自行联系原作者
