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

数据库课程设计完全代码-建库create database Bankon primary(name = Bank,filename=D:projectBank.mdf,size = 5,maxsize = 100,filegrowth = 10%)log on(name = Bank_log,filename = D:projectBank_log.ldf,size=2,filegrowth=1)go-建表use Bankcreate 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 Depositorsafter insertasdeclare year intselect year = BYear from insertedif year = 0insert into CurrentAccounts(BNo,BName,BStyle,BDate,BMoney) select BNo,BName,BStyle,BDate,BMoney from insertedelseinsert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) select BNo,BName,BStyle,BMoney,BYear,BDate from inserted删除触发器create trigger DeleteFromCAorFA on Depositorsinstead of deleteasdeclare no varchar(20)select no = BNo from deleteddelete from CurrentAccounts where BNo = nodelete from FixedAccounts where BNo = nodelete from Depositors where BNo = no（1）开户登记&（2）定期存款insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10001,张三,123456,1405115001,男,活期存款,2016-01-01,0,10000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10002,李四,123456,1405115002,男,活期存款,2016-01-02,0,20000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10003,王五,123456,1405115003,男,定期存款,2016-01-03,2,30000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10004,小丽,123456,1405115004,女,定期存款,2016-01-04,3,40000)create view ViewOfCurrentAccounts -参考asselect BNo 账号,BName 姓名,BStyle 操作类型,BCash 操作金额,BDate 操作时间,BInterest 利息,BMoney 账户余额from CurrentAccountsselect * from Depositorsselect * from CurrentAccountsselect * from FixedAccounts（3）定期取款create procedure FixedWithdrawNo varchar(20),Date datetimeasif(select BYear from FixedAccounts where BNo = No)= 1)beginif(select datediff(day,(select BDate from FixedAccounts where BNo = No),Date) 360)begininsert 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 = Noend elseprint 定期存款未满一年！endelse if(select BYear from FixedAccounts where BNo = No)= 2)beginif(select datediff(day,(select BDate from FixedAccounts where BNo = No),Date) 360*2)begininsert 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 = Noendelseprint 定期存款未满两年！endelsebeginif(select datediff(day,(select BDate from FixedAccounts where BNo = No),Date) 360*3)begininsert 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 = Noendelseprint 定期存款未满三年！endexec FixedWithdraw 10003,2018-01-04 -取款（4）&（5）活期存取款create proc CurrentWithdrawNo varchar(20),Money float,Date datetimeasdeclare 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) -存款begininsert 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) -显示存款记录endelse -取款if(abs(Money) temp)print 余额不足！elsebegininsert 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