plsql过程练习

 

 

create or replace procedure zhyw.SHA_GZGC_YSYK_CS(P_DAY VARCHAR2 DEFAULT TO_CHAR(SYSDATE - 1,'yyyymmdd')) as

SQL_STRING varchar2(30000);
v_day varchar2(8);
v_day1 varchar2(8);
v_day2 varchar2(8);
v_day5 varchar2(8);
v_day_qy1 varchar2(8);
v_month varchar2(6);
v_month_1 varchar2(6);
v_month_5 varchar2(6);
v_month1 varchar2(6);
v_monthq varchar2(6);
v_monthq_1 varchar2(6);
v_last_day varchar2(8);
v_last_day_2 varchar2(8);
v_last_day2 varchar2(8);
v_last_day3 varchar2(8);
v_monthS varchar2(6);
v_monthS3 varchar2(6);

v_monsr varchar2(6);
v_monsrq varchar2(6);
v_monsrh varchar2(6);

v_monsrq2 varchar2(6);
v_monsrq3 varchar2(6);
v_monsrq4 varchar2(6);
v_monsrq5 varchar2(6);
v_monsrq6 varchar2(6);
v_monsrq12 varchar2(6);
v_P_DAY varchar2(8);
V_TAB1 varchar2(900);
   zdyf   varchar2(10);

begin

    v_day        :=to_char(sysdate-1,'yyyymmdd');
    v_day1       :=to_char(sysdate-2,'yyyymmdd');
    v_day2       :=to_char(sysdate-3,'yyyymmdd');
    v_day_qy1    :=to_char(add_months(sysdate-2,-1),'yyyymmdd');
    v_day5       :=to_char(sysdate-5,'yyyymmdd');
    v_month      :=to_char(add_months(sysdate-1,-0),'yyyymm');
    v_month_1    :=to_char(add_months(sysdate-2,-0),'yyyymm');
    v_month_5    :=to_char(add_months(sysdate-5,-1),'yyyymm');
    v_month1     :=to_char(add_months(sysdate-1,1),'yyyymm');
    v_monthq     :=to_char(add_months(sysdate-1,-1),'yyyymm');
    v_monthq_1   :=to_char(add_months(sysdate-2,-1),'yyyymm');
    v_monthS     :=to_char(add_months(sysdate-1,-2),'yyyymm');
    v_monthS3    :=to_char(add_months(sysdate-1,-3),'yyyymm');
    v_last_day   :=to_char(last_day(add_months(sysdate-1,-1)),'yyyymmdd') ;
    v_last_day_2 :=to_char(last_day(add_months(sysdate-2,-1)),'yyyymmdd') ;
    v_last_day2  :=to_char(last_day(add_months(sysdate-1,-2)),'yyyymmdd') ;
    v_last_day3  :=to_char(last_day(add_months(sysdate-1,-3)),'yyyymmdd') ;
    v_monsr      :=substr(P_DAY,1,6);
    v_monsrq     :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-1),'yyyymm') ;
    v_monsrh     :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),1),'yyyymm') ;
    v_monsrq2    :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-2),'yyyymm') ;
    v_monsrq3    :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-3),'yyyymm') ;
    v_monsrq4    :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-4),'yyyymm') ;
    v_monsrq5    :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-5),'yyyymm') ;
    v_monsrq6    :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-6),'yyyymm') ;
    v_monsrq12   :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-12),'yyyymm') ;
    v_P_DAY      :=to_char(to_date(P_DAY,'yyyymmdd')-1,'yyyymmdd') ;
    zdyf         :=to_char(trunc(to_date(P_DAY,'yyyymmdd'),'mm'),'yyyymm');--统计月

/*支撑集团酬金关联关系维护表，按天更新。
报表字段：集团归属区县、集团编号、集团名称、客户经理编号、客户经理名称、集团产品编码、集团产品编号，产品开户时间、
酬金比例、渠道编号、渠道名称、酬金开始时间、酬金结束时间、结算月数、审批时间*/
SQL_STRING:='delete from xxx.szc_201709_jtcjwh a where a.日期=to_char(sysdate-1,''yyyymmdd'') ' ;
execute immediate (SQL_STRING);
commit;

---最大月份
    select max(a.OBJECT_NAME)  OBJECT_NAME into V_TAB1 From all_objects a 
                            where objEct_name like UPPER('%qcy_group_subscriber_2%')
                            and a.OWNER='ZHYW'  ;

SQL_STRING:='insert into  xxx.szc_201709_jtcjwh
select to_char(sysdate-1,''yyyymmdd'') 日期,
a.oid,a.orderid,q.name1 区县,a.SUBSID,h.servnumber,a.SUBSNAME,a.SAID,a.SANAME,a.GRPPRODTYPE,
a.REWARDACCSCALE 酬金比例,a.BEGINDATE 创建时间,a.ENDDATE,a.ACCOUNTCYCLE ,
a.status,B.DICTNAME 状态,a.statusdate 状态时间,
a1.集团编号,a1.集团名称,a1.区域编码,a1.服务区县,
a1.集团产品编号,a1.产品,a1.集团产品名称,a1.客户经理,c1.opername
from tbcs.grp_subs_reward_info@bcv a,
zhyw.rpt_county q,zhyw.subscriber h,
(select * from tbcs.dict_item@bcv WHERE GROUPID=''stcm'') B,
zhyw.'||V_TAB1||' a1,
tbcs.operator@bcv c1 
where substr(a.said,8,1)=q.county_id(+)
and A.STATUS=B.DICTID(+)
and a.subsid=h.subsid(+)
and a.subsid=a1.subsid(+)
and a1.客户经理=c1.operid(+)' ;
    execute immediate (SQL_STRING);
    commit;

---------------集团sa关联到期提醒的信息推送过程开始-----------------

fan_drop_retable(upper('jtcj_sagl_mx'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mx as
select a.日期,a.oid,a.subsid,a.said,a.grpprodtype,a.酬金比例,a.创建时间,a.accountcycle,a.状态,a.集团编号
from  xxx.szc_201709_jtcjwh a  ' ;
execute immediate (SQL_STRING);

fan_drop_retable(upper('jtcj_sagl_mxa'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxa as
select a.oid,a.subsid,a.said,a.grpprodtype,a.酬金比例,a.创建时间,a.accountcycle,a.集团编号,min(a.日期) 日期,
row_number() over (partition by a.oid  order by min(a.日期) desc ) 排名
from  xxx.jtcj_sagl_mx a
group by a.oid,a.subsid,a.said,a.grpprodtype,a.酬金比例,a.创建时间,a.accountcycle,a.集团编号  ' ;
execute immediate (SQL_STRING);

---最大月份
    select max(a.OBJECT_NAME)  OBJECT_NAME into V_TAB1 From all_objects a 
                            where objEct_name like UPPER('%qcy_group_subscriber_%')
                            and a.OWNER='ZHYW'  ;

fan_drop_retable(upper('jtcj_sagl_mxb'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxb as
select a.oid,a.subsid,b.acctid,b.集团名称,b.集团产品名称,a.said,a.grpprodtype,
a.酬金比例,a.日期 最早关联日期,to_char(a.创建时间,''yyyymm'') 关联时选择开始月,
a.accountcycle 有效月数,
nvl(case when to_char(add_months(to_date(a.日期,''yyyymmdd''),-1),''yyyymm'') >=to_char(a.创建时间,''yyyymm'') then 
  to_char(add_months(to_date(a.日期,''yyyymmdd''),-1),''yyyymm'') end,to_char(a.创建时间,''yyyymm'')) 实际开始月,
nvl(case when to_char(add_months(to_date(a.日期,''yyyymmdd''),-1),''yyyymm'') =to_char(a.创建时间,''yyyymm'') then 
  to_char(add_months(to_date(a.日期,''yyyymmdd''),-1+a.accountcycle-1),''yyyymm'') end,
  to_char(add_months(a.创建时间,a.accountcycle-1),''yyyymm'')) 实际结束月,b.产品编码
from  xxx.jtcj_sagl_mxa a,
 zhyw.'||V_TAB1||' b
where  a.subsid=b.subsid(+)
and a.排名=1   ' ;
execute immediate (SQL_STRING);

fan_drop_retable(upper('jtcj_sagl_mxc'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxc as
select distinct a.*,b.RULETYPE,b.VALUEDESC  ATTR
 from xxx.jtcj_sagl_mxb a ,
 (select * from  settle.ch_ngsettle_ruleconstvalue@choujin_new b where RULETYPE <>''P_GROUP_PRIV'') b
where a.产品编码=b.REMUNERAT(+)  ' ;
execute immediate (SQL_STRING);

fan_drop_retable(upper('jtcj_sagl_mxd'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxd as
select distinct b.RULETYPE,b.REMUNERAT,b.VALUEDESC,c.itemname
from  settle.ch_ngsettle_ruleconstvalue@choujin_new b,
(select distinct a.ruletype,a.ruletype,a.attr from xxx.jtcj_sagl_mxc a where a.ruletype is not null ) a,
 account.acctitem_def@zwbcv c
where b.REMUNERAT=a.attr
and b.VALUEDESC=c.itemcode(+)
and b.RULETYPE=''P_GROUP_BILLID''  ' ;
execute immediate (SQL_STRING);

fan_drop_retable(upper('jtcj_sagl_mxe'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxe as
select * from  xxx.szc_201709_jtcjwh a 
where a.日期=(select max(a.日期) from xxx.szc_201709_jtcjwh a )  ' ;
execute immediate (SQL_STRING);

fan_drop_retable(upper('jtcj_sagl_mxf'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxf as
select a.*,b.status,b.状态,b.状态时间,b.产品,b.客户经理,b.opername,c.CONTACTPHONE
from  xxx.jtcj_sagl_mxc a ,
 xxx.jtcj_sagl_mxe b ,
 tbcs.operator@bcv c
where a.oid=b.oid(+)
and b.客户经理=c.operid(+)
and 状态=''在网''
and a.实际结束月 in (to_char(sysdate,''yyyymm''),to_char(add_months(sysdate,1),''yyyymm''))   ' ;
execute immediate (SQL_STRING);

--比如客户经理a负责一个集团产品酬金，当计算到结束月份为次月时，就会给客户经理发一条微信和短信通知。
--内容：设置流水+集团名称+subsid +sa渠道编码+业务的类型+有效月数+实际结束月+酬金比例，即将到期，请核实是否需要重新关联，谢谢合作

fan_drop_retable(upper('jtcj_sagl_mxfs'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxfs as
select to_char(sysdate,''yyyymm'') cycle,
a.oid,a.subsid,a.said,a.酬金比例,a.有效月数,a.实际结束月,a.产品编码,a.产品,a.客户经理,a.opername,a.contactphone,
''您好!您有一条集团SA酬金:''||a.oid||'',''||a.集团名称||''_''||a.subsid||'' 对应渠道:''||a.said||'' 业务类型:''||
a.产品||'' 有效月数:''||a.有效月数||'' 结束月份:''||a.实际结束月||'' 酬金比例:''||a.酬金比例*100||''%,即将到期，请核实是否需要重新关联，谢谢合作'' 发送内容,0 发送状态
from  xxx.jtcj_sagl_mxf a
where a.contactphone is not null ' ;
execute immediate (SQL_STRING);

 SQL_STRING:='INSERT INTO  xxx.jtcj_sagl_mx_fsh 
 select * from xxx.jtcj_sagl_mxfs a 
 where not exists (select * from xxx.jtcj_sagl_mx_fsh b where a.cycle=b.cycle
 and a.oid=b.oid )  ' ;
execute immediate (SQL_STRING);
 
  SQL_STRING:='INSERT INTO zhyw.shc_sendmessage_weixin (SERVNUMBER    ,INSERTTIME    ,SENDSTAFF,    STATUS,    CONTENT1)
      select a.contactphone,sysdate,a.客户经理,1,a.发送内容 
      from xxx.jtcj_sagl_mx_fsh a where a.发送状态=0  ' ;
execute immediate (SQL_STRING);

SQL_STRING:='update xxx.jtcj_sagl_mx_fsh a set a.发送状态=1 where a.发送状态=0   ' ;
execute immediate (SQL_STRING);
commit;

---20200529
--集团酬金有查询，到期提醒，我看看再加一个欠费月份提醒吧，比如一个集团酬金关联的账单欠费三个月到五个月的时候提醒

---集团产品sa关联
  fan_drop_retable(upper('jtcj_sagl_qlsj'),'xxx');
  SQL_STRING:='create table xxx.jtcj_sagl_qlsj as
select a.*,b.status,b.状态,b.状态时间,b.产品,b.客户经理,b.opername,c.CONTACTPHONE
from  xxx.jtcj_sagl_mxc a ,
 xxx.jtcj_sagl_mxe b ,
 tbcs.operator@bcv c
where a.oid=b.oid(+)
and b.客户经理=c.operid(+)
and (b.状态=''在网'' 
or (b.状态<>''在网'' and b.状态时间>sysdate-360 ))  ' ;
  execute immediate (SQL_STRING);

---最大月份
    select max(a.OBJECT_NAME)  OBJECT_NAME into V_TAB1 From all_objects a 
                            where objEct_name like UPPER('%qcy_group_subs_member_2%')
                            and a.OWNER='ZHYW'  ;

  fan_drop_retable(upper('jtcj_sagl_qlsjhm'),'xxx');
  SQL_STRING:='create table xxx.jtcj_sagl_qlsjhm as
select distinct a.oid,a.subsid,c.servnumber,a.acctid,a.said,
a.有效月数,
a.酬金比例,
nvl(case when a.最早关联日期=''2017''||''0929'' then a.关联时选择开始月 end, a.实际开始月) 实际开始月,
a.实际结束月,
a.集团名称,
a.集团产品名称,
a.产品编码,
a.attr,
nvl(b.subsoid,a.subsid) subsoid,
nvl(b.memservnumber,c.servnumber) memservnumber,
nvl(b.acctoid,a.acctid) acctoid,
nvl(b.加入集团时间, to_date(nvl(case when a.最早关联日期=''2017''||''0929'' then a.关联时选择开始月 end, a.实际开始月),''yyyymm'') ) 加入集团时间,
row_number() over (partition by a.oid, nvl(b.subsoid,a.subsid)  
order by nvl(b.加入集团时间, to_date(nvl(case when a.最早关联日期=''2017''||''0929'' then a.关联时选择开始月 end, a.实际开始月),''yyyymm'') )  desc ) 排名
from   xxx.jtcj_sagl_qlsj a,
  zhyw.'||V_TAB1||' b,
 zhyw.subscriber c
where a.subsid=b.subsid(+)
and a.subsid=c.subsid(+)
---结束时间选择6个月内
and a.实际结束月>=to_char(sysdate-31*6,''yyyymm'')   ' ;
  execute immediate (SQL_STRING);

fan_drop_retable(upper('jtcj_sagl_qlsjhm_qf'),'xxx');
  SQL_STRING:='create table xxx.jtcj_sagl_qlsjhm_qf as
select b.oid,b.实际开始月,b.实际结束月,b.加入集团时间,a.ACCTID,a.SUBSID, 
substr(a.BILLCYCLE,1,6) BILLCYCLE ,a.ITEMTYPE    ,a.ITEMCODE,c.itemname,a.FEE
from account.v_billitem_owe533@zwbcv a,
  xxx.jtcj_sagl_qlsjhm b ,
  xxx.jtcj_sagl_mxd c
 where a.subsid=b.subsoid
 and substr(a.BILLCYCLE,1,6) >=b.实际开始月
 and substr(a.BILLCYCLE,1,6) <=b.实际结束月
 and b.attr=c.remunerat(+)
 and a.ITEMCODE=c.valuedesc(+)
 and a.ITEMTYPE<4   ' ;
  execute immediate (SQL_STRING);

  fan_drop_retable(upper('jtcj_sagl_qlsjhm_qfhz'),'xxx');
  SQL_STRING:='create table xxx.jtcj_sagl_qlsjhm_qfhz as
  select a.oid,
  count(distinct a.subsid) 欠费号码数,
  count(distinct a.billcycle) 欠费账期数,
  min(a.billcycle) 最早欠费账期,
  max(a.billcycle) 最近欠费账期,
  sum(a.fee)/100 欠费金额元,
  count(distinct (case when a.billcycle>=to_char(add_months(sysdate,-5),''yyyymm'') then a.billcycle end)) 近六月欠费账期数,
  nvl(min(case when a.billcycle>=to_char(add_months(sysdate,-5),''yyyymm'') then a.billcycle end),''无'')  近六月最早欠费账期,
  nvl(max(case when a.billcycle>=to_char(add_months(sysdate,-5),''yyyymm'') then a.billcycle end),''无'') 近六月最近欠费账期,
  sum(nvl(case when a.billcycle>=to_char(add_months(sysdate,-5),''yyyymm'')  then a.fee end,0))/100 近六月欠费金额元,
  sum(nvl(case when a.billcycle>=to_char(add_months(sysdate,-5),''yyyymm'') 
  and a.itemname is not null and to_char(a.加入集团时间,''yyyymm'')<=a.billcycle then a.fee end,0))/100 近六月可返酬欠费金额元
  from xxx.jtcj_sagl_qlsjhm_qf a
  group by a.oid ' ;
  execute immediate (SQL_STRING);

   SQL_STRING:=' delete xxx.jtcj_sagl_qlsjhm_qfhzm  a where 日期=TO_CHAR(SYSDATE - 1,''yyyymmdd'')  ' ;
   execute immediate (SQL_STRING);
   commit;

  SQL_STRING:=' insert into xxx.jtcj_sagl_qlsjhm_qfhzm 
  select TO_CHAR(SYSDATE - 1,''yyyymmdd'') 日期, a.*,b.欠费号码数,b.欠费账期数,b.最早欠费账期,b.最近欠费账期,b.欠费金额元,
  b.近六月欠费账期数,b.近六月最早欠费账期,b.近六月最近欠费账期,b.近六月欠费金额元,b.近六月可返酬欠费金额元 
  from xxx.jtcj_sagl_qlsj a,
 xxx.jtcj_sagl_qlsjhm_qfhz b
  where a.oid=b.oid  ' ;
  execute immediate (SQL_STRING);

   SQL_STRING:=' delete xxx.jtcj_sagl_qlsjhm_qfhzh  a where 日期=TO_CHAR(SYSDATE - 1,''yyyymmdd'')  ' ;
   execute immediate (SQL_STRING);
   commit;

  SQL_STRING:=' insert into xxx.jtcj_sagl_qlsjhm_qfhzh 
  select a.日期,a.said,a.客户经理,a.opername,a.contactphone,a.集团名称,
  count(distinct a.subsid) 欠费产品编码,
  sum(a.近六月欠费账期数) 近六月欠费账期数,
  min(a.近六月最早欠费账期) 近六月最早欠费账期,
  max(a.近六月最近欠费账期) 近六月最近欠费账期,
  sum(a.近六月欠费金额元) 近六月欠费金额元,
  sum(a.近六月可返酬欠费金额元) 近六月可返酬欠费金额元
  from    xxx.jtcj_sagl_qlsjhm_qfhzm a
  where a.近六月可返酬欠费金额元>0
  and a.日期=TO_CHAR(SYSDATE - 1,''yyyymmdd'') 
  group by a.日期,a.said,a.客户经理,a.opername,a.contactphone,a.集团名称   ' ;
  execute immediate (SQL_STRING);
  commit;
  
   SQL_STRING:=' delete xxx.jtcj_sagl_qlsjhm_qfhzh_dx  a where 日期=TO_CHAR(SYSDATE - 1,''yyyymmdd'')  ' ;
   execute immediate (SQL_STRING);
   commit;
  
  SQL_STRING:=' insert into xxx.jtcj_sagl_qlsjhm_qfhzh_dx 
  select a.日期,a.said,a.集团名称,
  a.客户经理,a.opername,a.contactphone,
  ''您好！截止:''||a.日期||'',集团名称:''||a.集团名称||'' 关联SA编码:''||a.said
  ||'' 欠费产品数:''||a.欠费产品编码||''个,近六个月内欠费月数:''
  ||a.近六月欠费账期数||''个月,最早欠费账期:''
  ||a.近六月最早欠费账期||''元,欠费金额:''
  ||a.近六月欠费金额元||''元,欠费将影响系统酬金正常核发，请及时关注处理。''  发送内容,0 发送状态
  from    xxx.jtcj_sagl_qlsjhm_qfhzh a 
  where a.近六月可返酬欠费金额元>0
  and a.日期=TO_CHAR(SYSDATE - 1,''yyyymmdd'') 
  and substr(a.日期,7,2) in (''05'',''20'')   ' ;
  execute immediate (SQL_STRING);
  commit;
  
    SQL_STRING:='INSERT INTO zhyw.shc_sendmessage_weixin (SERVNUMBER    ,INSERTTIME    ,SENDSTAFF,    STATUS,    CONTENT1)
      select a.contactphone,sysdate,a.客户经理,1,a.发送内容 
      from xxx.jtcj_sagl_qlsjhm_qfhzh_dx a where a.发送状态=0  ' ;
execute immediate (SQL_STRING);

SQL_STRING:='update xxx.jtcj_sagl_qlsjhm_qfhzh_dx a set a.发送状态=1 where a.发送状态=0   ' ;
execute immediate (SQL_STRING);
commit;

end;