Heim > Datenbank > MySQL-Tutorial > Mysql的存储过程示例_MySQL

Mysql的存储过程示例_MySQL

WBOY
Freigeben: 2016-06-01 13:12:10
Original
1062 Leute haben es durchsucht

存储过程文件名: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

Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage