mysql触发not found

2016-02-24 • DatabaseNo Comments

今天给后台用户批量授权时写了一小段存储过程,遇到一个比较初级的问题:例程里面如果触发了not found,是会退出程序的。下面是当时写的代码。


BEGIN

DECLARE count_num_i INT DEFAULT 0;
DECLARE brand_id_start INT DEFAULT 29;
DECLARE brand_id_end INT DEFAULT 37;
DECLARE brand_id_offset INT DEFAULT 0;
DECLARE last_flag INT DEFAULT 0;
DECLARE user_id_tmp INT DEFAULT 0;
DECLARE user_name_tmp VARCHAR(60) DEFAULT '';
DECLARE exists_permission INT DEFAULT 0;
DECLARE brand_user_cursor CURSOR FOR SELECT user_id, user_name FROM vz_user WHERE main_user_id = 0 OR main_user_id IS NULL  ORDER BY user_id ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_flag = 1; 

OPEN brand_user_cursor;
FETCH brand_user_cursor INTO user_id_tmp, user_name_tmp;
WHILE last_flag != 1 DO
    SET brand_id_offset = brand_id_start;
    SET count_num_i = count_num_i + 1;
    REPEAT
            SELECT COUNT(manage_sub_user_id) INTO exists_permission from vz_brand_manage WHERE manage_sub_user_id = user_id_tmp AND brand_id = brand_id_offset  LIMIT 1;
            IF exists_permission < 1 THEN
                INSERT INTO 
                vz_brand_manage (manage_user_id,manage_user_name, manage_sub_user_id, manage_sub_user_name, brand_id ) 
                VALUES 
                (user_id_tmp, user_name_tmp, user_id_tmp, user_name_tmp, brand_id_offset);
            END IF;
            SET brand_id_offset = brand_id_offset + 1;
    UNTIL  brand_id_offset > brand_id_end END REPEAT;
    FETCH brand_user_cursor INTO user_id_tmp, user_name_tmp;
END WHILE;
CLOSE brand_user_cursor;
RETURN count_num_i;

END

问题在于这一句:


SELECT COUNT(manage_sub_user_id) INTO exists_permission from vz_brand_manage WHERE manage_sub_user_id = user_id_tmp AND brand_id = brand_id_offset  LIMIT 1;

我之前是这样写的:

SELECT manage_sub_user_id INTO exists_permission from vz_brand_manage WHERE manage_sub_user_id = user_id_tmp AND brand_id = brand_id_offset  LIMIT 1;

这样写就会遇到找不到记录的情况,就是所谓的触发了not found,会直接退出例程。所以呢,可以查询count值,避免这种情况。这里感谢浩坚大大的帮忙,我决定要加上你的友链!

粤公网安备 44011102000950号