【转】MYSQL使用游标嵌套的两个实例

在实际业务逻辑开发中，难免用到游标嵌套，举例如下：

delimiter //

drop procedure if exists good_nested_cursors1

//

CREATE   PROCEDURE good_nested_cursors1(  )

   READS SQL DATA

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;

                select concat('年级：', l_grade_id);

    IF l_done=1 THEN

       LEAVE grade_loop;

    END IF;

    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('    班级：', l_class_id);

    END LOOP;

    CLOSE class_csr;

    SET l_done=0;

  

  END LOOP grade_loop;

  CLOSE grade_csr;

END;

//

delimiter ;

///////////////////////////////////////////////////////

//另一个例子：

CREATE PROCEDURE curdemo() 

NOT DETERMINISTIC 

CONTAINS SQL 

SQL SECURITY DEFINER 

COMMENT '' 

BEGIN 

declare done1,done2 int default 0; 

declare name1,name2 varchar(20); 

declare id1,id2 int;

   

declare cur1 cursor for select id,name from test1; 

declare continue handler for not found set done1 = 1;

open cur1;

repeat 

fetch cur1 into id1, name1; 

if not done1 then 

insert into test3(name) values(name1); 

begin 

declare cur2 cursor for select id,name from test2; 

declare continue handler for not found set done2 = 1; 

open cur2; 

repeat 

fetch cur2 into id2,name2; 

if not done2 then 

insert into test3(name) values(name2); 

end if;   

until done2 end repeat; 

close cur2; 

set done2=0; 

end; 

end if; 

until done1 end repeat; 

close cur1;

commit; 

END;

///

作者 蔡磊

摘自：http://www.qi9.cn/html/759_5400.html