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;