已阅读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. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 公益活动形象宣传大使合同
- 保险公司法务专员招聘协议
- 社会组织暂行管理办法
- 拆迁补偿安置
- 培训社区防疫知识与技能
- 博物馆消防工程升级合同
- 在线旅游平台风险管理
- 环卫垃圾处理机械租赁协议
- 玩具公司续租合同样本
- 影楼员工招聘协议
- 2024至2030年中国建筑设计行业市场调研分析及发展趋势研究预测报告
- 《食品营销学》教案全本
- 2023年度学校食堂食品从业人员考核试题(附答案)
- 主题人像摄影智慧树知到答案2024年四川工商职业技术学院
- 餐饮服务食品安全规范2024
- 酒业有限公司财务管理制度方案
- 高空蜘蛛人施工专项施工方案
- 全国新世纪版信息技术七年级上册第一单元第四课《电脑是如何工作的》教学设计
- 立冬主题课件
- 工程伦理与工程认识智慧树知到期末考试答案章节答案2024年哈尔滨工程大学
- 上海市2023-2024学年高一化学上学期期中试题
评论
0/150
提交评论