SQL语句过程：通过输入证件查询数据库内关停数

 

create or replace procedure biller533.shzc_crm_yyy_sfzhmd_query
(p_userid varchar2,p_sjhm varchar2,p_cxzq varchar2, p_note VARCHAR2,
p_cursor in out Results.ref_cursor_type) is

v_sql varchar2(4000);
SQL_STRING  VARCHAR2(6000);
v_month varchar2(6);
v_qx              VARCHAR2(10);
v_note          VARCHAR2(6000);
v_sjhm         VARCHAR2(18);
v_cxzq         varchar2(6);
v_sjhmcd    number;  



begin
  v_month:=to_char(sysdate,'yyyymm');
  v_sjhm   := substr(p_sjhm,1,18) ;
  v_sjhmcd := length(v_sjhm);
  v_cxzq   := substr(p_cxzq,1,6) ;
  v_note  := trim(p_note);
  
  select max(qx) into v_qx from zhyw.zibo_jyfx_staff where trim(userid)=p_userid;

if v_sjhmcd  in (15,18) then
        ----前台查询留存
        fan_drop_retable(upper('qths_crm_sfzyc_jfcx_dgh'),'SHZC');
        SQL_STRING:='create table SHZC.qths_crm_sfzyc_jfcx_dgh as
        select '''||p_userid||''' p_userid,sysdate in_time,
        '''||v_sjhm||''' p_sjhm,'''||v_cxzq||''' p_cxzq,'''||p_note||''' p_note
        from dual  ' ;
        execute immediate (SQL_STRING);

        SQL_STRING:='insert into shzc.qths_crm_sfzyc_jfcx_dgh_bd 
        select * from SHZC.qths_crm_sfzyc_jfcx_dgh a ' ;
        execute immediate (SQL_STRING);
        commit;
        
        fan_drop_retable(upper('qths_crm_sfzyc_jfcx_dgha'),'SHZC');
        SQL_STRING:='create table shzc.qths_crm_sfzyc_jfcx_dgha as
        select a.p_sjhm,b.subsid,b.servnumber,c.createdate,c.prodid,c.status,c.statusdate,''使用者'' 证件类型
        from shzc.qths_crm_sfzyc_jfcx_dgh a,
         shzc.shzc_age_jqb b,
         zhyw.subscriber c
        where a.p_sjhm=b.hd_iden_no
        and b.subsid=c.subsid(+) ' ;
        execute immediate (SQL_STRING);
        
        fan_drop_retable(upper('qths_crm_sfzyc_jfcx_dghb'),'SHZC');
        SQL_STRING:='create table shzc.qths_crm_sfzyc_jfcx_dghb as
        select t.p_sjhm,b.user_id subsid,b.product_no,c.createdate,c.prodid,c.status,c.statusdate,''注册者'' 证件类型
    from  shzc.shzc_age_zcjqb b,
    (select t.p_sjhm,zhyw.MD5(t.p_sjhm) MD5  from shzc.qths_crm_sfzyc_jfcx_dgh t ) t ,
    zhyw.subscriber c
    where upper(b.iden_no)=t.MD5
    and b.user_id=c.subsid(+) ' ;
        execute immediate (SQL_STRING);
        
        fan_drop_retable(upper('qths_crm_sfzyc_jfcx_dghc'),'SHZC');
        SQL_STRING:='create table shzc.qths_crm_sfzyc_jfcx_dghc as
    select b.*,row_number() over (partition by b.subsid,b.servnumber,b.p_sjhm  order by 证件类型 desc ) 排名 
    from 
    ( select * from shzc.qths_crm_sfzyc_jfcx_dgha b
    union all 
     select * from shzc.qths_crm_sfzyc_jfcx_dghb b ) b  ' ;
        execute immediate (SQL_STRING);
        
        fan_drop_retable(upper('qths_crm_sfzyc_jfcx_dghd'),'SHZC');
        SQL_STRING:='create table shzc.qths_crm_sfzyc_jfcx_dghd as
     select a.p_sjhm,a.subsid,a.servnumber,a.createdate,a.prodid,a.status,a.statusdate,
     max(case when a.排名=1 then a.证件类型 end ) 证件类型1,
     max(case when a.排名=2 then a.证件类型 end ) 证件类型2
     from shzc.qths_crm_sfzyc_jfcx_dghc a
     group by a.p_sjhm,a.subsid,a.servnumber,a.createdate,a.prodid,a.status,a.statusdate  ' ;
        execute immediate (SQL_STRING);
        
        fan_drop_retable(upper('qths_crm_sfzyc_jfcx_dghe'),'SHZC');
        SQL_STRING:='create table shzc.qths_crm_sfzyc_jfcx_dghe as
      select a.*,b.subsid,(case when a.recdefid in (''StopSubsQZ'',''ChangeProduct'')  
      and (nvl(a.notes,''0'') like ''%关于紧急关停异常高频号码的通知%'' 
      or nvl(a.notes,''0'') like ''%高频%'' or nvl(a.notes,''0'') like ''%举报%''
      or nvl(a.notes,''0'') like ''%诈骗%'' or nvl(a.notes,''0'') like ''%骚扰%''
      or nvl(a.notes,''0'') like ''%公安机关%关停%''
      or nvl(a.notes,''0'') like ''%12321%'') then ''停机''
                             when a.recdefid in (''OpenSubsQZ'',''ChangeProduct'')  
      and (nvl(a.notes,''0'') like ''%客户签署承诺书申请开机%''  
      or nvl(a.notes,''0'') like ''%公安机关%开机%''
      or nvl(a.notes,''0'') like ''%134站点%开机%''
      or nvl(a.notes,''0'') like ''%签署%'') then ''开机'' end) 停开机类型
      from  SHZC.tskhgt_sljl_hz  a,
       (select b.servnumber,b.subsid,b.createdate,b.status,
       nvl(case when b.status in (''US10'',''US30'') then to_date(''2099'',''yyyy'') end,b.statusdate) statusdate
        from  shzc.qths_crm_sfzyc_jfcx_dghd b where b.createdate is not null ) b
       where a.servnumber=b.servnumber
       and a.recdate>=b.createdate
       and a.recdate<b.statusdate  ' ;
        execute immediate (SQL_STRING);
        
        fan_drop_retable(upper('qths_crm_sfzyc_jfcx_dghf'),'SHZC');
        SQL_STRING:='create table shzc.qths_crm_sfzyc_jfcx_dghf as
        select a.subsid,count(distinct to_char(a.recdate,''yyyymmdd'')) oids ,
       count(distinct case when a.notes like ''%骚扰%'' and a.notes not like ''%诈骗%''  then a.oid end) 骚扰,
       count(distinct case when a.notes like ''%诈骗%'' then a.oid end) 诈骗
       from shzc.qths_crm_sfzyc_jfcx_dghe a 
       where a.停开机类型=''停机''
       and a.recdate>=sysdate-366 
       group by a.subsid  ' ;
        execute immediate (SQL_STRING);
        
        fan_drop_retable(upper('qths_crm_sfzyc_jfcx_dghg'),'SHZC');
        SQL_STRING:='create table shzc.qths_crm_sfzyc_jfcx_dghg as
        select a.*,nvl(b.oids,0) 强制关停次数,
       nvl(b.骚扰,0) 骚扰关停次数,nvl(b.诈骗,0) 诈骗关停次数,
       nvl(case when a.createdate>=d.createdate then 1 end,0) 开户前身份证黑名单,
       nvl(case when e.subsid is not null then 1 end,0)   被举报号码
        from shzc.qths_crm_sfzyc_jfcx_dghd a,
       shzc.qths_crm_sfzyc_jfcx_dghf b,
       shzc.khhmd_sfz_mx d,
       shzc.khhmd_bjb_mx e
       where a.subsid=b.subsid(+)
       and a.p_sjhm=d.servnumber(+)
       and a.subsid=e.subsid(+)  ' ;
        execute immediate (SQL_STRING);
 
        
        SQL_STRING:='insert into shzc.qths_crm_sfzyc_jfcx_dgh_mxbd 
       select b.p_userid,b.in_time,b.p_sjhm,b.p_note,a.subsid,a.servnumber,
       a.createdate,a.prodid,a.status,a.statusdate,a.证件类型1,a.证件类型2,
       a.强制关停次数,a.骚扰关停次数,a.诈骗关停次数,a.开户前身份证黑名单,a.被举报号码
       from SHZC.qths_crm_sfzyc_jfcx_dgh b,
        shzc.qths_crm_sfzyc_jfcx_dghg a
       where b.p_sjhm=a.p_sjhm(+) ' ;
        execute immediate (SQL_STRING);
        commit;
        
        fan_drop_retable(upper('qths_crm_sfzyc_jfcx_dghh'),'SHZC');
        SQL_STRING:='create table shzc.qths_crm_sfzyc_jfcx_dghh as
         select a.p_sjhm,sum(a.强制关停次数) 强制关停次数,sum(a.骚扰关停次数) 骚扰关停次数,sum(a.诈骗关停次数) 诈骗关停次数
         from shzc.qths_crm_sfzyc_jfcx_dghg a 
         group by a.p_sjhm  ' ;
        execute immediate (SQL_STRING);
        
        
         
        SQL_STRING:='insert into shzc.qths_crm_sfzyc_jfcx_dghi 
        select b.p_userid,b.in_time,b.p_sjhm,b.p_note,
        nvl(a.强制关停次数,0) 强制关停次数,nvl(a.骚扰关停次数,0) 骚扰关停次数,nvl(a.诈骗关停次数,0) 诈骗关停次数
       from SHZC.qths_crm_sfzyc_jfcx_dgh b,
        shzc.qths_crm_sfzyc_jfcx_dghh a
       where b.p_sjhm=a.p_sjhm(+) ' ;
        execute immediate (SQL_STRING);
        commit;


        v_sql:=' select b.p_userid,b.in_time,b.p_sjhm,b.p_note,
        nvl(a.骚扰关停次数,0) 骚扰关停次数,nvl(a.诈骗关停次数,0) 诈骗关停次数
       from SHZC.qths_crm_sfzyc_jfcx_dgh b,
        shzc.qths_crm_sfzyc_jfcx_dghh a
       where b.p_sjhm=a.p_sjhm(+)     '       ;
        open p_cursor for v_sql;

else
        v_sql:=' select * from 
        (select a.p_userid,a.in_time,a.p_sjhm,a.p_note,a.骚扰关停次数,a.诈骗关停次数
        from shzc.qths_crm_sfzyc_jfcx_dghi a where trim(a.p_userid)='''||p_userid||'''
        order by a.in_time desc ) a
        where rownum<=3
        '       ;
        open p_cursor for v_sql;
        
end if;   


        
        insert into shc.shc_tmp2(column10) values(v_sql);
        commit;
       open p_cursor for   v_sql;
end  shzc_crm_yyy_sfzhmd_query;