A SQL Server cursor and a loop nested stored procedure use case

A SQL Server cursor and a loop nested stored procedure use case Preparation code

--创建表CREATE TABLE everyoneAVG(    ID int primary key identity(1,1),    AccNo INT,    AVGTime INT,    AddTimes INT);DROP TABLE everyoneAVG;SELECT ID,AccNo AS ‘用户号‘,AVGTime/60 AS ‘平均充值时间间隔(小时)‘,AddTimes AS ‘总充值次数‘ FROM everyoneAVGORDER BY AVGTime/60 DESC;DELETE everyoneAVG;

Functional Requirements

Find out the average recharge interval for each person over a period of time

Implementation code

CREATE PROCEDURE p_getavgaddtimeasbegindeclare @AccountNo int,--account number @AVGTime INT--average time to recharge each person (minutes) @AddTimes   int,---the total number of individual recharge (ignoring the top-up in 10 minutes) @upConsumeTime datetime,--Last deal time @ConsumeTime datetime,--current deal time @TempTime INT --time interval for temporary record accumulation (minutes)--Declaring user number cursor DECLARE allacc_cursor cursor for SELECT top 99.9999 percent accountno from [CAS H]. [dbo].    [Tb_customer] ORDER by Accountno Open Allacc_cursor--open cursor FETCH next from allacc_cursor--get cursor next row of data into @AccountNo--make variable get The account number of the current cursor-specified row-----------an outer cursor, ORDER by Accountno while (@ @FETCH_STATUS =0) BEGIN set @TempTime =0 set @A Vgtime=0--average recharge time for each person set @AddTimes = 0--Each person recharge the initial set @upConsumeTime = ' 2017-11-09 00:00:00.000 ' DE CLARE consumetime_cursor Cursor for--Declare deal time--recharge record query SELECT top 99.9999 percent consumetime from [CASH]. [dbo].         [Tb_inf] WHERE [email protected] and grpno=98 and exchgdate>= ' 2017-11-09 00:00:00.000 ' andexchgdate<= ' 2018-04-16 00:00:00.000 ' ORDER by consumetime open Consumetime_cursor--opening deal time cursor FETCH Next from Consumetime_cursor--Gets the next row of data into the @ConsumeTime while (@ @FETCH_STATUS =0) BEGIN--                 First-time recharge processing IF (@upConsumeTime = ' 2017-11-09 00:00:00.000 ') BEGIN SET @[email protected] SET @[email protected]+1 END ELSE IF (DATEDIFF (MINUTE, @upConsumeTime            , @ConsumeTime) <10)----recharge time in 10 minutes ignores BEGIN SET @[email protected] END ELSE BEGIN Set @[email protected]+1--Recharge count cumulative set @[email protected]        +datediff (MINUTE, @upConsumeTime, @ConsumeTime)--Recharge time interval cumulative SET @[email protected] END FETCH NEXT from Consumetime_cursor--get the next line of data into @ConsumeTime END--recharge only once or never recharge IF (@AddTime       S=1) BEGIN     Set @AVGTime =0 end ELSE BEGIN SET @[email protected]/(@AddTimes-1) END --The resulting average is deposited into the temporary table BEGIN INSERT into [CASH]. [dbo].            [Everyoneavg] VALUES (@AccountNo, @AVGTime, @AddTimes) END close Consumetime_cursor--Close the internal cursor deallocate consumetime _cursor--Free internal cursor FETCH next from allacc_cursor--get cursor next row of data into @AccountNo--causes the variable to get the account number of the specified row of the current cursor END CLOSE al Lacc_cursor--Close the external cursor deallocate allacc_cursor--Release the external cursor Endgo

A SQL Server cursor and a loop nested stored procedure use case