--表格 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 set2、在存储过程中，可以使用动态执行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; 
