一个MySQL存储过程的模板

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

总结

以上是生活随笔为你收集整理的一个MySQL存储过程的模板的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。