mysql存储过程示例

mysql存储过程示例

DELIMITER $$
USE `mall_system`$$
//删除存储过程
DROP PROCEDURE IF EXISTS `get_statistics`$$
//创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_statistics`(IN shopId BIGINT)//传入的参数
BEGIN
	//定义变量
	/*今日数据*/
	DECLARE today_money DECIMAL(12,2);/*成交总额*/
	DECLARE today_visitor INT;/*访客数*/
	DECLARE today_order INT;/*订单数*/
	DECLARE total_user INT;/*累计客户数*/
	DECLARE pay_consumer_number INT;/*支付人数*/
	DECLARE browse_number INT;/*浏览量*/
	DECLARE use_date INT;/*使用天数*/
	
	
	/*昨日数据*/
	DECLARE yesterday_money DECIMAL(12,2);/*成交总额*/
	DECLARE yesterday_visitor INT;/*访客数*/
	DECLARE yesterday_order INT;/*订单数*/
	DECLARE yesterday_pay_consumer INT;/*支付人数*/
	DECLARE yesterday_browse_number INT;/*浏览量*/
	
	
	DECLARE wait_to_deliver INT;/*待发货*/
	DECLARE wait_to_pay INT;/*待付款*/
	DECLARE sku_low INT;/*查询库存过少商品数量*/
	
	
	/*今日查询*/
	
	/*成交总额*/
	SELECT IFNULL(SUM(total_real_price),0) INTO today_money FROM `shop_order` WHERE shop_id = shopId AND STATUS IN(1,2,3) AND DATEDIFF(CURDATE(),pay_time) = 0;
	/*访客数*/
	SELECT COUNT(*) INTO today_visitor FROM `visitor_records` WHERE shop_id = shopId AND DATEDIFF(CURDATE(),`date`) = 0;
	/*订单数*/
	SELECT COUNT(*) INTO today_order FROM `shop_order` WHERE shop_id = shopId AND STATUS IN(1,2,3) AND DATEDIFF(CURDATE(),pay_time) = 0;
	/*累计客户数*/
	SELECT COUNT(*) INTO total_user FROM `user_info` WHERE shop_id = shopId;
	/*支付人数*/
	SELECT IFNULL(COUNT(DISTINCT(user_id)),0) INTO pay_consumer_number FROM `shop_order` WHERE shop_id = shopId AND DATEDIFF(CURDATE(),pay_time)=0;
	/*浏览量*/
	SELECT IFNULL(SUM(times),0) INTO browse_number FROM `visitor_records` WHERE shop_id = shopId AND DATEDIFF(CURDATE(),`date`)=0;
	/*使用天数*/
	SELECT  DATEDIFF(CURDATE(),create_time)INTO use_date FROM `shop` WHERE id = shopId;
	
	/*昨日查询*/
	
	/*成交总额*/
	SELECT IFNULL(SUM(total_real_price),0) INTO yesterday_money FROM `shop_order` WHERE shop_id = shopId AND STATUS IN(1,2,3) AND DATEDIFF(CURDATE(),pay_time) = 1;
	/*访客数*/
	SELECT COUNT(*) INTO yesterday_visitor FROM `visitor_records` WHERE shop_id = shopId AND DATEDIFF(CURDATE(),`date`) = 1;
	/*订单数*/
	SELECT COUNT(*) INTO yesterday_order FROM `shop_order` WHERE shop_id = shopId AND STATUS IN(1,2,3) AND DATEDIFF(CURDATE(),pay_time) = 1;
	/*支付人数*/
	SELECT IFNULL(COUNT(DISTINCT(user_id)),0) INTO yesterday_pay_consumer FROM `shop_order` WHERE shop_id = shopId AND DATEDIFF(CURDATE(),pay_time)=1;
	/*浏览量*/
	SELECT IFNULL(SUM(times),0) INTO yesterday_browse_number FROM `visitor_records` WHERE shop_id = shopId AND DATEDIFF(CURDATE(),`date`)=1;
	
	
	/*待发货*/
	SELECT COUNT(*) INTO wait_to_deliver FROM `shop_order` WHERE shop_id = shopId AND STATUS = 1;
	/*待退款*/
	SELECT COUNT(*) INTO wait_to_pay FROM `shop_order` WHERE shop_id = shopId AND (`status`= 5 OR `status` = 4);
	/*查询库存过少商品数量*/
	SELECT COUNT(*) INTO sku_low FROM `prod_spu` WHERE shop_id = shopId AND total_inventory < 10 AND putaway = 1 AND isdelete = 0;
	//返回查询的结果
	SELECT today_money AS todayMoney ,today_visitor AS todayVisitor,today_order AS todayOrder,total_user AS totalUser,
	yesterday_money AS yesterdayMoney,yesterday_visitor AS yesterdayVisitor,yesterday_order AS yesterdayOrder,
	wait_to_deliver AS waitToDeliver,wait_to_pay AS waitToPay,sku_low AS skuLow,pay_consumer_number AS payConsumerNumber,
	browse_number AS browseNumber,yesterday_pay_consumer AS yesterdayPayConsumer,yesterday_browse_number AS yesterdayBrowseNumber,
	use_date AS useDate;
    END$$

DELIMITER ;