数据库课程设计-银行储蓄系统完全代码_第1页
数据库课程设计-银行储蓄系统完全代码_第2页
数据库课程设计-银行储蓄系统完全代码_第3页
数据库课程设计-银行储蓄系统完全代码_第4页
数据库课程设计-银行储蓄系统完全代码_第5页
已阅读5页,还剩1页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

.数据库课程设计完全代码-建库create database Bankon primary(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 Bankcreate table Depositors(BNo varchar(20) primary key, -账号BName varchar(20) not null, -姓名BPassword char(6) not null check(len(BPassword) = 6), -密码BID varchar(20) not null, -身份证号BSex char(2) not null check(BSex = 男 or BSex = 女), -性别BStyle varchar(20) not null check(BStyle = 活期存款 or BStyle = 定期存款), -业务类型BDate datetime not null, -开户时间BYear int not null check(BYear = 0 or BYear = 1 or BYear = 2 or BYear = 3), -存款期限,0表示活期BMoney decimal(10,4) not null check(BMoney = 0) -账户余额)create table CurrentAccounts(nID int primary key identity(1,1), -流水号BNo varchar(20) not null references Depositors(BNo), -账号BName varchar(20) not null, -姓名.BStyle varchar(20) not null check(BStyle = 活期存款 or BStyle = 活期取款), -操作类型BCash decimal(10,4) null check(BCash = 0), -操作金额BDate datetime not null, -操作时间BInterest decimal(10,4) null check(BInterest = 0), -利息BMoney decimal(10,4) not null check(BMoney = 0), -账户余额)create table FixedAccounts(nID int primary key identity(1,1), -流水号BNo varchar(20) not null references Depositors(BNo), -账号BName varchar(20) not null, -姓名BStyle varchar(20) not null check(BStyle = 定期存款 or BStyle = 定期取款), -操作类型BMoney decimal(10,4) not null check(BMoney = 0), -存取金额BYear int not null check(BYear = 1 or BYear = 2 or BYear = 3), -存款期限BDate datetime not null -存款时间插入触发器create trigger InsertIntoCAorFA on Depositorsafter insertasdeclare year intselect year = BYear from insertedif year = 0insert into CurrentAccounts(BNo,BName,BStyle,BDate,BMoney) select BNo,BName,BStyle,BDate,BMoney from insertedelseinsert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) select BNo,BName,BStyle,BMoney,BYear,BDate from inserted删除触发器create trigger DeleteFromCAorFA on Depositorsinstead of deleteasdeclare no varchar(20)select no = BNo from deleteddelete from CurrentAccounts where BNo = nodelete from FixedAccounts where BNo = nodelete from Depositors where BNo = no.(1)开户登记&(2)定期存款insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10001,张三,123456,1405115001,男,活期存款,2016-01-01,0,10000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10002,李四,123456,1405115002,男,活期存款,2016-01-02,0,20000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10003,王五,123456,1405115003,男,定期存款,2016-01-03,2,30000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10004,小丽,123456,1405115004,女,定期存款,2016-01-04,3,40000)create view ViewOfCurrentAccounts -参考asselect BNo 账号,BName 姓名,BStyle 操作类型,BCash 操作金额,BDate 操作时间,BInterest 利息,BMoney 账户余额from CurrentAccountsselect * from Depositorsselect * from CurrentAccountsselect * from FixedAccounts(3)定期取款create procedure FixedWithdrawNo varchar(20),Date datetimeasif(select BYear from FixedAccounts where BNo = No)= 1)beginif(select datediff(day,(select BDate from FixedAccounts where BNo = No),Date) 360)begininsert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) values(No,(select BName from FixedAccounts where BNo = No),定期取款,(select BMoney from FixedAccounts where BNo = No)*1.0275,1,Date) -利息计算select * from FixedAccounts where BNo = Noend elseprint 定期存款未满一年!end.else if(select BYear from FixedAccounts where BNo = No)= 2)beginif(select datediff(day,(select BDate from FixedAccounts where BNo = No),Date) 360*2)begininsert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) values(No,(select BName from FixedAccounts where BNo = No),定期取款,(select BMoney from FixedAccounts where BNo = No)*power(1.035,2),2,Date)select * from FixedAccounts where BNo = Noendelseprint 定期存款未满两年!endelsebeginif(select datediff(day,(select BDate from FixedAccounts where BNo = No),Date) 360*3)begininsert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) values(No,(select BName from FixedAccounts where BNo = No),定期取款,(select BMoney from FixedAccounts where BNo = No)*power(1.04,3),3,Date)select * from FixedAccounts where BNo = Noendelseprint 定期存款未满三年!endexec FixedWithdraw 10003,2018-01-04 -取款(4)&(5)活期存取款create proc CurrentWithdrawNo varchar(20),Money float,Date datetimeasdeclare temp decimal(10,4)select temp = (select datediff(day,(select max(BDate) from CurrentAccounts where BNo .= No),Date)/360.0*0.0035+1)*(select BMoney from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = No) as temp)+Money -当前余额if(Money 0) -存款begininsert into CurrentAccounts(BNo,BName,BStyle,BCash,BDate,BInterest,BMoney)values(No,(select distinct BName from CurrentAccounts where BNo = No),活期存款,Money,Date,(select datediff(day,(select max(BDate) from CurrentAccounts where BNo = No),Date)/360.0*0.0035*(select BMoney from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = No) as temp), -(6)利息计算temp)select * from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = No) as temp) -显示存款记录endelse -取款if(abs(Money) temp)print 余额不足!elsebegininsert into CurrentAccounts(BNo,BName,BStyle,BCash,BDate,BInterest,BMoney)values(No,(select distinct BName from CurrentAccounts where BNo = No),活期取款,abs(Money),Date,(select datediff(day,(select max(BDate) from CurrentAccounts where BNo = No),Date)/360.0*0.0035*(select BMoney from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = No) as temp),temp)select * from

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论