Sample storage process learning for integral acquisition and consumption

This article mainly introduces the integration acquisition and consumption of stored process Learning example, this is just learning how to use stored procedures, the need for friends can refer to the

1.GM_JF Customer account Integration form

2. Gm_jf_detail customer account points consumption record

3. Gm_jf_action _rules integral Action Rules table

4.gm_jf_goods _rules integral Commodity rules table

--=============== Test =======================================================

/*

declare @StatusCode int = 1;

exec sp_gm_jf_addscore ' admin ', ' an_jf_001_001 ', 1,5,0, ', @StatusCode output

Print @StatusCode

*/

-- ===========================================================================

/*

* To determine whether to repeatedly gain points (first perfect personal data, first change password, etc. can not be repeated gain points)

* Judgment is based on the number of data from the query cycle of the Gm_jf_detail (Details table) and Gm_jf_action_rules (Action Rule table) in the cycle of repeated times

* If the number is greater than or equal to the cycle repeat, get the integral

* Next

* 1. Data Warehousing of Details table

* 2. To determine whether the total integration table has a corresponding customer's total score no, insert a new one, read its data, and update

*

*/

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: Failure 1: Success 2: Cannot repeat fetch

As

BEGIN

---SET NOCOUNT on added to prevent extra result sets from

--interfering with SELECT statements.

SET NOCOUNT on;

Declare

@repetitionsCycle float=0,--cycle (days)

@repetitionsCycle_second int=0,--Period (sec)

@repetitionsFrequency int=0--the maximum number of times allowed in a cycle

@realFrequency int=0--The actual cycle

@USE_DATE datetime = GETDATE ();

--whether to get points repeatedly

Select Top (1) @repetitionsCycle =repetitionscycle, @repetitionsFrequency =repetitionsfrequency from Gm_jf_action_ The 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), 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), DATEADD (day,-(@repetitionsCycle-1), @USE_DATE), 120)

End

if (@realFrequency >= @repetitionsFrequency)--The actual period is greater than the number of cycles

Begin

Set @StatusCode = 2;

return 2;

End

declare @count int = 0; --Number of data bars

Declare @temp_table table-variables

(

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)

--Populating table variables

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;

--Judge and update total points (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 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