包

包

创建包规范

create or replace package emp_package is

 

  --   Author  : CHOWMIN

  -- Created   : 2012/4/28 17:22:11

  -- Purpose   :

 

  -- Public   type declarations

  type   t_ref_cursor is ref cursor;

 

  -- Public   constant declarations

  --<ConstantName>   constant <Datatype> := <Value>;

 

  -- Public   variable declarations

  --<VariableName>   <Datatype>;

 

  -- Public   function and procedure declarations

  function   get_emp_ref_cursor return t_ref_cursor;

 

  procedure   p_update_emp(p_empno in emp.empno%type, p_ename varchar2);

 

end emp_package;

创建包体

create or replace package body emp_package is

 

  -- Private   type declarations

  --type   <TypeName> is <Datatype>;

 

  -- Private   constant declarations

  --<ConstantName>   constant <Datatype> := <Value>;

 

  -- Private   variable declarations

  --<VariableName>   <Datatype>;

 

  --   Function and procedure implementations

  function   get_emp_ref_cursor return t_ref_cursor is

   

      t_emp_ref_cursor t_ref_cursor;

   

  begin

   

    open   t_emp_ref_cursor for select * from emp;

   

    return   t_emp_ref_cursor;

   

  end   get_emp_ref_cursor;

 

  procedure p_update_emp(p_empno   in emp.empno%type, p_ename varchar2) is

    v_count Integer;

   

  begin

    select count(*) into v_count from emp where empno =   p_empno;

   

    if v_count =   1 then

      update emp set ename =   p_ename where empno = p_empno;

      commit;

    end if;

    exception

      when others then

      rollback;

  end   p_update_emp;

 

end emp_package;

调用包中的函数和过程

select emp_package.get_emp_ref_cursor from dual

 

call emp_package.p_update_emp(7311,'周路敏');

查询包信息

select object_name,procedure_name from user_procedures where object_name='EMP_PACKAGE'