Oracle Sql Loader的学习使用

最近由于遇到oracle控制文件的使用，虽然不是很复杂，但是从来没有用过，专门花点时间看看。点击 这里 查看详细

1，概述：

Sql Loader: 一个批量工具，将文件数据导入到数据库。可以导入一个表或者多个表，甚至可以在导入时修改数据。

2，使用

a,你电脑需要装Oracle,不然你是找不到Sqlldr 这个命令的。

在控制输入台输入 sqlldr：

会列出相关的参数介绍。

> sqlldr...Usage: SQLLDR keyword=value [,keyword=value,...]Valid Keywords:    userid -- ORACLE username/password              control -- control file name                         log -- log file name                             bad -- bad file name                            data -- data file name                        discard -- discard file name                  discardmax -- number of discards to allow          (Default all)      skip -- number of logical records to skip    (Default 0)      load -- number of logical records to load    (Default all)    errors -- number of errors to allow            (Default 50)      rows -- number of rows in conventional path bind array or between directpath data saves               (Default: Conventional path 64, Direct path all)  bindsize -- size of conventional path bind array in bytes  (Default 256000)    silent -- suppress messages during run (header,feedback,errors,discards,partitions)    direct -- use direct path                      (Default FALSE)   parfile -- parameter file: name of file that contains parameter specifications  parallel -- do parallel load                     (Default FALSE)      file -- file to allocate extents from      skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(Default FALSE)skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)commit_discontinued -- commit loaded rows when load is discontinued  (DefaultFALSE)  readsize -- size of read buffer                  (Default 1048576)external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)columnarrayrows -- number of rows for direct path column array  (Default 5000)streamsize -- size of direct path stream buffer in bytes  (Default 256000)multithreading -- use multithreading in direct path  resumable -- enable or disable resumable for current session  (Default FALSE)resumable_name -- text string to help identify resumable statementresumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)date_cache -- size (in entries) of date conversion cache  (Default 1000)PLEASE NOTE: Command-line parameters may be specified either by position or by keywords.An example of the former case is 'sqlldr scott/tiger foo'; an example of the latter is 'sqlldr control=foo userid=scott/tiger'.One may specify parameters by position beforebut not after parameters specified by keywords.For example, 'sqlldr scott/tiger control=foologfile=log' is allowed, but 'sqlldr scott/tiger control=foo log' is not, even though theposition of the parameter 'log' is correct. 

 

 

b, sqlldr 将文本文件的导入到数据库

这里看个简单例子。看看sqlldr到底怎么工作的。

1,准备数据文件，例如input.txt.这个文件将导入到数据库中。

首先查看我们数据库的表格式。

create table student(SNAME VARCHAR(20),SAGE INTEGER,SEMAIL VARCHAR(20),SPHONE VARCHAR(20),SADDRESS VARCHAR(20))

input.txt 文件

12,12,[e-mail],12,address13,13,[e-mail],13,address14,14,[e-mail],14,address15,15,[e-mail],15,address16,16,[e-mail],16,address17,17,[e-mail],17,address18,18,[e-mail],18,address19,19,[e-mail],19,address

2，控制文件input.ctl

load datainfile 'input.txt'append into table student   --这里用的Append.fields terminated by ","   --这里表示逗号分割。(SNAME,SAGE,SEMAIL,SPHONE,SADDRESS)

这里用的Append, 追加数据，还有几个其他的参数：

   a，insert，为缺省方式，在数据装载开始时要求表为空  

   b，append，在表中追加新记录   

   c ，replace，删除旧记录，替换成新装载的记录

   d，truncate，同上  

 

3，sqlldr 调用控制文件

sqlldr username/password@Database control =input.ctl             //input.ctl 为控制文件

在这里需要提下，这里是会生成日志文件，默认为文件名文件名+.log. 当前为 input.log

如果执行失败了，会生成bad file. 如果在当前执行中错误，会生成input.bad file。 

 下面指定Log 和bad 文件，当然可以加上路径

sqlldr userid=username/password@database control=input.ctl log=input.log bad=input.bad  SILENT=(HEADER, FEEDBACK)

 

SILENT=(HEADER, FEEDBACK) 控制端不显示信息，例如下面的信息将不再控制端显示。只在日志文件中

Record 4: Rejected - Error on table EMPORA-00001: unique constraint <name> violated

 

 

当然是可以显示指定的。

load datainfile input.txtbadfile  t.baddiscardfile t.dscappend into table studentfields terminated by ","(SNAME,SAGE,SEMAIL,SPHONE,SADDRESS)

 

 看看日志文件：input.log

 View Code

 

4，查看数据库

 

到此一个简单的例子完成，从一个文本文件导入到数据库。

文件可以为不同格式文件，.dat,.csv都可以的。

 

C,sqlldr直接在控制文件中导入数据。

 

load datainfile *append into table studentfields terminated by ","(SNAME,SAGE,SEMAIL,SPHONE,SADDRESS)begindata20,20,[e-mail],20,address  --这里是数据

 

 

D,当文件数据是以绝对位置分开的，我们可以直接截取。当然，截取的开始与结束必须小心了。

load datainfile t.datappend into table student(SNAME  position(01:20), SAGE position(21:23) , SEMAIL position(41:60), SPHONE position(61:80), SADDRESS position(81:100) )

 

t.dat 文件

Jack                12                  [e-mail]       134998879           SingaporeJack2               12                  [e-mail]       134998879           SingaporeJack3               12                  [e-mail]       134998879           SingaporeJack4               12                  [e-mail]       134998879           SingaporeJack5               12                  [e-mail]       134998879           SingaporeJack6               12                  [e-mail]       134998879           SingaporeJack7               12                  [e-mail]       134998879           Singapore

 

还数据在Load to database 的时候，load的数据是可以改变的。

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')"  )BEGINDATA11111AAAAAAAAAA99120122222BBBBBBBBBB990112