Learning example of stored procedure for point acquisition and consumption

Time:2021-12-24

1.GM_ JF customer account points table

2. GM_ JF_ Detail customer account points consumption record

3. GM_ JF_ ACTION _ Rules integral action rule table

4.GM_ JF_ GOODS _ Rules points commodity rule table

Copy codeThe code is as follows:
–================== test=======================================================
/*
declare @StatusCode int = 1;
exec sp_GM_JF_AddScore ‘admin’,’AN_JF_001_001′,1,5,0,”,@StatusCode output
print @StatusCode
*/
— ===========================================================================
/*
*Judge whether to repeatedly obtain points (you can’t repeatedly obtain points by perfecting your personal data for the first time, changing your password for the first time, etc.)
*The judgment is based on GM_ JF_ The number of data pieces in the detail table query cycle is the same as GM_ JF_ ACTION_ Comparison of cycle repetition times in rules
*If it is greater than or equal to the number of cycle repetitions, the integral is obtained for the repetition
*Next
*     1. Data warehousing of detail table
*     2. Judge whether there is a total score corresponding to the customer in the total score table. If not, insert a new one. If yes, read its data and update it
*
*/
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 — status code: 0: failed 1: succeeded 2: cannot get repeatedly
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

declare
@Repetitionscycle – float = 0, — period (days)
@repetitionsCycle_ Second – int = 0, — period (seconds)
@Repetitionsfrequency – int = 0, — maximum number of times allowed in a cycle
@Realfrequency – int = 0, — actual period

@USE_DATE     datetime = GETDATE();
–Whether to obtain points repeatedly
select top(1) @repetitionsCycle=RepetitionsCycle,@repetitionsFrequency=RepetitionsFrequency from GM_JF_ACTION_RULES where [email protected]_CategoryNumber;

if(@repetitionsCycle<1)
BEGIN
set @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)
END
ELSE
BEGIN
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(10),DATEADD(DAY,-(@repetitionsCycle-1),@USE_DATE),120)
END   

If (@ realfrequency > = @ repetitionsfrequency) — the actual cycle is greater than the number of cycles
begin
set @StatusCode = 2;
return 2;
end

declare @count int = 0; — number of data pieces
declare @temp_ Table — table variable
(
ACCOUNT_ID varchar(30),
JF_AMOUNT decimal(16,2),
TTL_JF_AMOUNT decimal(16,2),
Last_Update_Time datetime,
[Version] int
);

begin tran;
–Insert details
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)

–Fill table variables
insert into @temp_table select ACCOUNT_ID,JF_AMOUNT,TTL_JF_AMOUNT,Last_Update_Time,[Version] from GM_JF where [email protected]_ID
select @count = count(1) from @temp_table;
–Judge and update the total score (0: add others: modify)
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, — total integral
@TTL_ JF_ Amount int, — available points
@Version int; — version number

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]_ID
end

Commit tran;
set @StatusCode = 1;

IF(@@ERROR<>0)
BEGIN
set @StatusCode = 0;
ROLLBACK tran;
END
END