溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
SQL*Loader對不同文件及格式的處理方法
一般的Excel文件最大行數不超過65536行，說明數據處理量并不大，處理Excel的方式是將其另存為CSV格式文件,然后即可按照正常方式導入即可。
有兩種方式可以參考：
1)修改數據文件，將分隔符替換為逗號。
2)修改控制文件，將FIELDS TERMINATED BY的值修改為實際的分隔符。
例如,要向scott.tb_loader表插入數據提供的數據格式如下：
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523
修改控制文件,注意下列示例代碼中的粗體字符,OPTIONALLY ENCLOSED BY參數指明定界符為雙引號(CSV格式文件默認定界符就是雙引號,你可以根據實際情況修改OPTIONALLY的參數值),如下所示：
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test2.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test2.dat'
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(ENAME,JOB,SAL)
--數據文件
[oracle@wjq SQL*Loader]$ vim wjq_test2.dat
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523sqlldr
運行如上代碼，并查詢結果如下：
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test2.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:56:40 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
--查詢結果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLEAK 3904
ALLEN SALER,M 2891
WARD SALER,"S" 3128
KING PRESIDENT 2523
如下的數據文件專業叫做定長字符串，sqlldr中處理定長字符串也輕而易舉。針對此例，我們將控制文件修改如下：
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test3.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26)
)
--數據文件
[oracle@wjq SQL*Loader]$ vim wjq_test3.dat
SMITH CLEAK 3904
ALLEN SALESMAN 2891
WARD SALESMAN 3128
KING PRESIDENT 252
position關鍵字用來指定列的開始和結束位置，如JOB position(10:18)是指從第10個字符開始截止到第18個字符作為ENAME列的列值。position的寫法也很靈活，要實現上述功能還可以換成下列幾種形式：
①position(*+2:18):直接指定數值的方式叫作絕對偏移量，如果使用*號，專業名詞叫相對偏移量，表示上一個字段從哪里結束，這次就從哪里開始，相對偏移量也 可以再做運算，比如Position(*+2:15)就表示從上次結束的位置+2的地方開始。
②position(*) char(9):這種相對偏移量+類型和長度的優勢在于，你只需要為第一列 指定開始位置，其他列只需要指定列長度就可以了，實際使用中比較省事。
sqlldr運行如上代碼，并查詢結果如下：
--sqlldr命令
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger
control=/u01/app/oracle/SQL*Loader/wjq_test3.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:04:13 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
--查詢結果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLEAK 3904
ALLEN SALESMAN 2891
WARD SALESMAN 3128
KING PRESIDENT 252
在前面幾個例子中，數文件中的列比表中的列要少的環境中演示的，這說明列少不怕，關鍵是看控制文件中的配置。但是如果缺少的列必須賦值又怎么辦呢？只需稍改下控制文件即可，直接指定COMM列，并賦初始值0(這里仍然引用ldr_case3.dat中的數據）：
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test4.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "0"
)
--sqlldr命令
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger
control=/u01/app/oracle/SQL*Loader/wjq_test4.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:08:50 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
--查看結果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLEAK 3904 0
ALLEN SALESMAN 2891 0
WARD SALESMAN 3128 0
KING PRESIDENT 252 0
COMM的值也可以根據其他列的值而定，修改控制文件如下
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test5.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "substr(:SAL,1,1)"
)
sqlldr執行上述代碼,結果如下，很明顯發現COMM的值是根據SAL的值的第1位數字獲得
--sqlldr命令
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger
control=/u01/app/oracle/SQL*Loader/wjq_test5.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:12:00 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
--執行結果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLEAK 3904 3
ALLEN SALESMAN 2891 2
WARD SALESMAN 3128 3
KING PRESIDENT 252 2
這里COMM列的值根據SAL列值而定，我們通過一個SQL中的函數substr取SAL值的第一列，賦予COMM列，當然這只是一個示例，DBA可以根據實際需求進行適當的 修改，通過SQL中的函數可以實現很多很有意思的轉換，也許能夠為你省下很大力氣，而且如果現有函數無法實現，甚至可以通過PL/SQL編寫自定義的函數，然后在sqlldr的 控制文件中調用，調用方式與系統自帶函數方式完全相同，這樣就可以根據需求對要加載 的列做審靈活的處理。
如果數據文件中的列比要導入的表中的列少，處理的時候可能麻煩些，多了反倒更簡單，針對不同情況，一般有以下兩種處理方式：
方式一：修改數據文件，將多余的數據刪除，不過以這種方式處理，小數據量時還可行，一旦數據文件較大，幾百甚至上千兆，修改數據文件耗時耗力。
方式二：使用sqlldr中控制文件FILLER來排除不需要的列
1）演示數據文件如下
--數據文件
[oracle@wjq SQL*Loader]$ vim wjq_test6.dat
SMITH 7369 CLERK
1020 20
ALLEN 7499 SALESMAN
1930 30
WARD 7521 SALESMAN
1580 30
JONES 7566 MANAGER
3195 20
MARTIN 7654 SALESMAN
1580 30
BLAKE 7698 MANAGER
3180 30
CLARK 7782 MANAGER
2172 10
SCOTT 7788 ANALYST
3220 20
KING 7839 PRESIDENT
4722 10
TURNER 7844 SALESMAN
1830 30
ADAMS 7876 CLERK
1320 20
JAMES 7900 CLERK
1280 30
FORD 7902 ANALYST
3220 20
MILLER 7934 CLERK
1022 10
此時我們的需求希望我們導入第1、3、4列而跳過2、5列，創建控制文件如下
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test6.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test6.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:6),
COL1 FILLER position(10:13),
JOB position(17:25),
SAL position(28:31)
)
sqlldr的控制文件中對列定義時支持FILLER關鍵字，可以用來指定過濾列，在上述控制文件中，我們就使用該關鍵字來過濾列，相當于第10到第13列之間的數據不導入。
事實上由于此處為定長字串，我們在控制文件中指定的position參數，己經限定了讀取的內容，你甚至可以刪除控制文件中TCOL FILLER position (10:13)那行。
執行sqlldr命令：
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test6.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:24:36 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 14
--查詢結果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLERK 1020
ALLEN SALESMAN 1930
WARD SALESMAN 1580
JONES MANAGER 3195
MARTIN SALESMAN 1580
BLAKE MANAGER 3180
CLARK MANAGER 2172
SCOTT ANALYST 3220
KING PRESIDENT 4722
TURNER SALESMAN 1830
ADAMS CLERK 1320
JAMES CLERK 1280
FORD ANALYST 3220
MILLER CLERK 1022
2）如果數據文件中字符串不是定長格式，而是通過分隔符來處理的，那控制文件中就需要注意，如數據文件如下：
--數據文件
[oracle@wjq SQL*Loader]$ vim wjq_test7.dat
SMITH,7369,CLERK,1020,20
ALLEN,7499,SALESMAN,1930,30
WARD,7521,SALESMAN,1580,30
JONES,7566,MANAGER,3195,20
MARTIN,7654,SALESMAN,1580,30
BLAKE,7698,MANAGER,3180,30
CLARK,7782,MANAGER,2172,10
SCOTT,7788,ANALYST,3220,20
KING,7839,PRESIDENT,4722,10
TURNER,7844,SALESMAN,1830,30
ADAMS,7876,CLERK,1320,20
JAMES,7900,CLERK,1280,30
FORD,7902,ANALYST,3220,20
MILLER,7934,CLERK,1022,10
此時創建控制文件時，控制文件中就必須制定FILLER，不然列中的值可能不對應，創建控制文件如下
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test7.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test7.dat'
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY ","
(
ENAME,COL1 FILLER,JOB,SAL
)
執行sqlldr命令，并查看結果
--sqlldr命令
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger
control=/u01/app/oracle/SQL*Loader/wjq_test7.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:32:48 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 14
--查看結果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLERK 1020
ALLEN SALESMAN 1930
WARD SALESMAN 1580
JONES MANAGER 3195
MARTIN SALESMAN 1580
BLAKE MANAGER 3180
CLARK MANAGER 2172
SCOTT ANALYST 3220
KING PRESIDENT 4722
TURNER SALESMAN 1830
ADAMS CLERK 1320
JAMES CLERK 1280
FORD ANALYST 3220
MILLER CLERK 1022
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
