《2022年数据库课程设计-银行储蓄系统完全代码 3》由会员分享，可在线阅读，更多相关《2022年数据库课程设计-银行储蓄系统完全代码 3（6页珍藏版）》请在金锄头文库上搜索。
1、数据库课程设计完全代码- 建库createdatabase Bank onprimary(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 Bank createtable Depositors(BNo varchar ( 20) primarykey , - 账号BName varchar (20)notnul
2、l,- 姓名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
3、 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 = 活
4、期取款 ), - 操作类型名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 6 页 - - - - - - - - - BCash decimal (10, 4)nullcheck( BCash = 0 ), - 操作金额BDatedatetime notnull,- 操作时间BInterestdecimal (10, 4)nullcheck( BInterest= 0 ), - 利息BMoney decimal ( 10, 4)notnullcheck( BMoney =
5、 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 ), - 存取金额BYeari
6、nt 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 ,
7、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 Curre
8、ntAccounts whereBNo = no deletefrom FixedAccounts whereBNo = no deletefrom Depositors whereBNo = no 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 6 页 - - - - - - - - - （1）开户登记 &（2）定期存款insertintoDepositors ( BNo , BName , BPassword, BID, BSex, BStyle , BDate, B
9、Year, 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, BI
10、D, 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 ViewOfCurrent
11、Accounts-参考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 FixedAc
12、counts 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 where
13、BNo =No )* 1.0275 , 1, Date ) - 利息计算名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 6 页 - - - - - - - - - select * fromFixedAccounts whereBNo = No endelseprint 定期存款未满一年！endelseif( select BYearfrom FixedAccounts whereBNo = No )= 2 )beginif ( select datediff( day,
14、( 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 * fromFixe
15、dAccounts 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 BM
16、oney fromFixedAccounts whereBNo =No )* power( 1.04, 3), 3, Date )select * fromFixedAccounts whereBNo = No endelseprint 定期存款未满三年！endexecFixedWithdraw10003 , 2018-01-04- 取款（4）&（5）活期存取款createproc CurrentWithdraw 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 6 页 -
17、 - - - - - - - - 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 nIDfr
18、omCurrentAccounts 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)
19、 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 CurrentAcc
20、ounts 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,(
21、 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
22、 CurrentAccounts whereBNo = No ) as temp ) - 显示取款记录end execCurrentWithdraw10001 , 5000, 2016-03-30- 存款名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 6 页 - - - - - - - - - execCurrentWithdraw10001 ,- 5000, 2016-05-30- 取款execCurrentWithdraw10001 , 5000, 2016-07-3
23、0- 存款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 页 - - - - - - - - -
