oracle中的包头(Package)与包体(Package body)

包头(Package)与包体(Package body)的应用

1.将有联系的对象打成包,方便使用
2.包中对象包括储存过程,函数,游标,自定义类型和变量,可以在PL_SQL块中应用这些对象.

定义包头:
----------------------------------------------------------------------------------------------
create or replace package <Package_name> is
  type <TypeName> is <Datatype>;--定义类型
  -- Public constant declarations
  <ConstantName> constant <Datatype> := <Value>;--声明常量
  -- Public variable declarations
  <VariableName> <Datatype>;  --数据类型
  -- Public function and procedure declarations
  function <FunctionName>(<Parameter> <Datatype>) return <Datatype>; --函数
end <Package_name>;

定义包体:
----------------------------------------------------------------------------------------------
create or replace package body <Package_name> 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 <FunctionName>(<Parameter> <Datatype>) return <Datatype> is --函数的具体内容
    <LocalVariable> <Datatype>;
  begin
    <Statement>;
    return(<Result>);
  end;
begin
  -- Initialization--初始化包体,每次调用时被初始化
  <Statement>;
end <Package_name>;

只有当包头编辑成功后才能编辑包体.其中的函数名与过程名须和包头中的函数过程一样.

学习包应用的一个例子:  
包的作用: 根据出生年月返回年龄function Getage,返回工资function Getsalary

--创建环境
Create Table T_PsnSalary  --工资表
(
Fpsncode varchar(4) default '',  --个人代码
Fpsndesc varchar(20) default '',  --描述
FpsnBirth varchar(20) default '', --生日
FpsnSalary number(8,2)            --工资
);

--添加数据
Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C001','张三','1986.01.10',1100);
Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C002','李四','1980.10.10',3000);
Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C003','王五','1996.12.10',800);
commit;

--创建包头
create or replace package A_GetData is
  function Getage(birthst varchar,birthend varchar) return integer;
  function Getsalary(VFpsncode varchar) return number;
end A_Getdata;

--创建包体
create or replace package body A_GETDATA is
  function Getage(birthst varchar,birthend varchar) return integer --得到年龄函数
  is
     V_birth integer;
     ToDateEnd Date;
     Toyear number(4);
     Tomonth number(4);
     Fromyear number(4);
     Frommonth number(4);
  begin
    if (birthend='') or (birthend is null) then
    select sysdate into ToDateEnd from dual; --得到系统时间
    end if;
    Toyear := to_number(to_char(ToDateEnd,'YYYY')); --得到最后年月
    Tomonth := to_number(to_char(ToDateEnd,'MM'));
    Fromyear := to_number(substr(birthst,1,4));--计算的年月
    Frommonth := to_number(substr(birthst,6,2));
    if Tomonth-Frommonth>0 then V_birth:=Toyear-fromyear;
    else V_birth:=Toyear-fromyear-1;
    end if;
    return(V_birth);
  end Getage;

  function getSalary(VFpsncode varchar) return number--返回工资情况
  is
    V_psnSalary number(8,2);
  begin
    Select FpsnSalary into V_psnSalary from T_PsnSalary  where Fpsncode=VFpsncode;
    return(V_psnSalary);
  end getSalary;
 
end A_GETDATA;

--测试
select a.*,A_getdata.Getage(Fpsnbirth,'')age from T_psnsalary a;  --调用包得到年龄功能
select A_getdata.Getsalary('C001') from dual;                     --代码得到工资

 

这在制作报表会用到,比如:

cts_base_funtion.F_get_Approve_Advice(t.Workflow_Id, '厂领导', 1) PRO_DEP_COMMENTS_spz

/*
  F_get_Approve_Advice '1'取得审批人，'2'取得审批意见，'3'取得审批时间, '4'取审批人电子签名图片
  */
  function F_get_Approve_Advice(v_item_key varchar2,
                                pnode_Name varchar2,
                                pType      number) return varchar is
    pResult         varchar2(200) := '';
    num             number;
    pApprove_status varchar2(200);
  begin
    if pType = 1 then
      select count(*)
        into num
        from OA_WORKFLOW_COMMENTS OWC, OA_WORKFLOW OW, co_rpt_report cr
       where OW.NODE_NAME = pnode_Name
         and OWC.Upid = OW.Id
         and ow.version = cr.last_version
         and OW.ITEM_KEY = cr.mainform_id
         and cr.mainform_id = v_item_key;
      if num = 1 then
        select OWC.EXECUTOR
          into pResult
          from OA_WORKFLOW_COMMENTS OWC, OA_WORKFLOW OW, co_rpt_report cr
         where OW.NODE_NAME = pnode_Name
           and OWC.Upid = OW.Id
           and ow.version = cr.last_version
           and OW.ITEM_KEY = cr.mainform_id
           and cr.mainform_id = v_item_key;
      end if;
    end if;
    if pType = 2 then
      select count(*)
        into num
        from OA_WORKFLOW_COMMENTS OWC, OA_WORKFLOW OW, co_rpt_report cr
       where OW.NODE_NAME = pnode_Name
         and OWC.Upid = OW.Id
         and ow.version = cr.last_version
         and OW.ITEM_KEY = cr.mainform_id
         and cr.mainform_id = v_item_key;
      if num = 1 then
        select OWC.COMMENTS
          into pResult
          from OA_WORKFLOW_COMMENTS OWC, OA_WORKFLOW OW, co_rpt_report cr
         where OW.NODE_NAME = pnode_Name
           and OWC.Upid = OW.Id
           and ow.version = cr.last_version
           and OW.ITEM_KEY = cr.mainform_id
           and cr.mainform_id = v_item_key;
        if pResult is null then
          select OWC.action
            into pResult
            from OA_WORKFLOW_COMMENTS OWC, OA_WORKFLOW OW, co_rpt_report cr
           where OW.NODE_NAME = pnode_Name
             and OWC.Upid = OW.Id
             and ow.version = cr.last_version
             and OW.ITEM_KEY = cr.mainform_id
             and cr.mainform_id = v_item_key;
          if pResult = 'ACCEPT' then
            pResult := '审批通过';
          end if;
          if pResult = 'REJECT' then
            pResult := '审批驳回';
          end if;
        end if;
      end if;
    end if;
    if pType = 4 then
      select count(*)
        into num
        from OA_WORKFLOW_COMMENTS OWC, OA_WORKFLOW OW, co_rpt_report cr
       where OW.NODE_NAME = pnode_Name
         and OWC.Upid = OW.Id
         and (OWC.Action = 'ACCEPT' or OWC.Action = 'REJECT')
         and ow.version = cr.last_version
         and OW.ITEM_KEY = cr.mainform_id
         and cr.mainform_id = v_item_key;
      if num = 1 then
        select OWC.EXECUTOR
          into pResult
          from OA_WORKFLOW_COMMENTS OWC, OA_WORKFLOW OW, co_rpt_report cr
         where OW.NODE_NAME = pnode_Name
           and OWC.Upid = OW.Id
           and ow.version = cr.last_version
           and OW.ITEM_KEY = cr.mainform_id
           and cr.mainform_id = v_item_key;
      end if;
      if pResult is not null then
        pResult := '<img src="../reportFiles/images/' || pResult ||
                   '.JPG">';
      end if;
      return pResult;
    end if;
    return pResult;
  end;