简易银行取款系统-2

 ---修改密码

use bank

go

create proc proc_setPass

@name varchar(15),@pass varchar(6)

as

begin transaction

update dbo.cardInfo set pass=@pass where customerID=(select customerID from dbo.userInfo where customerName=@name)

if @@error <> 0

begin

rollback transaction

print @name+'密码修改失败'

end

else

begin

commit transaction

print @name+'密码修该成功'

end

go

exec proc_setPass '张三','123456'

execute proc_setPass '李四','123123'

select * from CardInfo

go

---银行卡挂失

create procedure proc_cardLoss

@card varchar(19)

as

begin transaction

update dbo.cardInfo set IsReportLoss='是' where cardID=@card

if @@error <> 0

begin

rollback transaction

print '卡号'+@card+' 挂失失败'

end

else

begin

commit transaction

print '卡号'+@card+' 挂失成功'

end

go

execute proc_cardLoss '1010 3576 1212 1134'

select * from CardInfo

go

---统计银行的资金流通余额和盈利结算

declare @balanceSave money,@balancePay money,@payoff money

select @balanceSave=sum(transMoney) from transInfo where transType='存入'

select @balancePay=sum(transMoney) from transinfo where transtype='支出'

print '资金流通余额：'+Convert(varchar(20),@balanceSave-@balancePay)+'￥'

print '盈利结算'+Convert(varchar(20),@balancePay*0.008-@balanceSave*0.003)+'￥'

select * from transInfo

---查询本周的开户信息

print '查询本周的开户信息:'

select cardID as '卡号',curType as '币种',savingType as '存储类型',openData as '开户日期',openMoney as '开户金额',balance as '当前结余',pass as '密码',IsReportLoss as '挂失状态',customerName as '用户姓名' from cardInfo,userInfo where CardInfo.customerID=userInfo.customerID and datediff(dd,openData,getdate())<7

---查询本月交易最高的卡号

print '查询本月交易最高的卡号'

select top 1 cardInfo.cardID as '卡号' from transinfo,cardInfo where transInfo.cardID=cardInfo.cardID group by cardInfo.cardID order by sum(transMoney) desc

--- 查询挂失账户的客户信息

print '挂失账户的客户信息'

select customerName,PID,telephone,address from dbo.userInfo,dbo.cardInfo where userInfo.customerID=cardInfo.customerID and IsReportLoss='是'

---催款提醒

select customerName,telephone,balance from cardInfo inner join userInfo on userInfo.customerId=cardInfo.customerID where balance<200

---创建视图

---view_userInfo

use Bank

go

if exists(select * from sysobjects where name='view_userInfo')

drop view view_userInfo

go

create view view_userInfo

as

select customerID as '客户编号',customerName as '卡户名称',PID as '身份证号',telephone as '电话',address as '地址' from userInfo

go

---view_cardInfo

if exists(select * from sysobjects where name='view_cardInfo')

drop view view_cardInfo

go

create view view_cardInfo

as

select * from cardInfo

go

----view_Info

if exists(select * from sysobjects where name='view_Info')

drop view view_Info

go

create view view_Info

as

select * from transInfo

go

---调用view

select * from view_userInfo

select * from view_cardInfo

select * from view_Info

---创建存储过程

use Bank

go

if exists(select * from sysobjects where name ='proc_saveOrPay')

drop proc proc_saveOrPay

go

create proc proc_saveOrPay

@card varchar(19),@money money,@type varchar(4),@pass varchar(6)

as

declare @error int

set @error=0

print '交易进行中……'

print '卡号：'+convert(varchar(19),@card)+' 金额：'+convert(varchar(20),@money)+' 交易类型：'+@type

set nocount on

---存款过程

if @type='存入'

begin

begin transaction

update cardInfo set balance=balance+@money where cardID=@card

set @error=@error+@@error

insert into dbo.transInfo(transData,cardID,transType,transMoney) values(getdate(),@card,@type,@money)

set @error=@error+@@error

if @error<>0

begin

rollback transaction

print '存款失败'

set nocount on

select * from view_cardInfo

select * from view_Info

end

else

begin

commit transaction

print '存款成功'

set nocount on

select * from view_cardInfo

select * from view_Info

end

end

---取款过程

else if @type='支出'

begin

---若输入的密码正确

if exists(select * from cardInfo where cardID=@card and pass=@pass)

begin

begin transaction

update cardInfo set balance=balance-@money where cardID=@card

set @error=@error+@@error

insert into dbo.transInfo(transData,cardID,transType,transMoney) values(getdate(),@card,@type,@money)

set @error=@error+@@error

if @error<>0

begin

rollback transaction

print '取款失败'

set nocount on

select * from view_cardInfo

select * from view_Info

end

else

begin

commit transaction

print '取款成功'

set nocount on

select * from view_cardInfo

select * from view_Info

end

end

---若输入的密码错误

else

begin

print '输入的密码不正确，此次交易失败'

set nocount on

select * from view_cardInfo

select * from view_Info

end

end

else

begin

print '请输入正确的存储类型'

end

go

execute proc_saveOrPay '1010 3576 1212 1134',500,'存入','888888'

execute proc_saveOrPay '1010 3576 1234 5678',300,'支出','888888'

---产生随机卡号

use bank

if exists(select * from sysobjects where name ='proc_randomCardID')

drop procedure proc_randomCardID

go

create procedure proc_randomCardID

@randID varchar(19) output

as

declare @month int,@second int,@minSecond int, @randtemp int ,@random numeric(15,8)

set @month=datepart(mm,getdate())

set @second=datepart(ss,getdate())

set @minSecond=datepart(ms,getdate())

set @randtemp=@month*100000+@second+1000+@minSecond

set @random=rand(@randtemp)

set @randID='1010 3576 '+substring(convert(varchar(20),@random),charindex('.',convert(varchar(20),@random))+1,4)+' '+right(convert(varchar(20),@random),4)

-- select right(convert(varchar(20),145462.01234567),4)

-- select substring(convert(varchar(20),145462.01234567),charindex('.',convert(varchar(20),145462.01234567))+1,4)

go

declare @cardId varchar(19)

exec proc_randomCardID @randID= @cardId output

select @cardId as '产生的随机卡号为：'

---开户的储存过程

use Bank

go

if exists(select * from sysobjects where name ='proc_open')

drop procedure proc_open

go

create procedure proc_open

@name varchar(15),@PID varchar(18),@telep varchar(13),@address varchar(30),@curType varchar(10),@saveType varchar(8),@openMoney money,@pass varchar(6)

as

begin transaction

declare @errorSum int

set @errorSum=0

insert into userInfo values(@name,@PID,@telep,@address)

set @errorSum=@@error+@errorSum

---产生随机卡号

declare @card varchar(19)

exec proc_randomCardID @randID= @card output

set @errorSum=@@error+@errorSum

---查找该用户的ID

declare @customerID int

select @customerID = customerID from userInfo order by customerID asc ---将选中结果的最后一项的值赋给@customerID

set @errorSum=@@error+@errorSum

---插入并打印银行卡信息

insert into cardInfo values(@card,@curType,@saveType,getdate(),@openMoney,@openMoney,@pass,'否',@customerID)

set @errorSum=@@error+@errorSum

if @errorSum<>0

begin

print '很抱歉！开户失败！'

rollback transaction

end

else

begin

print '开户成功'

print '尊敬的客户，开户成功！系统为您产生的随机卡号为：'+@card

print '开户日期'+convert(varchar(10),getdate(),111)+' 开户金额：'+convert(varchar(10),@openMoney)

select * from view_userInfo

select * from view_cardInfo

commit transaction

end

go

execute proc_open '王','123456789011112123','0717-12345678','湖北宜昌','RMB','定期',2000,'123456'

---创建登录用户

use Bank

execute sp_addlogin 'sysAdmin1','1234' ---SQL添加SQL登录用户

execute sp_grantdbaccess 'sysAdmin1','sysAdminDBUser1' ----创建数据库用户

grant all privileges on transinfo to sysAdminDBUser1 ---创建所有试用权限给sysAdminDBUser1

----删除用户的修改表结构的权限

revoke alter from sysAdminDBUser1