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包体

CREATE OR REPLACE package body reportFunc as 
    --返回获取代办点信息的SQL 
 function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2 is
     sql_1 varchar2(2000);
 begin
     sql_1 := 'select a.id_,a.name_,e.name_ comm_category_name_,b.name_ city_company_name_,d.name_ district_name_,c.name_ county_company_name_,';
  sql_1 := sql_1 || 'case when (select name_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.contact_ else (select name_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end contact_,';
  sql_1 := sql_1 || 'case when (select tel_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.mobile_phone_ else (select tel_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end mobile_phone_,';
  sql_1 := sql_1 || 'case when (select person_id_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.person_id_ else (select person_id_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end person_id_,';
  sql_1 := sql_1 || 'case when (select bank_num_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.bank_num_ else (select bank_num_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end bank_num_';
  sql_1 := sql_1 || ' from commission_info_ a';
  sql_1 := sql_1 || ' left join organization_ b on a.city_company_id_=b.party_id_';
  sql_1 := sql_1 || ' left join organization_ c on a.county_company_id_=c.party_id_';
  sql_1 := sql_1 || ' left join district_ d on a.district_=d.id_';
  sql_1 := sql_1 || ' left join commission_category_ e on a.comm_cagegory_=e.id_';
  sql_1 := sql_1 || ' where a.id_ is not null';
  if p_fgs is not null then
     sql_1 := sql_1 || ' and a.city_company_id_=''' || p_fgs || '''';
  end if;
  if p_fwt is not null then
     sql_1 := sql_1 || ' and a.county_company_id_=''' || p_fwt || '''';
  end if;
  if p_comm is not null then
     sql_1 := sql_1 || ' and a.id_ in(' || p_comm || ')';
  end if;
  sql_1 := sql_1 || ' order by a.id_ asc';
  
  return sql_1;
 end;
 
 --返回获取卡类型信息的SQL 
 function getCardTypeSQL return varchar2 is
     sql_2 varchar2(1000);
 begin
     sql_2 := 'select a.id_,a.type_,a.card_brand_,b.description_ card_brand_name_';
  sql_2 := sql_2 || ' from sim_card_type_ a inner join enumeration_ b';
  sql_2 := sql_2 || ' on a.card_brand_=b.enum_id_ order by a.id_ asc';
  return sql_2;
 end;
 
 -- 数量Bean 
 function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean is
     s varchar2(2000);
  bean report.QuantityBean;
     c1 report.cursorType;
 begin
     s := 'select a.id_,';
  
  s := s || '(select count(id_) from sim_card_detail_ where commission_id_=a.id_ and status_ in(' || p_status || ') and card_type_id_=' || p_type;
  if p_date1 is not null then
       s := s || ' and status_date_>=' || p_date1;
  end if;
  if p_date2 is not null then
       s := s || ' and status_date_<' || p_date2;
  end if;
  s := s || ') pkl,';
  
  s := s || '(select count(id_) from sim_card_detail_ where commission_id_=a.id_ and card_type_id_=' || p_type;
  if p_date1 is not null then
       s := s || ' and active_date_>=' || p_date1;
  end if;
  if p_date2 is not null then
       s := s || ' and active_date_<' || p_date2;
  end if;
  s := s || ') jhl,';
  
  s := s || '(select count(id_) from sim_card_detail_ where is_integral_=1 and commission_id_=a.id_ and card_type_id_=' || p_type;
  if p_date1 is not null then
       s := s || ' and active_date_>=' || p_date1;
  end if;
  if p_date2 is not null then
       s := s || ' and active_date_<' || p_date2;
  end if;
  s := s || ') jfl,';
  
  s := s || '(select count(id_) from sim_card_detail_ where status_=''RESOURCE_STATUS_13'' and commission_id_=a.id_ and card_type_id_=' || p_type;
  if p_date1 is not null then
       s := s || ' and active_date_>=' || p_date1;
  end if;
  if p_date2 is not null then
       s := s || ' and active_date_<' || p_date2;
  end if;
  s := s || ') zsl';
  
  
  s := s || ' from commission_info_ a where a.id_=' || p_comm;
  
  open c1 for s;
  loop
      fetch c1 into bean;
   exit when c1%notfound;
  end loop;
  close c1;
  
     return bean;
 end;
 
end;