关于procedure+trigger+package+package body的综合应用

关于procedure+trigger+package+package body的综合应用

定义存储过程－procedure:
CREATE OR REPLACE PROCEDURE
P_FaceOf_t_ShenPiGuoCheng
( p_YWLSH     varchar2,
p_SJBBH     number,
p_SPBZH     number,
p_SPSXBH     varchar2,
p_SPSXZXBH     varchar2,
p_YXTYWLSH     varchar2,
p_SPHJDM     varchar2,
p_SPHJMC     varchar2,
p_SPRXM     varchar2,
p_SPRZWDM     varchar2,
p_SPRZWMC     varchar2,
p_SPYJ     varchar2,
p_SPSJ     date,
p_BZ     varchar2,
p_BYZDA     varchar2,
p_BYZDB     varchar2,
p_BYZDC     varchar2,
p_BYZDD     date
 ,p_return OUT VARCHAR2)
 /************************************************************
           用途：数据交换输入接口
************************************************************
 若字段 (YWLSH) 为空(NULL)或空字符串('')，则返回整数型数值 [-1]
 若字段 (SJBBH) 为空，则返回整数型数值  [-2]
 若字段 (SPBZH) 为空，则返回整数型数值  [-3]
 若字段 (SPSXBH) 为空(NULL)或空字符串('')，则返回整数型数值 [-4]
 若字段 (SPSXZXBH) 为空(NULL)或空字符串('')，则返回整数型数值 [-5]
 若字段 (YXTYWLSH) 为空(NULL)或空字符串('')，则返回整数型数值 [-6]
 若字段 (SPHJDM) 为空(NULL)或空字符串('')，则返回整数型数值 [-7]
 若字段 (SPHJMC) 为空(NULL)或空字符串('')，则返回整数型数值 [-8]
 若字段 (SPRXM) 为空(NULL)或空字符串('')，则返回整数型数值 [-9]
 若字段 (SPRZWDM) 为空(NULL)或空字符串('')，则返回整数型数值 [-10]
 若字段 (SPRZWMC) 为空(NULL)或空字符串('')，则返回整数型数值 [-11]
 若字段 (SPYJ) 为空(NULL)或空字符串('')，则返回整数型数值 [-12]
 若字段 (SPSJ) 为空，则返回整数型数值  [-13]
 返回正值请参照系统错误表。
 返回值为 0 成功执行。
************************************************************/
AS
BEGIN
p_return:=0;
if  p_YWLSH is null   then p_return :=-1;  END IF;
if p_SJBBH is null then p_return :=-2;  END IF;
if p_SPBZH is null then p_return :=-3;  END IF;
if p_SPSXBH is null then p_return := -4;  END IF;
--if p_SPSXZXBH is null then p_return := -5;  END IF;
if p_YXTYWLSH is null then p_return := -6;  END IF;
if p_SPHJDM is null then p_return := -7;  END IF;
if p_SPHJMC is null  then p_return := -8;  END IF;
if p_SPRXM is null then p_return := -9;  END IF;
if p_SPRZWDM is null then p_return := -10;  END IF;
if p_SPRZWMC is null then p_return := -11;  END IF;
if p_SPYJ is null then p_return := -12;  END IF;
if p_SPSJ is null then p_return := -13;  END IF;

insert into t_ShenPiGuoCheng (YWLSH,SJBBH,SPBZH,SPSXBH,SPSXZXBH,YXTYWLSH,SPHJDM,SPHJMC,SPRXM,SPRZWDM,SPRZWMC,SPYJ,SPSJ,BZ,BYZDA,BYZDB,BYZDC,BYZDD)values(p_YWLSH,p_SJBBH,p_SPBZH,p_SPSXBH,p_SPSXZXBH,p_YXTYWLSH,p_SPHJDM,p_SPHJMC,p_SPRXM,p_SPRZWDM,p_SPRZWMC,p_SPYJ,p_SPSJ,p_BZ,S_SHENPIGUOCHENG_BYZDA.Nextval,p_BYZDB,p_BYZDC,p_BYZDD)
;
exception
  when others then
         p_return :=sqlcode;
 
  END P_FaceOf_t_ShenPiGuoCheng;

定义触发器－trigger:
create or replace trigger completebackup_shenpi
       after insert  on t_sdjsj_censordiscusion
 
       referencing  new as new_value
       for each row
declare
 v_result varchar2(100):='';
  var_back_status varchar2(2) :='';
begin
   var_back_status := nvl(ces#sdjsj.completebackup_to_shenpi.getBackStatus(:new_value.item_id),'0');
   if  (:new_value.node_id='1460' and :new_value.censor_dis='同意'
   --退回标志：不为1表示没有被退回过，产生一条承办数据；否则退回过了不产生承办数据
   and var_back_status<>'1'
   ) then
     ces#sdjsj.completebackup_to_shenpi.insertshenpi1(
      :new_value.node_id,
      :new_value.item_id,
      :new_value.censor_dis,
      :new_value.create_user,
      :new_value.censor_date,
      v_result);
    end if;
   if (:new_value.node_id='1461' and :new_value.censor_dis='同意'
  --退回标志：不为1表示没有被退回过，产生审核，批准数据；否则退回过了不产生审核，批准数据
   and var_back_status<>'1'
   ) then
    ces#sdjsj.completebackup_to_shenpi.insertshenpi2(
      :new_value.node_id,
      :new_value.item_id,
      :new_value.censor_dis,
      :new_value.create_user,
      :new_value.censor_date,
      v_result);
  end if;
  --退回的情况
  if (:new_value.node_id='1460' and :new_value.censor_con='退回窗口') then
      ces#sdjsj.completebackup_to_shenpi.insertshenpi4tuihui(
      :new_value.node_id,
      :new_value.item_id,
      :new_value.censor_con,
      :new_value.create_user,
      :new_value.censor_date,
      v_result);
  end if;
end;

定义函数包－package:
create or replace package completebackup_to_shenpi
 as
       procedure insertshenpi1(p_node_id in varchar2,
                         p_item_id in varchar2,
                         p_censor_dis in varchar2,
                         p_create_user in varchar2,
                         p_censor_date in date,
                         p_result out varchar2) ;
        procedure insertshenpi2(p_node_id in varchar2,
                         p_item_id in varchar2,
                         p_censor_dis in varchar2,
                         p_create_user in varchar2,
                         p_censor_date in date,
                         p_result out varchar2) ;
        procedure insertshenpi3(p_node_id in varchar2,
                         p_item_id in varchar2,
                         p_censor_dis in varchar2,
                         p_create_user in varchar2,
                         p_censor_date in date,
                         p_result out varchar2);
       --退回的情况
       procedure insertshenpi4tuihui(p_node_id in varchar2,
                         p_item_id in varchar2,
                         p_censor_con in varchar2,
                         p_create_user in varchar2,
                         p_censor_date in date,
                         p_result out varchar2);                 
        function getUserName(p_userid in varchar2) return varchar2;
 function getShouLiId(p_construct_id in varchar2) return varchar2;
       --取得是否退回的状态位
       function getBackStatus(p_item_id in varchar2) return varchar2;
end completebackup_to_shenpi;

实现函数包体－package body:
create or replace package body completebackup_to_shenpi
 IS
   ---根据userID获取承办人姓名
 function getUserName(p_userid in varchar2) return varchar2
  IS
  r_returnName  varchar2(30); 
 begin
      select  t.user_name into r_returnName  from ces#system.t_sys_user  t
             where  t.user_id=p_userid;       
  return r_returnName;
 end;
-----根据审批的流水号ID取受理表的流水号ID
  function getShouLiId(p_construct_id in varchar2) return varchar2
  IS
       r_returnId  VARCHAR2(30);
  begin
      select tck.stream_id into r_returnId  from t_Ck_Datumreceive_Info tck where tck.memo=p_construct_id;
      return r_returnId;
  end;
  -----根据竣工验收备案流水号ID得本条竣工验收备案是否被退回过
  function getBackStatus(p_item_id in varchar2) return varchar2
  IS
       r_backStatus  VARCHAR2(30);
  begin
      select back_or_not into r_backStatus from t_complete_backup where complete_backup_id=p_item_id;
      return r_backStatus;
  end;
 ---插入审批承办信息
 procedure insertshenpi1(p_node_id in varchar2,
                         p_item_id in varchar2,
                         p_censor_dis in varchar2,
                         p_create_user in varchar2,
                         p_censor_date in date,
                         p_result out varchar2)
 IS
               
          V_YWLSH   varchar2(80):='';
         V_SJBBH   number(4, 0):=1;
          V_SPBZH   NUMBER(5,0):=1;
         V_SPSXBH  varchar2(18):='SD15B110';
         V_SPSXZXBH   varchar2(3):='000';
          v_YXTYWLSH    VARCHAR2(100):='';
         V_SPHJDM  varchar2(4):='0001';
         V_SPHJMC   varchar2(50):='承办';
         V_SPRXM   varchar2(60):='';
          V_SPRZWDM  VARCHAR2(5):='0002';
         V_SPRZWMC   VARCHAR2(50):='科员';
         V_SPYJ  varchar2(2000):='承办';
         V_SPSJ  DATE:= p_censor_date;
         V_BZ   varchar2(200):='';
         V_BYZDA   varchar2(100):='';
         V_BYZDB   varchar2(100):='I' ;
         V_BYZDC   varchar2(500):='';
         V_BYZDD   date ;
   begin
  
                V_SPRXM:=getUserName(p_create_user);
                  v_YXTYWLSH:=getShouLiId(p_item_id);
                V_YWLSH:=ces#sdjsj.createywlsh(V_SPSXBH,V_SPSXZXBH,V_YXTYWLSH);
                ces#sdjsj.p_faceof_t_shenpiguocheng(V_YWLSH ,V_SJBBH,V_SPBZH,V_SPSXBH,
                              V_SPSXZXBH,V_YXTYWLSH,V_SPHJDM,V_SPHJMC,V_SPRXM,V_SPRZWDM,V_SPRZWMC,V_SPYJ
                              ,V_SPSJ,V_BZ,V_BYZDA,V_BYZDB,V_BYZDC,V_BYZDD,p_result);
         
 end;
  ---插入审批审核和审批信息
 procedure insertshenpi2(p_node_id in varchar2,
                         p_item_id in varchar2,
                         p_censor_dis in varchar2,
                         p_create_user in varchar2,
                         p_censor_date in date,
                         p_result out varchar2)
 IS
               
          V_YWLSH   varchar2(80):='';
         V_SJBBH   number(4, 0):=1;
          V_SPBZH   NUMBER(5,0):=2;
         V_SPSXBH  varchar2(18):='SD15B110';
         V_SPSXZXBH   varchar2(3):='000';
          v_YXTYWLSH    VARCHAR2(100):='';
         V_SPHJDM  varchar2(4):='0002';
         V_SPHJMC   varchar2(50):='审核';
         V_SPRXM   varchar2(60):='';
          V_SPRZWDM  VARCHAR2(5):='0005';
         V_SPRZWMC   VARCHAR2(50):='科长';
         V_SPYJ  varchar2(2000):=p_censor_dis;
         V_SPSJ  DATE:= p_censor_date;
         V_BZ   varchar2(200):='';
         V_BYZDA   varchar2(100):='';
         V_BYZDB   varchar2(100):='I' ;
         V_BYZDC   varchar2(500):='';
         V_BYZDD   date ;
        
   begin
  
                V_SPRXM:=getUserName(p_create_user);
                v_YXTYWLSH:=getShouLiId(p_item_id);
                V_YWLSH:=ces#sdjsj.createywlsh(V_SPSXBH,V_SPSXZXBH,V_YXTYWLSH);
  
        
                ces#sdjsj.p_faceof_t_shenpiguocheng(V_YWLSH ,V_SJBBH,V_SPBZH,V_SPSXBH,
                              V_SPSXZXBH,V_YXTYWLSH,V_SPHJDM,V_SPHJMC,V_SPRXM,V_SPRZWDM,V_SPRZWMC,V_SPYJ
                              ,V_SPSJ,V_BZ,V_BYZDA,V_BYZDB,V_BYZDC,V_BYZDD,p_result);

              insertshenpi3(p_node_id ,
                         p_item_id,
                         p_censor_dis ,
                         p_create_user ,
                         p_censor_date ,
                         p_result ) ;
      
        
         
 end;

 procedure insertshenpi3(p_node_id in varchar2,
                         p_item_id in varchar2,
                         p_censor_dis in varchar2,
                         p_create_user in varchar2,
                         p_censor_date in date,
                         p_result out varchar2)
 IS
               
          V_YWLSH   varchar2(80):='';
         V_SJBBH   number(4, 0):=1;
          V_SPBZH   NUMBER(5,0):=3;
         V_SPSXBH  varchar2(18):='SD15B110';
         V_SPSXZXBH   varchar2(3):='000';
          v_YXTYWLSH    VARCHAR2(100):='';
         V_SPHJDM  varchar2(4):='0003';
         V_SPHJMC   varchar2(50):='批准';
         V_SPRXM   varchar2(60):='';
          V_SPRZWDM  VARCHAR2(5):='0005';
         V_SPRZWMC   VARCHAR2(50):='科长';
         V_SPYJ  varchar2(2000):=p_censor_dis;
         V_SPSJ  DATE:= p_censor_date;
         V_BZ   varchar2(200):='';
         V_BYZDA   varchar2(100):='';
         V_BYZDB   varchar2(100):='I' ;
         V_BYZDC   varchar2(500):='';
         V_BYZDD   date ;
   begin
               
                V_SPRXM:=getUserName(p_create_user);
                v_YXTYWLSH:=getShouLiId(p_item_id);
                V_YWLSH:=ces#sdjsj.createywlsh(V_SPSXBH,V_SPSXZXBH,V_YXTYWLSH);
                ces#sdjsj.p_faceof_t_shenpiguocheng(V_YWLSH ,V_SJBBH,V_SPBZH,V_SPSXBH,
                              V_SPSXZXBH,V_YXTYWLSH,V_SPHJDM,V_SPHJMC,V_SPRXM,V_SPRZWDM,V_SPRZWMC,V_SPYJ
                              ,V_SPSJ,V_BZ,V_BYZDA,V_BYZDB,V_BYZDC,V_BYZDD,p_result);
         
 end;
  --退回的情况
 procedure insertshenpi4tuihui(p_node_id in varchar2,
                         p_item_id in varchar2,
                         p_censor_con in varchar2,
                         p_create_user in varchar2,
                         p_censor_date in date,
                         p_result out varchar2)
 IS
               
          V_YWLSH   varchar2(80):='';
         V_SJBBH   number(4, 0):=1;
          V_SPBZH   NUMBER(5,0):=1;
         V_SPSXBH  varchar2(18):='SD15B110';
         V_SPSXZXBH   varchar2(3):='000';
          v_YXTYWLSH    VARCHAR2(100):='';
         V_SPHJDM  varchar2(4):='0001';
         V_SPHJMC   varchar2(50):='承办';
         V_SPRXM   varchar2(60):='';
          V_SPRZWDM  VARCHAR2(5):='0002';
         V_SPRZWMC   VARCHAR2(50):='科员';
         V_SPYJ  varchar2(2000):=p_censor_con;
         V_SPSJ  DATE:= p_censor_date;
         V_BZ   varchar2(200):='';
         V_BYZDA   varchar2(100):='';
         V_BYZDB   varchar2(100):='I' ;
         V_BYZDC   varchar2(500):='';
         V_BYZDD   date ;
   begin
  
                V_SPRXM:=getUserName(p_create_user);
                  v_YXTYWLSH:=getShouLiId(p_item_id);
                V_YWLSH:=ces#sdjsj.createywlsh(V_SPSXBH,V_SPSXZXBH,V_YXTYWLSH);
                ces#sdjsj.p_faceof_t_shenpiguocheng(V_YWLSH ,V_SJBBH,V_SPBZH,V_SPSXBH,
                              V_SPSXZXBH,V_YXTYWLSH,V_SPHJDM,V_SPHJMC,V_SPRXM,V_SPRZWDM,V_SPRZWMC,V_SPYJ
                              ,V_SPSJ,V_BZ,V_BYZDA,V_BYZDB,V_BYZDC,V_BYZDD,p_result);
         
 end;
end completebackup_to_shenpi;