oracle包的使用...

一、包规范

    1、report包

CREATE OR REPLACE package report as
    type cursorType is ref cursor;
        type CommInfo is record(
                id_ varchar2(60),
                name_ varchar2(200),
                comm_category_name_ varchar2(60),
                city_company_name_ varchar2(200),
                district_name_ varchar2(100),
                county_company_name_ varchar2(200),
                contact_ varchar2(60),
                mobile_phone_ varchar2(60),
                person_id_ varchar2(60),
                bank_num_ varchar2(60)
        );
        type CardType is record(
                id_ varchar2(60),
                type_ varchar2(60),
                card_brand_ varchar2(60),
                card_brand_name_ varchar2(60)
        );
        type QuantityBean is record(
                commId number,
                pkl number := 0,
                jhl number := 0,
                jfl number := 0,
                zsl number := 0
        );
        function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2;
end; 

   2、reportFunc包

CREATE OR REPLACE package reportFunc as
        function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2;
        function getCardTypeSQL return varchar2;
        function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean;
end;

二、包体

    1、report包体

CREATE OR REPLACE package body report as 
    --生成数据集
 function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2 is
        c1 cursorType;
        c2 cursorType;
        v_CommInfo CommInfo;
        v_CardType CardType;
  pkl_ number := 0; -- 值 
  jhl_ number := 0;
  jfl_ number := 0;
  zsl_ number := 0;
  jhRate number := 0;
  jfRate number := 0;
  v_pk_total_ number := 0; -- 值合计 
  v_jh_total_ number := 0;
  v_jf_total_ number := 0;
  v_zs_total_ number := 0;
  v_jhl_total_ number := 0;
  v_jfl_total_ number := 0;
  pk_sum number := 0; -- 值合计的合计  
  jh_sum number := 0;
  jf_sum number := 0;
  zs_sum number := 0;
  jhl_sum number := 0;
  jfl_sum number := 0;
  
  bean QuantityBean;
  result varchar2(200);
  lsh number := 0;
  createDate date := sysdate;
    begin
     delete from R_PIKA_STAT_ where create_date_<createDate-1;
     commit;
   
  select SEQ_LSH.nextval into lsh from dual;
  
        --代办点 
        open c1 for reportFunc.getCommSQL(p_fgs, p_fwt, p_comm);
        loop
   v_pk_total_ := 0;
   v_jh_total_ := 0;
   v_jf_total_ := 0;
   v_zs_total_ := 0;
   v_jhl_total_ := 0;
   v_jfl_total_ := 0;
    
            fetch c1 into v_CommInfo;
            exit when c1%notfound;
   
            --卡类型 
            open c2 for reportFunc.getCardTypeSQL;
            loop
                fetch c2 into v_CardType;
                exit when c2%notfound;
    
    bean := reportFunc.getQuantityBean(v_CommInfo.id_, v_CardType.id_, p_date1, p_date2, p_status);
    
    --批卡量
    pkl_ := bean.pkl;
    v_pk_total_ := v_pk_total_ + pkl_;
     
    insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
    county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
    card_brand_,card_brand_name_,data_type_,amount_,create_date_) 
    values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
    v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
    v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
    v_CardType.card_brand_,v_CardType.card_brand_name_,'1.批卡量(套数)', pkl_, createDate);
    
    --激活量
    jhl_ := bean.jhl;
    v_jh_total_ := v_jh_total_ + jhl_;
     
    insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
    county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
    card_brand_,card_brand_name_,data_type_,amount_,create_date_) 
    values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
    v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
    v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
    v_CardType.card_brand_,v_CardType.card_brand_name_,'2.激活量(套数)', jhl_, createDate);
    
    --成功积分登记量
    jfl_ := bean.jfl;
    v_jf_total_ := v_jf_total_ + jfl_;
     
    insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
    county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
    card_brand_,card_brand_name_,data_type_,amount_,create_date_) 
    values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
    v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
    v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
    v_CardType.card_brand_,v_CardType.card_brand_name_,'3.成功积分登记量(套数)', jfl_, createDate);
    
    --激活率
    jhRate := 0;
    if pkl_ > 0 then
     jhRate := (1.0 * jhl_) / pkl_;
    else
     if jhl_ > 0 then
      jhRate := 1.0;
     end if;
    end if;
     
    insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
    county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
    card_brand_,card_brand_name_,data_type_,amount_,create_date_) 
    values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
    v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
    v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
    v_CardType.card_brand_,v_CardType.card_brand_name_,'4.激活率(%)', jhRate, createDate);
     
    --积分率 = 已积分数量 / (已激活数量-赠送号激活量) 
    zsl_ := bean.zsl;
    v_zs_total_ := v_zs_total_ + zsl_;
     
    jfRate := 0;
    if (jhl_ - zsl_) > 0 then
     jfRate := (1.0 * jfl_) / (jhl_ - zsl_);
    else
     if jfl_ > 0 then
      jfRate := 1.0;
        end if;
    end if;
     
    insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
    county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
    card_brand_,card_brand_name_,data_type_,amount_,create_date_) 
    values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
    v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
    v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
    v_CardType.card_brand_,v_CardType.card_brand_name_,'5.积分率(%)', jfRate, createDate);
     
            end loop;
            close c2;
    
   --去掉值为0的代办点
   if v_pk_total_<=0 and v_jh_total_<=0 and v_jf_total_<=0 then
       delete from R_PIKA_STAT_ where comm_id_=v_CommInfo.id_;
   else
       --批卡合计
    update R_PIKA_STAT_ set pk_total_=v_pk_total_ where comm_id_=v_CommInfo.id_;
    pk_sum := pk_sum + v_pk_total_;
     
    --激活合计
    update R_PIKA_STAT_ set jh_total_=v_jh_total_ where comm_id_=v_CommInfo.id_;
    jh_sum := jh_sum + v_jh_total_;
    
    --积分量合计
    update R_PIKA_STAT_ set jf_total_=v_jf_total_ where comm_id_=v_CommInfo.id_;
    jf_sum := jf_sum + v_jf_total_;
    
    --激活率合计
    v_jhl_total_ := 0;
    if v_pk_total_ > 0 then
     v_jhl_total_ := (1.0 * v_jh_total_) / v_pk_total_;
    else
     if v_jh_total_ > 0 then
      v_jhl_total_ := 1.0;
        end if;
    end if;
    update R_PIKA_STAT_ set jhl_total_=v_jhl_total_ where comm_id_=v_CommInfo.id_;
    
    --积分率合计
    v_jfl_total_ := 0;
    if (v_jh_total_ - v_zs_total_) > 0 then
     v_jfl_total_ := (1.0 * v_jf_total_) / (v_jh_total_ - v_zs_total_);
    else
     if v_jf_total_ > 0 then
      v_jfl_total_ := 1.0;
     end if;
    end if;
    update R_PIKA_STAT_ set jfl_toal_=v_jfl_total_ where comm_id_=v_CommInfo.id_;
    zs_sum := zs_sum + v_zs_total_;
    
   end if;
   
        end loop;
        close c1;
        
  --合计的合计 
  if pk_sum > 0 then
      jhl_sum := (1.0 * jh_sum) / pk_sum;
  else
      if jh_sum > 0 then
        jhl_sum := 1.0;
   end if;
  end if;
  
  if (jh_sum - zs_sum) > 0 then
      jfl_sum := (1.0 * jf_sum) / (jh_sum - zs_sum);
  else
      if jf_sum > 0 then
       jfl_sum := 1.0;
   end if;
  end if;
  
  result := lsh || ',' || pk_sum || ',' || jh_sum || ',' || jf_sum || ',' || jhl_sum || ',' || jfl_sum;
  
  commit;
  
  return result;
   end;
    
 
end;

    2、reportFunc包体