通过录入号码生成一个工单号，根据工单ID创建系列后台表，避免查询生成数据冲突

最新推荐文章于 2022-05-11 18:32:42 发布
szc_1985
最新推荐文章于 2022-05-11 18:32:42 发布
阅读量560
点赞数
分类专栏：数据库办公记录语句文章标签：数据库
本文链接：https://blog.csdn.net/szc_1985/article/details/113198427
版权
数据库办公记录语句专栏收录该内容
40 篇文章 1 订阅
订阅专栏
create or replace procedure biller533.shzc_202101_dhm_qdgl_gc
(p_userid varchar2,p_hm VARCHAR2, is_lishi VARCHAR2, p_id  VARCHAR2,
p_cursor in out Results.ref_cursor_type)
as

v_hm              VARCHAR2(18);
v_qx               VARCHAR2(18);
v_sql             VARCHAR2(6000);
SQL_STRING  VARCHAR2(6000);

 v_last_day     varchar2(8);
 v_last_day2   varchar2(8);
v_day             VARCHAR2(8);
v_monq         VARCHAR2(6);


V_TAB1         varchar2(900);
v_lishi          VARCHAR2(10);

v_mm1            VARCHAR2(6);
v_mm2            VARCHAR2(6);
v_mm3            VARCHAR2(6);

v_orgid          VARCHAR2(50);
v_name          VARCHAR2(50);
v_sjhm          VARCHAR2(50);
v_id              VARCHAR2(10);

v_dfhm_cd number;

begin

v_hm         :=substr(trim(p_hm),1,18);
v_lishi        := substr( is_lishi,1,6);


v_day         :=to_char(sysdate-2,'yyyymmdd');
v_monq     :=to_char(add_months(sysdate-1,-1),'yyyymm') ;

 v_last_day   :=to_char(last_day(add_months(sysdate-1,-1)),'yyyymmdd') ;--上月最后一天
 v_last_day2  :=to_char(last_day(add_months(sysdate-1,-2)),'yyyymmdd') ;--上上月最后一天

v_mm1            :=to_char(sysdate-2,'yyyymm');
v_mm2            :=to_char(last_day(add_months(sysdate-1,-1)),'yyyymm') ;
v_mm3            :=to_char(last_day(add_months(sysdate-1,-2)),'yyyymm') ;

  ---经分工号获取区县
select max(qx)  into v_qx from 
(select a.userid,a.qx,NVL(case when a.qx='0' then 'SD.LC' END,'SD.LC.0'||A.QX ) ORGID from  zhyw.zibo_jyfx_staff a
UNION ALL
select OPERID,NVL(SUBSTR(ORGID,8,1),'0'),ORGID from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ;

select max(ORGID)  into v_orgid from 
(select a.userid,a.qx,NVL(case when a.qx='0' then 'SD.LC' END,'SD.LC.0'||A.QX ) ORGID from  zhyw.zibo_jyfx_staff a
UNION ALL
select OPERID,NVL(SUBSTR(ORGID,8,1),'0'),ORGID from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ;

select max(username)  into v_name from 
(select a.userid,a.username from  zhyw.zibo_jyfx_staff a
UNION ALL
select OPERID,opername from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ;

select max(mobilephone)  into v_sjhm from 
(select a.userid,a.mobilephone  from  zhyw.zibo_jyfx_staff a
UNION ALL
select OPERID,CONTACTPHONE from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ;

 if v_lishi='是' then
 
 
  v_sql:='  select ''ID'',''经分工号'',''申请人姓名'',''申请人号码'',''归属区县'',''组织机构'',''查询时间'',''查询号码'',''号码数'' from dual 
union all
select a.* from 
(select a.id,a.jfgh,a.申请人姓名,a.申请人号码,a.归属区县,a.组织机构,
to_char(a.in_time,''yyyymmddhh24miss'') in_time,a.hm,to_char(b.subsid) 号码数 
from shzc.shzc_dhm_qdgl_id_bdcd a,
shzc.shzc_dhm_qdgl_id_bdcd_jg b
where a.id=b.id(+)
and a.jfgh='''||p_userid||''' 
order by a.id desc) a'       ;

  elsif v_lishi='明细' then
  
  v_sql:='  select ''ID'',''SERVNUMBER'',''SUBSID'',''HD_IDEN_NO'',''CREATEDATE'',''NAME'',
  ''REGISTERORGID'',''ORGNAME'',''DICTNAME'',''状态时间'',''本地'',''漫游地'',''本月分钟数'',
  ''本月通话次数'',''上月分钟数'',''上月通话次数'',''通话次数'',''通话号码数'',''离散度'',''短信发送量'',
  ''短信发送号码'',''短信离散度'' from dual union all 
  select a.* from 
  (select ID,SERVNUMBER,SUBSID,substr(HD_IDEN_NO,1,6)||''******''||substr(HD_IDEN_NO,13,6),
  to_char(CREATEDATE,''yyyy-mm-dd hh24:mi:ss''),NAME,REGISTERORGID,ORGNAME,DICTNAME,
  to_char(状态时间,''yyyy-mm-dd hh24:mi:ss''),本地,漫游地,to_char(本月分钟数),to_char(本月通话次数),
  to_char(上月分钟数),to_char(上月通话次数),to_char(通话次数),to_char(通话号码数),to_char(离散度),
  to_char(短信发送量),to_char(短信发送号码),to_char(短信离散度) from 
  shzc.SHZC_DHM_QDGL_'||p_id||'_JG a
  order by a.HD_IDEN_NO desc ) a     '       ;
   

  elsif v_lishi='否' then

------原始导入
fan_drop_retable(upper('shzc_dhm_qdgl_id_bd'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_id_bd as
select '''||p_userid||''' jfgh,'''||v_name||''' 申请人姓名,'''||v_sjhm||''' 申请人号码,
        '''||v_qx||''' 归属区县,'''||v_orgid||''' 组织机构,sysdate in_time,'''||v_hm||''' hm from dual ' ;
execute immediate (SQL_STRING);
commit;

SQL_STRING:='insert into shzc.shzc_dhm_qdgl_id_bdcd (jfgh,申请人姓名,申请人号码,归属区县,组织机构,in_time,hm)
select a.jfgh,a.申请人姓名,a.申请人号码,a.归属区县,a.组织机构,a.in_time,a.hm 
from shzc.shzc_dhm_qdgl_id_bd a  ' ;
execute immediate (SQL_STRING);
commit;

select max(a.id)  into v_id from shzc.shzc_dhm_qdgl_id_bdcd a
where a.jfgh=p_userid
and a.hm=v_hm ;


fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_a'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_a as 
select a.* from 
(select a.id,a.hm,b.subsid,b.registerorgid,b.createdate,b.status,
row_number() over (partition by b.servnumber order by b.createdate desc ) 排名  
from shzc.shzc_dhm_qdgl_id_bdcd a ,
(select * from  zhyw.subscriber b where b.status not in (''US26'',''US28'')) b 
where a.id='''||v_id||'''
and a.hm=b.servnumber ) a
where a.排名=1   ' ;
execute immediate (SQL_STRING);


fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_b'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_b as 
select a.id,b.servnumber,b.subsid,b.registerorgid,b.createdate,b.status
from (select * from  zhyw.subscriber b where b.status not in (''US26'',''US28'')
and b.nettype=''GSM'') b,
 shzc.shzc_dhm_qdgl_'||v_id||'_a a
 where b.registerorgid=a.registerorgid
 and nvl(b.createdate,sysdate-9999)>a.createdate-31
 and nvl(b.createdate,sysdate-9999)<a.createdate+1  ' ;
execute immediate (SQL_STRING);
 

 
 fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_c'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_c as 
 select a.*,b.hd_iden_no 
 from shzc.shzc_dhm_qdgl_'||v_id||'_b a,
 shzc.shzc_age_jqb b
 where a.subsid=b.subsid(+) ' ;
execute immediate (SQL_STRING);
 

 
  fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_d'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_d as 
 select distinct a.* from 
 (select t.id,t.servnumber,to_char(t.subsid) subsid,t.registerorgid,t.createdate,t.status,t.hd_iden_no 
  from shzc.shzc_dhm_qdgl_'||v_id||'_c t 
 union all
 select a.id,to_char(b.servnumber),b.subsid,d.registerorgid,d.createdate,d.status, b.hd_iden_no
 from shzc.shzc_age_jqb b,
 (select distinct a.id,a.hd_iden_no from  shzc.shzc_dhm_qdgl_'||v_id||'_c a where nvl(a.hd_iden_no,''0'') <>''0'' ) a ,
 (select * from  zhyw.subscriber d where  nettype=''GSM'' ) d
 where b.hd_iden_no=a.hd_iden_no
 and b.subsid=d.subsid ) a ' ;
execute immediate (SQL_STRING);
 
--1、所有号码现在的使用状态
--2、所有号码的同一证件下其他号码的状态
--3、证件下所有号码的近一个月的漫游情况
--4、证件下所有号码近两个月的通话数量、分钟数
--5、办理的渠道名称，办理日期
--离散度
--短信发送量
 
--3、证件下所有号码的近一个月的漫游情况

    fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_e'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_e as 
   select a.user_id,a.roamcity_id,count(distinct a.call_tm) call_tm,sum(a.cs) cs,sum(a.call_duration_m) call_duration_m 
    from ZHYW.ZWZX_MHH_CALL_CELL_HD a,
  shzc.shzc_dhm_qdgl_'||v_id||'_d b
  where a.user_id=b.subsid
  group by a.user_id,a.roamcity_id  ' ;
execute immediate (SQL_STRING);
  
  fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_emy'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_emy as 
  select a.user_id,
  listagg (a.PROVINCENAME||''_''||a.AREAname,'' ,'')  within group (order by user_id) 漫游地 
  from 
  (select  B.PROVINCENAME,b.AREAname,a.*,
  row_number() over (partition by a.user_id  order by a.call_tm desc , a.call_duration_m desc ) 排名  
  from shzc.shzc_dhm_qdgl_'||v_id||'_e a ,
 (select * from  common.SA_DB_NATAREAINFO@bcv b WHERE AREATYPE=1 ) B
where a.ROAMCITY_ID=b.AREAID(+)
and a.roamcity_id<>''533'') a
group by a.user_id  ' ;
execute immediate (SQL_STRING);

  fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_ebd'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_ebd as 
  select a.user_id,
  listagg (a.PROVINCENAME||''_''||a.AREAname,'' ,'')  within group (order by user_id) 本地 
  from 
  (select  B.PROVINCENAME,b.AREAname,a.*,
  row_number() over (partition by a.user_id  order by a.call_tm desc , a.call_duration_m desc ) 排名  
  from shzc.shzc_dhm_qdgl_'||v_id||'_e a ,
 (select * from  common.SA_DB_NATAREAINFO@bcv b WHERE AREATYPE=1 ) B
where a.ROAMCITY_ID=b.AREAID(+)
and a.roamcity_id=''533'') a
group by a.user_id ' ;
execute immediate (SQL_STRING);
  

--4、证件下所有号码近两个月的通话数量、分钟数

    fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_f'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_f as 
select a.subsid,
nvl(b.本月_mou,0) 本月分钟数,nvl(b.本月通话次数,0) 本月通话次数,
nvl(b.上月_mou,0) 上月分钟数,nvl(b.上月通话次数,0) 上月通话次数
from shzc.shzc_dhm_qdgl_'||v_id||'_d a,
zhyw.shc_all_label_mx b
where a.subsid=b.subsid   ' ;
execute immediate (SQL_STRING);
 
 ---离散度
 fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_g'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_g as 
 select a.user_id,a.opposite_regular_no,a.call_tm,a.cs,a.call_duration_m,nvl(a.zj_duration_m,0) zj_duration_m 
 from zhyw.zwzx_mhh_call_oppo_hd a,
 shzc.shzc_dhm_qdgl_'||v_id||'_d b
 where a.user_id=b.subsid  ' ;
execute immediate (SQL_STRING);
 
 
  fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_gh'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_gh as 
 select a.user_id,sum(a.cs) 通话次数,count(distinct a.opposite_regular_no) 通话号码数,
 round(sum(a.cs)/count(distinct a.opposite_regular_no),4)  离散度
 from shzc.shzc_dhm_qdgl_'||v_id||'_g a
 group by a.user_id' ;
execute immediate (SQL_STRING);
 
 ---短信发送量
 
  fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_h'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_h as
 select a.* from zibo.cdr_sms_'||v_mm1||' a,
 shzc.shzc_dhm_qdgl_'||v_id||'_d b
 where a.user_id=b.subsid' ;
execute immediate (SQL_STRING);
 
   fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_hh'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_hh as
 select a.user_id,count(*) 短信发送量,count(distinct a.opposite_no) 短信发送号码,
 round(count(*)/count(distinct a.opposite_no),4)  短信离散度
  from shzc.shzc_dhm_qdgl_'||v_id||'_h a
 group by a.user_id' ;
execute immediate (SQL_STRING);

---组合
 fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_jg'),'SHZC');
SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_jg as
select a.id,a.servnumber,a.subsid,a.hd_iden_no,a.createdate ,
nvl(q.name,''全市'') name,a.registerorgid,b.orgname,zt.dictname,t.statusdate 状态时间,
nvl(bd.本地,''无'') 本地,nvl(my.漫游地,''无'') 漫游地,
nvl(f.本月分钟数,0) 本月分钟数,nvl(f.本月通话次数,0) 本月通话次数,
nvl(f.上月分钟数,0) 上月分钟数,nvl(f.上月通话次数,0) 上月通话次数,
nvl(g.通话次数,0) 通话次数,nvl(g.通话号码数,0) 通话号码数,nvl(g.离散度,0) 离散度,
nvl(h.短信发送量,0) 短信发送量,nvl(h.短信发送号码,0) 短信发送号码,nvl(h.短信离散度,0) 短信离散度

from  shzc.shzc_dhm_qdgl_'||v_id||'_d a,
(select * from tbcs.dict_item@bcv where groupid=''US'') zt,
tbcs.organization@bcv b,
zhyw.rpt_county q ,
shzc.shzc_dhm_qdgl_'||v_id||'_ebd bd,
shzc.shzc_dhm_qdgl_'||v_id||'_emy my,
shzc.shzc_dhm_qdgl_'||v_id||'_f f ,
shzc.shzc_dhm_qdgl_'||v_id||'_gh g,
shzc.shzc_dhm_qdgl_'||v_id||'_hh h,
 zhyw.subscriber t 
where a.status=zt.dictid(+)
and a.registerorgid=b.orgid(+)
and substr(a.registerorgid,8,1) =q.county_id(+)
and a.subsid=bd.user_id(+)
and a.subsid=my.user_id(+)
and a.subsid=f.subsid(+)
and a.subsid=g.user_id(+)
and a.subsid=h.user_id(+)
and a.subsid=t.subsid(+)' ;
execute immediate (SQL_STRING);

SQL_STRING:='insert into shzc.shzc_dhm_qdgl_id_bdcd_jg 
select a.id,count(distinct a.subsid) subsid
from shzc.shzc_dhm_qdgl_'||v_id||'_jg a
group by a.id ' ;
execute immediate (SQL_STRING);
commit;



   v_sql:=' select ''ID'',''经分工号'',''申请人姓名'',''申请人号码'',''归属区县'',''组织机构'',''查询时间'',''查询号码'',''号码数'' from dual 
union all
select a.* from 
(select a.id,a.jfgh,a.申请人姓名,a.申请人号码,a.归属区县,a.组织机构,
to_char(a.in_time,''yyyymmddhh24miss'') in_time,a.hm,to_char(b.subsid) 号码数 
from shzc.shzc_dhm_qdgl_id_bdcd a,
shzc.shzc_dhm_qdgl_id_bdcd_jg b
where a.id=b.id(+)
and a.jfgh='''||p_userid||''' 
order by a.id desc) a '       ;
     

     
end if ;     
  open p_cursor for v_sql;




end ;
szc_1985
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
通过录入号码生成一个工单号，根据工单ID创建系列后台表，避免查询生成数据冲突

create or replace procedure biller533.shzc_202101_dhm_qdgl_gc(p_userid varchar2,p_hm VARCHAR2, is_lishi VARCHAR2, p_id VARCHAR2,p_cursor in out Results.ref_cursor_type)asv_hm VARCHAR2(18);v_qx VARCHAR2(18);v_sql .
复制链接

扫一扫
专栏目录