数据库课程设计银行储蓄系统完全代码Word格式文档下载.docx
- 文档编号:20294545
- 上传时间:2023-01-21
- 格式:DOCX
- 页数:7
- 大小:17.01KB
数据库课程设计银行储蓄系统完全代码Word格式文档下载.docx
《数据库课程设计银行储蓄系统完全代码Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《数据库课程设计银行储蓄系统完全代码Word格式文档下载.docx(7页珍藏版)》请在冰豆网上搜索。
logon
Bank_log'
filename='
\project\Bank_log.ldf'
size=2,
filegrowth=1
go
--建表
useBank
createtableDepositors(
BNovarchar(20)primarykey,--账号
BNamevarchar(20)notnull,--姓名
BPasswordchar(6)notnullcheck(len(BPassword)=6),--密码
BIDvarchar(20)notnull,--身份证号
BSexchar
(2)notnullcheck(BSex='
男'
orBSex='
女'
),--性别
BStylevarchar(20)notnullcheck(BStyle='
活期存款'
orBStyle='
定期存款'
),--业务类型
BDatedatetimenotnull,--开户时间
BYearintnotnullcheck(BYear=0orBYear=1orBYear=2orBYear=3),--存款期限,0表示活期
BMoneydecimal(10,4)notnullcheck(BMoney>
=0)--账户余额
createtableCurrentAccounts(
nIDintprimarykeyidentity(1,1),--流水号
BNovarchar(20)notnullreferencesDepositors(BNo),--账号
活期取款'
),--操作类型
BCashdecimal(10,4)nullcheck(BCash>
=0),--操作金额
BDatedatetimenotnull,--操作时间
BInterestdecimal(10,4)nullcheck(BInterest>
=0),--利息
=0),--账户余额
createtableFixedAccounts(
定期取款'
=0),--存取金额
BYearintnotnullcheck(BYear=1orBYear=2orBYear=3),--存款期限
BDatedatetimenotnull--存款时间
插入触发器
createtriggerInsertIntoCAorFAonDepositors
afterinsert
as
declare@yearint
select@year=BYearfrominserted
if@year=0
insertintoCurrentAccounts(BNo,BName,BStyle,BDate,BMoney)selectBNo,BName,BStyle,BDate,BMoneyfrominserted
else
insertintoFixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)selectBNo,BName,BStyle,BMoney,BYear,BDatefrominserted
删除触发器
createtriggerDeleteFromCAorFAonDepositors
insteadofdelete
declare@novarchar(20)
select@no=BNofromdeleted
deletefromCurrentAccountswhereBNo=@no
deletefromFixedAccountswhereBNo=@no
deletefromDepositorswhereBNo=@no
(1)开户登记&
(2)定期存款
insertintoDepositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10001,'
张三'
123456,,'
'
-01-01'
0,10000)
insertintoDepositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10002,'
李四'
-01-02'
0,0)
insertintoDepositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10003,'
王五'
-01-03'
2,30000)
insertintoDepositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10004,'
小丽'
-01-04'
3,40000)
createviewViewOfCurrentAccounts--参考
selectBNo账号,BName姓名,BStyle操作类型,BCash操作金额,BDate操作时间,BInterest利息,BMoney账户余额
fromCurrentAccounts
select*fromDepositors
select*fromCurrentAccounts
select*fromFixedAccounts
(3)定期取款
createprocedureFixedWithdraw
@Novarchar(20),
@Datedatetime
if((selectBYearfromFixedAccountswhereBNo=@No)=1)
begin
if((selectdatediff(day,(selectBDatefromFixedAccountswhereBNo=@No),@Date))>
360)
begin
insertintoFixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)values(@No,(selectBNamefromFixedAccountswhereBNo=@No),'
(selectBMoneyfromFixedAccountswhereBNo=@No)*1.0275,1,@Date)--利息计算
select*fromFixedAccountswhereBNo=@No
end
else
print'
定期存款未满一年!
'
end
elseif((selectBYearfromFixedAccountswhereBNo=@No)=2)
360*2)
(selectBMoneyfromFixedAccountswhereBNo=@No)*power(1.035,2),2,@Date)
end
定期存款未满两年!
360*3)
(selectBMoneyfromFixedAccountswhereBNo=@No)*power(1.04,3),3,@Date)
定期存款未满三年!
execFixedWithdraw10003,'
--取款
(4)&
(5)活期存取款
createprocCurrentWithdraw
@Moneyfloat,
declare@tempdecimal(10,4)
select@temp=(((selectdatediff(day,(selectmax(BDate)fromCurrentAccountswhereBNo=@No),@Date))/360.0*0.0035+1)*(selectBMoneyfromCurrentAccountswherenID=(selectmax(temp.nID)from(selectnIDfromCurrentAccountswhereBNo=@No)astemp)))+@Money--当前余额
if(@Money>
0)--存款
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)astemp))),--(6)利息计算
@temp)
select*fromCurrentAccountswherenID=(selectmax(temp.nID)from(selectnIDfromCurrentAccountswhereBNo=@No)astemp)--显示存款记录
else--取款
if(abs(@Money)>
@temp)
print'
余额不足!
abs(@Money),
((selectdatediff(day,(selectmax(BDate)fromCurrentAccountswhereBNo=@No),@Date))/360.0*0.0035*(selectBMoneyfromCurrentAccountswherenID=(selectmax(temp.nID)from(selectnIDfromCurrentAccountswhereBNo=@No)astemp))),
select*fromCurrentAccountswherenID=(selectmax(temp.nID)from(selectnIDfromCurrentAccountswhereBNo=@No)astemp)--显示取款记录
execCurrentWithdraw10001,5000,'
-03-30'
--存款
execCurrentWithdraw10001,-5000,'
-05-30'
--取款
-07-30'
execCurrentWithdraw10001,-0,'
-08-30'
--取款,返回消息:
(7)
活期明细
createprocDetailOfCurrentAccount--活期明细
@novarchar(20)
select*fromCurrentAccountswhereBNo=@no
execDetailOfCurrentAccount10001
定期明细
createprocDetailOfFixedAccount--定期明细
select*fromFixedAccountswhereBNo=@no
execDetailOfFixedAccount10003
(8)数据库备份与恢复使用图形化界面操作即可
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 课程设计 银行 储蓄 系统 完全 代码