MySQL存储过程遍历游标用loop嵌套循环的例子

                MySQL存储过程遍历游标用loop嵌套循环的例子

一、表的结构
CREATE TABLE T_GROW_POINT_STATISTICS (
GROW_POINT_STATISTICS_ID int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
GROW_GROUP_ID int(11) DEFAULT NULL COMMENT ‘拓客活动组ID’,
SHOP_ID int(11) DEFAULT NULL COMMENT ‘店铺ID’,
SHOP_NAME varchar(50) DEFAULT NULL COMMENT ‘店铺名称’,
STAFF_ID int(11) DEFAULT NULL COMMENT ‘员工ID’,
STAFF_NAME varchar(10) DEFAULT NULL COMMENT ‘员工姓名’,
STAFF_PHONE varchar(12) DEFAULT NULL COMMENT ‘员工电话’,
COUNT_ORDER int(5) DEFAULT NULL COMMENT ‘累计成交订单数’,
COUNT_MONEY int(8) DEFAULT NULL COMMENT ‘累计成交金额’,
COUNT_OLD_ORDER int(5) DEFAULT NULL COMMENT ‘老客户成交单数’,
COUNT_OLD_MONEY int(8) DEFAULT NULL COMMENT ‘老客户成交金额’,
COUNT_NEW_ORDER int(5) DEFAULT NULL COMMENT ‘新客户成交单数’,
COUNT_NEW_MONEY int(8) DEFAULT NULL COMMENT ‘新客户成交金额’,
COUNT_VOTE int(8) DEFAULT NULL COMMENT ‘累计投票总数’,
COUNT_ADD_ONE int(5) DEFAULT NULL COMMENT ‘累计加一桶油数’,
COUNT_ADD_TWO int(5) DEFAULT NULL COMMENT ‘累计加二桶油数’,
COUNT_ADD_THREE int(5) DEFAULT NULL COMMENT ‘累计加三桶油数’,
COUNT_ADD_FIVE int(5) DEFAULT NULL COMMENT ‘累计加五桶油数’,
COUNT_ADD_TEN int(5) DEFAULT NULL COMMENT ‘累计加十桶油数’,
COUNT_POINT int(8) DEFAULT NULL COMMENT ‘总积分’,
CREATE_TIME varchar(20) DEFAULT NULL COMMENT ‘创建时间(时间最接近的为当前活动)’,
PRIMARY KEY (GROW_POINT_STATISTICS_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’拓客积分统计表(增加员工参加拓客活动的时候初始化)’;
二、输入组ID显示店名和店的总积分排名，和店下员工的积分排名
DELIMITER ;

  CREATE  PROCEDURE    proc(IN GROUP_ID INT )  

          BEGIN 
            // 定义变量店名   
    DECLARE Shop VARCHAR(30);
    //定义变量总积分
        DECLARE point INT DEFAULT 0;
        //定义变量员工名称
    DECLARE Shop1 VARCHAR(30);
    //定义变量员工个工积分
        DECLARE point1 INT DEFAULT 0;   
    DECLARE done INT DEFAULT FALSE;
    //变量输出结果
    DECLARE `result` varchar(2000);
    //定义一个接收店积分结果集排名的游标
    DECLARE cur_shop CURSOR FOR select  shop_name,sum(COUNT_POINT) from 
        t_grow_point_statistics where GROW_GROUP_ID = GROUP_ID  
    group by shop_name  
    order by sum(COUNT_POINT) desc;
    //定义一个接收店中员工积分结果集排名的游标
    DECLARE cur_staff CURSOR FOR select STAFF_NAME, sum(COUNT_POINT)  from 
    t_grow_point_statistics where GROW_GROUP_ID=GROUP_ID and shop_name=Shop
    group by  STAFF_ID
    order by sum(COUNT_POINT) desc;         

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    SET result = '';
    //打开店排名游标
    OPEN  cur_shop;
    //遍历店游标
        read_loop: LOOP
        //把店名和总积分赋给变量Shop和point
            FETCH  NEXT from cur_shop INTO Shop,point;
            IF done THEN
            LEAVE read_loop;
            END IF; 
            //拼接店的排名输出结果  
    set result = CONCAT(result,Shop,point );
    //打开员工排名游标
        OPEN  cur_staff;
        //遍历员工排名游标（内循环）
    inner_loop:LOOP
    FETCH  NEXT from cur_staff INTO Shop1,point1;
            IF done THEN
            LEAVE inner_loop;
            END IF;
            //拼接员工排名的输入结果
    set result = CONCAT(result,Shop1,point1 );      
        END LOOP inner_loop;
        //关团员工排名游标
    CLOSE cur_staff;
    SET done=0;
            END LOOP;
    //关闭店排名游标
        CLOSE cur_shop;
        //输出结果
    select `result`;
          END ;

DELIMITER
//调用存储过程
call proc(1111);