CH06_包练习

一、创建3张表，分别代表不同部门员工信息,不带数据
emp10
emp20
emp30

create table emp10 as select * from emp where 1=0;
create table emp20 as select * from emp where 1=0;
create table emp30 as select * from emp where 1=0;

二、创建包，用于统计部门信息
1)添加常量 ALLDEPTNAME 值为: '部门信息概况'
2)声明函数:用于统计某部门员工数(RETURN)
3)声明过程:将不同部门(In)的员工存放到不同表中
4)声明函数：统计该部门员工平均工资，及每月要发放工资的总和，并返回
5)声明函数：返回该部门的信息概况：部门号，部门名，部门所在地，该部门员工数

create or replace package pack_emp is
  ALLDEPTNAME constant varchar2(20) := '部门信息概况';
  function getEmpNum(v_eno emp.deptno%type) return number;
  procedure saveEmp(v_dno emp.deptno%type);
  function getSumSals(v_dno emp.deptno%type,v_avgsal out emp.sal%type,v_sumsal out emp.sal%type) return number;
  function getDeptInfo(v_dno in out dept.deptno%type,v_dname out dept.dname%type,v_loc out dept.loc%type) return number;
end pack_emp;

三、创建包体，实上以上过程

create or replace package body pack_emp is
  --用于统计某部门员工数
  function getEmpNum(v_eno emp.deptno%type) return number is
    nu number;
  begin
    select count(empno) into nu from emp where deptno = v_eno;
    return nu;
  end getEmpNum;
  --将不同部门(In)的员工存放到不同表中
  procedure saveEmp(v_dno emp.deptno%type) is
    cursor cur_emp is select * from emp where deptno = v_dno;
  begin
    for emp_record in cur_emp loop
     if v_dno = 10 then 
      insert into emp10  select * from emp where deptno = v_dno;
     elsif v_dno = 20 then 
      insert into emp20  select * from emp where deptno = v_dno;
     elsif v_dno = 30 then 
      insert into emp30  select * from emp where deptno = v_dno;
     end if;
   end loop;
  exception
    when no_data_found then
     dbms_output.put_line('此部门不存在');
    when others then
      dbms_output.put_line('操作有误！');
  end saveEmp;
  --统计该部门员工平均工资，及每月要发放工资的总和，并返回
  function getSumSals(v_dno emp.deptno%type,v_avgsal out emp.sal%type,v_sumsal out emp.sal%type) return number is  
  begin
    select avg(sal),sum(sal) into v_avgsal,v_sumsal from emp where deptno = v_dno;
    return 0;
  end getSumSals;
  --返回该部门的信息概况：部门号，部门名，部门所在地，该部门员工数
  function getDeptInfo(v_dno in out dept.deptno%type,v_dname out dept.dname%type,v_loc out dept.loc%type) return number is
    nu number;
  begin
     select d.dname,d.loc,count(e.empno) into v_dname,v_loc,nu 
     from emp e,dept d 
     where d.deptno = e.deptno and d.deptno=v_dno group by d.dname,d.loc;   
    return nu;
   end getDeptInfo;
end pack_emp;

四、分别调用验证

declare
  v_dno dept.deptno%type;
  v_no dept.deptno%type := 10;
  nu number(5);
  v_avgsal emp.sal%type;
  v_sumsal emp.sal%type;
  v_dname dept.dname%type;
  v_loc dept.loc%type;
begin
  nu := pack_emp.getEmpNum(10);
  dbms_output.put_line('员工数：'||nu);
  pack_emp.saveEmp(10);
  nu :=  pack_emp.getSumSals(10,v_avgsal,v_sumsal);
  dbms_output.put_line('平均工资:'||v_avgsal||'工资的总和:'||v_sumsal);
  nu := pack_emp.getDeptInfo(v_no,v_dname,v_loc);
  dbms_output.put_line('部门号:'||v_no||'部门名:'||v_dname||'部门所在地:'||v_loc||'部门员工数：'||nu);
end;