mysql存储过程相互调用事务_MySQL存储过程相互调用

--表格

mysql> select corderno,mshippingcharges,mgiftwrapcharges from orders;

+----------+------------------+------------------+

| corderno | mshippingcharges | mgiftwrapcharges |

+----------+------------------+------------------+

| 000001   |                6 |             1.25 |

| 000002   |                8 |                2 |

| 000003   |               12 |                0 |

+----------+------------------+------------------+

9 rows in set

--

create procedure prccharges(in orderno char(6),

out shippingCharges float(4,2),out wrapCharges float(4,2))

begin

select mshippingcharges into shippingCharges from

orders where corderno = orderno;

select mgiftwrapcharges into wrapcharges from orders where corderno = orderno;

end;

--执行时命令

call prccharges('000001',@pp,@qq);

select @pp,@qq;

--执行结果

mysql> select @pp,@qq;

+-----+------+

| @pp | @qq  |

+-----+------+

| 6   | 1.25 |

+-----+------+

1 row in set

create procedure prcHandLingCharges(in orderno char(6),

out handlingCharges float(4,2))

begin

declare ppp float(4,2);

declare qqq float(4,2);

call prccharges(orderno,ppp,qqq);

set handlingCharges = ppp + qqq;

end;

--执行时命令

call prcHandLingCharges('000001',@qqqq);

select @qqqq;

--执行结果

mysql> select @qqqq;

+-------+

| @qqqq |

+-------+

| 7.25  |

+-------+

1 row in set