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

《2022年数据库课程设计-银行储蓄系统完全代码 3.pdf》由会员分享，可在线阅读，更多相关《2022年数据库课程设计-银行储蓄系统完全代码 3.pdf（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)notnull,-姓名BPasswordchar(6)not

2、nullcheck(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)notnullchec

3、k(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),-操作金

4、额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(BStyl

5、e=定期存款 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 insertintoCurrentAccount

6、s(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=

7、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

8、-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)insertintoD

9、epositors(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*fromCurre

10、ntAccounts 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)valu

11、es(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,

12、(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 定期

13、存款未满两年！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*from

14、FixedAccounts 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 CurrentAccount

15、s 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 fromCurr

16、entAccounts 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 whe

17、renID=(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,(sel

18、ect 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 Current

19、Accounts 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 页 -