关闭

Oracle plsqlI 练习 传值

496人阅读 评论(0) 收藏 举报
分类:
--根据人名查个人薪水和本部门平均薪水

--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;
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:231658次
    • 积分:4881
    • 等级:
    • 排名:第5857名
    • 原创:227篇
    • 转载:93篇
    • 译文:0篇
    • 评论:13条
    博客专栏
    最新评论