这是我存储过程的写法:
create or replace procedure p_cdc_test
is
begin
for v_cur_cdc1 in (select * from cdc_test1 )
loop
IF v_cur_cdc1.operation$='I' THEN
insert into test_target(id,name,mark,startdate,enddate)
select t1.id,t1.name,t1.mark,t1.commit_timestamp$,to_date('9999-12-31','YYYY-MM-DD') from cdc_test1 t1 where t1.id=v_cur_cdc1.id;
elsIF v_cur_cdc1.operation$='D' THEN
update test_target t2
set t2.enddate=v_cur_cdc1.commit_timestamp$
where t2.id=v_cur_cdc1.id
and t2.enddate=to_date('9999-12-31','YYYY-MM-DD');
elsIF
v_cur_cdc1.operation$='UN' THEN
update test_target t3
set t3.enddate=v_cur_cdc1.commit_timestamp$
where t3.id=v_cur_cdc1.id
and t3.enddate=to_date('9999-12-31','YYYY-MM-DD');
insert into test_target(id,name,mark,startdate,enddate)
select t4.id,t4.name,t4.mark,t4.commit_timestamp$,to_date('9999-12-31','YYYY-MM-DD')
from cdc_test1 t4 where t4.id=v_cur_cdc1.id;
end if;
end loop;
commit;
end;
源表cdc_test1的数据如下:
执行存储过程后,目标表test_target的数据如下:
照理说目标表应该是4条记录,为什么会有9条记录?