Let Oracle help you with financial management

This case takes China Construction Bank as an example.
Download the created transaction table and name it ccb.txt.
At this time, you have two options: sqlldr or external table

 

(I) sqlldr
Because online banking can only be used on win, if your OS is Linux/Unix, you must specify the character set in CTL.
Enables UTF-8 to display gb18030

① Create a table

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

② Use 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 () Date (8) "yyyymmdd" terminated by ',', transac_address position (10) char terminated by ',', out_amount decimal external terminated by ', 'nullif out_amount = blks, in_amount decimal external terminated by', 'nullif in_amount = blks, balance decimal external terminated ', 'nullif balance = blanks, opposite_number char terminated by ',', opposite_username char terminated by ',', currency char terminated by ',', transac_comment char terminated by whitespace)

③ Query a table

 

Of course, you can customize your specific consumption expenses in the all_comment column, such as how much the telephone fee is, how much the clothes are bought, etc.

 

(Ii) External table
① Create a directory object

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

② Place ccb.txt under DIR
③ Use sqlldr to generate an External table creation statement and edit CCB. Log
Edit as follows:

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.

⑥ Query table

 

Conclusion: If your deposit is amazing or frequently accessed, the details are relatively large and external tables are a good choice.
However, whether it is sqlldr or external table
They are all for the convenience of our financial management, to make our life more heavy and wonderful.