游标嵌套异常1329无法捕获

LMUser

————————————————————————————————————————————————
MySQL-5.5.15
————————————————————————————————————————————————

CREATE TABLE tb_user (
   id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   u_name CHAR(30) DEFAULT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY u_name (u_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE tb_user_message (
   message_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   user_id INT(10) UNSIGNED NOT NULL,
   message_title CHAR(30) DEFAULT NULL,
   PRIMARY KEY (message_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE temp_message_100 (
   message_id INT(10) UNSIGNED NOT NULL,
   message_title CHAR(30) DEFAULT NULL,
   PRIMARY KEY (message_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE temp_message_200 (
   message_id INT(10) UNSIGNED NOT NULL,
   message_title CHAR(30) DEFAULT NULL,
   PRIMARY KEY (message_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE temp_message_300 (
   message_id INT(10) UNSIGNED NOT NULL,
   message_title CHAR(30) DEFAULT NULL,
   PRIMARY KEY (message_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

————————————————————————————————————————————————

DELIMITER $$
CREATE PROCEDURE sp_nested_cursor()
BEGIN
    DECLARE uid, m_id INT(10);
    DECLARE m_title VARCHAR(100);
    DECLARE done_outer TINYINT DEFAULT 0;

    DECLARE cur_user_id CURSOR FOR SELECT id FROM tb_user;
/* DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done_outer = 1; */
    DECLARE EXIT HANDLER FOR NOT FOUND, SQLSTATE '02000' SET done_outer = 1;

    OPEN cur_user_id;
    loop_user_id:LOOP
  
       FETCH cur_user_id INTO uid;
       IF done_outer THEN
          LEAVE loop_user_id;
       END IF;

       BEGIN
           DECLARE done_inner TINYINT DEFAULT 0;
           DECLARE cur_user_message CURSOR FOR SELECT message_id, message_title FROM tb_user_message WHERE user_id = uid;
           DECLARE EXIT HANDLER FOR SQLSTATE '02000' SET done_inner = 1;

           OPEN cur_user_message;
           loop_user_message:LOOP

                FETCH cur_user_message INTO m_id, m_title;
                IF done_inner THEN
                   LEAVE loop_user_message;
                END IF;

                IF m_id < 100 THEN
                   INSERT INTO temp_message_100 VALUES (m_id, m_title);
                ELSEIF m_id < 200 THEN
                   INSERT INTO temp_message_200 VALUES (m_id, m_title);
                ELSEIF m_id < 20000 THEN
                   INSERT INTO temp_message_300 VALUES (m_id, m_title);
                END IF;

           END LOOP;
           CLOSE cur_user_message;
       END;

    END LOOP;
    CLOSE cur_user_id;
   
END; $$
DELIMITER ;

————————————————————————————————————————————————
mysql> CALL sp_nested_cursor();

Error 1329 No data - zero rows fetched, selected, or processed

LMUser

回复 2# kerlion


    是说MySQL文档手册上的游标代码吗？
   那个不是嵌套循环啊，只是两个游标的循环；
   对比看一下我这个就知道了。