SQL Loader 导入数据

load  data　　　　　　　　　　  --1、控制文件标识 
　　infile  'studinfo1.txt'　　　　　　  --2、要输入的数据文件名为test.txt 
　　append  into  table  studinfonew　　　　--3、向表test中追加记录 
　　fields  terminated  by  X'09'　　--4、字段终止于X'09'，是一个制表符（TAB） 
      TRAILING NULLCOLS
       (studno,studname,studsex,
studbirthday 
   "to_date(:Studbirthday,'YYYY-MM-DD')",
         classid)　　  -----定义列对应顺序 

LOAD DATA

INFILE 'datafile.csv' --(这里是数据文件的位置，保存为csv文件（逗号分隔的文件）)

INSERT INTO TABLE table_name --(欲装载的目的表)

INSERT/APPEND/REPLACE --（insert为默认的方式，他要求目的表在装载时数据为空，append是在表原有的数 据上追加，replace替换原有的数据，还有一个truncate与replace一样）

FIELDS TERMINATED BY ','

TRAILING NULLCOLS --(这个语句很关键是说你装载时也装载那些空数据不写这个语句会报一个错误)

（

COL1，

COL2，

COL3 CHAR(50000), --注意这个的这个CHAR（50000）也很关键因为LDR默认的装载字符好像是255太小了，如果你的数据文件里的COL3是字符型数据且都比较大的话，不加这个通常会报错。

COL4 DATE 'YYYY-MM-DD HH24:MI:SS' 这里如果你的目的表COL4是日期类型的数据且数据文件的日期格式为'YYYY-MM-DD HH24:MI:SS'这个，可以这样限制，也可以使用其他的形式'yyyy-mm'or'yyyy-mm-dd

）

控制文件基本上就是这样

在DOS下写装载命令时也要注意：

SQLLDR USERID=username/ps@sid control=cnname(控制文件).ctl log=name.log bad=name.bad

errors=5000这里一定要把日志文件与坏文件记录好，因为那些不符合规范没装载进去的记录都在这里，不过如果datafile整理的好，控制文件限制的也比较好的话一般都没有坏数据。

This article shows  some  important  features  provided by  SQL loader.
Q: How to load data into a table with clob column ？

A: Yes, SQL Loader support it.
  
   Example:
  
    SQL> Create table animal_feeding (
         animal_id number,
         feeding_date DATE,
         pounds_eaten number(5,2)
         note clob);
        
  
   comma-delimited file:
  
   100,12-jan-2004,12.34,note_100.txt
   101,12-jan-2004,23.45,note_105.txt
   102,12-jan-2004,34.56,note_112.txt
   103,13-jan-2004,45.67
   104,13-jan-2004,0,note_104.txt
   105,13-jan-2004,56.0
  
  
   Control file:
  
   load data
     infile 'animal_feeding_clob.csv'
       badfile 'animal_feeding_clob'
     append
     into table animal_feeding
       trailing nullcols
       (
         animal_id         integer external terminated by "," ,
         feeding_date      date "dd-mon-yyyy" terminated by "," ,
         pounds_eaten      decimal external terminated by "," ,
         note_file_name    filler char terminated by "," ,
         note              lobfile(note_file_name)
                           terminated by eof
       )

Q:  How to load data using expressions ?

A:  Yes, you can.

    Examples:
   
      SQL> Create table animal_feeding (
             animal_id number,
             feeding_date DATE,
             pounds_eaten number(5,2)
             note varchar2(80));
     
      CSV file:
     
         100,12-jan-2004,12.34, "Fillper seemd unusally hungry today."
         101,12-jan-2004,90.12, "Spread over three meals."
         102,12-jan-1004,10,"No comment."
         103,13-jan-2004,55
         104,13-jan-2004,32.0
         105,13-jan-2004,2.5,"Nosey wasn't very hungry."
     
      Request:  
        1. Convert pound to kilograms:
        2. Change the note to uppercase
       
        pound_eaten decimal external terminated by  ","
                    ":pounds_eaten * 2.2",
     

      Control file:
     
         load data
           infile 'animal_feeding_expr.csv'
             badfile 'animal_feeding_expr'
         append
         into table animal_feeding
           trailing nullcols
           (
             animal_id         integer external terminated by "," ,
             feeding_date      date "dd-mon-yyyy" terminated by "," ,
             pounds_eaten      decimal external terminated by "," ,
                               ":pounds_eaten * 2.2",
             note              char terminated by ","
                               optional enclosed by '"'
                               "upper(:note)"
           )
        
     
      True insert operation:
     
        Insert into animal_feeding (animal_id,feeding_date,pounds_eaten,note)
        values (:animal_id,:feeding_date,:pounds_eaten * 2.2,:note)
       

Q:  Can i load fixed_width data ?

A:  Certainly.

    Example:
   
       CSV file:
        
         10001-jan-200402350Flipper seemed unusally hungry today.
         10101-jan-200409945Spread over three meals.
         10201-jan-200401000No comment
         10301-jan-200405500
         10401-jan-200400250Nosey wasn't very hungry
        
         ...
        
        
      Control file:
     
        load data
          infile 'animal_feeding_fixed_1.dat'
            badfile 'animal_feeding_fixed_1'
          infile ''animal_feeding_fixed_2.dat'    
            badfile 'animal_feeding_fixed_2'
          append
          into table animal_feeding
            trailing nullcols
            ( 
               animal_id      position (1:3)  interger external,
               feeding_date   position (4:14) date "dd-mon-yyyy",
               pounds_eaten   position (15:19) zoned (5,2),
               note position  position (20:99) char
            )
           

Q:  My csv file consists of physical records(not included in one row), How to load it?

A:  Look at this:

    Example:
   
      CSV file:
     
         100,4-jan-2004,23.5,
         "Flipper seemed unusally hungary today."
         101,4-jan-2004,90.15,
         "Spread over three meals."
         102,4-jan-2004,10,
         "No comment."
         103,5-jan-2004,55
         104,5-jan-2004,1.5,
         "Nosey wasn't very hungry today."
        
         ...
   
     
      Control file:
     
        load data
          infile 'animal_feeding_concat.csv'
            badfile 'animal_feeding_concat'
          append
          continue last = ","
          into table animal_feeding
            trailing nullcols
            (
               animal_id      interger external terminated by ",",
               feeding_date   date "dd-mon-yyyy" terminated by ",",
               pounds_eaten   decimal external terminated by ",",
               note           char terminated by ","
                              optional enclosed by '"'
            )