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

数据库课程设计完全代码-- 建库createdatabase Bank onprimary(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 createtable Depositors(BNo varchar ( 20) primarykey , -- 账号BName varchar (20)notnull,-- 姓名BPasswordchar( 6)notnullcheck(len (BPassword)= 6 ), -- 密码BID varchar ( 20)notnull,-- 身份证号BSex char ( 2)notnullcheck( BSex =' 男' orBSex=' 女' ), -- 性别BStyle varchar ( 20)notnullcheck( BStyle =' 活期存款 ' orBStyle =' 定期存款 ' ), -- 业务类型BDatedatetime notnull,-- 开户时间BYearint notnullcheck(BYear= 0 orBYear= 1 orBYear= 2 or BYear= 3 ), -- 存款期限 ,0 表示活期BMoney decimal ( 10, 4)notnullcheck( BMoney >= 0 ) -- 账户余额)createtableCurrentAccounts (nIDintprimarykeyidentity(1, 1), -- 流水号BNo varchar ( 20)notnullreferences Depositors( BNo ), -- 账号BName varchar (20)notnull,-- 姓名BStyle varchar ( 20)notnullcheck( BStyle =' 活期存款 ' orBStyle =' 活期取款 ' ), -- 操作类型名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 6 页 - - - - - - - - - BCash decimal (10, 4)nullcheck( BCash >= 0 ), -- 操作金额BDatedatetime notnull,-- 操作时间BInterestdecimal (10, 4)nullcheck( BInterest>= 0 ), -- 利息BMoney decimal ( 10, 4)notnullcheck( BMoney >= 0 ), -- 账户余额)createtableFixedAccounts (nIDintprimarykeyidentity(1, 1), -- 流水号BNo varchar ( 20)notnullreferences Depositors( BNo ), -- 账号BName varchar (20)notnull,-- 姓名BStyle varchar ( 20)notnullcheck( BStyle =' 定期存款 ' orBStyle =' 定期取款 ' ), -- 操作类型BMoney decimal ( 10, 4)notnullcheck( BMoney >= 0 ), -- 存取金额BYearint notnullcheck(BYear= 1 orBYear= 2 orBYear= 3 ), -- 存款期限BDatedatetime notnull-- 存款时间插入触发器createtriggerInsertIntoCAorFAon Depositors afterinsertasdeclare @year intselect @year =BYearfrom inserted if @year = 0 insertintoCurrentAccounts (BNo , BName , BStyle , BDate, BMoney ) select BNo , BName , BStyle , BDate, BMoney from inserted elseinsertintoFixedAccounts (BNo , BName , BStyle , BMoney , BYear, BDate) select BNo , BName , BStyle ,BMoney , BYear, BDatefrom inserted 删除触发器createtriggerDeleteFromCAorFAon Depositors insteadofdeleteasdeclare @no varchar ( 20)select @no =BNo from deleted deletefrom CurrentAccounts whereBNo = @no deletefrom FixedAccounts whereBNo = @no deletefrom Depositors whereBNo = @no 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 6 页 - - - - - - - - - （1）开户登记 &（2）定期存款insertintoDepositors ( BNo , BName , BPassword, BID, BSex, BStyle , BDate, BYear, BMoney ) values (10001, ' 张三' , 123456, 1405115001, ' 男' , ' 活期存款 ' , '2016-01-01', 0, 10000)insertintoDepositors ( BNo , BName , BPassword, BID, BSex, BStyle , BDate, BYear, BMoney ) values (10002, ' 李四' , 123456, 1405115002, ' 男' , ' 活期存款 ' , '2016-01-02', 0, 20000)insertintoDepositors ( BNo , BName , BPassword, BID, BSex, BStyle , BDate, BYear, BMoney ) values (10003, ' 王五' , 123456, 1405115003, ' 男' , ' 定期存款 ' , '2016-01-03', 2, 30000)insertintoDepositors ( BNo , BName , BPassword, BID, BSex, BStyle , BDate, BYear, BMoney ) values (10004, ' 小丽' , 123456, 1405115004, ' 女' , ' 定期存款 ' , '2016-01-04', 3, 40000)createview ViewOfCurrentAccounts--参考asselect BNo 账号, BName 姓名 , BStyle 操作类型 , BCash 操作金额 , BDate操作时间 , BInterest利息 , BMoney账户余额fromCurrentAccounts select * from Depositors select * fromCurrentAccounts select * fromFixedAccounts （3）定期取款createprocedureFixedWithdraw @No varchar ( 20),@Date datetimeasif (( select BYearfrom FixedAccounts whereBNo = @No )= 1 )beginif (( select datediff( day,( select BDatefromFixedAccounts whereBNo = @No ), @Date ))> 360)begininsertintoFixedAccounts (BNo , BName , BStyle , BMoney , BYear, BDate) values (@No ,( select BNamefromFixedAccounts whereBNo = @No ), ' 定期取款 ' ,( select BMoney fromFixedAccounts whereBNo =@No )* 1.0275 , 1, @Date ) -- 利息计算名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 6 页 - - - - - - - - - select * fromFixedAccounts whereBNo = @No endelseprint ' 定期存款未满一年！'endelseif(( select BYearfrom FixedAccounts whereBNo = @No )= 2 )beginif (( select datediff( day,( select BDatefromFixedAccounts whereBNo = @No ), @Date ))> 360*2)begininsertintoFixedAccounts (BNo , BName , BStyle , BMoney , BYear, BDate) values (@No ,( select BNamefromFixedAccounts whereBNo = @No ), ' 定期取款 ' ,( select BMoney fromFixedAccounts whereBNo =@No )* power( 1.035 , 2), 2, @Date )select * fromFixedAccounts whereBNo = @No endelseprint ' 定期存款未满两年！'endelsebeginif (( select datediff( day,( select BDatefromFixedAccounts whereBNo = @No ), @Date ))> 360*3)begininsertintoFixedAccounts (BNo , BName , BStyle , BMoney , BYear, BDate) values (@No ,( select BNamefromFixedAccounts whereBNo = @No ), ' 定期取款 ' ,( select BMoney fromFixedAccounts whereBNo =@No )* power( 1.04, 3), 3, @Date )select * fromFixedAccounts whereBNo = @No endelseprint ' 定期存款未满三年！'endexecFixedWithdraw10003 , '2018-01-04'-- 取款（4）&（5）活期存取款createproc CurrentWithdraw 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 6 页 - - - - - - - - - @No varchar ( 20),@Money float,@Date datetimeasdeclare @temp decimal ( 10, 4)select @temp =((( select datediff( day,( select max ( BDate) from CurrentAccounts whereBNo =@No ), @Date ))/ 360.0 *0.0035 +1)*( select BMoney from CurrentAccounts wherenID=(select max (temp. nID) from ( select nIDfromCurrentAccounts whereBNo = @No ) as temp )))+ @Money -- 当前余额if ( @Money > 0 ) -- 存款begininsertintoCurrentAccounts (BNo , BName , BStyle , BCash , BDate, BInterest, BMoney )values ( @No ,( selectdistinctBName fromCurrentAccounts whereBNo = @No ),' 活期存款 ' ,@Money ,@Date ,(( select datediff( day,( select max ( BDate) from CurrentAccounts whereBNo =@No ), @Date ))/ 360.0 *0.0035 *( select BMoney fromCurrentAccounts wherenID=(select max ( temp. nID)from (select nIDfrom CurrentAccounts whereBNo = @No )as temp ))),-- （6）利息计算@temp )select *from CurrentAccounts wherenID=(select max (temp. nID)from ( select nIDfrom CurrentAccounts whereBNo = @No ) as temp ) -- 显示存款记录endelse -- 取款if ( abs( @Money)> @temp )print ' 余额不足！ 'elsebegininsertintoCurrentAccounts (BNo , BName , BStyle , BCash , BDate, BInterest, BMoney )values ( @No ,( selectdistinctBName fromCurrentAccounts whereBNo = @No ),' 活期取款 ' ,abs( @Money),@Date ,(( select datediff( day,( select max ( BDate) from CurrentAccounts whereBNo =@No ), @Date ))/ 360.0 *0.0035 *( select BMoney fromCurrentAccounts wherenID=(select max ( temp. nID)from (select nIDfrom CurrentAccounts whereBNo = @No )as temp ))),@temp )select *from CurrentAccounts wherenID=(select max (temp. nID)from ( select nIDfrom CurrentAccounts whereBNo = @No ) as temp ) -- 显示取款记录end execCurrentWithdraw10001 , 5000, '2016-03-30'-- 存款名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 6 页 - - - - - - - - - execCurrentWithdraw10001 ,- 5000, '2016-05-30'-- 取款execCurrentWithdraw10001 , 5000, '2016-07-30'-- 存款execCurrentWithdraw10001 ,- 20000, '2016-08-30'-- 取款，返回消息：余额不足！（7）活期明细createproc DetailOfCurrentAccount-- 活期明细@no varchar ( 20)asselect * fromCurrentAccounts whereBNo = @no execDetailOfCurrentAccount10001 定期明细createproc DetailOfFixedAccount-- 定期明细@no varchar ( 20)asselect * fromFixedAccounts whereBNo = @no execDetailOfFixedAccount10003 （8）数据库备份与恢复使用图形化界面操作即可名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 6 页 - - - - - - - - - 。