1回答

• 

  chenxinmomota

  2019-07-21 19:52:19

  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;

  /

  
  

  0 0