## MySQL双重循环游标--为每个部门下的人员重新排序
CREATE DEFINER=`root`@`%` PROCEDURE `sortByOrg`()
BEGIN
#定义结束变量,必须设置默认值
DECLARE out_done int DEFAULT 0;
#定义变量,将游标中的数据取出,放入变量中
DECLARE orgId BIGINT;
#定义游标
DECLARE out_cursor CURSOR FOR SELECT su.org_id AS orgId FROM sys_user su GROUP BY su.org_id;
#定义游标循环结束时,结束变量的值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET out_done=1;
#开启游标
OPEN out_cursor;
#开启外层循环
WHILE out_done <> 1 DO
#取出游标中的值,放入变量,多个变量 -- orgId,count
FETCH out_cursor INTO orgId;
#开启内层循环
BEGIN
DECLARE inner_done int DEFAULT 0;
DECLARE userId BIGINT;
DECLARE i int DEFAULT 1;
DECLARE inner_cursor CURSOR FOR SELECT su.id AS userId FROM sys_user su WHERE su.org_id=orgId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET inner_done=1;
OPEN inner_cursor;
WHILE inner_done <> 1 DO
FETCH inner_cursor INTO userId;
#必须多加一层if判断,否则最后的排序比上一次排序多1,即多循环了一次
IF inner_done <> 1 THEN
UPDATE sys_user su SET su.sort = i WHERE su.id = userId;
SET i = i + 1;
END IF;
END WHILE;
CLOSE inner_cursor;
END;
END WHILE;
CLOSE out_cursor;
END