ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【SQL*Loader】SQL*Loader实验之(5)--固定长字符串数据处理时position的3种写法

【SQL*Loader】SQL*Loader实验之(5)--固定长字符串数据处理时position的3种写法

原创 Linux操作系统 作者:chicheng_cn421 时间:2011-06-16 07:40:43 0 删除 编辑


【实验内容】
这个实验拿实验(4)的数据文件来稍作修改进行这个实验。

要加载的数据没有分隔符,而是固定长字符串时,可以在SQL*Loader的控制文件中通过position关键字用来指定列的开始和结束位置。position通常有下面3种写法:

■指定绝对偏移量方式
     绝对偏移量方式是指直接指定数值的方式。
     举个例子:
------------------------------------------
     LOAD DATA
     INFILE 'dept2_ShiYan05_01.dat'
     BADFILE 'dept2_ShiYan05_01.bad'
     INTO TABLE dept2
     APPEND
     ( DEPTNO position(1:2), --指定截取数据文件一行记录的第1个字符到第2个字符作为DEPTNO列的列值。
       DNAME position(4:13), --指定截取数据文件一行记录的第4个字符到第13个字符作为DNAME列的列值。
       LOC position(15:22)   --指定截取数据文件一行记录的第15个字符到第22个字符作为LOC列的列值。
     )
------------------------------------------

■指定相对偏移量方式
     相对偏移量方式的最大特点是使用*号,*号代表了上一个字段的结束位置,当前字段的起始位置根据上一字段的结束位置,通过相对偏移运算得出。
     举个例子:
------------------------------------------
     LOAD DATA
     INFILE 'dept2_ShiYan05_02.dat'
     BADFILE 'dept2_ShiYan05_02.bad'
     INTO TABLE dept2
     APPEND
     ( DEPTNO position(1:2), --指定截取数据文件一行记录的第1个字符到第2个字符作为DEPTNO列的列值。
       DNAME position(*+1:13), --指定从上个字段的结束位置开始偏移1个字符的位置作为本列数据的开始位置。即截取数据文件一行记录的第4个字符到第13个字符作为DNAME列的列值。
       LOC position(*+1:22)   --指定从上个字段的结束位置开始偏移1个字符的位置作为本列数据的开始位置。即截取数据文件一行记录的第15个字符到第22个字符作为LOC列的列值。
     )
------------------------------------------

■指定"相对偏移量+类型和长度"的方式
     这种方式实际上是上一种"■指定相对偏移量方式"的变化形式。它的优势在于,你只需要为第一列指定开始位置,其他列只需要指定列长度就可以了,实际使用中比较省事。
     举个例子:
------------------------------------------
     LOAD DATA
     INFILE 'dept2_ShiYan05_03.dat'
     BADFILE 'dept2_ShiYan05_03.bad'
     INTO TABLE dept2
     APPEND
     ( DEPTNO position(1)char(2), --指定从数据文件一行记录的第1个字符开始,截取2个字符作为DEPTNO列的列值。
       DNAME position(*+1)char(10), --指定从上个字段的结束位置开始偏移1个字符的位置作为本列数据的开始位置,截取10个字符作为DNAME列的列值。
       LOC position(*+1)char(8)   --指定从上个字段的结束位置开始偏移1个字符的位置作为本列数据的开始位置,截取8个字符作为LOC列的列值。
     )
------------------------------------------


好,上面三种方法已经简单介绍完毕,现在开始实验。


【实验过程】

■指定绝对偏移量方式

//(1)数据文件dept2_ShiYan05_01.dat
----------------------------------------
14 ACCOUNTING NEW YORK
24 RESEARCH   DALLAS
34 SALES      CHICAGO
44 OPERATIONS BOSTON
----------------------------------------

//(2)控制文件dept2_ShiYan05_01.ctl
------------------------------------------
     LOAD DATA
     INFILE 'dept2_ShiYan05_01.dat'
     BADFILE 'dept2_ShiYan05_01.bad'
     INTO TABLE dept2
     APPEND
     ( DEPTNO position(1:2), --指定截取数据文件一行记录的第1个字符到第2个字符作为DEPTNO列的列值。
       DNAME position(4:13), --指定截取数据文件一行记录的第4个字符到第13个字符作为DNAME列的列值。
       LOC position(15:22)   --指定截取数据文件一行记录的第15个字符到第22个字符作为LOC列的列值。
     )
------------------------------------------

//(3)数据导入前dept2表中的数据状况
--------------------
C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 6月 16 07:25:34 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn scott@test
输入口令:
已连接。
SQL> set feedback on
SQL> set linesize 3000
SQL> set pagesize 3000
SQL> select * from dept2;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        13 ACCOUNTING     NEW YORK
        23 RESEARCH       DALLAS
        33 SALES          CHICAGO
        43 OPERATIONS     BOSTON
        12 ACCOUNTING     NEW YORK
        22 RESEARCH       DALLAS
        32 SALES,LVA      CHICAGO
        42 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        11 ACCOUNTING     NEW YORK
        21 RESEARCH       DALLAS
        31 SALES          CHICAGO
        41 OPERATIONS     BOSTON

已选择16行。

SQL>
--------------------

//(4)启动并执行sqlloader。

sqlldr scott/tiger control="dept2_ShiYan05_01.ctl"  log="dept2_ShiYan05_01.log"

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

D:\Oracle学习\test\sqlloader\实验5>sqlldr scott/tiger control="dept2_ShiYan05_01.ctl"  log="dept2_ShiYan05_01.log"

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 6月 16 07:30:07 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 3
达到提交点 - 逻辑记录计数 4

D:\Oracle学习\test\sqlloader\实验5>

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

产生的Log文件
--------------------

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 6月 16 07:30:07 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

控制文件:      dept2_ShiYan05_01.ctl
数据文件:      dept2_ShiYan05_01.dat
  错误文件:    dept2_ShiYan05_01.bad
  废弃文件:    未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 50
绑定数组: 64 行, 最大 256000 字节
继续:    未作指定
所用路径:       常规

表 DEPT2,已加载从每个逻辑记录
插入选项对此表 APPEND 生效

   列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                                1:2     2           CHARACTER           
DNAME                                4:13    10           CHARACTER           
LOC                                 15:22     8           CHARACTER           


表 DEPT2:
  4 行 加载成功。
  由于数据错误, 0 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空的, 0 行 没有加载。


为绑定数组分配的空间:                  1664 字节 (64 行)
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:          0
读取的逻辑记录总数:             4
拒绝的逻辑记录总数:          0
废弃的逻辑记录总数:        0

从 星期四 6月  16 07:30:07 2011 开始运行
在 星期四 6月  16 07:30:08 2011 处运行结束

经过时间为: 00: 00: 00.69
CPU 时间为: 00: 00: 00.22

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

执行后 dept2中的数据状况。
--------------------

SQL> select * from dept2;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        13 ACCOUNTING     NEW YORK
        23 RESEARCH       DALLAS
        33 SALES          CHICAGO
        43 OPERATIONS     BOSTON
        14 ACCOUNTING     NEW YORK
        24 RESEARCH       DALLAS
        34 SALES          CHICAGO
        44 OPERATIONS     BOSTON
        12 ACCOUNTING     NEW YORK
        22 RESEARCH       DALLAS
        32 SALES,LVA      CHICAGO
        42 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        11 ACCOUNTING     NEW YORK
        21 RESEARCH       DALLAS
        31 SALES          CHICAGO
        41 OPERATIONS     BOSTON

已选择20行。

SQL>
--------------------


■指定相对偏移量方式

//(1)数据文件dept2_ShiYan05_02.dat
----------------------------------------
15 ACCOUNTING NEW YORK
25 RESEARCH   DALLAS
35 SALES      CHICAGO
45 OPERATIONS BOSTON
----------------------------------------

//(2)控制文件dept2_ShiYan05_02.ctl
------------------------------------------
     LOAD DATA
     INFILE 'dept2_ShiYan05_02.dat'
     BADFILE 'dept2_ShiYan05_02.bad'
     INTO TABLE dept2
     APPEND
     ( DEPTNO position(1:2), --指定截取数据文件一行记录的第1个字符到第2个字符作为DEPTNO列的列值。
       DNAME position(*+1:13), --指定从上个字段的结束位置开始偏移1个字符的位置作为本列数据的开始位置。即截取数据文件一行记录的第4个字符到第13个字符作为DNAME列的列值。
       LOC position(*+1:22)   --指定从上个字段的结束位置开始偏移1个字符的位置作为本列数据的开始位置。即截取数据文件一行记录的第15个字符到第22个字符作为LOC列的列值。
     )
------------------------------------------

//(3)数据导入前dept2表中的数据状况
--------------------

SQL> select * from dept2;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        13 ACCOUNTING     NEW YORK
        23 RESEARCH       DALLAS
        33 SALES          CHICAGO
        43 OPERATIONS     BOSTON
        14 ACCOUNTING     NEW YORK
        24 RESEARCH       DALLAS
        34 SALES          CHICAGO
        44 OPERATIONS     BOSTON
        12 ACCOUNTING     NEW YORK
        22 RESEARCH       DALLAS
        32 SALES,LVA      CHICAGO
        42 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        11 ACCOUNTING     NEW YORK
        21 RESEARCH       DALLAS
        31 SALES          CHICAGO
        41 OPERATIONS     BOSTON

已选择20行。

SQL>
--------------------


//(4)启动并执行sqlloader。

sqlldr scott/tiger control="dept2_ShiYan05_02.ctl"  log="dept2_ShiYan05_02.log"

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

D:\Oracle学习\test\sqlloader\实验5>sqlldr scott/tiger control="dept2_ShiYan05_02.ctl"  log="dept2_ShiYan05_02.log"

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 6月 16 07:32:28 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 3
达到提交点 - 逻辑记录计数 4

D:\Oracle学习\test\sqlloader\实验5>
--------------------

产生的Log文件
--------------------

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 6月 16 07:32:28 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

控制文件:      dept2_ShiYan05_02.ctl
数据文件:      dept2_ShiYan05_02.dat
  错误文件:    dept2_ShiYan05_02.bad
  废弃文件:    未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 50
绑定数组: 64 行, 最大 256000 字节
继续:    未作指定
所用路径:       常规

表 DEPT2,已加载从每个逻辑记录
插入选项对此表 APPEND 生效

   列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                                1:2     2           CHARACTER           
DNAME                           NEXT+1:13    12           CHARACTER           
LOC                             NEXT+1:22    21           CHARACTER           


表 DEPT2:
  4 行 加载成功。
  由于数据错误, 0 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空的, 0 行 没有加载。


为绑定数组分配的空间:                  2560 字节 (64 行)
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:          0
读取的逻辑记录总数:             4
拒绝的逻辑记录总数:          0
废弃的逻辑记录总数:        0

从 星期四 6月  16 07:32:28 2011 开始运行
在 星期四 6月  16 07:32:29 2011 处运行结束

经过时间为: 00: 00: 00.37
CPU 时间为: 00: 00: 00.23

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

执行后 dept2中的数据状况。
--------------------

SQL> select * from dept2;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        13 ACCOUNTING     NEW YORK
        23 RESEARCH       DALLAS
        33 SALES          CHICAGO
        43 OPERATIONS     BOSTON
        14 ACCOUNTING     NEW YORK
        24 RESEARCH       DALLAS
        34 SALES          CHICAGO
        44 OPERATIONS     BOSTON
        15 ACOUNTING      NEW YORK
        25 RESEARCH        DALLAS
        35 SALES           CHICAGO
        45 OPERATIONS      BOSTON
        12 ACCOUNTING     NEW YORK
        22 RESEARCH       DALLAS
        32 SALES,LVA      CHICAGO
        42 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        11 ACCOUNTING     NEW YORK
        21 RESEARCH       DALLAS
        31 SALES          CHICAGO
        41 OPERATIONS     BOSTON

已选择24行。

SQL>
--------------------


■指定"相对偏移量+类型和长度"的方式

//(1)数据文件dept2_ShiYan05_03.dat
----------------------------------------
16 ACCOUNTING NEW YORK
26 RESEARCH   DALLAS
36 SALES      CHICAGO
46 OPERATIONS BOSTON
----------------------------------------

//(2)控制文件dept2_ShiYan05_03.ctl
------------------------------------------
     LOAD DATA
     INFILE 'dept2_ShiYan05_03.dat'
     BADFILE 'dept2_ShiYan05_03.bad'
     INTO TABLE dept2
     APPEND
     ( DEPTNO position(1)char(2), --指定从数据文件一行记录的第1个字符开始,截取2个字符作为DEPTNO列的列值。
       DNAME position(*+1)char(10), --指定从上个字段的结束位置开始偏移1个字符的位置作为本列数据的开始位置,截取10个字符作为DNAME列的列值。
       LOC position(*+1)char(8)   --指定从上个字段的结束位置开始偏移1个字符的位置作为本列数据的开始位置,截取8个字符作为LOC列的列值。
     )
------------------------------------------

//(3)数据导入前dept2表中的数据状况
--------------------
SQL> select * from dept2;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        13 ACCOUNTING     NEW YORK
        23 RESEARCH       DALLAS
        33 SALES          CHICAGO
        43 OPERATIONS     BOSTON
        14 ACCOUNTING     NEW YORK
        24 RESEARCH       DALLAS
        34 SALES          CHICAGO
        44 OPERATIONS     BOSTON
        15 ACOUNTING      NEW YORK
        25 RESEARCH        DALLAS
        35 SALES           CHICAGO
        45 OPERATIONS      BOSTON
        12 ACCOUNTING     NEW YORK
        22 RESEARCH       DALLAS
        32 SALES,LVA      CHICAGO
        42 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        11 ACCOUNTING     NEW YORK
        21 RESEARCH       DALLAS
        31 SALES          CHICAGO
        41 OPERATIONS     BOSTON

已选择24行。

SQL>
--------------------


//(4)启动并执行sqlloader。

sqlldr scott/tiger control="dept2_ShiYan05_03.ctl"  log="dept2_ShiYan05_03.log"

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

D:\Oracle学习\test\sqlloader\实验5>sqlldr scott/tiger control="dept2_ShiYan05_03.ctl"  log="dept2_ShiYan05_03.log"

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 6月 16 07:38:06 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 3
达到提交点 - 逻辑记录计数 4

D:\Oracle学习\test\sqlloader\实验5>

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

产生的Log文件
--------------------

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 6月 16 07:38:06 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

控制文件:      dept2_ShiYan05_03.ctl
数据文件:      dept2_ShiYan05_03.dat
  错误文件:    dept2_ShiYan05_03.bad
  废弃文件:    未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 50
绑定数组: 64 行, 最大 256000 字节
继续:    未作指定
所用路径:       常规

表 DEPT2,已加载从每个逻辑记录
插入选项对此表 APPEND 生效

   列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                                  1     2           CHARACTER           
DNAME                              NEXT+1    10           CHARACTER           
LOC                                NEXT+1     8           CHARACTER           


表 DEPT2:
  4 行 加载成功。
  由于数据错误, 0 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空的, 0 行 没有加载。


为绑定数组分配的空间:                  1664 字节 (64 行)
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:          0
读取的逻辑记录总数:             4
拒绝的逻辑记录总数:          0
废弃的逻辑记录总数:        0

从 星期四 6月  16 07:38:06 2011 开始运行
在 星期四 6月  16 07:38:07 2011 处运行结束

经过时间为: 00: 00: 00.52
CPU 时间为: 00: 00: 00.31

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

执行后 dept2中的数据状况。
--------------------

SQL> select * from dept2;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        13 ACCOUNTING     NEW YORK
        23 RESEARCH       DALLAS
        33 SALES          CHICAGO
        43 OPERATIONS     BOSTON
        14 ACCOUNTING     NEW YORK
        24 RESEARCH       DALLAS
        34 SALES          CHICAGO
        44 OPERATIONS     BOSTON
        15 ACOUNTING      NEW YORK
        25 RESEARCH        DALLAS
        35 SALES           CHICAGO
        45 OPERATIONS      BOSTON
        12 ACCOUNTING     NEW YORK
        22 RESEARCH       DALLAS
        32 SALES,LVA      CHICAGO
        42 OPERATIONS     BOSTON
        16 ACCOUNTING     NEW YORK
        26 RESEARCH       DALLAS
        36 SALES          CHICAGO
        46 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        11 ACCOUNTING     NEW YORK
        21 RESEARCH       DALLAS
        31 SALES          CHICAGO
        41 OPERATIONS     BOSTON

已选择28行。

SQL>
--------------------

以上。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20335819/viewspace-698052/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-05

  • 博文量
    261
  • 访问量
    377990