set serveroutput on
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 number;
count2 number;
count3 number;
--各部门的工资总额
saltotal number;
begin
--打开部门光标
open cdept;
loop
--初始化值
count1 := 0;
count2 := 0;
count3 := 0;
saltotal := 0;
--取得每个部门的部门号
fetch cdept into pdeptno;
exit when cdept%notfound;
--打开员工薪水光标
open cemp(pdeptno);
loop
fetch cemp into psal;
exit when cemp%notfound;
saltotal := saltotal+psal;
--判断员工薪水的字段
if psal < 3000 then count1:=count1+1;
elsif psal >= 3000 and psal <= 6000 then count2:=count2+1;
else count3:=count3+1;
end if;
end loop;
--关闭员工薪水光标
close cemp;
--保存当前部门的结果
insert into msg values(pdeptno,count1,count2,count3,saltotal);
end loop;
--关闭部门光标
close cdept;
dbms_output.put_line('统计结束');
end;
/
