讓oracle幫你理財

          本案例以建設銀行為例
          請將建行的交易明細表載下，並命名為ccb.txt
          此時，你有兩種選擇：sqlldr or external table

 

          ㈠ sqlldr
          因為網銀只有在win上才能用，所以如果你的os是linux/unix，那麼在ctl中必須指定字元集
          讓UTF-8可以顯示GB18030

             ① 建表

create table t_bank_invoice  ( account_number   number,   sub_number       number,   transac_date     date,   transac_address  varchar2(600),   transac_comment  varchar2(600),   currency         varchar2(100),   remit            varchar2(100),   in_amount        number(20,2),   out_amount       number(20,2),   balance          number(20,2),   opposite_number  number,   opposite_username varchar2(100),   all_comment       varchar2(2000));

             ②  用sqlldr

[oracle@localhost ~]$ sqlldr hr/hr control=ccb.ctl

                 ccb.ctl如下：

[oracle@localhost ~]$ sqlldr hr/hr control=ccb.ctl                 ccb.ctl如下：LOAD DATAcharacterset ZHS16GBKINFILE 'ccb.txt'DISCARDFILE 'ccb.dis'appendINTO table t_bank_invoiceTRAILING NULLCOLS (    account_number "11111111111",   sub_number "00000",    transac_date position(1:8)  DATE(8) "YYYYMMDD" TERMINATED BY ',',     transac_address   position(10)   char  TERMINATED BY ',',                 out_amount decimal external  TERMINATED BY ','  nullif out_amount=blanks,   in_amount decimal external  TERMINATED BY ',' nullif in_amount=blanks  ,   balance decimal external  TERMINATED BY ',' nullif balance=blanks,   opposite_number char  TERMINATED BY ',',        opposite_username char  TERMINATED BY ',',                                      currency char  TERMINATED BY ',',   transac_comment  char  TERMINATED BY whitespace                 )

             ③ 查詢表

 

             當然、你可以在all_comment列個人化你的具體消費開支，比如，交電話費多少錢、買衣服多少錢等等

 

          ㈡ external table
             ① 建立directory對象

sys@ORCL> create directory dir as '/home/oracle/';Directory created.sys@ORCL> grant read,write on directory dir to public;Grant succeeded.

             ② 把ccb.txt放在dir下
             ③ 用sqlldr產生外部表格的建立語句，並編輯ccb.log
                編輯如下：

CREATE TABLE T_BANK_INVOICE(  "ACCOUNT_NUMBER" VARCHAR(255),  "SUB_NUMBER" VARCHAR(255),  "TRANSAC_DATE" DATE,  "TRANSAC_ADDRESS" VARCHAR2(600),  "OUT_AMOUNT" NUMBER(20,2),  "IN_AMOUNT" NUMBER(20,2),  "BALANCE" NUMBER(20,2),  "OPPOSITE_NUMBER" NUMBER,  "OPPOSITE_USERNAME" VARCHAR2(100),  "CURRENCY" VARCHAR2(100),  "TRANSAC_COMMENT" VARCHAR2(600))ORGANIZATION external(  TYPE oracle_loader  DEFAULT DIRECTORY dir  ACCESS PARAMETERS  (    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK    BADFILE 'ccb1.bad'    DISCARDFILE 'ccb1.dis'    LOGFILE 'ccb.log_xt'    READSIZE 1048576    FIELDS LDRTRIM    MISSING FIELD VALUES ARE NULL    REJECT ROWS WITH ALL NULL FIELDS    (      "ACCOUNT_NUMBER" CHAR(1),      "SUB_NUMBER" CHAR(1),      "TRANSAC_DATE" (1:8) CHAR(8)        DATE_FORMAT DATE MASK "YYYYMMDD",      "TRANSAC_ADDRESS" (10) CHAR(255)        TERMINATED BY ",",      "OUT_AMOUNT" CHAR(255)        TERMINATED BY ","        NULLIF ("OUT_AMOUNT" = BLANKS),      "IN_AMOUNT" CHAR(255)        TERMINATED BY ","        NULLIF ("IN_AMOUNT" = BLANKS),      "BALANCE" CHAR(255)        TERMINATED BY ","        NULLIF ("BALANCE" = BLANKS),      "OPPOSITE_NUMBER" CHAR(255)        TERMINATED BY ",",      "OPPOSITE_USERNAME" CHAR(255)        TERMINATED BY ",",      "CURRENCY" CHAR(255)        TERMINATED BY ",",      "TRANSAC_COMMENT" CHAR(255)        TERMINATED BY WHITESPACE    )  )  location  (    'ccb.txt'  ))REJECT LIMIT UNLIMITED;

             ④

[oracle@localhost ~]$ cp ccb.log ccb.sql

             ⑤

hr@ORCL> @ccb.sqlTable created.

             ⑥ 查詢表

 

             小結：如果您的存款驚人、或者經常存取，明細比較大、外部表格是個不錯的選擇
                        但不論是sqlldr還是external table
                        都是為了方便我們理財、讓我們的生活更厚重和精彩