SQL*LOAD经常使用的一些例子

来源：互联网发布：dll不兼容windows 编辑：程序博客网时间：2024/06/08 07:17

参考:http://www.orafaq.com/faq/sql_loader

使用SQLLOAD导入变长字符:

LOAD DATA
    INFILE *
    append
    INTO TABLE tmp_test
    FIELDS TERMINATED BY ","
    OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    ( data1,
       data2
    )
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

使用SQLLOAD导入定长字符:

LOAD DATA
     INFILE *
     INTO TABLE load_positional_data
     ( data1 POSITION(1:5),
        data2 POSITION(6:15)
     )
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

使用SQLLOAD导入数据时不导入从文件头开始的指定行数据.

OPTIONS (SKIP 1)
LOAD DATA
INFILE *
skip 1
append
INTO TABLE tmp_test
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
...
#sqlldr sms/admin control=test.ctl skip=1

使用SQLLOAD导入数据时,如何添加/修改数据

LOAD DATA
     INFILE *
     INTO TABLE tmp_test
     ( 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
   )

使用SQL*LOAD一次导入多个文件到同一个表

LOAD DATA
INFILE file1.dat
INFILE file2.dat
INFILE file3.dat
APPEND
INTO TABLE emp
( empno POSITION(1:4)   INTEGER EXTERNAL,
   ename POSITION(6:15) CHAR,
    deptno POSITION(17:18) CHAR,
    mgr    POSITION(20:23) INTEGER EXTERNAL
)

使用SQL*LOAD将一个文件导入不同的表

LOAD DATA
    INFILE *
    INTO TABLE tab1 WHEN tab = 'tab1'
      ( tab FILLER CHAR(4),
        col1 INTEGER
      )
    INTO TABLE tab2 WHEN tab = 'tab2'
      ( tab FILLER POSITION(1:4),
        col1 INTEGER
      )
BEGINDATA
tab1|1
tab1|2
tab2|2
tab3|3
==============
LOAD DATA
    INFILE 'mydata.dat'
    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
    )

使用SQL*LOAD有选择性的导入数据到一个或多个表中

注:
    SQL*LOAD不允许在when语句中使用OR...只能使用AND
    (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) = '20031217'
     (
        region              CONSTANT '31',
        service_key         POSITION(01:11)   INTEGER EXTERNAL,
        call_b_no           POSITION(12:29)   CHAR
     )
==============
LOAD DATA
     INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
     APPEND
     INTO TABLE my_selective_table
     WHEN (01) <> 'H' and (01) <> 'T'
     (
        region              CONSTANT '31',
        service_key         POSITION(01:11)   INTEGER EXTERNAL,
        call_b_no           POSITION(12:29)   CHAR
     )
     INTO TABLE my_selective_table
     WHEN (30:37) = '20031217'
     (
        region              CONSTANT '31',
        service_key         POSITION(01:11)   INTEGER EXTERNAL,
        call_b_no           POSITION(12:29)   CHAR
     )

使用SQL*LOAD导入数据时,过滤数据文件中的指定列

LOAD DATA
    TRUNCATE INTO TABLE T1
    FIELDS TERMINATED BY ','
    ( field1,
      field2 FILLER,
      field3
    )

使用SQL*LOAD导入LOB等大对象

CREATE TABLE image_table (
    image_id   NUMBER(5),
    file_name VARCHAR2(30),
    image_data BLOB);
LOAD DATA
    INFILE *
    INTO TABLE image_table
    REPLACE
    FIELDS TERMINATED BY ','
    (
     image_id   INTEGER(5),
     file_name CHAR(30),
     image_data LOBFILE (file_name) TERMINATED BY EOF
    )
    BEGINDATA
    001,image1.gif
    002,image2.jpg
    003,image3.jpg