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

数据库课程设计完全代码

--建库

createdatabase Bank onprimary (

name ='Bank',

filename='D:\\project\\Bank.mdf', size = 5, maxsize= 100, filegrowth= 10% ) logon (

name ='Bank_log',

filename='D:\\project\\Bank_log.ldf', size=2, filegrowth=1 ) go

--建表 use Bank

createtable Depositors(

BNovarchar(20)primarykey,--账号 BNamevarchar(20)notnull,--姓名

BPasswordchar(6)notnullcheck(len(BPassword)= 6),--密码 BID varchar(20)notnull,--身份证号

BSexchar(2)notnullcheck(BSex='男'orBSex='女'),--性别

BStylevarchar(20)notnullcheck(BStyle='活期存款'orBStyle='定期存款'),--业务类型 BDatedatetimenotnull,--开户时间

BYearintnotnullcheck(BYear= 0 orBYear= 1 orBYear= 2 orBYear= 3),--存款期限,0表示活期 BMoneydecimal(10,4)notnullcheck(BMoney>= 0)--账户余额 )

createtableCurrentAccounts(

nIDintprimarykeyidentity(1,1),--流水号

BNovarchar(20)notnullreferences Depositors(BNo),--账号 BNamevarchar(20)notnull,--姓名

BStylevarchar(20)notnullcheck(BStyle='活期存款'orBStyle='活期取款'),--操作类型

BCashdecimal(10,4)nullcheck(BCash>= 0),--操作金额 BDatedatetimenotnull,--操作时间

BInterestdecimal(10,4)nullcheck(BInterest>= 0),--利息 BMoneydecimal(10,4)notnullcheck(BMoney>= 0),--账户余额 )

createtableFixedAccounts(

nIDintprimarykeyidentity(1,1),--流水号

BNovarchar(20)notnullreferences Depositors(BNo),--账号 BNamevarchar(20)notnull,--姓名

BStylevarchar(20)notnullcheck(BStyle='定期存款'orBStyle='定期取款'),--操作类型 BMoneydecimal(10,4)notnullcheck(BMoney>= 0),--存取金额

BYearintnotnullcheck(BYear= 1 orBYear= 2 orBYear= 3),--存款期限 BDatedatetimenotnull--存款时间

插入触发器

createtriggerInsertIntoCAorFAon Depositors afterinsert as

declare @year int

select @year =BYearfrom inserted if @year = 0

insertintoCurrentAccounts(BNo,BName,BStyle,BDate,BMoney)selectBNo,BName,BStyle,BDate,BMoneyfrom inserted else

insertintoFixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)selectBNo,BName,BStyle,BMoney,BYear,BDatefrom inserted

删除触发器

createtriggerDeleteFromCAorFAon Depositors insteadofdelete as

declare @no varchar(20) select @no =BNofrom deleted

deletefromCurrentAccountswhereBNo= @no deletefromFixedAccountswhereBNo= @no deletefrom Depositors whereBNo= @no

（1）开户登记&（2）定期存款

insertinto

Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10001,'张三',123456,1405115001,'男','活期存款','2016-01-01',0,10000) insertinto

Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10002,'李四',123456,1405115002,'男','活期存款','2016-01-02',0,20000) insertinto

Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10003,'王五',123456,1405115003,'男','定期存款','2016-01-03',2,30000) insertinto

Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10004,'小丽',123456,1405115004,'女','定期存款','2016-01-04',3,40000)

createviewViewOfCurrentAccounts--参考 as

selectBNo账号,BName姓名,BStyle操作类型,BCash操作金额,BDate操作时间,BInterest利息,BMoney账户余额

fromCurrentAccounts

select*from Depositors select*fromCurrentAccounts select*fromFixedAccounts

（3）定期取款

createprocedureFixedWithdraw @No varchar(20), @Date datetime as

if((selectBYearfromFixedAccountswhereBNo= @No)= 1) begin

insertintoFixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)values(@No,(selectBNamefromFixedAccountswhereBNo= @No),'定期取款',(selectBMoneyfromFixedAccountswhereBNo= @No)*1.0275,1,@Date)--利息计算

if((selectdatediff(day,(selectBDatefromFixedAccountswhereBNo= @No),@Date))> 360) begin

end

end

select*fromFixedAccountswhereBNo= @No

else

print'定期存款未满一年！'

elseif((selectBYearfromFixedAccountswhereBNo= @No)= 2) begin

insertintoFixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)values(@No,(selectBNamefromFixedAccountswhereBNo= @No),'定期取款',(selectBMoneyfromFixedAccountswhereBNo= @No)*power(1.035,2),2,@Date) end else begin

insertintoFixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)values(@No,(selectBNamefromFixedAccountswhereBNo= @No),'定期取款',(selectBMoneyfromFixedAccountswhereBNo= @No)*power(1.04,3),3,@Date) end

execFixedWithdraw10003,'2018-01-04'--取款

end else

print'定期存款未满三年！'

select*fromFixedAccountswhereBNo= @No

if((selectdatediff(day,(selectBDatefromFixedAccountswhereBNo= @No),@Date))> 360*3) begin end else

print'定期存款未满两年！'

select*fromFixedAccountswhereBNo= @No

if((selectdatediff(day,(selectBDatefromFixedAccountswhereBNo= @No),@Date))> 360*2) begin

（4）&（5）活期存取款

createprocCurrentWithdraw

@No varchar(20), @Money float, @Date datetime as

declare @temp decimal(10,4)

select @temp =(((selectdatediff(day,(selectmax(BDate)fromCurrentAccountswhereBNo= @No),@Date))/360.0*0.0035+1)*(selectBMoneyfromCurrentAccountswherenID=(selectmax(temp.nID)from (selectnIDfromCurrentAccountswhereBNo= @No)as temp)))+@Money --当前余额 if(@Money > 0)--存款 begin

insertintoCurrentAccounts(BNo,BName,BStyle,BCash,BDate,BInterest,BMoney)

values(@No,

(selectdistinctBNamefromCurrentAccountswhereBNo= @No), '活期存款', @Money, @Date,

((selectdatediff(day,(selectmax(BDate)fromCurrentAccountswhereBNo=

@No),@Date))/360.0*0.0035*(selectBMoneyfromCurrentAccountswherenID=(selectmax(temp.nID)from (selectnIDfromCurrentAccountswhereBNo= @No)as temp))),--（6）利息计算 end

else--取款

if(abs(@Money)> @temp)

print'余额不足！' else begin

insertintoCurrentAccounts(BNo,BName,BStyle,BCash,BDate,BInterest,BMoney)

values(@No,

(selectdistinctBNamefromCurrentAccountswhereBNo= @No), '活期取款', abs(@Money), @Date,

((selectdatediff(day,(selectmax(BDate)fromCurrentAccountswhereBNo=

@temp)

select*fromCurrentAccountswherenID=(selectmax(temp.nID)from

(selectnIDfromCurrentAccountswhereBNo= @No)as temp)--显示存款记录

@No),@Date))/360.0*0.0035*(selectBMoneyfromCurrentAccountswherenID=(selectmax(temp.nID)from (selectnIDfromCurrentAccountswhereBNo= @No)as temp))), end

@temp)

select*fromCurrentAccountswherenID=(selectmax(temp.nID)from

(selectnIDfromCurrentAccountswhereBNo= @No)as temp)--显示取款记录

execCurrentWithdraw10001,5000,'2016-03-30'--存款

execCurrentWithdraw10001,-5000,'2016-05-30'--取款 execCurrentWithdraw10001,5000,'2016-07-30'--存款

execCurrentWithdraw10001,-20000,'2016-08-30'--取款，返回消息：余额不足！

（7） 活期明细

createprocDetailOfCurrentAccount--活期明细 @no varchar(20) as

select*fromCurrentAccountswhereBNo= @no

execDetailOfCurrentAccount10001

定期明细

createprocDetailOfFixedAccount--定期明细 @no varchar(20) as

select*fromFixedAccountswhereBNo= @no

execDetailOfFixedAccount10003

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

execCurrentWithdraw10001,-5000,'2016-05-30'--取款 execCurrentWithdraw10001,5000,'2016-07-30'--存款

execCurrentWithdraw10001,-20000,'2016-08-30'--取款，返回消息：余额不足！

（7） 活期明细

createprocDetailOfCurrentAccount--活期明细 @no varchar(20) as

select*fromCurrentAccountswhereBNo= @no

execDetailOfCurrentAccount10001

定期明细

createprocDetailOfFixedAccount--定期明细 @no varchar(20) as

select*fromFixedAccountswhereBNo= @no

execDetailOfFixedAccount10003

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

本文来源：https://www.bwwdw.com/article/r6q6.html