oracle数据库包package小例子

来源：互联网发布：vb if else语句编辑：程序博客网时间：2024/05/02 02:38

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

1、创建包头

create or replace package chen_packis    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_packas    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

1 0