ORACLE的存储过程可以执行成功 可是表中的数据没有发生变化
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;
tab op_m_test1%rowtype;
begin
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 tab1.optime=to_char(sysdate,'yyyymmdd')-1 and tab1.status!=0 then
update op_m_test1 set stop_time=tab1.stoptime,
datafile_day=tab1.optime,
status=tab1.status,
status_name=(case tab1.status
when 0 then 'zz成功'
when 1 then '正在抽取'
when -1 then '执行失败'
end)
where table_name='DW_SRV_4GCUUSER_DT';
end if;
if sql%found then
DBMS_OUTPUT.PUT_LINE('this is successful!');
end if;
end pro_op_m_test;