Oracle笔记 十三、PL/SQL面向对象之package

--将方法和过程用包定义
create or replace package pkg_emp
as
       --输入员工编号查询出员工信息
       procedure pro_findInfo(
         in_empno emp2.empno%type,
         out_name out emp2.ename%type,
         out_sal out emp2.sal%type
       );
       --根据部门编号修改本部门员工工资
       procedure pro_editInfo(
         in_emp_record emp2%rowtype,
         out_flag out boolean
       );
       --输入新员工信息并保存到数据库
       procedure pro_addInfo(
         in_emp_new_record emp2%rowtype
       );
       --统计工资信息
       function fun_sum(
         num_a number,
         num_b number
       ) return number;
end pkg_emp;

--实现包
create or replace package body pkg_emp
as
       --输入员工编号查询出员工信息
       procedure pro_findInfo(
         in_empno emp2.empno%type,
         out_name out emp2.ename%type,
         out_sal out emp2.sal%type
       )
       as
       begin
         select ename, sal into out_name, out_sal from emp2 where empno = in_empno;
       end pro_findInfo;

       --根据部门编号修改本部门员工工资
       procedure pro_editInfo(
         in_emp_record emp2%rowtype,
         out_flag out boolean
       )
       is
       begin
         update emp2 set sal = in_emp_record.sal where deptno = in_emp_record.deptno;
         out_flag := true;
         /*exception
           when no_data_found then
             out_flag := false;
         commit;*/
         if (sql%rowcount < 1) then
           out_flag := false;
         else
           out_flag := true;
           commit;
         end if;
       end pro_editInfo;

       --输入新员工信息并保存到数据库
       procedure pro_addInfo(
         in_emp_new_record emp2%rowtype
       )
       as
         temp_sql varchar2(200);
       begin
         temp_sql := 'insert into emp2(empno, ename, sal, comm, deptno) values(:1, :2, :3, :4, :5)';
         execute immediate temp_sql using in_emp_new_record.empno, in_emp_new_record.ename,
               in_emp_new_record.sal, in_emp_new_record.comm, in_emp_new_record.deptno;
         commit;
       end;

       --统计工资信息
       function fun_sum(
         num_a number,
         num_b number
       ) return number
       is
       begin
         return num_a + num_b;
       end fun_sum;
end pkg_emp;


--测试1
declare
       out_name emp2.ename%type;
       out_sal emp2.sal%type;
begin
     pkg_emp.pro_findInfo(7369, out_name, out_sal);
     dbms_output.put_line(out_name);
     dbms_output.put_line(out_sal);
end;

--测试2
select * from emp2;
declare
       in_emp_record emp2%rowtype;
       flag boolean;
begin
     in_emp_record.deptno := &部门编号;
     in_emp_record.sal := &员工工资;
     pkg_emp.pro_editInfo(in_emp_record, flag);
     if (flag = false) then
       dbms_output.put_line('no');
     else
       dbms_output.put_line('yes');
     end if;
end;

--测试3
declare
       new_emp_record emp2%rowtype;
begin
     new_emp_record.empno := &员工编号;
     new_emp_record.ename := &姓名;
     new_emp_record.sal := &工资;
     new_emp_record.comm := &奖金;
     new_emp_record.deptno := &部门编号;
     pkg_emp.pro_addInfo(new_emp_record);
end;

--测试4
declare
     sum_emp number;
begin
     select pkg_emp.fun_sum(sal, nvl(comm, 0)) into sum_emp from emp2
     where empno = &员工编号;
     dbms_output.put_line('员工总工资:' || sum_emp);
end;