一个MySQL存储过程的模板

最新推荐文章于 2022-10-10 10:58:52 发布

阿拉斯猪饲养员

最新推荐文章于 2022-10-10 10:58:52 发布

阅读量287

点赞数

分类专栏：数据库 mysql 文章标签： myspan idtransmark s procdure

本文链接：https://blog.csdn.net/qq_33432559/article/details/53288531

版权

mysql 同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

数据库

10 篇文章 0 订阅

订阅专栏

BEGIN
DECLARE securitiesnum_v decimal DEFAULT 0.0;
DECLARE assigned_v decimal DEFAULT 0.0;
#将查询到结果赋值到变量
select SUM(securitiesnum) into securitiesnum_v from zxzq_holding where memberid = v_in_memberid;
select assigned * 0.8 into assigned_v from zxzq_information where memberid = v_in_memberid;

#v_in_securitiesnumSum < i.assigned * 0.8的情况
update zxzq_holding h, zxzq_information i
set h.securitiesnum = if(securitiesnum_v > i.assigned * 0.8, h.securitiesnum, 0),
i.assigned = IF(h.securitiesnum > i.assigned * 0.8, h.securitiesnum, TRUNCATE(i.assigned - h.securitiesnum * 1.25,2))
where h.memberid = v_in_memberid and i.memberid = v_in_memberid and i.assigned > 0;

#v_in_securitiesnumSum > i.assigned * 0.8的情况
if securitiesnum_v > assigned_v THEN
WHILE(assigned_v > 0) do
update zxzq_holding h ,zxzq_information i set h.securitiesnum = if(h.securitiesnum > i.assigned * 0.8, TRUNCATE(h.securitiesnum - i.assigned * 0.8,2), 0),
i.assigned = if(h.securitiesnum > i.assigned * 0.8,0, TRUNCATE(i.assigned - h.securitiesnum * 1.25,2)) where h.memberid = v_in_memberid and i.memberid = v_in_memberid and i.assigned > 0;

select assigned into assigned_v from zxzq_information where memberid = v_in_memberid;
END WHILE;
end if;
END