积分获取和消费的存储过程

概述积分获取和消费的存储过程

下面是内存溢出 jb51.cc 通过网络收集整理的代码片段。

内存溢出小编现在分享给大家，也给大家做个参考。

-- ===============测试=======================================================/*declare @StatusCode int = 1;exec sp_GM_JF_Addscore 'admin','AN_JF_001_001',1,5,'',@StatusCode outputprint @StatusCode*/-- ===========================================================================/** 判断是否重复获取积分(首次完善个人资料,首次修改密码等等不能重复获取积分)* 判断是根据 从GM_JF_DETAIL(详情表)查询周期内的数据条数与GM_JF_ACTION_RulES(动作规则表)内的周期重复次数对比* 如果大于等于周期重复次数,则为重复获取积分* 接下来*     1.详情表的数据入库*     2.判断总积分表是否存在对应客户的总积分 没有则插入一条新的,有 则读取其数据,并更新**/ALTER PROCEDURE [dbo].[sp_GM_JF_Addscore]@ACCOUNT_ID     varchar(30),@JF_categoryNumber varchar(15),@CARD_NUM     int,@HQ_JF_AMOUNT     int,@State     varchar(16),@USE_DESC     varchar(400),@StatusCode     int output     -- 状态码: 0:失败 1:成功 2: 不能重复获取ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;declare @repetitionsCycle     float=0,--周期（天）@repetitionsCycle_second    int=0,--周期（秒）@repetitionsFrequency     int=0,--一个周期内允许最大次数@realFrequency     int=0,--实际周期@USE_DATE     datetime = GETDATE();--是否重复获取积分select top(1) @repetitionsCycle=RepetitionsCycle,@repetitionsFrequency=RepetitionsFrequency from GM_JF_ACTION_RulES where [email protected]_categoryNumber;if(@repetitionsCycle<1)BEGINset @repetitionsCycle_second = (@repetitionsCycle-1)*24*60*60;select @realFrequency=COUNT(1) from GM_JF_DETAIL where [email protected]_ID and [email protected]_categoryNumber and USE_DATE <= @USE_DATE and USE_DATE >= CONVERT(varchar(19),DATEADD(SECOND,[email protected]_second,@USE_DATE),120)ENDELSEBEGINselect @realFrequency=COUNT(1) from GM_JF_DETAIL where [email protected]_ID and [email protected]_categoryNumber and USE_DATE <= @USE_DATE and USE_DATE >= CONVERT(varchar(10),DATEADD(DAY,-(@repetitionsCycle-1),120)END    if(@realFrequency>[email protected])    --实际周期大于周期次数beginset @StatusCode = 2;return 2;enddeclare @count int = 0;     --数据条数declare @temp_table table    --表变量(ACCOUNT_ID varchar(30),JF_AMOUNT decimal(16,2),TTL_JF_AMOUNT decimal(16,Last_Update_Time datetime,[Version] int);begin tran;--插入详情insert into GM_JF_DETAIL(ACCOUNT_ID,JF_categoryNumber,CARD_NUM,HQ_JF_AMOUNT,[State],USE_DESC)values(@ACCOUNT_ID,@JF_categoryNumber,@CARD_NUM,@HQ_JF_AMOUNT,@State,@USE_DESC)--填充表变量insert into @temp_table select ACCOUNT_ID,JF_AMOUNT,TTL_JF_AMOUNT,Last_Update_Time,[Version] from GM_JF where [email protected]_IDselect @count = count(1) from @temp_table;--判断并更新总积分（0:添加 其他:修改）IF(@count=0)begininsert into GM_JF(ACCOUNT_ID,TTL_JF_AMOUNT)values(@ACCOUNT_ID,@HQ_JF_AMOUNT)endelsebegindeclare @JF_AMOUNT int,--总积分@TTL_JF_AMOUNT int,--可用积分@Version int;     --版本号select @JF_AMOUNT=JF_AMOUNT,@TTL_JF_AMOUNT=TTL_JF_AMOUNT,@Version=[Version] from @temp_table where [email protected]_ID;update GM_JF set JF_AMOUNT=(@[email protected]_JF_AMOUNT),TTL_JF_AMOUNT=(@[email protected]_JF_AMOUNT),Last_Update_Time=GETDATE(),[Version]=(@Version+1) where [email protected]_IDendCommit tran;set @StatusCode = 1;IF(@@ERROR<>0)BEGINset @StatusCode = 0;RolLBACK tran;ENDEND

以上是内存溢出(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

总结

以上是内存溢出为你收集整理的积分获取和消费的存储过程全部内容，希望文章能够帮你解决积分获取和消费的存储过程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。