• 欢迎访问小杰博客网站
  • 欢迎访问小杰博客网站哦

mysql存储过程变量调用方式

mysql 小杰 1个月前 (02-25) 29次浏览 已收录 0个评论

DROP PROCEDURE IF EXISTS get_user_charge_level;

CREATE DEFINER = newycsd @% PROCEDURE get_user_charge_level (
openid varchar(50)
)
BEGIN
set @per_user_amount = 0;
SET @csql = CONCAT( ‘select
ifnull((select sum(price) from orders o
inner join users u2 ON o.uid=u2.id where u2.openid=u.openid and o.status=”PAID”),0)/count(distinct u.id) as per_user_amount
into @per_user_amount
from users u
where u.openid=”‘, openid, ‘”
group by u.openid
having min(u.created_at) <= date_sub(now(), interval 72 HOUR)
‘ );

— SELECT @csql;
— select @ret_data;
PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
deallocate prepare create_stmt;

IF @per_user_amount > 30
THEN
set @user_charge_level = ‘HIGH’;
ELSEIF @per_user_amount >=10
THEN
set @user_charge_level = ‘MID’;
ELSEIF @per_user_amount > 0
THEN
set @user_charge_level = ‘LOW’;
ELSE
set @user_charge_level = ‘NONE’;
END IF;

select @per_user_amount;
select @user_charge_level;
END;

call get_user_charge_level(‘oAcqg1LRHNKN2jaEkJ5v56HOwPEQ’);
call get_user_charge_level(‘2222’);


小杰博客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:mysql存储过程变量调用方式
喜欢 (1)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址