測試TOM==SQLLDR載入固定格式資料

oracle_db發表於2012-05-14
SQL
如果平面檔案中的資料格式固定，如NAME1-5位元組，CITY6-10位元組。這種格式的資料最適合用SQLLDR來處理，載入的速度也最快，要實現這一功能只需要用到SQLLDR的POSITION選項，這個選項是用來指定列的資料位置範圍的。
測試開始
會話1：清空測試表，重建控制檔案
SQL> select * from dept_load;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 sales          virginia

SQL> truncate table dept_load;

Table truncated.

SQL> 

[oracle@oraclelinux ~]$ ls -ltr dept*
-rw-r--r--  1 oracle oinstall  123 May 14 11:06 dept_load4.ctl
-rw-r--r--  1 oracle oinstall 1548 May 14 11:07 dept_load4.log
[oracle@oraclelinux ~]$ cp dept_load4.ctl dept_load5.ctl
[oracle@oraclelinux ~]$ rm -f dept_load4*
[oracle@oraclelinux ~]$ ls -ltr dept*
-rw-r--r--  1 oracle oinstall 123 May 14 11:17 dept_load5.ctl
[oracle@oraclelinux ~]$ vi dept_load5.ctl

      1 load data
      2 infile *
      3 into table dept_load
      4 replace
      5 (deptno position(1:2),
      6 dname position(3:16),
      7 loc position(17:29)
      8 )
      9 begindata
     10 10accounting virginia,usa
~
~
會話1：載入資料
[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load5.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 11:22:30 2012

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

Commit point reached - logical record count 1
[oracle@oraclelinux ~]$ !
bash: syntax error near unexpected token `newline'
[oracle@oraclelinux ~]$ exit
exit

SQL> select * from dept_load;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 accounting vir ginia,usa

SQL> 
測試結束
這個控制檔案中沒有使用FIELDS TERMINATED BY,使用POSITION來指定列從那開始，從那裡結束
關於POSITON選項的另一用法，利用它在記錄中來回反覆，不過好像沒有什麼實際意義。

測試開始：

會話1：重建控制檔案
[oracle@oraclelinux ~]$ ls -ltr dept*
-rw-r--r--  1 oracle oinstall  151 May 14 11:20 dept_load5.ctl
-rw-r--r--  1 oracle oinstall 1548 May 14 11:22 dept_load5.log
[oracle@oraclelinux ~]$ cp dept_load6.ctl
cp: missing destination file
Try `cp --help' for more information.
[oracle@oraclelinux ~]$ cp dept_load5.ctl dept_load6.ctl
[oracle@oraclelinux ~]$ rm -f dept_load5*
[oracle@oraclelinux ~]$ ls -ltr dept_*
-rw-r--r--  1 oracle oinstall 151 May 14 11:29 dept_load6.ctl
[oracle@oraclelinux ~]$ vi dept_load6.ctl

      1 load data
      2 infile *
      3 into table dept_load
      4 replace
      5 (deptno position(1:2),
      6 dname position(3:16),
      7 loc position(17:29),
      8 entire_line position(1:29)
      9 )
     10 begindata
     11 10accounting virginia,usa
會話1：載入資料
oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load6.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 11:33:03 2012

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

Commit point reached - logical record count 1
[oracle@oraclelinux ~]$ exit
exit

SQL> select * from dept_load;

    DEPTNO DNAME          LOC           ENTIRE_LINE
---------- -------------- ------------- -----------------------------
        10 accounting vir ginia,usa     10accounting virginia,usa

SQL> 
這裡把所有記錄放在了一個欄位裡邊
測試結束
關於POSITON選項的另一說明：在用這個選項的時候可以使用，相對便宜量和絕對便宜量，使用絕對全稱量得去計算資料的開始結束位置，如果很多列的話，這會讓人覺得很煩！
測試相對偏移量開始
會話1：清空測試表，重新控制檔案
[oracle@oraclelinux ~]$ vi dept_load7.ctl

      1 load data
      2 infile *
      3 into table dept_load
      4 replace
      5 (deptno position(1:2),
      6 dname position(*:16),
      7 loc position(*:29),
      8 entire_line position(1:29)
      9 )
     10 begindata
     11 10accounting virginia,usa
~
會話1：載入資料
[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load7.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 11:42:13 2012

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

Commit point reached - logical record count 1
[oracle@oraclelinux ~]$ 
[oracle@oraclelinux ~]$ exit
exit

SQL> select * from dept_load;

    DEPTNO DNAME          LOC           ENTIRE_LINE
---------- -------------- ------------- -----------------------------
        10 accounting vir ginia,usa     10accounting virginia,usa

SQL> 
可以看到效果和使用絕對偏移量是一樣的，這裡使用*號來自動計算上一個列結束的位置，另外使用*號時也可以把它和偏移量相加，像這樣（*+2：16），這就表示從上一欄位結束後在加上兩個位置做為新欄位的起始位置，新欄位結束位置16.
測試偏移量結束
POSITION選項另一說明：POSITION的結束位置必須是資料結束的絕對位置，有些時候可能指定每個欄位的長度更省事，特別是欄位是連續的情況下。採用這種方式只需要告訴SQLLDR:記錄從第1個位元組開始，然後指定每個列的長度就可以了，這樣不需要計算開始和結束偏移量。

測試指定列長度開始
會話1：清空測試資料，重建控制檔案，載入資料
SQL> truncate table dept_load;

Table truncated.

SQL> 1
  1* truncate table dept_load
SQL> !
[oracle@oraclelinux ~]$ ls -ltr dept*
-rw-r--r--  1 oracle oinstall  178 May 14 11:41 dept_load7.ctl
-rw-r--r--  1 oracle oinstall 1628 May 14 11:42 dept_load7.log
[oracle@oraclelinux ~]$ rm -f dept_load7*
[oracle@oraclelinux ~]$ vi dept_load8.ctl

      1 load data
      2 infile *
      3 into table dept
      4 replace
      5 (deptno position(1) char(2),
      6 dname position(*) char(14),
      7 loc position(*) char(13),
      8 entire_line position(1) char(29)
      9 )
     10 begindata
     11 10accounting virginia,usa
[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load8.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 12:16:36 2012

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

Commit point reached - logical record count 2
[oracle@oraclelinux ~]$ exit
exit

SQL> select * from dept_load;

    DEPTNO DNAME          LOC           ENTIRE_LINE
---------- -------------- ------------- -----------------------------
        10 accounting vir ginia,usa     10accounting virginia,usa

SQL> 
測試結束





來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15720542/viewspace-723496/，如需轉載，請註明出處，否則將追究法律責任。

相關文章