mysql作业嵌套游标的使用一直有问题

发布网友发布时间：2022-04-26 14:18

共1个回答

热心网友时间：2022-04-08 06:05

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