让oracle帮你理财

来源：互联网发布：sql server 编码格式编辑：程序博客网时间：2024/04/28 11:44

          本案例以建设银行为例
          请将建行的交易明细表载下，并命名为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
                       都是为了方便我们理财、让我们的生活更厚重和精彩