按部门编号从小到大的顺序输出雇员名字、工资以及工资与平均工资的差。

--1.按部门编号从小到大的顺序输出雇员名字、工资以及工资与平均工资的差。
--分析：1.需要两游标：c_dept :select deptno from dept order by deptno;
--c_emp(p_deptno): select empno,sal from emp where deptno = p_deptno order by empno;
--变量：v_deptno ,v_empno,v_sal,v_avg_sal
set serveroutput on
declare
v_deptno number;
v_name varchar2(10);
v_sal number;
v_avg_sal number;
cursor c_dept is select deptno from dept order by deptno;
cursor c_emp(p_deptno number) is select ename,sal from emp where deptno = p_deptno order by empno;
begin
  open c_dept;
    loop
      fetch c_dept into v_deptno;
      exit when c_dept%notfound;
      dbms_output.put_line('----------'||v_deptno||'号部门信息----------');
      dbms_output.put_line(rpad('雇员名字',10)||rpad('工资',6)||rpad('工资与平均工资的差',20));
      begin
        open c_emp(v_deptno);
          loop
            fetch c_emp into v_name,v_sal;
            exit when c_emp%notfound;
            select round(avg(sal),2) into v_avg_sal from emp where deptno = v_deptno;
            dbms_output.put_line(rpad(v_name,10)||rpad(v_sal,10)||to_char(v_sal-v_avg_sal));
          end loop;
        close c_emp;
      end;
    end loop;
  close c_dept;
end;