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
