SQLLDR——CTL檔案：欄位設定

brj880719發表於2017-12-21
SQL

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24851054/viewspace-2149012/，如需轉載，請註明出處，否則將追究法律責任。

相關文章