银行储蓄管理系统---Oracle数据库表结构、视图、触发器、序列、函数、过程等的sql文件---Bank_Oracle.sql
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了银行储蓄管理系统---Oracle数据库表结构、视图、触发器、序列、函数、过程等的sql文件---Bank_Oracle.sql，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含13490字，纯文字阅读大概需要20分钟。
内容图文
-------------------------------------------
-- 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=
本文系统来源：http://www.cnblogs.com/yixianyong/p/6940531.html
内容总结
以上是互联网集市为您收集整理的银行储蓄管理系统---Oracle数据库表结构、视图、触发器、序列、函数、过程等的sql文件---Bank_Oracle.sql全部内容，希望文章能够帮你解决银行储蓄管理系统---Oracle数据库表结构、视图、触发器、序列、函数、过程等的sql文件---Bank_Oracle.sql所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】