mysql存储过程及调用

mysql存储过程好久没写了都忘记的差不多了，时间久了不写手生现在工作需要写一些也是觉得蛮好玩的，虽然写的不怎么好不怎么高大上，但是感觉很欣慰写多了就熟练一些高级一些吧，花了3,4个小时写的不好请大家多指正，测试过的。在数据库软件Navicat Premium 12中新建存储过程的类型是INOUT类型：

//开始---------------------

CREATE DEFINER=`xxxx`@`%` PROCEDURE `export_seller_amount`(IN `_user_id` int,

OUT totalPayFee decimal(10,2),

OUT personSells decimal(10,2),

OUT selfTotalPayFee decimal(10,2),

OUT selfCommissionAmountTotal decimal(10,2),

OUT teamCommissionAmountTotal decimal(10,2))

BEGIN

 

 

declare totalPayFee decimal(10,2); //定义变量类型

declare personSells decimal(10,2);

declare selfTotalPayFee decimal(10,2);

declare selfCommissionAmountTotal decimal(10,2);

declare teamCommissionAmountTotal decimal(10,2);

 

-- 总销售额 (个人销售额 + 团队销售额)  into赋值

SELECT SUM(b.pay_fee) INTO totalPayFee FROM `wr_fx_aa` `a` INNER JOIN `pay_bb` `b` ON a.serial_id=b.serial_id and b.pay_status=0 INNER JOIN `pay_bb` `c` ON b.serial_id=c.serial_id and c.is_refund=0 WHERE ((`a`.`fx_user_id`=_user_id) AND (`a`.`is_delete`=0)) AND (`b`.`create_time` >= 0) GROUP BY `a`.`fx_user_id`;

 

-- 个人销售额(如xx卖出去 的订单总额)

select sum(pay_fee) INTO personSells from pay_bb where pay_status = 0 and is_fx = 1 and fx_share_from = _user_id;

 

 

-- 收益概况 

-- SELECT  sum(a.pay_fee) as selfTotalPayFee, SUM(c.extract_amount) as selfCommissionAmountTotal, SUM(c.fx_user_amount) as teamCommissionAmountTotal FROM `pay_bb` `a` INNER JOIN `pay_cc` `b` ON a.serial_id=b.serial_id and b.is_refund=0 LEFT JOIN `wr_fx_aa` `c` ON a.serial_id=c.serial_id and c.fx_user_id=_user_id WHERE (`a`.`fx_share_from`=_user_id) AND (`a`.`is_fx`=1) AND (`a`.`pay_status`=0) GROUP BY `a`.`fx_share_from`;

 

SELECT  sum(a.pay_fee) INTO selfTotalPayFee FROM `pay_bb` `a` INNER JOIN `pay_cc` `b` ON a.serial_id=b.serial_id and b.is_refund=0 LEFT JOIN `wr_fx_aa` `c` ON a.serial_id=c.serial_id and c.fx_user_id=_user_id WHERE (`a`.`fx_share_from`=_user_id) AND (`a`.`is_fx`=1) AND (`a`.`pay_status`=0) GROUP BY `a`.`fx_share_from`;

 

SELECT  SUM(c.extract_amount) INTO selfCommissionAmountTotal FROM `pay_bb` `a` INNER JOIN `pay_cc` `b` ON a.serial_id=b.serial_id and b.is_refund=0 LEFT JOIN `wr_fx_aa` `c` ON a.serial_id=c.serial_id and c.fx_user_id=_user_id WHERE (`a`.`fx_share_from`=_user_id) AND (`a`.`is_fx`=1) AND (`a`.`pay_status`=0) GROUP BY `a`.`fx_share_from`;

 

SELECT SUM(c.fx_user_amount) INTO teamCommissionAmountTotal FROM `pay_bb` `a` INNER JOIN `pay_cc` `b` ON a.serial_id=b.serial_id and b.is_refund=0 LEFT JOIN `wr_fx_aa` `c` ON a.serial_id=c.serial_id and c.fx_user_id=_user_id WHERE (`a`.`fx_share_from`=_user_id) AND (`a`.`is_fx`=1) AND (`a`.`pay_status`=0) GROUP BY `a`.`fx_share_from`;

 

set totalPayFee = totalPayFee;

set personSells = personSells;

set selfTotalPayFee = selfTotalPayFee;

set selfCommissionAmountTotal = selfCommissionAmountTotal;

set teamCommissionAmountTotal = teamCommissionAmountTotal;

 

 

 SELECT totalPayFee,personSells,selfTotalPayFee,selfCommissionAmountTotal,teamCommissionAmountTotal;

-- SELECT personSells;

-- SELECT selfTotalPayFee;

-- SELECT selfCommissionAmountTotal;

-- SELECT teamCommissionAmountTotal;

 

 

END

//保存调试，输入参数user_id运行结果看博客的附件图片

 

用php程序如何调取存储过程返回的数据(YII框架)：

 $reg = 273948;  //$user['user_id']

 $cmd = \Yii::$app->db->createCommand("call export_seller_amount(:reg,@totalPayFee,@personSells,@selfTotalPayFee,@selfCommissionAmountTotal,@teamCommissionAmountTotal)");

   $cmd->bindParam(':reg',$reg,\PDO::PARAM_STR,50);

   $data = $cmd->queryOne();

 打印输出结果：

C:\wamp\www\rainbowAdmin\backend\controllers\FxDistributorController.php:495:

array (size=5)

  'totalPayFee' => string '74528.7' (length=6)

  'personSells' => string '72929.93' (length=5)

  'selfTotalPayFee' => string '72929.93' (length=5)

  'selfCommissionAmountTotal' => string '3521.12' (length=4)

  'teamCommissionAmountTotal' => string '410.59' (length=4)

 

 //初步测试 使用存储过程 消耗时间是php读取数据表消耗时间的40%左右，效率还是不错的毕竟mysql是功能齐全数据库引擎机器并且少了来回请求数据的过程时间，初步这么写等一下还要去优化一下