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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24851054/viewspace-2149012/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24851054/viewspace-2149012/