绩效存储过程

批量修改用户角色

CREATE DEFINER=`root`@`%` PROCEDURE `p_role_insert`(IN role_id_param BIGINT, OUT result_size_out INT)
BEGIN

DECLARE done INT DEFAULT 0;
DECLARE resultSize INT DEFAULT 0;
DECLARE user_id_param BIGINT;
DECLARE isExistSize INT;

DECLARE userCursor CURSOR FOR SELECT user_id FROM sys_user WHERE status = 'ENABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 

OPEN userCursor;
out_loop:LOOP
    FETCH userCursor INTO user_id_param;
    IF done = 1 THEN
       LEAVE out_loop;
  END IF;
    SELECT count(*) INTO isExistSize FROM sys_users_roles WHERE user_id = user_id_param and role_id = role_id_param;
    IF (isExistSize = 0) THEN
        INSERT INTO `sys_users_roles` (`user_id`, `role_id`) VALUES (user_id_param, role_id_param);
        set resultSize = resultSize + 1;
    END IF;
END LOOP out_loop;
CLOSE userCursor;
set result_size_out = resultSize;
END