1:返回单个值
-- 返回单个值
CREATE PROCEDURE fetch_ufmall_code_seq(IN inseqName varchar(50),OUT outseqcode int)
BEGIN
select seq_code into outseqcode from m_ufmall_seq_produce where seq_name = inseqName;
update m_ufmall_seq_produce set seq_code = seq_code + 1 where seq_name = inseqName;
select outseqcode;
END;
-- 调用
call fetch_ufmall_code_seq('produce_order_payment_seq',@a);
2:返回多个值
-- 返回多个值
CREATE PROCEDURE proc_stat_ufmall_verify(OUT total_order_num int,OUT total_refund_num int,OUT total_cost_money decimal,OUT total_in_money decimal)
BEGIN
-- 商城商品已售单数
select count(0) into total_order_num from m_ufmall_order_payment where not EXISTS
(select id from m_ufmall_order_refund where refund_money is not null and m_ufmall_order_payment.order_code = m_ufmall_order_refund.order_code);
-- 商城商品已退款单数
select count(0) into total_refund_num from m_ufmall_order_refund where refund_money is not null;
-- 商城商品成本费用合计
select sum(product_cost_price) into total_cost_money from m_ufmall_product mup INNER JOIN m_ufmall_order muo ON mup.id = muo.product_id
and EXISTS(
select m_ufmall_order_payment.order_code from m_ufmall_order_payment where not EXISTS
(select id from m_ufmall_order_refund where refund_money is not null and m_ufmall_order_payment.order_code = m_ufmall_order_refund.order_code)
and muo.order_code = m_ufmall_order_payment.order_code
);
-- 商城商品收入
select sum(actual_order_money) into total_in_money from m_ufmall_order_payment where not EXISTS
(select id from m_ufmall_order_refund where refund_money is not null and m_ufmall_order_payment.order_code = m_ufmall_order_refund.order_code);
select total_order_num,total_refund_num,total_cost_money,total_in_money;
END;
-- 调用
call proc_stat_ufmall_verify(@a,@b,@c,@d);