-- ============================================= -- 更新合同存储过程 -- ============================================= -- creating the store procedure Use DaluMis GO BEGIN TRANSACTION UpTran IF EXISTS (SELECT name FROM sysobjects WHERE name = 'UpDataContract' AND type = 'P') DROP PROCEDURE UpDataContract GOCREATE PROCEDURE UpDataContract AS Declare @CurrentTime smalldatetime --当前时间 Declare @ContractID varchar(20) --查询符合要求的合同号 set @CurrentTime = GetDate() Declare CustCursor Cursor for --声明游标 Select ContractID From ContractInfo Where (@CurrentTime > day(NextPayTime) And IsActivity = 1) /*(当前时间>下次合同执行时间)AND(该合同处于活动状态)*/ Order By ContractID For Read Only Open CustCursor Begin Fetch Next From CustCursor Into @ContractID while(@@Fetch_Status = 0) Begin print @@Fetch_Status Declare @sqjy Money --上期结余 Declare @mqyf Money --每期应付 Declare @PriorPayTime smalldatetime --到期时间 Declare @xqyfsj SmallDatetime --下期应付时间 Declare @MartID varchar(20) --商铺编号 Declare @fkfs varchar(8) --付款方式 Declare @totalRent Money --总租金 Declare @WriteTime char(7) --当前记录时间
Select @MartID = MartID ,@PriorPayTime = NextPayTime ,@sqjy = -Balance From ContractInfo Where (@ContractID = ContractID) -- 获取商铺编号 Select @totalRent = RealTotalRent , @fkfs = PayType From MartInfo Where (@MartID = MartID) Begin --计算每期应付的租金 if(@fkfs = '月') Begin set @mqyf = @totalRent /12 set @xqyfsj = dateadd(m,1,@PriorPayTime) End if(@fkfs = '季') Begin set @mqyf = @totalRent / 4 set @xqyfsj = dateadd(m,3,@PriorPayTime) End if(@fkfs = '半年') Begin set @mqyf = @totalRent / 2 set @xqyfsj = dateadd(m,6,@PriorPayTime) End if(@fkfs = '年') Begin set @mqyf = @totalRent set @xqyfsj = dateadd(yy,1,@PriorPayTime) End End
UpDate ContractInfo Set IsActivity = 0 where (ContractID = @ContractID)--更新旧数据 /*插入一条新的数据*/ Insert into ContractInfo(MartID,ContractTime,ShouldPay,ReducePay,RealPay,Balance,NextPayTime,IsActivity) values(@MartID,@WriteTime,@sqjy+@mqyf,0,0,0-@sqjy-@mqyf,@xqyfsj,1) Fetch Next From CustCursor Into @ContractID
End End if(@@error > 0) Begin rollback raisError('更新错误!',16,2); End
commit TRANSACTION UpTran Close CustCursor Deallocate CustCursor GO-- ============================================= -- example to execute the store procedure -- ============================================= EXECUTE UpDataContract GO这是我写的存储过程,请大虾们指点
我改了下,用临时表,把检索出来的数据放进去,遍历这个表,可以么? 但是好像临时表不会写哎,说不能嵌套子查询,怎么做啊? create table # ( TmpID int identity(1,1), ContractID int, ) insert into # (ComtractID) values ((Select ContractID From ContractInfo Where (@CurrentTime > day(NextPayTime) And IsActivity = 1) --(当前时间>下次合同执行时间)AND(该合同处于活动状态) Order By ContractID) )这个是我写的,错的:(
注意:/*插入一条新的数据*/Insert into ContractInfo游标是从表ContractInfo中获取数据,在循坏中你又更新这个表的数据,所以会不断的循坏。 也就是说你循坏一次插入一条新的记录,这样会停止吗。 所以游标本身没有问题,而是你的算法不对,请考虑别的算法吧
-- 更新合同存储过程
-- =============================================
-- creating the store procedure
Use DaluMis
GO
BEGIN TRANSACTION UpTran
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'UpDataContract'
AND type = 'P')
DROP PROCEDURE UpDataContract
GOCREATE PROCEDURE UpDataContract
AS
Declare @CurrentTime smalldatetime --当前时间
Declare @ContractID varchar(20) --查询符合要求的合同号
set @CurrentTime = GetDate()
Declare CustCursor Cursor for --声明游标
Select ContractID
From ContractInfo
Where (@CurrentTime > day(NextPayTime) And IsActivity = 1) /*(当前时间>下次合同执行时间)AND(该合同处于活动状态)*/
Order By ContractID
For Read Only
Open CustCursor
Begin
Fetch Next From CustCursor Into @ContractID
while(@@Fetch_Status = 0)
Begin
print @@Fetch_Status
Declare @sqjy Money --上期结余
Declare @mqyf Money --每期应付
Declare @PriorPayTime smalldatetime --到期时间
Declare @xqyfsj SmallDatetime --下期应付时间
Declare @MartID varchar(20) --商铺编号
Declare @fkfs varchar(8) --付款方式
Declare @totalRent Money --总租金
Declare @WriteTime char(7) --当前记录时间
Select @MartID = MartID ,@PriorPayTime = NextPayTime ,@sqjy = -Balance From ContractInfo Where (@ContractID = ContractID) -- 获取商铺编号
Select @totalRent = RealTotalRent , @fkfs = PayType From MartInfo Where (@MartID = MartID)
Begin --计算每期应付的租金
if(@fkfs = '月')
Begin
set @mqyf = @totalRent /12
set @xqyfsj = dateadd(m,1,@PriorPayTime)
End
if(@fkfs = '季')
Begin
set @mqyf = @totalRent / 4
set @xqyfsj = dateadd(m,3,@PriorPayTime)
End
if(@fkfs = '半年')
Begin
set @mqyf = @totalRent / 2
set @xqyfsj = dateadd(m,6,@PriorPayTime)
End
if(@fkfs = '年')
Begin
set @mqyf = @totalRent
set @xqyfsj = dateadd(yy,1,@PriorPayTime)
End
End
UpDate ContractInfo Set IsActivity = 0 where (ContractID = @ContractID)--更新旧数据
/*插入一条新的数据*/
Insert into ContractInfo(MartID,ContractTime,ShouldPay,ReducePay,RealPay,Balance,NextPayTime,IsActivity) values(@MartID,@WriteTime,@sqjy+@mqyf,0,0,0-@sqjy-@mqyf,@xqyfsj,1) Fetch Next From CustCursor Into @ContractID
End
End
if(@@error > 0)
Begin
rollback
raisError('更新错误!',16,2);
End
commit TRANSACTION UpTran
Close CustCursor
Deallocate CustCursor
GO-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE UpDataContract
GO这是我写的存储过程,请大虾们指点
但是好像临时表不会写哎,说不能嵌套子查询,怎么做啊?
create table #
(
TmpID int identity(1,1),
ContractID int,
)
insert into # (ComtractID) values
((Select ContractID
From ContractInfo
Where (@CurrentTime > day(NextPayTime) And IsActivity = 1) --(当前时间>下次合同执行时间)AND(该合同处于活动状态)
Order By ContractID)
)这个是我写的,错的:(
也就是说你循坏一次插入一条新的记录,这样会停止吗。
所以游标本身没有问题,而是你的算法不对,请考虑别的算法吧