第46次：2014-10-09

botang

1. sqlldr hr/oracle_4U control=prod.ctl data=prod_master.dat  external_table=GENERATE_ONLY  log=prod.sql

复制代码

bank.sql

1. create table t_bank_invoice (
   
2.     account_number  number ,
   
3.      sub_number number ,
   
4.     transac_date   date ,
   
5.       transac_address  varchar2(600),
   
6.     transac_comment    varchar2(600),
   
7.        currency    varchar2(100),
   
8.       remit   varchar2(100),
   
9.      in_amount  number(20,2)  ,
   
10.    out_amount   number(20,2),
    
11.     balance     number(20,2),
    
12.    opposite_number    number  ,
    
13.   opposite_username    varchar2(100),
    
14.     all_comment      varchar2(2000)) ;

复制代码

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. )
    

复制代码

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. )
    

复制代码