宜宾网站建设公司

SQLLDR中怎样CTL文件字段设置-创新互联

这篇文章将为大家详细讲解有关SQLLDR中怎样CTL文件字段设置，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

成都创新互联公司专注为客户提供全方位的互联网综合服务，包含不限于成都网站设计、成都网站建设、蟠龙网络推广、小程序开发、蟠龙网络营销、蟠龙企业策划、蟠龙品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们大的嘉奖；成都创新互联公司为所有大学生创业者提供蟠龙建站搭建服务，24小时服务热线：18982081108，官方网址：www.cdcxhl.com

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

普通 

  1. Load DATA

  2. INFILE *

  3. INTO TABLE BONUS

  4. FIELDS TERMINATED BY ","

  5. (ENAME,JOB,SAL)

  6. BEGINDATA

  7. SMITH,CLEAK,3904

  8. ALLEN,SALESMAN,2891

  9. WARD,SALESMAN,3128

  10. KING,PRESIDENT,2523

没有分隔符

  1. LOAD DATA

  2. INFILE *

  3. TRUNCATE INTO TABLE BONUS

  4. (

  5. ENAME position(1:5),

  6. JOB position(7:15),

  7. SAL position(17:20)

  8. )

  9. BEGINDATA

  10. SMITH CLEAK 2891

  11. ALLEN SALESMAN 2891

  12. WARD SALESMAN 3128

  13. KING PRESIDENT 2523

比导入的表列少

  1. LOAD DATA

  2. INFILE *

  3. TRUNCATE INTO TABLE BONUS

  4. (

  5. ENAME position(1:5),

  6. JOB position(7:15),

  7. SAL position(17:20),

  8. comm "0"

  9. )

  10. BEGINDATA

  11. SMITH CLEAK 2891

  12. ALLEN SALESMAN 2891

  13. WARD SALESMAN 3128

  14. KING PRESIDENT 2523

比导入的表列多 

  1. LOAD DATA

  2. INFILE *

  3. TRUNCATE INTO TABLE BONUS

  4. (

  5. ENAME position(1:6),

  6. TCOL FILLER position(8:11),

  7. JOB position(13:21),

  8. SAL position(23:26)

  9. )

  10. BEGINDATA

  11. SMITH  7369 CLERK     800  20

  12. ALLEN  7499 SALESMAN  1600 30

  13. WARD   7521 SALESMAN  1250 30

  14. JONES  7566 MANAGER   2975 20

  15. MARTIN 7654 SALESMAN  1250 30

  16. BLAKE  7698 MANAGER   2850 30

  17. CLARK  7782 MANAGER   2450 10

  18. KING   7839 PRESIDENT 5000 10

  19. TURNER 7844 SALESMAN  1500 30

  20. JAMES  7900 CLERK     950  30

  21. FORD   7902 ANALYST   3000 20

  22. MILLER 7934 CLERK     1300 10

导入不同表

  1. LOAD DATA

  2. INFILE *

  3. TRUNCATE

  4. INTO TABLE BONUS

  5. WHEN Tab='BON'

  6. (

  7. Tab FILLER position(1:3),

  8. ENAME position(5:9),

  9. JOB position(11:19),

  10. SAL position(21:24)

  11. )

  12. INTO TABLE MANAGER

  13. WHEN Tab='MGR'

  14. (

  15. Tab FILLER position(1:3),

  16. MGRNO position(5:6),

  17. MNAME position(8:14),

  18. JOB position(16:28)

  19. )

  20. BEGINDATA

  21. BON SMITH CLERK     3904

  22. BON ALLEN SALER,M   2891

  23. BON WARD  SALER,"S" 3128

  24. BON KING  PRESIDENT 2523

  25. MGR 10 SMITH   SALES MANAGER

  26. MGR 11 ALLEN.W TECH MANAGER

  27. MGR 16 BLAKE   HR MANAGER

  28. TMP SMITH 7369 CLERK    800  20

  29. TMP ALLEN 7499 SALESMAN 1600 30

  30. TMP WARD  7521 SALESMAN 1250 30

  31. TMP JONES 7566 MANAGER  2975 20

换行符处理

  1. LOAD DATA

  2. INFILE *

  3. TRUNCATE INTO TABLE BONUS

  4. FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

  5. (ENAME,JOB,SAL)

  6. BEGINDATA

  7. SMITH,CLEAK,3904

  8. ALLEN,"SALER,M",2891

  9. WARD,"SALER,""S""",3182

  10. KING,PRESIDENT,2523

函数使用

  1. LOAD DATA

  2. INFILE *

  3. TRUNCATE INTO TABLE BONUS

  4. (

  5. ENAME position(1:5),

  6. JOB position(7:15),

  7. SAL position(17:20),

  8. comm "substr(:sal,1,1)"

  9. )

  10. BEGINDATA

  11. SMITH CLEAK     3904

  12. ALLEN SALESMAN  2891

  13. WARD  SALESMAN  3128

  14. KING  PRESIDENT 2523

大字段处理

  1. LOAD DATA

  2. INFILE * "str '\r\n'"

  3. TRUNCATE INTO TABLE MANAGER

  4. FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

  5. (MGRNO,MNAME,JOB,REMARK char(100000))

  6. BEGINDATA

  7. 10,SMITH,SALES MANAGER,This is SMITH.

  8. He is a Sales Manager.|

  9. 11,ALLEN.W,TECH MANAGER,This is ALLEN.W.

  10. He is a Tech Manager.|

  11. 16,BLAKE,HR MANAGER,"This is BLAKE. He is a Hr Manager. The jobs responsibilities are in the following:

  12. 1. Ensure the effective local implementation of corporate level HRinitiatives and new programs.

  13. 2. Take initiatives in defining HR strategy on attracting, hiring, integrating, developing, managing, rewarding and retaining talents for the sustainable development of company business.

  14. 3. Oversee stanard recruiting an procedures to ensure the company's staffing requirements are met in a timely manner, and interview management level candidates

  15. 4. Provide employees with fair and appropriate compensation and benefit, to ensure market competitiveness.

  16. 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."

加载文件内容到大字段

  1. LOAD DATA

  2. INFILE *

  3. TRUNCATE INTO TABLE LOBTBL

  4. (

  5. CREATE_DATE POSITION(1：17) DATE 'YYYY-MM-DD HH24：MI',

  6. FILESIZE POSITION(*+1：25) "to_number(:FILESIZE, '99，999，999')",

  7. FILEOWNER POSITION(*+1：34),

  8. FILENAME POSITION(*+1) char(200) "substr(:FILENAME,instr(:FILENAME, '\\',-1)+1)",

  9. FILEDATA LOBFILE(FILENAME) TERMINATED BY EOF

  10. )

  11. BEGINDATA

  12. 2009-03-17  09:43 154     JUNSANSI F:\oracle\script\ldr_case11_1.ctl

  13. 2009-03-17  09:44 189     JUNSANSI F:\oracle\script\ldr_case11_1.dat

  14. 2009-03-17  09:44 2,369   JUNSANSI F:\oracle\script\ldr_case11_1.log

  15. 2009-03-16  16:50 173     JUNSANSI F:\oracle\script\ldr_case11_2.ctl

  16. 2009-03-16  16:49 204     JUNSANSI F:\oracle\script\ldr_case11_2.dat

  17. 2009-03-16  16:50 1,498   JUNSANSI F:\oracle\script\ldr_case11_2.log

  18. 2009-03-16  17:41 145     JUNSANSI F:\oracle\script\ldr_case11_3.ctl

  19. 2009-03-16  17:44 130     JUNSANSI F:\oracle\script\ldr_case11_3.dat

  20. 2009-03-16  17:44 1,743   JUNSANSI F:\oracle\script\ldr_case11_3.log

  21. 2009-03-17  11:01 132     JUNSANSI F:\oracle\script\ldr_case11_4.ctl

  22. 2009-03-17  11:02 188     JUNSANSI F:\oracle\script\ldr_case11_4.dat

  23. 2009-03-17  11:02 1,730   JUNSANSI F:\oracle\script\ldr_case11_4.log

载入每行的行号

  1. load data

  2. infile *

  3. into table t

  4. replace

  5. (

  6. seqno RECNUM //载入每行的行号

  7. text Position(1:1024)

  8. )

  9. BEGINDATA

  10. testline1

  11. testline2

关于SQLLDR中怎样CTL文件字段设置就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。


网站栏目：SQLLDR中怎样CTL文件字段设置-创新互联
文章起源：http://ybzwz.com/article/deishc.html

其他资讯