|
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); (责任编辑：IT) |