Shulou(Shulou.com)06/01 Report--

At ITPUB: http://www.itpub.net/viewthread.php? tid=1134085&pid=13049789&page=1&extra=#pid13049789 Someone posted that cursors cannot nest,

And the answer is,"I'm sorry.

[@more@]

create table heyf_6 (empid int , deptid int );

insert into heyf_6 values (1,20),(2,30),(5,20),(4,50),(6,20);

--Print EMPID in each section from small to large by DEPTID

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;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.