一个MySQL存储过程的模板

来源：互联网发布：备案好的域名买卖编辑：程序博客网时间：2024/05/16 05:34

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

0 0