mysql 存储过程 嵌套游标_MYsql-存储过程-游标的嵌套

自己搞了一下,答案确实是..[@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 =@dept ;

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;