MySQL存储过程遍历游标用loop嵌套循环的例子
时间:2019-02-18 13:51 来源:linux.it.net.cn 作者:IT
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)
| 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) |