数据库过程留档：设定传入参数，记录点击输入内容，对工单进行反馈显示

 

 

create or replace procedure biller533.shzc_202103_jh_zdyw_fxyjgc
(p_userid varchar2,p_lx varchar2,p_yjywlx varchar2, p_id varchar2, 
p_czyy varchar2,p_iswf varchar2,p_notes varchar2,p_qx varchar2,
p_cursor in out Results.ref_cursor_type)
as

v_sql             VARCHAR2(20000);
SQL_STRING VARCHAR2(20000);

v_lx             VARCHAR2(10);
v_yjywlx    VARCHAR2(15);
v_id          VARCHAR2(15);
v_yjywm    VARCHAR2(50);

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


v_czyy      VARCHAR2(500);
v_iswf      VARCHAR2(500);
v_notes    VARCHAR2(500);

begin

v_yjywlx  :=nvl(p_yjywlx,0); 
v_id  :=nvl(p_id,'0'); ---申请id null 处理
v_lx         :=trim(p_lx);

v_czyy        :=nvl(trim(p_czyy),'无');
v_iswf         :=nvl(trim(p_iswf),'无');
v_notes         :=nvl(trim(p_notes),'无');



  ---经分工号获取区县
select max(qx)  into v_qx_id 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 ;

select max(预警业务类型)  into v_yjywm from shzc.jh_zdyw_fxyj_yjlx where trim(xl)=v_yjywlx;



      fan_drop_retable(upper('jh_zdyw_fxyj_sjcda'),'SHZC');
      SQL_STRING:='create table shzc.jh_zdyw_fxyj_sjcda as
      select a.* from 
      (select a.区县,a.问题,nvl(b.预警业务类型,a.预警业务类型) 预警业务类型,e.xl 预警业务类型id,a.usid,a.in_time,
      zhyw.shzc_zfc_zftq_tscl(a.问题,''【'',''】'') 渠道名,nvl(d.区县,a.区县) 渠道区县,d.渠道编码,
      row_number() over (partition by a.区县,a.问题,nvl(b.预警业务类型,a.预警业务类型)  order by a.in_time  ) 排名 
      from shzc.jh_zdyw_fxyj_sjcd a ,
      shzc.jh_zdyw_fxyj_yjlx b,
      (select * from  (select d.*,row_number() over (partition by d.渠道编码  order by d.导入日期 desc ) 排名 
            from  SHZC.tmp_szc_201511_dlhz d ) d where d.排名=1 ) d,
      shzc.jh_zdyw_fxyj_yjlx e
      where a.区县<>''区县'' 
      and a.预警业务类型=to_char(b.xl(+))
      and nvl(b.预警业务类型,a.预警业务类型)=e.预警业务类型(+)
      and zhyw.shzc_zfc_zftq_tscl(a.问题,''【'',''】'') =d.渠道名称(+)) a
      where 排名=1 ' ;
      execute immediate (SQL_STRING);
      
        ----插入数据
      SQL_STRING:='insert into shzc.jh_zdyw_fxyj_sjcd_bd(区县,问题,预警业务类型,USID,IN_TIME,渠道名,渠道区县,渠道编码,预警业务类型id)
      select a.区县,问题,预警业务类型,USID,IN_TIME,渠道名,渠道区县,渠道编码,预警业务类型id
      from shzc.jh_zdyw_fxyj_sjcda a 
      where not exists (select 1 from shzc.jh_zdyw_fxyj_sjcd_bd t 
      where a.区县=t.区县 and a.问题=t.问题 and a.预警业务类型=t.预警业务类型 and a.in_time=t.in_time) ' ;
      execute immediate (SQL_STRING);
      commit;

        fan_drop_retable(upper('jh_zdyw_fxyj_sjcd_fkqk'),'SHZC');
        SQL_STRING:='create table SHZC.jh_zdyw_fxyj_sjcd_fkqk as
        select '''||p_userid||''' p_userid,'''||v_name||''' 申请人姓名,'''||v_sjhm||''' 申请人号码,'''||v_qx_id||''' 归属区县,
        '''||v_orgid||''' 组织机构,sysdate in_time,'''||v_lx||''' 操作类型,'''||v_yjywlx||''' 预警业务类型,'''||v_id||''' 选择id,
        '''||v_czyy||''' 查证原因, '''||v_iswf||''' 是否违规, '''||v_notes||''' 处罚办法, '''||v_yjywm||''' 预警业务类型名
         from dual  ' ;
        execute immediate (SQL_STRING);
        
        SQL_STRING:='insert into SHZC.jh_zdyw_fxyj_sjcd_fkqk_bd
        select a.* from SHZC.jh_zdyw_fxyj_sjcd_fkqk a  ' ;
        execute immediate (SQL_STRING);
        commit;


  --1	全部涉案数据
  --2	公安部涉案数据
  --3	工信部举报数据
  --4	工单涉及号码

  
  ---信息安全考核指标和清单报表
if v_lx='查看' then ---查看
   
             

             if v_yjywlx=0 then
                      if v_id='0' then
                              v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'',
                              ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法''
                               from dual union all 
                              select a.* from 
                              (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名,
                              b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法
                              from shzc.jh_zdyw_fxyj_sjcd_bd a,
                              SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b
                              where ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' )
                              and a.id=b.选择id(+)
                              order by a.id desc  ) a' ;
                       else
                              v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'',
                              ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法''
                               from dual union all 
                              select a.* from 
                              (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名,
                              b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法
                              from shzc.jh_zdyw_fxyj_sjcd_bd a,
                              SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b
                              where a.id='''||v_id||'''
                              and a.id=b.选择id(+)
                              and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' )
                              order by a.id desc  ) a' ;
                        end if ;  
              else
                        if v_id='0' then
                              v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'',
                              ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法''
                               from dual union all 
                              select a.* from 
                              (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名,
                              b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法
                              from shzc.jh_zdyw_fxyj_sjcd_bd a,
                              SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b
                              where a.预警业务类型='''||v_yjywm||'''
                              and a.id=b.选择id(+)
                              and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' )
                              order by a.id desc  ) a' ;
                         else
                              v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'',
                              ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法''
                               from dual union all 
                              select a.* from 
                              (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名,
                              b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法
                              from shzc.jh_zdyw_fxyj_sjcd_bd a,
                              SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b
                              where a.预警业务类型='''||v_yjywm||'''
                              and a.id=b.选择id(+)
                              and a.id='''||v_id||'''
                              and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' )
                              order by a.id desc  ) a' ;
                         end if ; 
              end if ;                  
elsif v_lx='反馈' then 
            if v_id<>'0' then  
                         
                         SQL_STRING:='insert into SHZC.jh_zdyw_fxyj_sjcd_fkqk_fbd
                         select a.* from SHZC.jh_zdyw_fxyj_sjcd_fkqk a  ' ;
                         execute immediate (SQL_STRING);
                         commit;
                         
                         fan_drop_retable(upper('jh_zdyw_fxyj_sjcd_fkqk_ebd'),'SHZC');
                         SQL_STRING:='create table  SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd as
                         select a.*,b.orgname from 
                         (select a.*,row_number() over (partition by a.选择id  order by a.in_time desc ) 排名 
                         from  SHZC.jh_zdyw_fxyj_sjcd_fkqk_fbd a ) a,
                         tbcs.organization@bcv b
                         where a.组织机构=b.orgid(+)
                         and a.排名=1 ' ;
                         execute immediate (SQL_STRING);
              
            end if;
                             
            if v_yjywlx=0 then
                      if v_id='0' then
                              v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'',
                              ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法''
                               from dual union all 
                              select a.* from 
                              (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名,
                              b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法
                              from shzc.jh_zdyw_fxyj_sjcd_bd a,
                              SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b
                              where ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' )
                              and a.id=b.选择id(+)
                              order by a.id desc  ) a' ;
                       else
                              v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'',
                              ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法''
                               from dual union all 
                              select a.* from 
                              (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名,
                              b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法
                              from shzc.jh_zdyw_fxyj_sjcd_bd a,
                              SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b
                              where a.id='''||v_id||'''
                              and a.id=b.选择id(+)
                              and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' )
                              order by a.id desc  ) a' ;
                        end if ;  
              else
                        if v_id='0' then
                              v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'',
                              ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法''
                               from dual union all 
                              select a.* from 
                              (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名,
                              b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法
                              from shzc.jh_zdyw_fxyj_sjcd_bd a,
                              SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b
                              where a.预警业务类型='''||v_yjywm||'''
                              and a.id=b.选择id(+)
                              and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' )
                              order by a.id desc  ) a' ;
                         else
                              v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'',
                              ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法''
                               from dual union all 
                              select a.* from 
                              (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名,
                              b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法
                              from shzc.jh_zdyw_fxyj_sjcd_bd a,
                              SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b
                              where a.预警业务类型='''||v_yjywm||'''
                              and a.id=b.选择id(+)
                              and a.id='''||v_id||'''
                              and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' )
                              order by a.id desc  ) a' ;
                         end if ; 
              end if ;  
                             
                              

   end if ;     
 
  open p_cursor for v_sql;

end ;