Oracle笔记 十二、PLSQL面向对象之package.docVIP

--将方法和过程用包定义 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_m, in_emp_new_record.deptno; commit; end; --