为了把某一个模块的函数、存储过程等方便查询维护，可以把它们打到一个包里。下面给出一个简单的小例子。

1、创建包头

create or replace package chen_pack

is

    function f_c_getstaffNum(in_status in varchar2) return number;

    procedure p_c_sendmsg(receiver in varchar2,content in varchar2);

end;

2、创建包体

create or replace package body chen_pack

as

    function f_c_getstaffNum(in_status in varchar2)

     return number

     as

         outnum number;

     begin

         select count(1) into outnum from icdpub.t_ucp_staffbasicinfo g where g.staffidstatus = in_status;

     return outnum;

    end f_c_getstaffNum;

    procedure p_c_sendmsg(receiver in varchar2,content in varchar2)

      as

      begin

        insert into t_c_msg(sender,receiver,content,sendtime) values('10658666',receiver,content,sysdate);

        commit;

    end p_c_sendmsg;

end chen_pack;

3.调用

可以在存储过程中调用

declare

  v_staffnum number;

begin

  v_staffnum :=chen_pack.f_c_getstaffNum('01');

  dbms_output.put_line(v_staffnum);

  chen_pack.p_c_sendmsg('138','测试短信'||sysdate);

end;

也可以直接在sql中调用函数

select chen_pack.f_c_getstaffNum('01') from dual;

详细的可以参考这位同仁的文章：

http://www.cnblogs.com/lovemoon714/archive/2012/02/29/2373695.html