mysql存储过程及调用

程序员文章站 2022-05-30 10:13:07

...

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

mysql存储过程及调用

博客分类： mysql mysql存储过程及调用

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

mysql存储过程及调用

博客分类： mysql mysql存储过程及调用

用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();

mysql存储过程及调用

博客分类： mysql mysql存储过程及调用
打印输出结果：