Oracle數據庫SqlLoad常用技巧總結
作者：nsj820
本文我們主要介紹了Oracle數據庫SqlLoad常用技巧的相關知識，共包括14種常用的使用技巧以及測試用文件的源代碼，希望能夠對您有所收獲！
Oracle數據庫SqlLoad常用技巧的相關知識是本文我們主要要介紹的內容，本文我們總結了14種SqlLoad的使用技巧，并給出了測試用的文件源碼，接下來我們就開始一一介紹這部分內容，希望能夠對您有所幫助。
1、控制文件中注釋用“--”。
2、為防止導入出現中文亂碼，在控制文件中加入字符集控制
- LOAD DATA
- CHARACTERSET ZHS16GBK
3、讓某一列成為行號，用RECNUM關鍵字
- load data
- infile *
- into table t
- replace
- ( seqno RECNUM //載入每行的行號
- text Position(1:1024))
- BEGINDATA
- fsdfasj
4、過濾某一列，用FILLER關鍵字
- LOAD DATA
- TRUNCATE INTO TABLE T1
- FIELDS TERMINATED BY ','
- ( field1,
- field2 FILLER,
- field3
- )
5、過濾行
在INTO TABLE table_name后加WHEN過濾條件，但功能有限，如果以豎線分隔符的文件，不能實現字段級的過濾，定長的還好。
- 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
- )
6、過濾首行，用OPTIONS (SKIP 1)選項，也可以寫在命令行中，如：
sqlldr sms/admin control=test.ctl skip=1
7、TRAILING NULLCOLS的使用，作用是表的字段沒有對應的值時允許為空
如：
- LOAD DATA
- INFILE *
- INTO TABLE DEPT
- REPLACE
- FIELDS TERMINATED BY ','
- TRAILING NULLCOLS // 其實下面的ENTIRE_LINE在BEGINDATA后面的數據中是沒有直接對應的列的值的如果第一行改為 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
- (DEPTNO,
- DNAME "upper(:dname)", // 使用函數
- LOC "upper(:loc)",
- LAST_UPDATED date 'dd/mm/yyyy', // 日期的一種表達方式 還有'dd-mon-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
8、添加、修改數據
（1）、
- 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
（2）、
- 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
- )
9、合并多行記錄為一行記錄
通過關鍵字concatenate 把幾行的記錄看成一行記錄：
- LOAD DATA
- INFILE *
- concatenate 3 // 通過關鍵字concatenate 把幾行的記錄看成一行記錄
- INTO TABLE DEPT
- replace
- FIELDS TERMINATED BY ','
- (DEPTNO,
- DNAME "upper(:dname)",
- LOC "upper(:loc)",
- LAST_UPDATED date 'dd/mm/yyyy'
- )
- BEGINDATA
- 10,Sales, // 其實這3行看成一行 10,Sales,Virginia,1/5/2000
- Virginia,
- 1/5/2000
10、用”|+|”分隔符，避免數據混淆：fields terminated by "|+|"
11、如果數據文件包含在控制文件中，用INFILE *
如下：
- 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,"
12、一次導入多個文件到同一個表
- 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
- )
13、將一個文件導入到不同的表
（1）、
- 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
- ==============
（2）、
- 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
- )
14、過濾掉的數據文件路徑指定
- /opt/app/oracle/product/10.2.0/bin/sqlldr APS/APS control=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTL LOG=/home/oracle/APS_LOAD/log/$yesterday/AP_CONTRACT_$yesterday.log bad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.bad rows=10000 readsize=20000000 bindsize=20000000 DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis
15、附：測試用控制文件
- LOAD DATA
- INFILE '/home/oracle/APS_LOAD/dat/APS_AP_CONTRACT.dat'
- TRUNCATE
- INTO TABLE AP_CONTRACT
- WHEN (01)<>'1'
- FIELDS TERMINATED BY "|"
- TRAILING NULLCOLS
- (
- AGMT_NO "(TRIM(:AGMT_NO ))",
- CONTRACT_NO FILLER, -- "(TRIM(:CONTRACT_NO ))",
- LOAN_AMT "(TRIM(:LOAN_AMT ))",
- AGMT_HOLDER "(TRIM(:AGMT_HOLDER ))",
- LOAN_TYPE_CD "(TRIM(:LOAN_TYPE_CD ))",
- CURR_CD "(TRIM(:CURR_CD ))",
- BALANCE "(TRIM(:BALANCE ))",
- LOAN_DIRC_CD "(TRIM(:LOAN_DIRC_CD ))",
- AGMT_START_DATE "(TRIM(:AGMT_START_DATE ))",
- AGMT_END_DATE "(TRIM(:AGMT_END_DATE ))",
- AGMT_BELONG_ORG_NO "(TRIM(:AGMT_BELONG_ORG_NO ))",
- MANAGER_NO "(TRIM(:MANAGER_NO ))",
- PROCESS_RATE "(TRIM(:PROCESS_RATE ))",
- INSURE_METH_TYPE_CD "(TRIM(:INSURE_METH_TYPE_CD ))",
- AGMT_SIGN_DATE "(TRIM(:AGMT_SIGN_DATE ))",
- LOAN_PROP_CD "(TRIM(:LOAN_PROP_CD ))",
- LOAN_USE_TYPE "(TRIM(:LOAN_USE_TYPE ))",
- ENTRUST_LOAN_FLAG "(TRIM(:ENTRUST_LOAN_FLAG ))",
- ENTRUST_NAME "(TRIM(:ENTRUST_NAME ))",
- FARM_LOAN_FLAG "(TRIM(:FARM_LOAN_FLAG ))",
- FARM_LOAN_TYPE_CD "(TRIM(:FARM_LOAN_TYPE_CD ))",
- LOAN_BIZ_TYPE_CD "(TRIM(:LOAN_BIZ_TYPE_CD ))",
- ID_TEST RECNUM ,
- CHAR_TEST CONSTANT '31',
- SQ "sqlldr.nextval",
- TEST_4 "TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')",
- TEST_5 "(TRIM(:LOAN_BIZ_TYPE_CD)||'---'||TRIM(:AGMT_NO))"
- )
關于Oracle數據庫SqlLoad常用技巧的相關知識就介紹到這里了，希望本次的介紹能夠對您有所收獲！
【編輯推薦】
責任編輯：趙鵬
來源：
CSDN博客
