Mysql的存储过程示例

存储过程文件名：award_inviter，输出参数：OUT msg varchar(5)

BEGIN
DECLARE v_inviter_userid bigint;  /** 定义相关变量值 **/
DECLARE v_inviter_inviterid bigint;
DECLARE v_inviter_user_name varchar(20);
DECLARE v_inviter_inviter_name varchar(20);


DECLARE v_account_total_user DOUBLE DEFAULT 0;
DECLARE v_account_usemoney_user DOUBLE DEFAULT 0;
DECLARE v_account_nousemoney_user DOUBLE DEFAULT 0;
DECLARE v_account_collection_user DOUBLE DEFAULT 0;


DECLARE v_account_total_inviter DOUBLE DEFAULT 0;
DECLARE v_account_usemoney_inviter DOUBLE DEFAULT 0;
DECLARE v_account_nousemoney_inviter DOUBLE DEFAULT 0;
DECLARE v_account_collection_inviter DOUBLE DEFAULT 0;


DECLARE v_user_gain_point DOUBLE DEFAULT 0;
DECLARE v_user_accoumt_point DOUBLE DEFAULT 0;


DECLARE v_inviter_gain_point DOUBLE DEFAULT 0;
DECLARE v_inviter_accoumt_point DOUBLE DEFAULT 0;


DECLARE done int DEFAULT 0;
DECLARE t_error int DEFAULT 0;


/** 邀请记录 **/
DECLARE c_inviter CURSOR FOR   /** 定义游标变量，并将查询结果存放入游标变量中 **/
SELECT a.id, a.inviterid from rocky_member a left join (select sum(money) sum, user_id from rocky_rechargerecord where status = 1 group by user_id having sum >= 1000) b on b.user_id = a.id where b.user_id is not null and a.inviterid != 0 and a.awardmoney = 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;   /** 定义查询状态 **/

START TRANSACTION;  /** 开始事务 **/

OPEN c_inviter;   /** 打开游标变量 **/
REPEAT    /** 循环遍历开始 **/
FETCH c_inviter INTO v_inviter_userid,v_inviter_inviterid;  /** 依次遍历游标变量中的记录，并将相应的值赋给定义的变量 **/
IF NOT done THEN  /** 判断查询状态 **/
/** 被邀请人账户记录 **/
SELECT TOTAL,USE_MONEY,NO_USE_MONEY,COLLECTION INTO 
        v_account_total_user,v_account_usemoney_user,v_account_nousemoney_user,v_account_collection_user
        FROM rocky_account WHERE USER_ID = v_inviter_userid FOR UPDATE;   /** into 将查询出来的字段值赋给对应的变量，for update 锁定该记录 **/
SET v_account_total_user = v_account_total_user + 10;
SET v_account_usemoney_user = v_account_usemoney_user + 10;
/** 奖励10元 **/
UPDATE rocky_account SET TOTAL = v_account_total_user, USE_MONEY = v_account_usemoney_user WHERE USER_ID = v_inviter_userid;
/** 生成资金明细记录 **/
SELECT USERNAME INTO v_inviter_user_name FROM rocky_member WHERE ID = v_inviter_userid;
INSERT INTO rocky_accountlog (USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME)
VALUES (v_inviter_userid, 'web_recharge',v_account_total_user,
            10,v_account_usemoney_user,v_account_nousemoney_user,v_account_collection_user,v_inviter_userid,
            '首充1000奖励费用',UNIX_TIMESTAMP());




/** 邀请人账户记录 **/
SELECT TOTAL,USE_MONEY,NO_USE_MONEY,COLLECTION INTO 
        v_account_total_inviter,v_account_usemoney_inviter,v_account_nousemoney_inviter,v_account_collection_inviter
FROM rocky_account WHERE USER_ID = v_inviter_inviterid FOR UPDATE; 
SET v_account_total_inviter = v_account_total_inviter + 10;
SET v_account_usemoney_inviter = v_account_usemoney_inviter + 10;
/** 奖励10元 **/
UPDATE rocky_account SET TOTAL = v_account_total_inviter, USE_MONEY = v_account_usemoney_inviter WHERE USER_ID = v_inviter_inviterid;
/** 生成资金明细记录 **/
SELECT USERNAME INTO v_inviter_inviter_name FROM rocky_member WHERE ID = v_inviter_inviterid;
INSERT INTO rocky_accountlog (USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME)
VALUES (v_inviter_inviterid, 'web_recharge',v_account_total_inviter,
            10,v_account_usemoney_inviter,v_account_nousemoney_inviter,v_account_collection_inviter,v_inviter_inviterid,
CONCAT('您推荐的用户：',v_inviter_inviter_name,',首充1000,奖励10元已入账'),UNIX_TIMESTAMP());





/** 被邀请人记录**/
SELECT GAINACCUMULATEPOINTS,ACCUMULATEPOINTS INTO v_user_gain_point,v_user_accoumt_point FROM rocky_member WHERE ID = v_inviter_userid FOR UPDATE;
/** 奖励10个积分 **/
UPDATE rocky_member SET GAINACCUMULATEPOINTS = v_user_gain_point + 10, ACCUMULATEPOINTS = v_user_accoumt_point + 10, AWARDMONEY = 10 WHERE ID = v_inviter_userid;
/** 新增积分记录 **/
INSERT INTO ROCKY_MEMBER_ACCUMULATE_POINTS(MEMBERID,TYPE,ACCUMULATEPOINTS,GAINACCUMULATEPOINTS,GAINDATE,POINTSMAGNIFICATION) VALUES(v_inviter_userid,10,10,10, SUBSTR(NOW() FROM 1 FOR 19),1);



/** 邀请人记录**/
SELECT GAINACCUMULATEPOINTS,ACCUMULATEPOINTS INTO v_inviter_gain_point,v_inviter_accoumt_point FROM rocky_member WHERE ID = v_inviter_inviterid FOR UPDATE;
/** 奖励10个积分 **/
UPDATE rocky_member SET GAINACCUMULATEPOINTS = v_inviter_gain_point + 10, ACCUMULATEPOINTS = v_inviter_accoumt_point + 10 WHERE ID = v_inviter_inviterid;
/** 新增积分记录 **/
INSERT INTO ROCKY_MEMBER_ACCUMULATE_POINTS(MEMBERID,TYPE,ACCUMULATEPOINTS,GAINACCUMULATEPOINTS,GAINDATE,POINTSMAGNIFICATION) VALUES(v_inviter_inviterid,10,10,10, SUBSTR(NOW() FROM 1 FOR 19),1);



END IF;
 UNTIL done 
  END REPEAT;  /** 循环遍历结束 **/
  CLOSE c_inviter;

IF t_error=1 THEN 
SET msg = '00000';
ROLLBACK; -- 事务回滚  
  ELSE 
SET msg = '00001';
    COMMIT; -- 事务提交  
  END IF;
END