用PL/SQL语言编写一个程序,实现按部门分段(6000以上,3000-6000,3000以下)统计各工资段的职工人数 和工资总额(scott--emp)
declare
cursor cdeptno is select deptno from dept;
cd dept.deptno%type;
cursor cempno(ce number) is select empno,sal from emp where deptno=cd;
ce emp.empno%type;
cs emp.sal%type;
sumsal number:=0;
count3 number:=0;
count36 number:=0;
count6 number:=0;
begin
open cdeptno;
dbms_output.put_line('部门'||'小于3000'||'3000-6000'||'大于6000'||'sumsal');
loop
fetch cdeptno into cd;
exit when cdeptno%notfound;
open cempno(cd);
sumsal :=0;
count3 :=0;
count36 :=0;
count6 :=0;
loop
fetch cempno into ce,cs;
exit when cempno%notfound;
if cs >=6000 then count6 :=count6+1;
elsif cs < 6000 and cs>=3000 then count36 :=count36+1;
elsif cs<3000 then
count3 :=count3+1;
end if;
sumsal :=sumsal + cs;
end loop;
close cempno;
dbms_output.put_line(cd||' '||count3||' '||count36||' '||count6||' '||sumsal);
end loop;
close cdeptno;
end;
/