--1.修改
update [dbo].[cardInfo] set [pass]='123456'
where[cardID] ='1010 3576 1234 5678';
update [dbo].[cardInfo] set [pass]='123123'
where [cardID]='1010 3576 1212 1134';
--2.挂失
select [cardID] as 卡号,[curID] as 货币, [savingName] as 储蓄类型, [openDate] as 开户日期, [openMoney] as 开户金额, [balance] as 余额,[pass] as 密码,[IsReportLoss] as 是否挂失,[customerName] as 客户姓名
from [dbo].[cardInfo],[dbo].[userInfo],[dbo].[Deposit]
where [dbo].[cardInfo].customerID=[dbo].[userInfo].customerID and [dbo].[Deposit].[savingID]=[dbo].[cardInfo].[savingID]
update [dbo].[cardInfo] set [IsReportLoss]=1
where [dbo].[cardInfo].[cardID]='1010 3576 1212 1134';
--3.统计银行资金流通金额和盈利计算
Declare @inmoney int--总存入金额
Declare @paymoney int --总支取金额
Declare @money int
select @inmoney=SUM([tradeMoney])from [dbo].[tradeInfo]
where [tradeType]='存入';
select @paymoney =SUM([tradeMoney])from [dbo].[tradeInfo]
where [tradeType]='支取';
set @Money =@inmoney * 0.008-@paymoney * 0.003
print '银行流通余额总计为:'+convert (varchar(20),@inmoney-@paymoney)+'RMB'
print '盈利结算为:'+convert (varchar(20),@money)+'RMB'
--4.创建视图
--1
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 [dbo].[userInfo]
go
--2.查询银行卡信息
if exists (select * from sysobjects where name = 'view_cardInfo')
drop view view_cardInfo
go
create VIEW view_cardInfo --银行卡信息表视图
AS
select c.cardID as 卡号,u.customerName as 客户,c.curID as 货币种类, d.savingName as 存款类型,c.openDate as 开户日期,
c.balance as 余额,c.pass 密码,
case c.IsReportLoss when 0 then '挂失'
when 1 then '正常'
end as 是否挂失
from cardInfo c, deposit d,userinfo u
where c.savingID=d.savingID and c.customerID=u.customerID
GO
--3.查看交易信息
if exists (select * from sysobjects where name ='view——tradeInfo')
drop view view_tradeInfo
go
create view view_tradeInfo
AS
select [tradeDate] as 交易日期,[tradeType] as 交易类型,[cardID] as 卡号,[tradeMoney] as 交易金额,[remark] as 备注
from [dbo].[tradeInfo]
go
--.用例6
--1.取钱或存钱的存储过程
if exists (select * from sysobjects where name = 'usp_takeMoney')
drop proc usp_takeMoney
go
create procedure usp_takeMoney
@card char(19),
@m money,
@type char(4),
@inputPass char(6)=''
AS
print '交易正进行,请稍后......'
if (@type='支取')
if ((SELECT pass FROM cardInfo WHERE cardID=@card)<>@inputPass )
begin
raiserror ('密码错误!',16,1)
return -1
end
DECLARE @mytradeType char(4),@outMoney MONEY,@myCardID char(19)
SELECT @mytradeType=tradeType,@outMoney=tradeMoney ,@myCardID=cardID FROM tradeInfo where cardID=@card
DECLARE @mybalance money
SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card
if (@type='支取')
if (@mybalance>=@m+1)
update cardInfo set balance=balance-@m WHERE cardID=@myCardID
else
begin
raiserror ('交易失败!余额不足!',16,1)
print '卡号'+@card+' 余额:'+convert(varchar(20),@mybalance)
return -2
end
else
update cardInfo set balance=balance+@m WHERE cardID=@card
print '交易成功!交易金额:'+convert(varchar(20),@m)
SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card
print '卡号'+@card+' 余额:'+convert(varchar(20),@mybalance)
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES(@type,@card,@m)
RETURN 0
GO
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='张三'
EXEC usp_takeMoney @card,10 ,'支取','123456'
GO
select * from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='张三'
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='李四'
EXEC usp_takeMoney @card,500 ,'存入'
select * from view_cardInfo
select * from view_tradeInfo
GO
--2.产生随机卡号
if exists (select * from sysobjects where name ='usp_ID')
drop proc usp_ID
go
create procedure usp_ID @randCardID char(19) output
as
declare @rrrrr numeric(15,8)
declare @temStr char(10)
select @rrrrr=RAND((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
set @temStr=convert(char(10),@rrrrr)
set @randCardID='1010 3576 '+SUBSTRING(@temStr,3,4)+' '+SUBSTRING(@temStr,7,4)
GO
--测试产生随机卡号
DECLARE @mycardID char(19)
EXECUTE usp_ID @mycardID OUTPUT
print '产生的随机号号为:'+@mycardID
GO
--3.完成开户业务
if exists (select * from sysobjects where name = 'usp_openAccount')
drop proc usp_openAccount
GO
create procedure usp_openAccount @customerName char(8),@PID char(18),@telephone char(13)
,@openMoney money,@savingName char(8),@address varchar(50)=''
AS
DECLARE @mycardID char(19),@cur_customerID int, @savingID int
--调用产生随机卡号的存储过程获得随机卡号
EXECUTE usp_ID @mycardID OUTPUT
while exists(SELECT * FROM cardInfo WHERE cardID=@mycardID)
EXECUTE usp_ID @mycardID OUTPUT
print '尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@mycardID
print '开户日期'+convert(char(10),getdate(),111)+' 开户金额:'+convert(varchar(20),@openMoney)
IF not exists(select * from userInfo where PID=@PID)
INSERT INTO userInfo(customerName,PID,telephone,address )
VALUES(@customerName,@PID,@telephone,@address)
SELECT @savingID = savingID FROM deposit WHERE savingName =@savingName
-- SELECT savingID FROM deposit WHERE savingName ='活期'
if @savingID is NULL
BEGIN
RAISERROR('存款类型不正确,请重新输入!',16,1)
RETURN -1
END
--PRINT CAST(@savingID AS varchar(10))
select @cur_customerID=customerID from userInfo where PID=@PID
--PRINT CAST(@cur_customerID AS varchar(10))
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES(@mycardID,@savingID,@openMoney,@openMoney,@cur_customerID)
GO
--调用存储过程重新开户
EXEC usp_openAccount '王','333333333333333333','2222-66666666',100,'活期','河南新乡'
EXEC usp_openAccount '赵','222222222222222222','0760-44444444',1,'定期'
select * from view_userInfo
select * from view_cardInfo
GO
--4.分页显示查询交易数据
if exists (select * from sysobjects where name = 'usp_pagingDisplay')
DROP PROCEDURE usp_pagingDisplay
GO
CREATE PROCEDURE usp_pagingDisplay
@records int = 10,
@page int = 1
AS
SET NOCOUNT ON
DECLARE @rec1 int
SET @rec1 = @records --@page * @records
DECLARE @rec2 int
SET @rec2 = (@page - 1) * @records
DECLARE @statement nvarchar(200)
SET @statement='SELECT TOP ' + CAST(@rec1 AS varchar(10)) + ' tradeDate 交易日期,tradeType 交易类型,cardID 卡号,trademoney 交易金额 FROM tradeInfo WHERE cardID not in (SELECT TOP '+ CAST(@rec2 AS varchar(10)) + ' cardID FROM tradeInfo)'
--print @statement
EXEC SP_EXECUTESQL @statement,N'@rec1 int,@rec2 int',@rec1,@rec2
GO
--
EXEC usp_pagingDisplay 2,2
--5打印客户帐单
if exists (select * from sysobjects where name = 'usp_CheckSheet')
drop proc usp_CheckSheet
GO
CREATE PROCEDURE usp_CheckSheet
@cardID varchar(19),
@date1 datetime=NULL,
@date2 datetime=NULL
AS
DECLARE @custName varchar(20)
DECLARE @curName varchar(20)
DECLARE @savingName varchar(20)
DECLARE @openDate datetime
SELECT @cardID=c.cardID, @curName=c.curID, @custName=u.customerName,
@savingName=d.savingName , @openDate=c.openDate
FROM cardInfo c, userInfo u, deposit d
WHERE c.customerID=u.customerID and c.savingID = d.savingID and cardID = @cardID --and u.customerName = user_name()
PRINT '卡号:' + @cardID
PRINT '姓名:' + @custName
PRINT '货币:' + @curName
PRINT '存款类型:' + @savingName
PRINT '开户日期:' + CAST(DATEPART(yyyy,@openDate) AS VARCHAR(4))+'年' + CAST(DATEPART(mm,@openDate) AS VARCHAR(2))+'月' + CAST(DATEPART(dd,@openDate) AS VARCHAR(2))+'日'
PRINT ' '
print '--------------------------------------------------------------------'
IF @date1 IS NULL AND @date2 IS NULL
BEGIN
SELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注
FROM tradeInfo
WHERE cardID='1010 3576 1212 1134'--@cardID
ORDER BY tradeDate
RETURN
END
ELSE IF @date2 IS NULL
SET @date2 = getdate()
SELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注
FROM tradeInfo
WHERE cardID=@cardID AND tradeDate BETWEEN @date1 AND @date2
ORDER BY tradeDate
GO
--测试打印对帐单
EXEC usp_CheckSheet '1010 3576 1212 1134'
EXEC usp_CheckSheet '1010 3576 1212 1134','2009-11-2','2009-11-30'