積分獲得和花費的存儲進程進修示例。本站提示廣大學習愛好者：（積分獲得和花費的存儲進程進修示例）文章只能為提供參考，不一定能成為您想要的結果。以下是積分獲得和花費的存儲進程進修示例正文
1.GM_JF客戶賬戶積分表
2. GM_JF_DETAIL客戶賬戶積分花費記載
3. GM_JF_ACTION _RULES積分舉措規矩表
4.GM_JF_GOODS _RULES積分商品規矩表
-- ===============測試=======================================================
/*
declare @StatusCode int = 1;
exec sp_GM_JF_AddScore 'admin','AN_JF_001_001',1,5,0,'',@StatusCode output
print @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: 不克不及反復獲得
AS
BEGIN
-- 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 AN_CategoryNumber=@JF_CategoryNumber;
if(@repetitionsCycle<1)
BEGIN
set @repetitionsCycle_second = (@repetitionsCycle-1)*24*60*60;
select @realFrequency=COUNT(1) from GM_JF_DETAIL where ACCOUNT_ID=@ACCOUNT_ID and JF_CategoryNumber=@JF_CategoryNumber and USE_DATE <= @USE_DATE and USE_DATE >= CONVERT(varchar(19),DATEADD(SECOND,-@repetitionsCycle_second,@USE_DATE),120)
END
ELSE
BEGIN
select @realFrequency=COUNT(1) from GM_JF_DETAIL where ACCOUNT_ID=@ACCOUNT_ID and JF_CategoryNumber=@JF_CategoryNumber and USE_DATE <= @USE_DATE and USE_DATE >= CONVERT(varchar(10),DATEADD(DAY,-(@repetitionsCycle-1),@USE_DATE),120)
END
if(@realFrequency>=@repetitionsFrequency) --現實周期年夜於周期次數
begin
set @StatusCode = 2;
return 2;
end
declare @count int = 0; --數據條數
declare @temp_table table --表變量
(
ACCOUNT_ID varchar(30),
JF_AMOUNT decimal(16,2),
TTL_JF_AMOUNT decimal(16,2),
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 ACCOUNT_ID=@ACCOUNT_ID
select @count = count(1) from @temp_table;
--斷定並更新總積分（0:添加 其他:修正）
IF(@count=0)
begin
insert into GM_JF(ACCOUNT_ID,JF_AMOUNT,TTL_JF_AMOUNT)
values
(@ACCOUNT_ID,@HQ_JF_AMOUNT,@HQ_JF_AMOUNT)
end
else
begin
declare @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 ACCOUNT_ID=@ACCOUNT_ID;
update GM_JF set JF_AMOUNT=(@JF_AMOUNT+@HQ_JF_AMOUNT),TTL_JF_AMOUNT=(@TTL_JF_AMOUNT+@HQ_JF_AMOUNT),Last_Update_Time=GETDATE(),[Version]=(@Version+1) where ACCOUNT_ID=@ACCOUNT_ID
end
Commit tran;
set @StatusCode = 1;
IF(@@ERROR<>0)
BEGIN
set @StatusCode = 0;
ROLLBACK tran;
END
END
