declare

cursor cdept is select deptno from dept;

pdeptno dept.deptno%type;

cursor cemp(dno number) is select sal from emp where deptno = dno;

psal emp.sal%type;

count1 int;

count2 int;

count3 int;

totalMon number := 0;

flag number;

begin

  open cdept;

  --外层循环

  loop

    fetch cdept into pdeptno;

    exit when cdept%notfound;

    --判断部门是否存在，如果部门不存在直接退出所有循环

    select count(1) into flag from emp where deptno = pdeptno;

    if flag = 0 then return;

    end if;

    --第一层循环内给变量赋值为0，保证每次内层循环的计数器都从零开始（必须要写外层循环内，内层循环外）

    count1 := 0;

    count2 := 0;

    count3 := 0;

    open cemp(pdeptno);

    loop

      select sum(sal) into totalMon from emp where deptno = pdeptno;

      fetch cemp into psal;

      exit when cemp%notfound;

      if psal <3000 then count1 := count1+1;

      elsif psal <6000 then count2 := count2+1;

      else count3 := count3+1;

      end if;

    end loop;

    close cemp;

    --保存到msg表

    insert into msg values(pdeptno,count1,count2,count3,totalMon);

    --输出

    --dbms_output.put_line('部门：'||pdeptno||' 3000以下为：'||count1||' 3000-6000为：'||count2||' 6000以上为：'||count3||' 总额为：'||totalMon);

  end loop;

  close cdept;

  commit;

  dbms_output.put_line('统计完成');

end;

/