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

2、在存储过程中，可以使用动态执行sql的方式来返回结果集。eg：

DELIMITER $$

DROP procedure IF EXISTS pro_report_data $$

CREATE procedure pro_report_data(in startT varchar(100),in stopT varchar(100),in buyerFilter varchar(20),gfs varchar(2000),qfs varchar(2000))

BEGIN

declare colsStr varchar(2000);

declare tableStr varchar(2000);

declare whereStr varchar(2000);

declare groupStr varchar(2000);

declare sqls varchar(2000);

call createReportSql4Other(null,null,0,buyerFilter,gfs,qfs,2,startT,stopT,0,colsStr,tableStr,whereStr,groupStr);

set sqls= concat('select ',colsStr,' from ',tableStr,' where ',whereStr,' group by ',groupStr);

set @ms=sqls;

PREPARE s1 from @ms;

EXECUTE s1;

deallocate prepare s1;

END$$

DELIMITER;