SQLLDR——CTL文件：字段设置

position 关键字用来指定列的开始和结束位置
　 position(m:n)：指从第 m 个字符开始截止到第 n 个字符作为列值
　 position(*+2:15)：直接指定数值的方式叫做绝对偏移量，如果使用*号，则为相对偏移量，表示上一个字段哪里结束，这次就哪里开始，相对便宜量也可以再做运算。
　 position(*) char(9)：这种相对偏移量+类型和长度的优势在于，你只需要为第一列指定开始位置，其他列只需要指定列长度就可以。
FILLER：控制文件中指定 FILLER，表示该列值不导入表中。

普通 

Load DATAINFILE *INTO TABLE BONUSFIELDS TERMINATED BY ","(ENAME,JOB,SAL)BEGINDATASMITH,CLEAK,3904ALLEN,SALESMAN,2891WARD,SALESMAN,3128KING,PRESIDENT,2523

没有分隔符

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

比导入的表列少

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

比导入的表列多 

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

导入不同表 

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

换行符处理 

LOAD DATAINFILE *TRUNCATE INTO TABLE BONUSFIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'(ENAME,JOB,SAL)BEGINDATASMITH,CLEAK,3904ALLEN,"SALER,M",2891WARD,"SALER,""S""",3182KING,PRESIDENT,2523

函数使用 

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

大字段处理 

LOAD DATAINFILE * "str '\r\n'"TRUNCATE INTO TABLE MANAGERFIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'(MGRNO,MNAME,JOB,REMARK char(100000))BEGINDATA10,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 candidates4. 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 DATAINFILE *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)BEGINDATA2009-03-17  09:43 154     JUNSANSI F:\oracle\script\ldr_case11_1.ctl2009-03-17  09:44 189     JUNSANSI F:\oracle\script\ldr_case11_1.dat2009-03-17  09:44 2,369   JUNSANSI F:\oracle\script\ldr_case11_1.log2009-03-16  16:50 173     JUNSANSI F:\oracle\script\ldr_case11_2.ctl2009-03-16  16:49 204     JUNSANSI F:\oracle\script\ldr_case11_2.dat2009-03-16  16:50 1,498   JUNSANSI F:\oracle\script\ldr_case11_2.log2009-03-16  17:41 145     JUNSANSI F:\oracle\script\ldr_case11_3.ctl2009-03-16  17:44 130     JUNSANSI F:\oracle\script\ldr_case11_3.dat2009-03-16  17:44 1,743   JUNSANSI F:\oracle\script\ldr_case11_3.log2009-03-17  11:01 132     JUNSANSI F:\oracle\script\ldr_case11_4.ctl2009-03-17  11:02 188     JUNSANSI F:\oracle\script\ldr_case11_4.dat2009-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 testline1testline2