存储过程--结果集

佚名 6年前 (2019-04-23) 随笔 749人围观抢沙发百度已收录

/**
*根据省份id和时间查询不同班次累计域名和url次数
**/
DROP PROCEDURE IF EXISTS proc_get_province_count_info;

CREATE PROCEDURE proc_get_province_count_info (
	IN province_id INT,
	IN start_time  varchar(50),
	IN end_time  varchar(50),
	OUT domain_count INT,
	OUT url_count INT,
	OUT last_domain_count INT,
	OUT last_url_count INT

)
BEGIN
-- 查询所有省份id
     DECLARE done INT DEFAULT 0;  
     DECLARE cur_province_ids CURSOR FOR SELECT distinct subject_id FROM shop_subject_user_relation;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
  

 -- 本班次累计域名
OPEN cur_province_ids; 
province_ids: LOOP  
FETCH cur_province_ids INTO province_id;
  IF done = 1 THEN -- 循环结束
        LEAVE province_ids; 
  ELSE
 SELECT

      count(*) INTO domain_count
        FROM
            shop_work_order
        where url <>''    group by url;
-- 本班次累计URL
 SELECT

      count(*) INTO url_count
        FROM
            shop_work_order sp
        where url <>''   group by url;
-- 上班次累计域名
 SELECT

      count(*) INTO last_domain_count
        FROM
            shop_work_order
        where url <>''   group by url;
-- 上班次累计URL
 SELECT

      count(*) INTO last_url_count
        FROM
            shop_work_order
        where url <>''   group by url;
END IF;
END LOOP province_ids;
CLOSE cur_province_ids;
END

 
-- 调用
call proc_get_province_count_info('1','2','2',@d1,@d2,@d3,@d4);
SELECT @d1,@d2,@d3,@d4;