Question

mysql 游标中可以嵌套游标吗

Answer 1

CREATE PROCEDURE SumOfStu(OUT Sdeptno int,OUT Sdeptname char(20),OUT sumBefore int,OUT sumAfter int)
BEGIN
DECLARE l_sum int;
DECLARE l_Sdeptno int;
DECLARE l_sno int;
DECLARE l_sumBefore int;
DECLARE done, done2 INT DEFAULT 0;
DECLARE cur_out cursor for select SdeptNo,population from Department;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_out;
REPEAT
SET l_sum=0;
FETCH cur_out INTO l_Sdeptno,l_sumBefore;
IF NOT done THEN
BEGIN
DECLARE cur_inner cursor for select Sno from student where SdeptNo=l_Sdeptno;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1;
OPEN cur_inner;
REPEAT
FETCH cur_inner into l_sno;
IF NOT done2 THEN
SET ps_count = 0;
SET l_sum=l_sum+1;
END IF;
UNTIL done2
END REPEAT;
CLOSE cur_inner;
SET done2 = 0;
END;
IF(l_sum<>l_sumBefore) THEN
update department SET population=l_sum where SdeptNo=l_Sdeptno;
select l_Sdeptno INTO Sdeptno;
select SdeptName INTO Sdeptname from Department where SdeptNo=l_Sdeptno;
select l_sumBefore INTO sumBefore;
select l_sum INTO sumAfter;
end IF;
END IF;
UNTIL done
END REPEAT;
CLOSE cur_out;
END