--根据人名查个人薪水和本部门平均薪水
--select语句实现
select a.deptno "部门编号",
a.dname "部门名称",
a.ename "姓名",
a.sal "薪水",
b.avg_sal "部门平均薪水"
from (select d.deptno, d.dname, e.ename, sal + nvl(comm, 0) sal
from emp e
join dept d on e.deptno = d.deptno
where ename = '&name') a
join (select deptno, avg(sal + nvl(comm, 0)) avg_sal
from emp
where deptno in (select deptno from emp where ename = '&name')
group by deptno) b on a.deptno = b.deptno;
--匿名块实现
declare
v_deptno emp.deptno%type;
v_dname dept.dname%type;
v_ename varchar(20);
v_sal emp.sal%type;
v_avg_sal emp.sal%type;
begin
v_ename := '&请输入姓名：';
select deptno into v_deptno from emp where ename = v_ename;
dbms_output.put_line('部门编号：' || v_deptno);
select dname into v_dname from dept where deptno = v_deptno;
dbms_output.put_line('部门名称：' || v_dname);
dbms_output.put_line('员工姓名：' || v_ename);
select sal + nvl(comm, 0) into v_sal from emp where ename = v_ename;
dbms_output.put_line('员工薪水：' || v_sal);
select avg(sal + nvl(comm, 0))
into v_avg_sal
from emp
where deptno = v_deptno;
dbms_output.put_line('部门平均薪水：' || v_avg_sal);
end;
--游标存储过程实现（无select语句）
create or replace procedure p1011(v_ename in emp.ename%type) is
cursor c is select * from emp;
cursor cd is select * from dept;
v_sal emp.sal%type;
v_avg_sal emp.sal%type := 0;
v_dname dept.dname%type;
v_deptno dept.deptno%type;
v_n number := 0;
begin
for v_emp in c loop
if v_emp.ename = v_ename then
v_sal := v_emp.sal + nvl(v_emp.comm,0);
v_deptno := v_emp.deptno;
end if;
end loop;
for v_dept in cd loop
if v_dept.deptno = v_deptno then
v_dname := v_dept.dname;
end if;
end loop;
for v_emp in c loop
if v_emp.deptno = v_deptno then
v_avg_sal := v_avg_sal + v_emp.sal + nvl(v_emp.comm,0);
v_n := v_n + 1;
end if;
end loop;
v_avg_sal := v_avg_sal / v_n;
dbms_output.put_line('员工姓名：' || v_ename);
dbms_output.put_line('员工薪水：' || v_sal);
dbms_output.put_line(v_dname || '部门的平均薪水是：' || v_avg_sal);
end;