求问各路大神！！！！！！！！

[Lishyer]

下面是我写的一段存储过程 是指每隔一段时间就要对op_m_test1表进行更新，写完可以通过，但是扩展性差，现在是只有5条数据，以后可能要上百条数据，有什么办法可以对其改善吗



create or replace procedure pro_op_m_test
as
tab1 dw2.dw2_etl_com@bi52%rowtype;
tab2 dw2.dw2_etl_com@bi52%rowtype;
tab3 dw2.dw2_etl_com@bi52%rowtype;
tab4 Itf2.Inter_Cfg@bi52%rowtype;
tab5 ST.BI_JOB_LIST@FJBIJS_ST%rowtype;
op_tab1 op_m_test1%rowtype;
op_tab2 op_m_test1%rowtype;
op_tab3 op_m_test1%rowtype;
op_tab4 op_m_test1%rowtype;
op_tab5 op_m_test1%rowtype;
begin
select * into op_tab1 from op_m_test1 where table_name='DW_SRV_4GCUUSER_DT';
select * into op_tab2 from op_m_test1 where table_name='DW_IS_MVUSER_DI';
select * into op_tab3 from op_m_test1 where table_name='DW_IS_ALUSER_DI';
select * into op_tab4 from op_m_test1 where table_name='ODS_2IMSG_MSGJH';
select * into op_tab5 from op_m_test1 where table_name='CELL_INFO';
select * into tab1
from dw2.dw2_etl_com@bi52 t where t.tablename='DW_SRV_4GCUUSER_DT';
select * into tab2
from dw2.dw2_etl_com@bi52 t where t.tablename='DW_IS_MVUSER_DI';
select * into tab3
from dw2.dw2_etl_com@bi52 t where t.tablename='DW_IS_ALUSER_DI';
select * into tab4
from Itf2.Inter_Cfg@bi52 T WHERE T.TABLENAME='ODS_2IMSG_MSGJH';
select * into tab5
from ST.BI_JOB_LIST@FJBIJS_ST T WHERE T.ITFDATE='20170911' AND SRCTABLEname='CELL_INFO';
if op_tab1.datafile_day=to_char(sysdate,'yyyymmdd')-1 and op_tab1.status=0 then
dbms_output.put_line('no update');
else
update op_m_test1 set stop_time=tab1.stoptime,
datafile_day=tab1.optime,
status=tab1.status,
status_name=(case tab1.status
when 0 then '加载成功'
when 1 then '正在抽取'
when -1 then '执行失败'
end)
where table_name='DW_SRV_4GCUUSER_DT';
insert into op_m_h_test select table_name,datafile_day,status,status_name,sysdate from op_m_test1 where table_name='DW_SRV_4GCUUSER_DT';
end if;
if op_tab2.datafile_day=to_char(sysdate,'yyyymmdd')-1 and op_tab2.status=0 then
dbms_output.put_line('no update');
else
dbms_output.put_line('this');
update op_m_test1 set stop_time=tab2.stoptime,
datafile_day=tab2.optime,
status=tab2.status,
status_name=(case tab2.status
when 0 then '加载成功'
when 1 then '正在抽取'
when -1 then '执行失败'
end)
where table_name='DW_IS_MVUSER_DI';
insert into op_m_h_test select table_name,datafile_day,status,status_name,sysdate from op_m_test1 where table_name='DW_IS_MVUSER_DI';
end if;
if op_tab3.datafile_day=to_char(sysdate,'yyyymmdd')-1 and op_tab3.status=0 then
dbms_output.put_line('no update');
else
update op_m_test1 set stop_time=tab3.stoptime,
datafile_day=tab3.optime,
status=tab3.status,
status_name=(case tab3.status
when 0 then '加载成功'
when 1 then '正在抽取'
when -1 then '执行失败'
end)
where table_name='DW_IS_ALUSER_DI';
insert into op_m_h_test select table_name,datafile_day,status,status_name,sysdate from op_m_test1 where table_name='DW_IS_ALUSER_DI';
end if;
if op_tab4.datafile_day=to_char(sysdate,'yyyymmdd')-1 and op_tab4.status=0 then
dbms_output.put_line('no update');
else
update op_m_test1 set stop_time=tab4.endtime,
datafile_day=tab4.itfdate,
status=tab4.loadyn,
status_name=(case tab4.loadyn
when 0 then '加载成功'
when 1 then '正在抽取'
when -1 then '执行失败'
end)
where table_name='ODS_2IMSG_MSGJH';
insert into op_m_h_test select table_name,datafile_day,status,status_name,sysdate from op_m_test1 where table_name='ODS_2IMSG_MSGJH';
end if;
if op_tab5.datafile_day=to_char(sysdate,'yyyymmdd')-1 and op_tab5.status=0 then
dbms_output.put_line('no update');
else
update op_m_test1 set stop_time=tab5.endtime,
datafile_day=tab5.itfdate,
status=tab5.status,
status_name=(case tab5.status
when 0 then '加载成功'
when 1 then '正在抽取'
when -1 then '执行失败'
end)
where table_name='CELL_INFO';
insert into op_m_h_test select table_name,datafile_day,status,status_name,sysdate from op_m_test1 where table_name='CELL_INFO';
end if;
commit;
end pro_op_m_test;

[Lishyer]

op_m_test1的数据内容是根据3张不同的表 抽取出的数据 每天根据这3张表对op_m_test1进行更新

[碧水幽幽泉]

你的存储过程可以优化如下：

create or replace procedure pro_op_m_test
as 
begin  
   for rec in(select table_name,datafile_day,status
                from op_m_test1 
			   where table_name in ('DW_SRV_4GCUUSER_DT','DW_IS_MVUSER_DI','DW_IS_ALUSER_DI','ODS_2IMSG_MSGJH','CELL_INFO')
			 ) loop
	   if (rec.datafile_day = to_char(trunc(sysdate-1),'yyyymmdd') and rec.status = 0 then 
		  dbms_output.put_line('no update');
		
	   else 
		   update op_m_test1 
			  set (stop_time,optime,status,status_name) = (select stop_time,optime,status,
																  decode(status,0,'加载成功',1,'正在抽取','执行失败') status_name
															 from dw2.dw2_etl_com@bi52
															where table_name = rec.table_name
														   )
		   where exists (select null from dw2.dw2_etl_com@bi52 where table_name = rec.table_name);
		   
		   insert into op_m_h_test select table_name,datafile_day,status,status_name,sysdate from op_m_test1 where table_name = rec.table_name;
		end if;
   end loop;
   
   commit;

end pro_op_m_test;

[碧水幽幽泉]

以后如何要扩展的话，改起来也非常方便。 只要把

select table_name,datafile_day,status
from op_m_test1 
  where table_name in ('DW_SRV_4GCUUSER_DT','DW_IS_MVUSER_DI','DW_IS_ALUSER_DI','ODS_2IMSG_MSGJH','CELL_INFO')

改成：

select table_name,datafile_day,status
from op_m_test1 
  where table_name in (关于table_name的子查询)