SqlLoad 使用

一、导入文件的区分

----执行语句
sqlldr user/passwd@SID control="\hm.txt"

-----------------------------自己的模板

LOAD DATA
CHARACTERSET ZHS16GBK                                                        #让导入的为 中文 防止乱码
infile '/home/oracle/D0807020090224000001.AVL' "str X'0A'"       #"str X'0A'"   表示 以'0A'结尾
Append
INTO TABLE TD_B_PACKAGE
FIELDS TERMINATED BY X'01'
(PACKAGE_ID
,PACKAGE_NAME
,PACKAGE_TYPE_CODE
,PACKAGE_DESC
,START_DATE    "to_date(:START_DATE,'''YYYYMMDDHH24MISS''')"
,END_DATE      "to_date(:END_DATE,'''YYYYMMDDHH24MISS''')"
,UPDATE_STAFF_ID
,UPDATE_DEPART_ID
,UPDATE_TIME   "to_date(:UPDATE_TIME,'''YYYYMMDDHH24MISS''')"
,MIN_NUMBER
,MAX_NUMBER
,NEED_EXP)

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

今天有朋友询问：
我们...应用系统很多，它们之间要交换很多数据，目前是以文本方式交换，问题是文本的分隔符号是（|+|），为三个字符，主要是避免数据的混淆.....

这样的文件能够用sqlldr导入么?

我测试了一下，试验证明是可以的，sqlldr支持多字符分隔符文件导入。
首先看我的数据文件和控制文件:

[oracle@jumper tmp]$ cat data.ctl
load data
into table TEST
fields terminated by "|+|"
(
T_ID,
T_VOL
)
[oracle@jumper tmp]$ cat data.txt
20021228000000|+|00120000
20021228000000|+|00130000
20021228000000|+|00140000
20021||8000000|+|00140000
20021++8000000|+|00140000

创建测试表:

[oracle@jumper tmp]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 21 13:21:16 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> create table test
2 (T_ID varchar2(20),
3 T_VOL varchar2(20)
4 );

Table created.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

加载数据:

[oracle@jumper tmp]$ sqlldr eygle/eygle control=data.ctl data=data.txt

SQL*Loader: Release 9.2.0.4.0 - Production on Tue Nov 21 13:23:53 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 5
[oracle@jumper tmp]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 21 13:23:57 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> select * from test;

T_ID T_VOL
-------------------- --------------------
20021228000000 00120000
20021228000000 00130000
20021228000000 00140000
20021||8000000 00140000
20021++8000000 00140000

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

---------------------------------------------------------------------------------------------
参考: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

-----------------------以上转自http://blog.itpub.net/9700459/viewspace-671934/--------------------------------------------------------------------------------------------------------------------------------------------------------------------------