银行ATM取款系统（sql server数据库源码）

/* 说明：建立bandDB数据库 作者：刘鹏 时间：2010—03-11 */ /*--创建数据库，检测要创建的数据库是否存在，存在就删除数据库--*/ if exists(select * from sysdatabases where name ='bankDB') drop database bankDB create database bankDB --创建数据库 on ( name = 'bandDB_data', filename = 'F:\S2课程和作业\SQL-Server\银行ATM取款系统\bankDB.mdf', size = 10mb ) log on ( name = 'bankDB_log', filename = 'F:\S2课程和作业\SQL-Server\银行ATM取款系统\bankDB.ldf', size = 1mb, maxsize = 3mb, filegrowth = 15% ) GO /*--创建用户表，检测该表是否存在，存在就删除该表--*/ if exists(select * from sysobjects where name = 'userInfo') drop table userInfo /*--创建用户表--*/ create table userInfo ( customerID int primary key identity(1,1) not null,--主键，用户编号，表示列 customerName varchar(20) not null,--用户姓名 PID varchar(50) not null unique(PID) check(len(PID) = 18),--用户身份证号 telephone varchar(50) not null check(telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' ortelephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' ), [address] varchar(200) null--用户地址 ) /*--创建银行信息表--*/ if exists(select* from sysobjects where name = 'cardInfo') drop table cardInfo /*--创建银行信息表--*/ create table cardInfo ( cardID int primary key identity(1,1) not null ,--卡号，标识列，自动增长 curType varchar(20) not null default('RMB'),--货币类型，默认为RMB savingType varchar(10) not null, -- 存款类型 openTime datetime default(Getdate()) not null, --开户时间 openMoney money not null check(openMoney >= 1) ,--开户金额，大于1 balace money not null check(balace>=1),--余额 pass int not null check(len(pass) = 6 ),--用户密码 IsReportLoss varchar(2) not null check(IsReportLoss like ' 是' or IsReportLoss like '否') default('否'), customerID int not null --顾客编号 ) /*--创建交易信息表--*/ if exists(select * from sysobjects where name ='tranInfo') drop table tranInfo create table tranInfo ( transDate datetime not null default(Getdate()),--交易日期 cardID int not null ,--卡号 transType varchar(4) not null check(transType like '存入' or transType like '支取'),--交易类型 transMoney money not null check(transMoney > 0),--交易金额 remark text null --备注 ) /*添加银行信息表与用户表之间的外键约束*/ alter table cardInfo add constraint FK_customerID foreign key(customerID) references userInfo(customerID) /*-添加交易信息表与银行信息表的外键约束-*/ alter table tranInfo add constraint FK_cardID foreign key(cardID) references cardInfo(cardID) /*--添加测试数据--*/ exec sp_helpconstraint cardInfo --向用户表中添加数据 insert into userInfo values('张三',123456789123456789,'0100-00000000','北京市') insert into userInfo values('李四',123456789123456788,'0100-00000001','上海市') --向银行信息表中添加数据 insert into cardInfo values('1010 3576 1234 5768',default,'活期',default,'1000','1000','000000','否',1) insert into cardInfo values('1010 3576 1212 1134',default,'定期',default,'1','1','000000','否',2) /*--创建tranInfo表触发器--*/ alter trigger tri_tranInfo on tranInfo --触发的表名 for insert --功能 as declare @cardID varchar(50)--卡号 declare @Type varchar(4)--交易类型 declare @outmoneys money--交易金额 declare @Time datetime --交易时间 declare @moneys money --余额 select @moneys = balace from cardInfo where cardID = @cardID--查询并赋予变量数据 select @time = transDate,@Type = transType,@outmoneys=transMoney,@cardID = cardID from inserted if @Type ='支取' begin if(@outmoneys>@moneys) begin print '余额不足,请下次交易' rollback transaction end else begin update cardInfo set balace = balace - @outmoneys where cardID = @cardID print '交易成功!请取卡' end end else begin update cardInfo set balace = balace + @outmoneys where cardID = @cardID print '交易成功!请取卡' end GO /*--向交易信息表中添加一条数据-*/ set nocount on --不显示受影响行数 insert into tranInfo values(default,'1010 3576 1234 5768','支取','900',default) insert into tranInfo values(default,'1010 3576 1212 1134','存入','5000',default) --修改张三密码 update cardInfo set pass = '123456' where cardID = '1010 3576 1234 5768' --修改李四密码 update cardInfo set pass = '123123' where cardID = '1010 3576 1212 1134' --李四的账号挂失，修改李四的挂失状态 update cardInfo set IsReportLoss = '是' where cardID = '1010 3576 1212 1134' GO /*--查询并显示银行资金流通余额和盈利---*/ declare @inMoney money --存入总量 declare @outMoney money --总支取量 select @inMoney = sum(transMoney) from tranInfo where transType = '存入' select @outMoney = sum(transMoney) from tranInfo where transType= '支取' print '银行的流通余额总计为：'+convert(varchar(20),@inMoney-@outMoney) print '盈利结算为：'+convert(varchar(20),@outMoney*0.008-@inMoney*0.003) GO --查询本周开户的卡号 select * from cardInfo where datediff(dd,openTime,getdate()) <= 7 --本周开户的用户 /*--查询本月交易金额最高的用户卡号-*/ --声明 变量保存总的交易金额 declare @SumMoney money --当前月份 declare @month1 int set @month1 = datepart(mm,getdate()) --开户日期的月份 declare @month2 int select @month2 = datepart(mm,openTime) from cardInfo --声明变量保存卡号 declare @ID varchar(50) if (@month1 = @month2) select @ID = cardID, @SumMoney = sum(transMoney) from tranInfo group by cardID print'交易金额最高的卡号：' + @ID --查询挂失账户 print '挂失账户为：' select customerName as 姓名,telephone as 联系电话 from userInfo where customerID in(select customerID from cardInfo where IsReportLoss = '是') --催款提醒业务 print'要提醒催款的用户为：' select u.customerName as 姓名,u.telephone as 联系电话 ,c.balace as 余额 from userInfo as u join cardInfo as c on u.customerID = c.customerID where c.balace < 200 /*--创建卡号的非聚集索引--*/ --判断该索引是否存在 if exists(select name from sysindexes where name = 'ix_index_cardID') drop index tranInfo.ix_index_cardID--删除该索引 create nonclustered index ix_index_cardID--创建缩影 on tranInfo(cardID)--表 with fillfactor = 70--填充因子 GO --查询索引张三的交易记录 select * from tranInfo with(index = ix_index_cardID) where cardID = '1010 3576 1212 1134' /*--创建用户信息视图表--*/ --检查该视图是否存在 if exists(select * from sysobjects where name = 'view_userInfo') drop view view_userInfo create view view_userInfo as select 客户编号=customerID,开户姓名 = customerName,身份证号=PID,联系电话 = telephone,地址=[address] from userInfo GO /*--创建银行信息视图表--*/ --检查该视图是否存在 if exists(select * from sysobjects where name = 'view_cardInfo') drop view view_cardInfo create view view_cardInfo as select 卡号 = cardID,货币类型 =curType,存款类型 =savingType,开户日期 =openTime,余额 = balace,密码 =pass ,是否挂失 = IsReportLoss,客户编号 = customerID from cardInfo GO /*--创建交易信息视图表--*/ --检查该视图是否存在 -检查该视图是否存在 if exists(select * from sysobjects where name = 'view_Info') drop view view_Info create view view_Info as select 交易日期 = transDate ,交易类型 = transType,卡号 = cardID,交易金额 = transMoney,备注 = remark from tranInfo GO /*查询视图*/ select * from view_userInfo --用户信息表 select * from view_cardInfo --银行信息表 select * from view_Info --交易信息表 /*--创建存储过程--*/ --检测该存储过程是否存在 if exists(select * from sysobjects where name = 'proc_takeMoney') drop procedure proc_takeMoney /*-开始存储过程--*/ alter proc proc_takeMoney @card varchar(20),--卡号 @money money, -- 交易金额 @Type varchar(10),--交易类型 @pass varchar(10) = ''--密码 as declare @YuE money --余额 select @YuE = balace from cardInfo where cardID = @card if(@Type = '支取') begin if @pass = (select pass from cardInfo where cardID = @card )--判断密码 begin if(@YuE > @money)--支取金额足够 begin print '交易正在