共計 4329 個字符，預計需要花費 11 分鐘才能閱讀完成。
這篇文章將為大家詳細講解有關 SQLLDR 中怎樣 CTL 文件字段設置，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。
position 關鍵字用來指定列的開始和結束位置
position(m:n)：指從第 m 個字符開始截止到第 n 個字符作為列值
position(*+2:15)：直接指定數值的方式叫做絕對偏移量，如果使用 * 號，則為相對偏移量，表示上一個字段哪里結束，這次就哪里開始，相對便宜量也可以再做運算。
position(*) char(9)：這種相對偏移量 + 類型和長度的優勢在于，你只需要為第一列指定開始位置，其他列只需要指定列長度就可以。
FILLER：控制文件中指定 FILLER，表示該列值不導入表中。
普通
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
比導入的表列少
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
比導入的表列多
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
導入不同表
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
函數使用
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.
加載文件內容到大字段
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
關于 SQLLDR 中怎樣 CTL 文件字段設置就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
