MYsql-儲存過程-遊標的巢狀

在ITPUB: http://www.itpub.net/viewthread.php?tid=1134085&pid=13049789&page=1&extra=#pid13049789上有人發貼說遊標不能巢狀,

自己搞了一下,答案確實是..

[@more@]

create table heyf_6 (empid int , deptid int );
insert into heyf_6 values (1,20),(2,30),(5,20),(4,50),(6,20);

--按DEPTID從小到大列印各個部分裡的EMPID

DROP PROCEDURE IF EXISTS proc_empid;
DELIMITER //

CREATE PROCEDURE proc_empid()
BEGIN
declare done int default 0;
declare v_dept int ;
declare cur_dept cursor For select distinct deptid from heyf_6 order by deptid ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
open cur_dept;
cursor_loop:loop
fetch cur_dept into v_dept;
set @dept = v_dept;
if done=1 then leave cursor_loop ;
end if ;
select 'DEPT: ' ,v_dept ;
begin
declare done1 int default 0;
declare v_emp int;
declare cur_emp cursor For select empid from heyf_6 where deptid ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1 = 1;
open cur_emp;
cur_loop:loop
fetch cur_emp into v_emp ;
if done1=1 then leave cur_loop ;
end if ;
select '------------',@dept,v_emp ;
end loop cur_loop;
close cur_emp;
end;
end loop cursor_loop ;
close cur_dept;
END ;//
DELIMITER ;

call proc_empid;