数据库优秀课程设计银行储蓄系统完全代码.docx

数据库课程设计完全代码
--建库
create database Bank
on primary
(
name = 'Bank',
filename='D:\project\Bank.mdf',
size = 5,
maxsize = 100,
filegrowth = 10%
)
log on
(
name = 'Bank_log',
filename = 'D:\project\Bank_log.ldf',
size=2,
filegrowth=1
)
go

--建表
use Bank
create table Depositors(
BNo varchar(20) primary key, --账号
BName varchar(20) not null, --姓名
BPassword char(6) not null check(len(BPassword) = 6), --密码
BID varchar(20) not null, --身份证号
BSex char(2) not null check(BSex = '男' or BSex = '女'), --性别
BStyle varchar(20) not null check(BStyle = '活期存款' or BStyle = '定时存款'), --业务类型
BDate datetime not null, --开户时间
BYear int not null check(BYear = 0 or BYear = 1 or BYear = 2 or BYear = 3), --存款期限,0表示活期
BMoney decimal(10,4) not null check(BMoney >= 0) --账户余额
)

create table CurrentAccounts(
nID int primary key identity(1,1), --流水号
BNo varchar(20) not null references Depositors(BNo), --账号
BName varchar(20) not null, --姓名
BStyle varchar(20) not null check(BStyle = '活期存款' or BStyle = '活期取款'), --操作类型
BCash decimal(10,4) null check(BCash >= 0), --操作金额
BDate datetime not null, --操作时间
BInterest decimal(10,4) null check(BInterest >= 0), --利息
BMoney decimal(10,4) not null check(BMoney >= 0), --账户余额
)

create table FixedAccounts(
nID int primary key identity(1,1), --流水号
BNo varchar(20) not null references Depositors(BNo), --账号
BName varchar(20) not null, --姓名
BStyle varchar(20) not null check(BStyle = '定时存款' or BStyle = '定时取款'), 操作类型
BMoney decimal(10,4) not null check(BMoney >= 0), --存取金额
BYear int not null check(BYear = 1 or BYear = 2 or BYear = 3), --存款期限
BDate datetime not null --存款时间

插入触发器
create trigger InsertIntoCAorFA on Depositors
after insert
as
declare @year int
select @year = BYear from inserted

if @year = 0
insert into CurrentAccounts(BNo,BName,BStyle,BDate,BMoney) select BNo,BName,BStyle,BDate,BMoney from inserted
else
insert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) select BNo,BName,BStyle,BMoney,BYear,BDate from inserted

删除触发器
create trigger DeleteFromCAorFA on Depositors
instead of delete
as
declare @no varchar(20)
select @no = BNo from deleted
delete from CurrentAccounts where BNo = @no
delete from FixedAccounts where BNo = @no
delete from Depositors where BNo = @no

（1）开户登记&（2）定时存款
insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10001,'张三',123456,,'男','活期存款','-01-01',0,10000)
insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10002,'李四',123456,,'男','活期存款','-01-02',0,0)
insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10003,'王五',123456,,'男','定时存款','-01-03',2,30000)
insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10004,'小丽',123456,,'女','定时存款','-01-04',3,40000)

create view ViewOfCurrentAccounts --参考
as
select BNo 账号,BName 姓名,BStyle 操作类型,BCash 操作金额,BDate 操作时间,BInterest 利息,BMoney 账户余额
from CurrentAccounts

select * from Depositors
select * from CurrentAccounts
select * from FixedAccounts

（3）定时取款
create procedure FixedWithdraw
@No varchar(20),
@Date datetime
as
if((select BYear from FixedAccounts where BNo = @No)= 1)
begin
if((select datediff(day,(select BDate from FixedAccounts where BNo = @No),@Date)) > 360)
begin
insert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) values(@No,(select BName from FixedAccounts where BNo = @No),'定时取款',(select BMoney from FixedAccounts where BNo = @No)*1.0275,1,@Date) --利息计算
select * from FixedAccounts where BNo = @No
end
else
print '定时存款未满十二个月！'
end

else if((select BYear from FixedAccounts where BNo = @No)= 2)
begin
if((select datediff(day,(select BDate from FixedAccounts where BNo = @No),@Date)) > 360*2)
begin
insert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) values@No,(select BName from FixedAccounts where BNo = @No),'定时取款',(select BMoney from FixedAccounts where BNo = @No)*power(1.035,2),2,@Date)
select * from FixedAccounts where BNo = @No
end
else
print '定时存款未满两年！'
end

else
begin
if((select datediff(day,(select BDate from FixedAccounts where BNo = @No),@Date)) > 360*3)
begin
insert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) values(@No,(select BName from FixedAccounts where BNo = @No),'定时取款',(select BMoney from FixedAccounts where BNo = @No)*power(1.04,3),3,@Date)
select * from FixedAccounts where BNo = @No
end

else
print '定时存款未满三年！'
end

exec FixedWithdraw 10003,'-01-04' --取款

（4）&（5）活期存取款
create proc CurrentWithdraw
@No varchar(20),
@Money float,
@Date datetime
as
declare @temp decimal(10,4)
select @temp = (((select datediff(day,(select max(BDate) from CurrentAccounts where BNo = @No),@Date))/360.0*0.0035+1)*(select BMoney from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = @No) as temp)))+@Money --目前余额
if(@Money > 0) --存款
begin
insert into CurrentAccounts(BNo,BName,BStyle,BCash,BDate,BInterest,BMoney)
values(@No,
(select distinct BName from CurrentAccounts where BNo = @No),
'活期存款',
@Money,
@Date,
((select datediff(day,(select max(BDate) from CurrentAccounts where BNo = @No),@Date))/360.0*0.0035*(select BMoney from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = @No) as temp))), --（6）利息计算
@temp)
select * from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = @No) as temp) --显示存款统计
end

else --取款
if(abs(@Money) > @temp)
print '余额不足！'
else
begin
insert into CurrentAccounts(BNo,BName,BStyle,BCash,BDate,BInterest,BMoney)
values(@No,
(select distinct BName from CurrentAccounts where BNo = @No),
'活期取款',
abs(@Money),
@Date,
((select datediff(day,(select max(BDate) from CurrentAccounts where BNo = @No),@Date))/360.0*0.0035*(select BMoney from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = @No) as temp))),
@temp)
select * from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = @No) as temp) --显示取款统计
end

exec CurrentWithdraw 100015000,'-03-30' --存款
exec CurrentWithdraw 10001,-5000,'-05-30' --取款
exec CurrentWithdraw 10001,5000,'-07-30' --存款
exec CurrentWithdraw 10001,-0,'-08-30' --取款，返回消息：余额不足！

（7）
活期明细
create proc DetailOfCurrentAccount --活期明细
@no varchar(20)
as
select * from CurrentAccounts where BNo = @no

exec DetailOfCurrentAccount 10001

定时明细
create proc DetailOfFixedAccount --定时明细
@no varchar(20)
as
select * from FixedAccounts where BNo = @no

exec DetailOfFixedAccount 10003

（8）数据库备份和恢复使用图形化界面操作即可