这篇文章将为大家详细讲解有关SQLLDR中怎样CTL文件字段设置,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。
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文件字段设置就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
原创文章,作者:3628473679,如若转载,请注明出处:https://blog.ytso.com/tech/database/203966.html