银行储蓄管理系统---Oracle数据库表结构、视图、触发器、序列、函数、过程等的sql文件---Bank_Oracle.sql

------------------------------------------- -- Export file for user SCOTT@ORCL -- -- Created by Johnny on 2017/6/4, 14:28:22 -- --------------------------------------------- set define off spool Bank_Oracle.log prompt prompt Creating table ACCOUNT prompt ====================== prompt create table SCOTT.ACCOUNT ( id NUMBER(20) not null, password VARCHAR2(6) not null, balance NUMBER(20,2) not null, opendate DATE not null, openaddress VARCHAR2(20) not null, isdignity NUMBER(1) not null, islost NUMBER(1) default 0 not null, other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.ACCOUNT add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table CLOSING_INSURANCE prompt ================================ prompt create table SCOTT.CLOSING_INSURANCE ( id INTEGER not null, productname VARCHAR2(50) not null, username VARCHAR2(10) not null, idcardnum NUMBER not null, closing_insurance_date DATE not null, amount NUMBER(20,2) not null, other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.CLOSING_INSURANCE add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table CREDIT prompt ===================== prompt create table SCOTT.CREDIT ( cardint NUMBER not null, username VARCHAR2(10) not null, withid NUMBER(20) not null, balance NUMBER(20,2) not null, creditlimit NUMBER(20,2) not null, creditstatus VARCHAR2(10) default ‘good‘ not null, other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.CREDIT add primary key (CARDINT) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table DEL_ACCOUNT prompt ========================== prompt create table SCOTT.DEL_ACCOUNT ( delid NUMBER(20) not null, id NUMBER(20) not null, balance_beforedel NUMBER(20,2) not null, deldate DATE not null, operaterid NUMBER(10) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.DEL_ACCOUNT add primary key (DELID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table DEPOSIT prompt ====================== prompt create table SCOTT.DEPOSIT ( depositid NUMBER(20) not null, id NUMBER(20) not null, amount NUMBER(20,2) not null, depositdate DATE not null, deposittype VARCHAR2(4) not null, interest NUMBER(20,6) not null, operaterid NUMBER(10) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.DEPOSIT add primary key (DEPOSITID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table DEPUTE prompt ===================== prompt create table SCOTT.DEPUTE ( id INTEGER not null, amount NUMBER(20,2), deputename VARCHAR2(50), depute_service VARCHAR2(50), other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table DRAW prompt =================== prompt create table SCOTT.DRAW ( drawid NUMBER(20) not null, id NUMBER(20) not null, amount NUMBER(20,2) not null, interest NUMBER(20,2) not null, balance NUMBER(20,2), drawdate DATE not null, drawtype VARCHAR2(10) not null, operaterid NUMBER(10) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.DRAW add primary key (DRAWID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table INTEREST_RATE prompt ============================ prompt create table SCOTT.INTEREST_RATE ( id NUMBER(20) not null, type VARCHAR2(4) not null, interest NUMBER(11,10) not null, timelimit NUMBER(3) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.INTEREST_RATE add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table LOAN prompt =================== prompt create table SCOTT.LOAN ( loanid INTEGER not null, withid NUMBER(20) not null, username VARCHAR2(20) not null, idcardnum NUMBER(20) not null, userphone VARCHAR2(20), loanamount NUMBER(20,2) not null, loanlimit INTEGER not null, loaninterstrate NUMBER(11,10) not null, loandate DATE not null, deadline DATE not null, reamountpermontht NUMBER(20,2) not null, other VARCHAR2(50) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.LOAN add primary key (LOANID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table LOST prompt =================== prompt create table SCOTT.LOST ( id NUMBER(20) not null, balance NUMBER(20,2) not null, lostdate DATE not null, operaterid VARCHAR2(10) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.LOST add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table MANAGER prompt ====================== prompt create table SCOTT.MANAGER ( id NUMBER(10), password VARCHAR2(6), grade NUMBER(1), username VARCHAR2(10), idcardnum VARCHAR2(18), phone NUMBER(15), address VARCHAR2(80), photo VARCHAR2(50), other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table PLAN_TABLE prompt ========================= prompt create table SCOTT.PLAN_TABLE ( statement_id VARCHAR2(30), plan_id NUMBER, timestamp DATE, remarks VARCHAR2(4000), operation VARCHAR2(30), options VARCHAR2(255), object_node VARCHAR2(128), object_owner VARCHAR2(30), object_name VARCHAR2(30), object_alias VARCHAR2(65), object_instance INTEGER, object_type VARCHAR2(30), optimizer VARCHAR2(255), search_columns NUMBER, id INTEGER, parent_id INTEGER, depth INTEGER, position INTEGER, cost INTEGER, cardinality INTEGER, bytes INTEGER, other_tag VARCHAR2(255), partition_start VARCHAR2(255), partition_stop VARCHAR2(255), partition_id INTEGER, other LONG, distribution VARCHAR2(30), cpu_cost INTEGER, io_cost INTEGER, temp_space INTEGER, access_predicates VARCHAR2(4000), filter_predicates VARCHAR2(4000), projection VARCHAR2(4000), time INTEGER, qblock_name VARCHAR2(30), other_xml CLOB ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table RECORD_PAY_INSURANCE prompt =================================== prompt create table SCOTT.RECORD_PAY_INSURANCE ( recordid INTEGER not null, typename VARCHAR2(30), username VARCHAR2(30), idcard NUMBER(18), dotime DATE, amount NUMBER(20,2), other VARCHAR2(20) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.RECORD_PAY_INSURANCE add primary key (RECORDID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table REPAYMENT prompt ======================== prompt create table SCOTT.REPAYMENT ( repayint INTEGER not null, loanid INTEGER not null, deadline DATE not null, repaymentamount NUMBER(20,2) not null, isbreakpromise INTEGER not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.REPAYMENT add primary key (REPAYINT) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table TRANSFER prompt ======================= prompt create table SCOTT.TRANSFER ( transferid NUMBER(20) not null, sourceid NUMBER(20) not null, destinateid NUMBER(20) not null, amount NUMBER(20,2) not null, transferdate DATE not null, brokerage NUMBER(20,2) not null, operaterid NUMBER(10) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.TRANSFER add primary key (TRANSFERID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table USERS prompt ==================== prompt create table SCOTT.USERS ( id NUMBER(20) not null, idcardnum NUMBER(18), username VARCHAR2(10), phone VARCHAR2(11), photo VARCHAR2(50), other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.USERS add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating sequence ACCOUNTIDSEQUENCE prompt =================================== prompt create sequence SCOTT.ACCOUNTIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 8600000041 increment by 1 cache 20; prompt prompt Creating sequence CREDITIDSEQUENCE prompt ================================== prompt create sequence SCOTT.CREDITIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 8180021 increment by 1 cache 20; prompt prompt Creating sequence DEL_ID_SEQUENCE prompt ================================= prompt create sequence SCOTT.DEL_ID_SEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 120 increment by 1 cache 20; prompt prompt Creating sequence DEPOSITIDSEQUENCE prompt =================================== prompt create sequence SCOTT.DEPOSITIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 8880081 increment by 1 cache 20; prompt prompt Creating sequence DEPUTEIDINCREASE prompt ================================== prompt create sequence SCOTT.DEPUTEIDINCREASE minvalue 1 maxvalue 999999999999999999999999999 start with 60001 increment by 1 cache 20; prompt prompt Creating sequence IDINCREASE prompt ============================ prompt create sequence SCOTT.IDINCREASE minvalue 1 maxvalue 999999999999999999999999999 start with 1000061 increment by 1 cache 20; prompt prompt Creating sequence LOANIDSEQUENCE prompt ================================ prompt create sequence SCOTT.LOANIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 5150041 increment by 1 cache 20; prompt prompt Creating sequence RECORDID_CO_INS_SEQUENCE prompt ========================================== prompt create sequence SCOTT.RECORDID_CO_INS_SEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 30061 increment by 1 cache 20; prompt prompt Creating sequence RECORD_PAYINSUR_ID_SEQUENCE prompt ============================================= prompt create sequence SCOTT.RECORD_PAYINSUR_ID_SEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 2310061 increment by 1 cache 20; prompt prompt Creating sequence REPAYIDSEQUENCE prompt ================================= prompt create sequence SCOTT.REPAYIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 5250021 increment by 1 cache 20; prompt prompt Creating sequence TRANSFERIDSEQUENCE prompt ==================================== prompt create sequence SCOTT.TRANSFERIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 1230041 increment by 1 cache 20; prompt prompt Creating sequence USERIDSEQUENCE prompt ================================ prompt create sequence SCOTT.USERIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 1010061 increment by 1 cache 20; prompt prompt Creating function CALCULATEINTEREST prompt =================================== prompt create or replace function scott.calculateInterest(pid int) return number as fsysdate Date; finterest deposit.interest%type; starttime Date; ftype varchar(4); fdeposit deposit.amount%type; durtime int; fsave deposit.amount%type; begin select sysdate into fsysdate from dual; select depositdate,deposittype into starttime,ftype from deposit where deposit.depositid=pid; select interest,timelimit into fdeposit,durtime from interest_rate where interest_rate.type=ftype; if (fsysdate-starttime)/30>=durtime then select amount into fsave from deposit where deposit.depositid=