实用复杂的sql loader案例

botang

Oracle Database 12c: Administration Workshop P116:
1
-- This is a sample control file
2 LOAD DATA
3 INFILE ’SAMPLE.DAT’
4 BADFILE ’sample.bad’
5 DISCARDFILE ’sample.dsc’
’
’
6 APPEND
7 INTO TABLE emp
8 WHEN (57) = ’.’
9 TRAILING NULLCOLS
10 (hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(3)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,’$99,999.99’)",
comm INTEGER EXTERNAL ENCLOSED BY ’(’ AND ’%’
":comm * 100"
)

原文件在此：

1. 
   
2.   
   
3.                Today's Newly Hired Employees
   
4. 
   
5. Dept  Job       Manager   MgrNo  Emp Name  EmpNo  Salary/Commission
   
6. ----  --------  --------  -----  --------  -----  -----------------
   
7. 20    Salesman  Blake      7698  Shepard    8061  $1,600.00 (3%)
   
8.                                  Falstaff   8066  $1,250.00 (5%)
   
9.                                  Major      8064  $1,250.00 (14%)
   
10. 
    
11. 30    Clerk     Scott      7788  Conrad     8062  $1,100.00
    
12.                 Ford       7369  DeSilva    8063    $800.00
    
13.       Manager   King       7839  Provo      8065  $2,975.00
    

复制代码

ulcase7.dat (561 Bytes, 下载次数: 198)

2020-11-15 17:03 上传

点击文件名下载附件

控制文件：
ulcase7.ctl (1.34 KB, 下载次数: 168)

2020-11-15 17:03 上传

点击文件名下载附件

1. LOAD DATA
   
2. INFILE 'ulcase7.dat'
   
3. DISCARDFILE 'ulcase7.dis'
   
4. APPEND
   
5. INTO TABLE emp
   
6.   WHEN (57)='.'
   
7.   TRAILING NULLCOLS
   
8.   (hiredate SYSDATE,
   
9.    deptno   POSITION(1:2)  INTEGER EXTERNAL(3)
   
10.             NULLIF deptno=BLANKS,
    
11.    job      POSITION(7:14)   CHAR  TERMINATED BY WHITESPACE
    
12.             NULLIF job=BLANKS  "UPPER(:job)",
    
13.    mgr      POSITION(28:31)  INTEGER EXTERNAL TERMINATED BY WHITESPACE
    
14.             NULLIF mgr=BLANKS,
    
15.    ename    POSITION (34:41) CHAR  TERMINATED BY WHITESPACE
    
16.             "UPPER(:ename)",
    
17.    empno    INTEGER EXTERNAL  TERMINATED BY WHITESPACE,
    
18.    sal      POSITION(51)  CHAR  TERMINATED BY WHITESPACE
    
19.             "TO_NUMBER(:sal,'$99,999.99')",
    
20.    comm     INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'  
    
21.             ":comm * 100"
    
22.   )
    
23. ~         

复制代码

做实验的过程：

1. set termout off
   
2. rem host write sys$output "Building case 6 demonstration tables.  Please wait"
   
3. 
   
4. drop table emp;
   
5. 
   
6. create table emp
   
7.        (empno number(4) not null,
   
8.         ename char(10),
   
9.         job char(9),
   
10.         mgr number(4),
    
11.         hiredate date,
    
12.         sal number(7,2),
    
13.         comm number(7,2),
    
14.         deptno number(2));
    
15. 
    
16. create unique index empix on emp(empno);
    
17. 
    
18. exit
    

复制代码

1. [oracle@classroom demo]$ sqlldr hr/oracle_4U  control=ulcase7.ctl  direct=y

复制代码

8061    SHEPARD       SALESMAN     7698    17-NOV-20    1600    300    20
8066    FALSTAFF              17-NOV-20    1250    500   
8064    MAJOR                 17-NOV-20    1250    1400   
8062    CONRAD        CLERK        7788    17-NOV-20    1100        30
8063    DESILVA           7369    17-NOV-20    800        
8065    PROVO         MANAGER      7839    17-NOV-20    2975        

1. [oracle@classroom demo]$ sqlldr hr/oracle_4U  control=ulcase7.ctl  external_table=GENERATE_ONLY  log=ext.sql
   
2. 
   
3. SQL*Loader: Release 12.1.0.2.0 - Production on 星期二 11月 17 20:33:16 2020
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. SQL*Loader-307: Warning: conflicting lengths 2 and 3 specified for column DEPTNO table EMP
   
8. Path used:      External Table
   

复制代码

-----------------------------------

1. CREATE TABLE ext1
   
2. (
   
3.   "DEPTNO" NUMBER(2),
   
4.   "JOB" VARCHAR2(255),
   
5.   "MGR" NUMBER(4),
   
6.   "ENAME" VARCHAR2(255),
   
7.   "EMPNO" NUMBER(4),
   
8.   "SAL" VARCHAR2(255),
   
9.   "COMM" VARCHAR2(255)
   
10. )
    
11. ORGANIZATION external
    
12. (
    
13.   TYPE oracle_loader
    
14.   DEFAULT DIRECTORY dir1
    
15.   ACCESS PARAMETERS
    
16.   (
    
17.     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
    
18.     LOAD WHEN ((57: 57) = ".")
    
19.     BADFILE 'ulcase7.bad'
    
20.     DISCARDFILE 'ulcase7.dis'
    
21.     LOGFILE 'ext.log'
    
22.     READSIZE 1048576
    
23.     FIELDS LDRTRIM
    
24.     MISSING FIELD VALUES ARE NULL
    
25.     REJECT ROWS WITH ALL NULL FIELDS
    
26.     (
    
27.       "DEPTNO" (1:2) INTEGER EXTERNAL(3)
    
28.         NULLIF ("DEPTNO" = BLANKS),
    
29.       "JOB" (7:14) CHAR(8)
    
30.         NULLIF ("JOB" = BLANKS),
    
31.       "MGR" (28:31) CHAR(4)
    
32.         NULLIF ("MGR" = BLANKS),
    
33.       "ENAME" (34:41) CHAR(8),
    
34.       "EMPNO" CHAR(255)
    
35.         TERMINATED BY WHITESPACE,
    
36.       "SAL" (51) CHAR(255)
    
37.         TERMINATED BY WHITESPACE,
    
38.       "COMM" CHAR(255)
    
39.          ENCLOSED BY "(" AND "%"
    
40.     )
    
41.   )
    
42.   location
    
43.   (
    
44.     'ulcase7.dat'
    
45.   )
    
46. )REJECT LIMIT UNLIMITED;
    

复制代码

bank.sql (450 Bytes, 下载次数: 148)

2020-11-17 20:47 上传

点击文件名下载附件

ccb.ctl:

1. LOAD DATA
   
2. characterset ZHS16GBK
   
3. INFILE 'ccb.dat'
   
4. DISCARDFILE 'ccb.dis'
   
5. append
   
6. INTO table t_bank_invoice
   
7. TRAILING NULLCOLS
   
8. (
   
9.    account_number "4367421823250099999",
   
10.    sub_number "00000",
    
11.    transac_date position(1:8)  DATE(8) "YYYYMMDD" TERMINATED BY ',',  
    
12.    transac_address   position(10)   char  TERMINATED BY ',',              
    
13.    out_amount decimal external  TERMINATED BY ','  nullif out_amount=blanks,
    
14.    in_amount decimal external  TERMINATED BY ',' nullif in_amount=blanks  ,
    
15.    balance decimal external  TERMINATED BY ',' nullif balance=blanks,
    
16.    opposite_number char  TERMINATED BY ',',     
    
17.    opposite_username char  TERMINATED BY ',',                                   
    
18.    currency char  TERMINATED BY ',',
    
19.    transac_comment  char  TERMINATED BY whitespace               
    
20. )
    
21. 
    

复制代码

1. 账　　号：4367421823250099999
   
2. 开户机构：福建省
   
3. 币    种： 人民币
   
4. 发生日期,交易地点,支出,收入,账户余额,对方账号,对方户名,币种,摘要
   
5. 20100209,008350183980040,150.00,,6756.67,,,人民币,消费
   
6. 20100210,,,2765.90,9522.57,,,人民币,工资/奖金
   
7. 20100216,,3.00,,9519.57,,,人民币,自定义
   
8. 20100217,,4859.10,,4660.47,,,人民币,自定义
   
9. 
   

复制代码

icbc.ctl:

1. LOAD DATA
   
2. characterset ZHS16GBK
   
3. INFILE 'icbc.dat'
   
4. DISCARDFILE 'icbc.dis'
   
5. append
   
6. INTO table t_bank_invoice
   
7. when (5) ='^'
   
8. FIELDS TERMINATED BY '^'
   
9. TRAILING NULLCOLS
   
10. (
    
11.    account_number "1402029101000999999",
    
12.    sub_number "00000",
    
13.    transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
    
14.    transac_address TERMINATED BY WHITESPACE
    
15.     "substr(:transac_address,2)",
    
16.    transac_comment TERMINATED BY WHITESPACE
    
17.     "substr(:transac_comment,2)",
    
18.    currency TERMINATED BY WHITESPACE
    
19.     "substr(:currency,2)",
    
20.    remit TERMINATED BY WHITESPACE
    
21.     "substr(:remit,2)",
    
22.    in_amount TERMINATED BY WHITESPACE
    
23.     "TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
    
24.    out_amount TERMINATED BY WHITESPACE
    
25.     "TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
    
26.    balance TERMINATED BY WHITESPACE
    
27.     "TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
    
28.    all_comment TERMINATED BY WHITESPACE
    
29.     "substr(:all_comment,2)"
    
30. )
    
31. 
    

复制代码

1. 明细查询文件下载
   
2. 
   
3.     卡号: 1402029101000999999    卡别名:
   
4. 
   
5.     子账户序号:     子账户类别:     子账户别名:
   
6. 
   
7.   ^交易日期       ^交易场所                 ^业务摘要  ^币种      ^钞/汇     ^收入金额    ^支出金额    ^余额
   
8.     ^2010-05-10     ^                         ^工资      ^人民币    ^钞        ^564.94      ^            ^6,748.42            
   
9.     ^2010-05-14     ^                         ^工资      ^人民币    ^钞        ^5,989.54    ^            ^12,737.96           
   
10.     ^2010-05-26     ^金山支行营业部           ^支取      ^人民币    ^钞        ^            ^12,700.00   ^37.96               
    
11.     ^2010-06-12     ^                         ^工资      ^人民币    ^钞        ^5,811.04    ^            ^5,849.00            
    
12.     ^2010-06-21     ^                         ^息        ^人民币    ^钞        ^5.03        ^            ^5,854.03            
    
13.     ^2010-07-01     ^金山支行营业部           ^支取      ^人民币    ^钞        ^            ^5,854.00    ^0.03               
    
14.    
    
15. 
    
16.    #人民币合计                                                                 ^163,952.85   ^170,134.00   
    

复制代码