mysql loop嵌套,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);