SQLLDR中怎樣CTL文件字段設(shè)置-創(chuàng)新互聯(lián)

這篇文章將為大家詳細(xì)講解有關(guān)SQLLDR中怎樣CTL文件字段設(shè)置，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

成都創(chuàng)新互聯(lián)公司專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)、蟠龍網(wǎng)絡(luò)推廣、小程序開發(fā)、蟠龍網(wǎng)絡(luò)營銷、蟠龍企業(yè)策劃、蟠龍品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們大的嘉獎；成都創(chuàng)新互聯(lián)公司為所有大學(xué)生創(chuàng)業(yè)者提供蟠龍建站搭建服務(wù)，24小時(shí)服務(wù)熱線：18982081108，官方網(wǎng)址：muchs.cn

position 關(guān)鍵字用來指定列的開始和結(jié)束位置
　 position(m:n)：指從第 m 個字符開始截止到第 n 個字符作為列值
　 position(*+2:15)：直接指定數(shù)值的方式叫做絕對偏移量，如果使用*號，則為相對偏移量，表示上一個字段哪里結(jié)束，這次就哪里開始，相對便宜量也可以再做運(yùn)算。
　 position(*) char(9)：這種相對偏移量+類型和長度的優(yōu)勢在于，你只需要為第一列指定開始位置，其他列只需要指定列長度就可以。
FILLER：控制文件中指定 FILLER，表示該列值不導(dǎo)入表中。

普通

Load DATA
INFILE *
INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523

沒有分隔符

LOAD DATA
INFILE *
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(7:15),
SAL position(17:20)
)
BEGINDATA
SMITH CLEAK 2891
ALLEN SALESMAN 2891
WARD SALESMAN 3128
KING PRESIDENT 2523

比導(dǎo)入的表列少

LOAD DATA
INFILE *
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(7:15),
SAL position(17:20),
comm "0"
)
BEGINDATA
SMITH CLEAK 2891
ALLEN SALESMAN 2891
WARD SALESMAN 3128
KING PRESIDENT 2523

比導(dǎo)入的表列多

LOAD DATA
INFILE *
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:6),
TCOL FILLER position(8:11),
JOB position(13:21),
SAL position(23:26)
)
BEGINDATA
SMITH 7369 CLERK 800 20
ALLEN 7499 SALESMAN 1600 30
WARD 7521 SALESMAN 1250 30
JONES 7566 MANAGER 2975 20
MARTIN 7654 SALESMAN 1250 30
BLAKE 7698 MANAGER 2850 30
CLARK 7782 MANAGER 2450 10
KING 7839 PRESIDENT 5000 10
TURNER 7844 SALESMAN 1500 30
JAMES 7900 CLERK 950 30
FORD 7902 ANALYST 3000 20
MILLER 7934 CLERK 1300 10

導(dǎo)入不同表

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE BONUS
WHEN Tab='BON'
(
Tab FILLER position(1:3),
ENAME position(5:9),
JOB position(11:19),
SAL position(21:24)
)
INTO TABLE MANAGER
WHEN Tab='MGR'
(
Tab FILLER position(1:3),
MGRNO position(5:6),
MNAME position(8:14),
JOB position(16:28)
)
BEGINDATA
BON SMITH CLERK 3904
BON ALLEN SALER,M 2891
BON WARD SALER,"S" 3128
BON KING PRESIDENT 2523
MGR 10 SMITH SALES MANAGER
MGR 11 ALLEN.W TECH MANAGER
MGR 16 BLAKE HR MANAGER
TMP SMITH 7369 CLERK 800 20
TMP ALLEN 7499 SALESMAN 1600 30
TMP WARD 7521 SALESMAN 1250 30
TMP JONES 7566 MANAGER 2975 20

換行符處理

LOAD DATA
INFILE *
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3182
KING,PRESIDENT,2523

函數(shù)使用

LOAD DATA
INFILE *
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(7:15),
SAL position(17:20),
comm "substr(:sal,1,1)"
)
BEGINDATA
SMITH CLEAK 3904
ALLEN SALESMAN 2891
WARD SALESMAN 3128
KING PRESIDENT 2523

大字段處理

LOAD DATA
INFILE * "str '\r\n'"
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(MGRNO,MNAME,JOB,REMARK char(100000))
BEGINDATA
10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.|
16,BLAKE,HR MANAGER,"This is BLAKE. He is a Hr Manager. The jobs responsibilities are in the following:
1. Ensure the effective local implementation of corporate level HRinitiatives and new programs.
2. Take initiatives in defining HR strategy on attracting, hiring, integrating, developing, managing, rewarding and retaining talents for the sustainable development of company business.
3. Oversee stanard recruiting an procedures to ensure the company's staffing requirements are met in a timely manner, and interview management level candidates
4. Provide employees with fair and appropriate compensation and benefit, to ensure market competitiveness.
5. Develop, implement and oversee the training and development programs to upgrade the skills of the employee and to enhance the company's capabilities to met business goals and future challenges."

加載文件內(nèi)容到大字段

LOAD DATA
INFILE *
TRUNCATE INTO TABLE LOBTBL
(
CREATE_DATE POSITION(1：17) DATE 'YYYY-MM-DD HH24：MI',
FILESIZE POSITION(*+1：25) "to_number(:FILESIZE, '99，999，999')",
FILEOWNER POSITION(*+1：34),
FILENAME POSITION(*+1) char(200) "substr(:FILENAME,instr(:FILENAME, '\\',-1)+1)",
FILEDATA LOBFILE(FILENAME) TERMINATED BY EOF
)
BEGINDATA
2009-03-17 09:43 154 JUNSANSI F:\oracle\script\ldr_case11_1.ctl
2009-03-17 09:44 189 JUNSANSI F:\oracle\script\ldr_case11_1.dat
2009-03-17 09:44 2,369 JUNSANSI F:\oracle\script\ldr_case11_1.log
2009-03-16 16:50 173 JUNSANSI F:\oracle\script\ldr_case11_2.ctl
2009-03-16 16:49 204 JUNSANSI F:\oracle\script\ldr_case11_2.dat
2009-03-16 16:50 1,498 JUNSANSI F:\oracle\script\ldr_case11_2.log
2009-03-16 17:41 145 JUNSANSI F:\oracle\script\ldr_case11_3.ctl
2009-03-16 17:44 130 JUNSANSI F:\oracle\script\ldr_case11_3.dat
2009-03-16 17:44 1,743 JUNSANSI F:\oracle\script\ldr_case11_3.log
2009-03-17 11:01 132 JUNSANSI F:\oracle\script\ldr_case11_4.ctl
2009-03-17 11:02 188 JUNSANSI F:\oracle\script\ldr_case11_4.dat
2009-03-17 11:02 1,730 JUNSANSI F:\oracle\script\ldr_case11_4.log

載入每行的行號

load data
infile *
into table t
replace
(
seqno RECNUM //載入每行的行號
text Position(1:1024)
)
BEGINDATA
testline1
testline2

關(guān)于SQLLDR中怎樣CTL文件字段設(shè)置就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

本文題目：SQLLDR中怎樣CTL文件字段設(shè)置-創(chuàng)新互聯(lián)
轉(zhuǎn)載來于：http://muchs.cn/article42/deishc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供靜態(tài)網(wǎng)站、網(wǎng)站改版、手機(jī)網(wǎng)站建設(shè)、軟件開發(fā)、網(wǎng)站營銷、虛擬主機(jī)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容