測試TOM=SQLLDR函式使用1

oracle_db發表於2012-05-14
SQL函式
一個列可以是對一個或者多個其它列引用函式的結果，類似拼接字串那種意思，只不過這裡是列孫是字元

測試
會話1：建立新的控制檔案
[oracle@oraclelinux ~]$ vi dept_load11.ctl

load data
infile *
into table dept_load
replace
fields terminated by ','
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
last_updated date 'dd/mm/yyyy',
entire_line ":deptno||:dname||:loc||:last_updated"
)
begindata

10,sales,virginia,1/5/2000
20,accounting,virginia,21/6/1999
30,consulting,virginia,5/1/2000
40,finance,virginia,15/3/2001
會話1：載入資料
[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load11.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 13:37:30 2012

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

會話1：檢視錯誤日誌：
[oracle@oraclelinux ~]$ cat dept_load11.log

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 13:37:30 2012

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

Control File:   dept_load11.ctl
Data File:      dept_load11.ctl
  Bad File:     dept_load11.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table DEPT_LOAD, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER            
DNAME                                NEXT     *   ,       CHARACTER            
    SQL string for column : "upper(:dname)"
LOC                                  NEXT     *   ,       CHARACTER            
    SQL string for column : "upper(:loc)"
LAST_UPDATED                         NEXT     *   ,       DATE dd/mm/yyyy      
ENTIRE_LINE                          NEXT     *   ,       CHARACTER            
    SQL string for column : ":deptno||:dname||:loc||:last_updated"

Record 1: Rejected - Error on table DEPT_LOAD, column DEPTNO.
Column not found before end of logical record (use TRAILING NULLCOLS)==沒等處理完所有列，記錄中就沒有資料了
Record 2: Rejected - Error on table DEPT_LOAD, column ENTIRE_LINE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table DEPT_LOAD, column ENTIRE_LINE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table DEPT_LOAD, column ENTIRE_LINE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 5: Rejected - Error on table DEPT_LOAD, column ENTIRE_LINE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 6: Rejected - Error on table DEPT_LOAD, column DEPTNO.
Column not found before end of logical record (use TRAILING NULLCOLS)

Table DEPT_LOAD:
  0 Rows successfully loaded.
  6 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  82560 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             6
Total logical records rejected:         6
Total logical records discarded:        0

Run began on Mon May 14 13:37:30 2012
Run ended on Mon May 14 13:37:30 2012

Elapsed time was:     00:00:00.28
CPU time was:         00:00:00.08
[oracle@oraclelinux ~]$ 

會話1：修改控制檔案

因為資料沒處理完，就沒記錄，所以入果輸入記錄中，不存在某一列的資料，SQLLDR就給該列一個空值，通過TRALING NULLCOLS 會導致繫結變數：ENTIRE_LINE會成為NULL.

[oracle@oraclelinux ~]$ cat dept_load12.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_LOAD
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy',
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,finance,virginia,15/3/2001
[oracle@oraclelinux ~]$ 

會話1：載入資料

[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load12.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 13:48:44 2012

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

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

SQL> select * from dept_load;

    DEPTNO DNAME          LOC           ENTIRE_LINE                   LAST_UPDA
---------- -------------- ------------- ----------------------------- ---------
        10 SALES          VIRGINIA      10SalesVirginia1/5/2000       01-MAY-00
        20 ACCOUNTING     VIRGINIA      20AccountingVirginia21/6/1999 21-JUN-99
        30 CONSULTING     VIRGINIA      30ConsultingVirginia5/1/2000  05-JAN-00
        40 FINANCE        VIRGINIA      40financevirginia15/3/2001    15-MAR-01

SQL> 

為什麼會是這個效果？SQLLDR在檢視控制檔案中的列，並根據這些列建立繫結變數。

以沒有任何函式的情況為例子，SQLLDR構建INSERT過程如下

INSERT INTO DEPT ( DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE )
VALUES ( :DEPTNO, :DNAME, :LOC, :LAST_UPDATED, :ENTIRE_LINE );
                                     ||                      ||
                                     ||                      ||
                           ****** 解析輸入量 *********
                                               ||
                         ** *****將值給繫結變數，執行語句****                

有函式的情況

INSERT INTO T (DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE)
VALUES ( :DEPTNO, upper(:dname), upper(:loc), :last_updated,
:deptno||:dname||:loc||:last_updated );=====》解析=====》輸入繫結到語句，並執行   

上面的測試情況屬於第2種有函式的情況
注：SQL能做的事，SQLLDR都可以結合做。
測試結束

                 
    


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

相關文章