SQLLDR中怎樣CTL文件字段設置

這篇文章將為大家詳細講解有關SQLLDR中怎樣CTL文件字段設置，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

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文件字段設置就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。