使用存储过程读取源表数据到目标表
源表cdc_test
目标表:test_source
select operation$,commit_timestamp$,id,name,mark from cdc_test
查询到一条数据:
OP COMMIT_TIMESTA ID NAME
-- -------------- ---------- ------------------------------
MARK
--------------------------------------------------
I 05-3月 -18 4 renqinglei4
aa4
存储过程的写法:
create or replace procedure A
is
begin
IF cdc_test.operation$='I' THEN
insert into test_source(id,name,mark,statrdate,enddate)
select t1.id,t1.name,t1.mark,t1.commit_timestamp$,'31-12月-9999' from cdc_test t1;
end if;
IF cdc_test.operation$='D' THEN
update test_source
set enddate=cdc_test.commit_timestamp$
where id=cdc_test.id
and cdc_test.operation$='D';
end if;
IF
cdc_test.operation$='UN' THEN
update test_source
set enddate=cdc_test.commit_timestamp$
where id=cdc_test.id
and enddate='31-12月-9999'
end if;
IF
cdc_test.operation$='UN' THEN
insert into test_source(id,name,mark,Startdate,enddate)
select t1.id,t1.name,t1.mark,t1.commit_timestamp$,'31-12月-9999' from cdc_test t1
end if;
COMMIT;
end;
执行存储过程无法读取源表的数据到目标表,请问是为什么?