mysql 存储过程

最新推荐文章于 2025-08-22 11:10:38 发布

weixin_34234823

最新推荐文章于 2025-08-22 11:10:38 发布

阅读量66

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/dslcode/blog/1590505

2019独角兽企业重金招聘Python工程师标准>>>

mysql 存储过程

-- DELETE FROM t_order_one_day;-- 清空

-- DROP PROCEDURE insert_order_one_day;-- 删除存储过程

-- CALL insert_order_one_day(20, 1459094400, 1459180799, '2016-03-28');-- 调用存储过程

CREATE PROCEDURE insert_order_one_day(-- 创建存储过程，命名为：insert_order_one_day

IN order_state tinyint(4),-- 输入参数：订单状态

IN begin_time INT,-- 输入参数：一天凌晨的开始时间

IN end_time INT,-- 输入参数：一天凌晨的结束时间

IN time_str CHAR(10)-- 输入参数：一天时间字符串，'yyyy-MM-dd'

) BEGIN

DECLARE is_over BOOLEAN DEFAULT 0;-- 声明局部参数：是否查询完成，结束标志

DECLARE channel_id INT DEFAULT NULL;-- 声明局部参数：渠道id

DECLARE order_count INT DEFAULT 0;-- 声明局部参数：订单总数

DECLARE order_income DECIMAL(19, 2) DEFAULT 0.00;-- 声明局部参数：总收入

DECLARE parent_income DECIMAL(10, 2) DEFAULT NULL;-- 声明局部参数：父渠道总收入

DECLARE order_total DECIMAL(19, 2) DEFAULT 0.00;-- 声明局部参数：总金额

DECLARE parent_channel_id INT DEFAULT NULL;-- 声明局部参数：父渠道id

DECLARE yesterday_order_channel_ids CURSOR FOR-- 声明游标：来自sql

SELECT DISTINCT ord.channel_id FROM t_order AS ord WHERE ord.order_state >= order_state AND (ord.add_time BETWEEN begin_time AND end_time);

DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_over = 1;-- 持续操作直到没有发现更多结果设置完成标志

OPEN yesterday_order_channel_ids;-- 开启游标

REPEAT-- 开始遍历

FETCH yesterday_order_channel_ids INTO channel_id;-- 将游标指针移动到的数据交给渠道id

IF NOT is_over THEN-- 如果结束标志为0则操作

-- 订单总数

SELECT COUNT(ord1.order_id) FROM t_order AS ord1 WHERE ord1.channel_id = channel_id AND ord1.order_state >= order_state AND (ord1.add_time BETWEEN begin_time AND end_time) INTO order_count;

-- 总金额

SELECT SUM(ord3.order_amount) FROM t_order AS ord3 WHERE ord3.channel_id = channel_id AND ord3.order_state >= order_state AND (ord3.add_time BETWEEN begin_time AND end_time) INTO order_total;

-- 父渠道id

SELECT chann.parent_id FROM t_channel AS chann WHERE chann.id = channel_id INTO parent_channel_id;

IF parent_channel_id IS NULL THEN-- 没有父渠道，直接计算出总收入

SELECT SUM(order_goods.goods_num*order_goods.goods_commission)

FROM t_order_goods AS order_goods INNER JOIN t_order AS ord2 ON order_goods.order_id=ord2.order_id

WHERE ord2.channel_id = channel_id AND ord2.order_state >= order_state AND (ord2.add_time BETWEEN begin_time AND end_time) INTO order_income;

ELSE-- 拥有父渠道，分别计算

-- 渠道总收入

SELECT SUM(order_goods.goods_num*order_goods.goods_commission*(ord2.order_point/100.0))

FROM t_order_goods AS order_goods INNER JOIN t_order AS ord2 ON order_goods.order_id=ord2.order_id

WHERE ord2.channel_id = channel_id AND ord2.order_state >= order_state AND (ord2.add_time BETWEEN begin_time AND end_time) INTO order_income;

-- 父渠道总收入

SELECT SUM(order_goods.goods_num*order_goods.goods_commission*(1.0-ord4.order_point/100.0))

FROM t_order_goods AS order_goods INNER JOIN t_order AS ord4 ON order_goods.order_id=ord4.order_id

WHERE ord4.channel_id = channel_id AND ord4.order_state >= order_state AND (ord4.add_time BETWEEN begin_time AND end_time) INTO parent_income;

END IF;

IF order_income IS NULL THEN

SET order_income = 0.00;

END IF;

INSERT INTO t_order_one_day (order_date, channel_id, order_count, order_day_int, order_income, parent_income, order_total, parent_channel_id) VALUES (

time_str,

channel_id,

order_count,

begin_time,

order_income,

parent_income,

order_total,

parent_channel_id

);

SET channel_id = NULL;

SET parent_channel_id = NULL;

SET order_count = 0;