mysql 存储过程 游标嵌套

CREATE TABLE `org_grade` (
  `grade_id` int(11) NOT NULL,
  PRIMARY KEY (`grade_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

CREATE TABLE `org_class` (
  `class_id` int(11) NOT NULL,
  `grade_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

insert into org_grade values(1),(2),(3);

insert into org_class  values (1,1),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,3);

 

 

delimiter //
drop procedure if exists good_nested_cursors1
//
CREATE   PROCEDURE good_nested_cursors1()
BEGIN
  DECLARE l_grade_id INT;
  DECLARE l_class_id   INT;
  DECLARE l_class_cnt     INT DEFAULT 0 ;
  DECLARE l_done          INT DEFAULT  0;

  DECLARE grade_csr cursor  FOR    SELECT grade_id FROM org_grade;
  DECLARE class_csr cursor  FOR     SELECT class_id FROM org_class  WHERE grade_id=l_grade_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
  OPEN grade_csr;
  grade_loop: LOOP   -- Loop through org_grade
    FETCH grade_csr into l_grade_id;
               
    IF l_done=1 THEN
       LEAVE grade_loop;
    END IF;
 
 select concat('grade:', l_grade_id);

    OPEN class_csr;
    SET l_class_cnt=0;
    class_loop: LOOP      -- Loop through class in grade.
      FETCH class_csr INTO l_class_id;

      IF l_done=1 THEN
         LEAVE class_loop;
      END IF;
      SET l_class_cnt=l_class_cnt+1;
      select  concat(concat('grade:',l_grade_id),concat(' class:', l_class_id));
    END LOOP;
    CLOSE class_csr;
    SET l_done=0;
 END LOOP grade_loop;
  CLOSE grade_csr;
END;
//
delimiter ;

 

 

 

注意：

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; （游标取不到下一条记录时 done=1)

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1; (查询不到结果时 l_done=1)

这两条语句的作用域分别是声明处的 block 中 (一对begin，end算一个block) 。 如果block中的语句触发了这个handler ，记得重置该值(上例）。或者如下例，采用独立的BLOCK 声明独立的 handler；

--------------------------------------------------------------------

 

delimiter //
drop procedure if exists test1//
create procedure test1()
  begin   
        declare done int default 0;
        declare v_order_id int(11);       
        declare v_tagr_id int(11);   
       
            block1:begin
            declare cur1 cursor for select order_id from tbl_order where date_add(add_time,interval 2 day)<now();   
            declare cur2 cursor for select tag_id from tbl_tags_record limit 2;           
            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
            open cur1;
            loop1:loop
            fetch cur1 into v_order_id;               
            if done then
                close cur1;
                leave loop1;
            end if;   
            select concat('out1:',v_order_id);
            block2:begin
           
            DECLARE done2 int default 0;
            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
            open cur2;
            loop2:loop
            fetch cur2 into v_tagr_id;               
            if done2 then
                close cur2;
                leave loop2;
            end if;       
            select concat('in1:',v_tagr_id);
            end loop loop2;
            end block2;   
           
           
            end loop loop1;       
            end block1;
  end
 //
 delimiter ;

 

参考资料：http://bbs.chinaunix.net/viewthread.php?tid=772236

http://school.cnd8.com/mysql/jiaocheng/34969.htm

http://hideto.iteye.com/blog/195275

http://xp19830105.iteye.com/blog/667974

http://godzwj.iteye.com/blog/398707