sql load 模板

a.
1.创建一个用于下载数据的表，字段的类型和长度和原来数据数据库中字段一样。
create table temp1
( name                  varchar2(15 byte),
  birth_dt              date
)

2.编写load文件，load.ctl
LOAD DATA
INFILE '/home/dev/personal/a.txt'
BADFILE '/home/dev/personal/1.bad'
DISCARDFILE '/home/dev/personal/1.dsc'
INTO TABLE temp1
APPEND
(name            position(1:5),
birth_dt        position(60:69) date 'YYYY-mm-dd'
)
note:position(x,y)是字段在INFILE文件里的起始和结束位置。
                       
3.在unix里运行：
sqlldr userid=用户名/密码@数据库名字 control=load.ctl parallel=true  direct=true
即可将文件a.txt中的数据下载到temp1表里。

b.也可以在控制文件中直接导入数据
LOAD DATA
INFILE *
BADFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.BAD'
DISCARDFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.DSC'
INSERT INTO TABLE EMCCOUNTRY
Fields terminated by ";" Optionally enclosed by '"'
(
COUNTRYID NULLIF (COUNTRYID="NULL"),--默认可以为空
COUNTRYCODE,
COUNTRYNAME,
CONTINENTID NULLIF (CONTINENTID="NULL"),
MAPID NULLIF (MAPID="NULL"),
CREATETIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (CREATETIME="NULL"),
LASTMODIFIEDTIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (LASTMODIFIEDTIME="NULL")
)
BEGINDATA
1;"JP";"Japan";1;9;"09/16/2004 16:31:32";NULL
2;"CN";"China";1;10;"09/16/2004 16:31:32";NULL


-------------------------------------------------------
　　跳过数据行：
　　可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如：
　 　LOAD DATA
　　INFILE *
　　INTO TABLE load_positional_data
　　SKIP 5
　 　( data1 POSITION(1:5),
　　data2 POSITION(6:15)
　　)
　　BEGINDATA
　 　11111AAAAAAAAAA
　　22222BBBBBBBBBB

-------------------------------------------------------

insert，为缺省方式，在数据装载开始时要求表为空
append，在表中追加新记录
replace，删除旧记录，替换成新装载的记录
-
fields terminated by ","
fields terminated by X'09'　　字段终止于X'09'，是一个制表符（TAB）
-------------------------------------------------------

导入数据时修改数据：
　　在导入数据到数据库时，可以 修改数据。注意，这仅适合于常规导入，并不适合 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
　 　)


跳过某一行：
OPTIONS(SKIP=1)
　　LOAD DATA
　　TRUNCATE INTO TABLE T1
　　 FIELDS TERMINATED BY ','
　　( field1,
　　field2 FILLER,
　　field3
　 　)

-------------------------------------------------------
导入多行记录：
　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


-------------------------------------------------------
　　SQL*Loader 数据的提交：
　　一般情况下 是在导入数据文件数据后提交的。
　　也可以通过指定 ROWS= 参数来指定每次提交记录数。
　　提高 SQL*Loader 的性能：
　 　1) 一个简单而容易忽略的问题是，没有对导入的表使用任何索引和/或约束(主键)。如果这样做，甚至在使用ROWS=参数时，会很明显降低数据库导入性能。
　 　2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。
　　3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。
　　4) 可以同时运行多个导入任务.
　 　常规导入与direct导入方式的区别：
　　常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中。

-------------------------------------------------------


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