前台界面输入 查询号码、日期与备注原因,以参数形式传入,后台数据提取相关设备使用记录,并且根据使用设备,延伸查找其他使用号码,进行数据展示。
create or replace procedure biller533.shzc_202106_dkxd_yhimei_gc (p_userid varchar2,p_hm VARCHAR2,p_jbrq VARCHAR2, p_note VARCHAR2, p_cursor in out Results.ref_cursor_type) as v_hm VARCHAR2(18); v_jbrq VARCHAR2(8); v_note VARCHAR2(800); v_sql VARCHAR2(6000); SQL_STRING VARCHAR2(6000); V_ZMLX VARCHAR2(30); V_ZMLX_SZ VARCHAR2(30); v_monsr varchar2(6); P_DAY varchar2(8); begin v_hm :=substr(trim(p_hm),1,11); v_jbrq :=substr(trim(p_jbrq),1,8); v_note := substr( trim(p_note),1,800); v_monsr :=substr( to_char(sysdate-1,'yyyymm') ,1,6); P_DAY :=substr( to_char(sysdate-1,'yyyymmdd') ,1,8); if v_hm<>'0' then -----举报号码存档 fan_drop_retable(upper('dkxd_yh_imei_ckqk'),'SHZC'); SQL_STRING:='create table SHZC.dkxd_yh_imei_ckqk as select '''||p_userid||''' p_userid,sysdate in_time,'''||v_hm||''' 查询号码,'''||v_jbrq||''' 举报日期, '''||v_note||''' 备注信息 from dual ' ; execute immediate (SQL_STRING); SQL_STRING := 'insert into shzc.dkxd_yh_imei_ckqk_bd select * from SHZC.dkxd_yh_imei_ckqk ' ; execute immediate (SQL_STRING); commit; fan_drop_retable(upper('gagx_sasz_hmmx_cbcda'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcda as select a.subsid,a.servnumber,a.prodid,a.createdate,a.registerorgid,a.ownerorgid, a.status,a.statusdate,b.邮件转派时间,b.工单类型, row_number() over (partition by b.邮件转派时间,b.servnumber,b.工单类型 order by a.createdate desc ) 排名 from zhyw.subscriber a, (select to_date(b.举报日期,''yyyymmdd'') 邮件转派时间,b.查询号码 servnumber,substr(b.备注信息,1,16) 工单类型 from shzc.dkxd_yh_imei_ckqk b ) b where a.servnumber=b.servnumber and to_char(a.createdate,''yyyymmdd'')<=to_char(b.邮件转派时间,''yyyymmdd'') '; EXECUTE IMMEDIATE (SQL_STRING); fan_drop_retable(upper('gagx_sasz_hmmx_cbcdb'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcdb as select * from shzc.gagx_sasz_hmmx_cbcda a where a.排名=1 '; EXECUTE IMMEDIATE (SQL_STRING); ---用户imei后台表 --提供表名 fan_drop_retable(upper('gagx_sasz_hmmx_cbcdc'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcdc as select * from (select a.OBJECT_NAME ,row_number() over (partition by substr(a.OBJECT_NAME,14,6) order by a.OBJECT_NAME desc ) 排名 From all_objects a where objEct_name like UPPER(''dw_call_imei_2%'') and a.OWNER=''ZIBO'' and length(a.OBJECT_NAME)=21 and (substr(a.OBJECT_NAME,14,8)>=to_char(sysdate-2,''yyyymmdd'') or substr(a.OBJECT_NAME,20,2) in (''28'',''29'',''30'',''31'')) order by a.OBJECT_NAME) a where 排名=1 '; EXECUTE IMMEDIATE (SQL_STRING); ---清空本月数据 SQL_STRING :=' delete shzc.gagx_sasz_hmmx_cbcd_cd a '; EXECUTE IMMEDIATE (SQL_STRING); commit; -----循环过程--遍历所有表名然后插入基础表,排除已导入过的 DECLARE CURSOR ZMLX IS select OBJECT_NAME from shzc.gagx_sasz_hmmx_cbcdc a where substr(a.OBJECT_NAME,14,6)>=to_char(add_months( to_date( v_monsr ,'yyyymm'),-2),'yyyymm') ; BEGIN OPEN ZMLX; LOOP FETCH ZMLX INTO V_ZMLX; EXIT WHEN ZMLX%NOTFOUND; SQL_STRING :=' insert into shzc.gagx_sasz_hmmx_cbcd_cd select distinct a.op_time,a.product_no,a.city_id,a.call_duration,a.call_counts,a.imei_tac,a.imei,a.first_time,a.last_time from zibo.'||V_ZMLX ||' a, shzc.gagx_sasz_hmmx_cbcdb b where a.product_no=b.servnumber and not exists ( select 1 from shzc.gagx_sasz_hmmx_cbcd_cd t where t.op_time=a.op_time and t.product_no=a.product_no and t.imei=a.imei) '; EXECUTE IMMEDIATE (SQL_STRING); commit; END LOOP; end; ------另一组经分下发 用户imei使用表 fan_drop_retable(upper('gagx_sasz_hmmx_cbcdd'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcdd as select a.OBJECT_NAME from all_objects a where objEct_name like UPPER(''dw_imei_user_2%'') and a.OWNER=''ZIBO'' and length(a.OBJECT_NAME)=19 '; EXECUTE IMMEDIATE (SQL_STRING); ---清空本月数据 SQL_STRING :=' delete shzc.gagx_sasz_hmmx_cbcd_bc a '; EXECUTE IMMEDIATE (SQL_STRING); commit; -----循环过程--遍历所有表名然后插入基础表,排除已导入过的 DECLARE CURSOR ZMLX IS select OBJECT_NAME from shzc.gagx_sasz_hmmx_cbcdd a where substr(a.OBJECT_NAME,14,6)>=to_char(add_months( to_date( v_monsr ,'yyyymm'),-2),'yyyymm') ; BEGIN OPEN ZMLX; LOOP FETCH ZMLX INTO V_ZMLX; EXIT WHEN ZMLX%NOTFOUND; SQL_STRING :='insert into shzc.gagx_sasz_hmmx_cbcd_bc select distinct a.op_time,a.user_id,a.product_no,a.city_id,substr(a.imei,1,8) imei_tac,a.imei,a.imei_get_date from zibo.'||V_ZMLX ||' a, shzc.gagx_sasz_hmmx_cbcdb b where a.user_id=to_char(b.subsid) and not exists ( select 1 from shzc.gagx_sasz_hmmx_cbcd_bc t where t.op_time=a.op_time and t.user_id=a.user_id and t.imei=a.imei) '; EXECUTE IMMEDIATE (SQL_STRING); commit; END LOOP; end; ----用户使用imei集合 fan_drop_retable(upper('gagx_sasz_hmmx_cbcd_bcd'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcd_bcd as select distinct a.op_time,to_char(b.subsid) subsid,a.product_no,a.imei_tac,substr(a.imei,1,14) imei, to_date(substr(a.first_time,1,10),''yyyy-mm-dd'') first_time from shzc.gagx_sasz_hmmx_cbcd_cd a, (select distinct 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.gagx_sasz_hmmx_cbcdb b where b.createdate is not null ) b where a.product_no=b.servnumber and a.op_time>=b.createdate and a.op_time<b.statusdate union all select a.op_time,a.user_id,a.product_no,a.imei_tac,substr(a.imei,1,14),a.imei_get_date from shzc.gagx_sasz_hmmx_cbcd_bc a where a.imei <>''0'' '; EXECUTE IMMEDIATE (SQL_STRING); fan_drop_retable(upper('gagx_sasz_hmmx_cbcde'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcde as select a.subsid,a.product_no,a.imei_tac,a.imei, count(distinct to_char(a.op_time,''yyyymm'')) op_times, min(a.first_time) first_time, min(a.op_time) op_time_min, max(a.op_time) op_time_max, row_number() over (partition by a.subsid order by max(a.op_time) desc ) 排名 from shzc.gagx_sasz_hmmx_cbcd_bcd a group by a.subsid,a.product_no,a.imei_tac,a.imei '; EXECUTE IMMEDIATE (SQL_STRING); ---清空本月数据 SQL_STRING :=' delete shzc.gagx_sasz_hmmx_cbcd_ycd a '; EXECUTE IMMEDIATE (SQL_STRING); commit; -----循环过程--遍历所有表名然后插入基础表,排除已导入过的 DECLARE CURSOR ZMLX IS select OBJECT_NAME from shzc.gagx_sasz_hmmx_cbcdc a where substr(a.OBJECT_NAME,14,6)>=to_char(add_months( to_date( v_monsr ,'yyyymm'),-2),'yyyymm') ; BEGIN OPEN ZMLX; LOOP FETCH ZMLX INTO V_ZMLX; EXIT WHEN ZMLX%NOTFOUND; SQL_STRING :='insert into shzc.gagx_sasz_hmmx_cbcd_ycd select distinct a.op_time,a.product_no,a.city_id,a.call_duration,a.call_counts,a.imei_tac,a.imei,a.first_time,a.last_time from zibo.'||V_ZMLX ||' a, (select distinct b.imei from shzc.gagx_sasz_hmmx_cbcde b ) b where substr(a.imei,1,14)=b.imei and not exists ( select * from shzc.gagx_sasz_hmmx_cbcd_ycd t where t.op_time=a.op_time and t.product_no=a.product_no and t.imei=a.imei) '; EXECUTE IMMEDIATE (SQL_STRING); commit; END LOOP; end; ---清空本月数据 SQL_STRING :=' delete shzc.gagx_sasz_hmmx_cbcd_ybc a '; EXECUTE IMMEDIATE (SQL_STRING); commit; -----循环过程--遍历所有表名然后插入基础表,排除已导入过的 DECLARE CURSOR ZMLX IS select OBJECT_NAME from shzc.gagx_sasz_hmmx_cbcdd a where substr(a.OBJECT_NAME,14,6)>=to_char(add_months( to_date( v_monsr ,'yyyymm'),-2),'yyyymm') ; BEGIN OPEN ZMLX; LOOP FETCH ZMLX INTO V_ZMLX; EXIT WHEN ZMLX%NOTFOUND; SQL_STRING :='insert into shzc.gagx_sasz_hmmx_cbcd_ybc select distinct a.op_time,a.user_id,a.product_no,a.city_id,substr(a.imei,1,8) imei_tac,a.imei,a.imei_get_date from zibo.'||V_ZMLX ||' a, (select distinct b.imei from shzc.gagx_sasz_hmmx_cbcde b ) b where substr(a.imei,1,14)=b.imei and not exists ( select 1 from shzc.gagx_sasz_hmmx_cbcd_ybc t where t.op_time=a.op_time and t.user_id=a.user_id and t.imei=a.imei) '; EXECUTE IMMEDIATE (SQL_STRING); commit; END LOOP; end; fan_drop_retable(upper('gagx_sasz_hmmx_cbcdf'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcdf as select distinct a.product_no from shzc.gagx_sasz_hmmx_cbcd_ycd a '; EXECUTE IMMEDIATE (SQL_STRING); fan_drop_retable(upper('gagx_sasz_hmmx_cbcdg'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcdg as select b.subsid,b.servnumber,b.prodid,b.createdate,b.registerorgid, b.ownerorgid,b.status,b.statusdate from zhyw.subscriber b, shzc.gagx_sasz_hmmx_cbcdf a where b.servnumber=a.product_no and b.status not in (''US26'',''US28'') '; EXECUTE IMMEDIATE (SQL_STRING); fan_drop_retable(upper('gagx_sasz_hmmx_cbcd_ybcd'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcd_ybcd as select distinct a.op_time,to_char(b.subsid) subsid,a.product_no,a.imei_tac,substr(a.imei,1,14) imei, to_date(substr(a.first_time,1,10),''yyyy-mm-dd'') first_time from shzc.gagx_sasz_hmmx_cbcd_ycd a, (select distinct 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.gagx_sasz_hmmx_cbcdg b where b.createdate is not null ) b where a.product_no=b.servnumber and a.op_time>=b.createdate and a.op_time<b.statusdate union all select a.op_time,to_char(b.subsid) user_id,a.product_no,a.imei_tac,substr(a.imei,1,14),a.imei_get_date from shzc.gagx_sasz_hmmx_cbcd_ybc a , (select distinct 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.gagx_sasz_hmmx_cbcdg b where b.createdate is not null ) b where a.product_no=b.servnumber and a.op_time>=b.createdate and a.op_time<b.statusdate and a.imei <>''0'' and a.user_id not like ''533%'' union all select a.op_time,a.user_id,a.product_no,a.imei_tac,substr(a.imei,1,14),a.imei_get_date from shzc.gagx_sasz_hmmx_cbcd_ybc a where a.imei <>''0'' and a.user_id like ''533%'' '; EXECUTE IMMEDIATE (SQL_STRING); fan_drop_retable(upper('gagx_sasz_hmmx_cbcd_ybcde'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcd_ybcde as select a.subsid,a.product_no,a.imei_tac,a.imei, count(distinct to_char(a.op_time,''yyyymm'')) op_times, min(a.first_time) first_time, min(a.op_time) op_time_min, max(a.op_time) op_time_max, row_number() over (partition by a.subsid order by max(a.op_time) desc ) 排名 from shzc.gagx_sasz_hmmx_cbcd_ybcd a group by a.subsid,a.product_no,a.imei_tac,a.imei '; EXECUTE IMMEDIATE (SQL_STRING); fan_drop_retable(upper('gagx_sasz_hmmx_cbcd_sjacd'),'SHZC'); SQL_STRING:='create table shzc.gagx_sasz_hmmx_cbcd_sjacd as select '''||p_userid||''' p_userid,sysdate in_time,'''||v_hm||''' 查询号码,'''||v_jbrq||''' 举报日期, '''||v_note||''' 备注信息,''涉案号码'' 号码类型, a.subsid,a.product_no,a.imei,a.op_times,a.first_time,a.op_time_max,a.排名 from shzc.gagx_sasz_hmmx_cbcde a union all select '''||p_userid||''' p_userid,sysdate in_time,'''||v_hm||''' 查询号码,'''||v_jbrq||''' 举报日期, '''||v_note||''' 备注信息,''延伸号码'' 号码类型, b.subsid,b.product_no,b.imei,b.op_times,b.first_time,b.op_time_max,b.排名 from shzc.gagx_sasz_hmmx_cbcd_ybcde b where not exists (select 1 from shzc.gagx_sasz_hmmx_cbcde a where a.subsid=b.subsid and a.imei=b.imei) '; EXECUTE IMMEDIATE (SQL_STRING); SQL_STRING:='insert into shzc.gagx_sasz_hmmx_cbcd_sjacd_bd select * from shzc.gagx_sasz_hmmx_cbcd_sjacd'; EXECUTE IMMEDIATE (SQL_STRING); commit; ---展示结果 v_sql:=' select * from shzc.gagx_sasz_hmmx_cbcd_sjacd ' ; else v_sql:=' select * from shzc.gagx_sasz_hmmx_cbcd_sjacd_bd a where a.p_userid='''||p_userid||''' and a.in_time in (select max(b.in_time) from shzc.gagx_sasz_hmmx_cbcd_sjacd_bd b ) ' ; end if; open p_cursor for v_sql; end ;