具有额外列到%ROWTYPE列的集合(比较两个数据集的双向减号)

Question

我正在寻找一种创建集合的方法，该集合将有一个额外的列，源表/视图列由%ROWTYPE引用，以简化。

TYPE my_type IS RECORD ( mode varchar2(10), some_table%ROWTYPE );

目标:

我想用双向减号操作比较一个表和它的源视图，并读取源视图一次:

select * from (
select 'INSERT' as mode, a.* from (
select ... from aView
MINUS
select ... from aTable ) a
UNION ALL
select 'DELETE' as mode, b.* from (
select ... from aTable
MINUS
select ... from aView) b
)

我开始考虑为INSERT ALL执行的INSERT和DELETE模式创建全局临时表(gtt)，但我有25个表，因此将添加50个gtt:-/

INSERT ALL
WHEN mode=INSERT THEN INTO inserts_GTT01_tbl VALUES(...)
WHEN mode=DELETE THEN INTO deletes_GTT01_tbl VALUES(...)
SELECT * FROM two-way-minus-view

Answer 1

我希望我正确理解了你的困境，尽管我可能会错过一些东西。如果我正确理解了你的想法，你想在一组表上操作，并基于负双向生成要删除的记录和要插入的记录。

我将使用全局临时表，使用commit on preserve rows，但我会将代码集中在procedure

首先是控制表

create table control_process 
( id_time timestamp default systimestamp , 
source_table varchar2(128), 
source_view varchar2(128), 
rows_processed number, 
exit_code number, 
error_message varchar2(400) 
); 

使用输入参数表和视图的gtt程序

根据你的需要调整下面的代码是非常重要的:

• 如果表和视图中的列不相同，则必须调整GTT创建后的查询以使用必要的列。如果要动态地执行，则必须创建另一个游标来匹配作为输入参数的表和视图之间的列。
为了删除/插入，您必须将GTT与原始表进行比较。这就是为什么我使用游标和列来创建动态结构，用于插入和删除。
• 我没有一个地方来验证代码，所以请注意，它可能是打字错误或错误。
• 控制表是可选的，但是允许你有一个地方来存储每个表/视图的结果
• 这个解决方案允许你在一个地方使用所有的表和视图。
• 的materialize提示将帮助您在您阅读一次视图和表中的负双向构建。

话虽如此，这可能是一种方法(您必须使其适应您的需求)

create or replace procedure pr_generate_rows ( psourcetable in varchar2 , psourceview in varchar2 ) 
is 
vddl clob;
vdml clob;
vsql clob;
vcode pls_integer;
verrm varchar2(300);
out_string varchar2(128);
cursor c_tab_columns 
is 
select column_name, count(*) over () tot_rows 
from all_tab_columns where table_name = psourcetable and owner = 'MY_SCHEMA'
order by column_id;
begin
vddl := ' create global temporary table gtt_'||psourcetable||' on commit preserve rows
as
with x 
as 
( select /*+materialize */ * from '||psourceview||' 
), 
y as 
( select /*+materialize */ * from '||psourcetable||' 
)
select * from (
select ''INSERT'' as mode, a.* from 
(
select ... from x
MINUS
select ... from y 
) a
UNION ALL
select ''DELETE'' as mode, b.* from (
select ... from y
MINUS
select ... from x ) b
)
' ;
execute immediate vddl;

vdml := ' insert into control_process ( source_table, source_view , rows_processed , exit_code )
select '''||psourcetable||''' as source_table , 
'''||psourceview||'''  as source_view , 
( select count(*) from gtt_'||psourcetable||' ) as rows_processed , 
0 as exit_code from dual
' ;

execute immediate vdml ;

-- Perform Insert and Delete over final table 

-- Delete 

vsql := ' delete from '||psourcetable||' 
where exists 
( select 1 from gtt_'||psourcetable||' a join '||psourcetable||' b 
on ( ';
for item in c_tab_columns
loop
out_string := item.COLUMN_NAME;
if  c_tab_columns%rowcount = 1 
then
vexpression := ' a.'||out_string||' = b.'||out_string||' and ' ;
dbms_lob.append(vsql,vexpression);
dbms_lob.append(vsql,''||chr(10)||'');
elsif c_tab_columns%rowcount < item.tot_rows then
vexpression := ' a.'||out_string||' = b.'||out_string||' and ' ;
dbms_lob.append(vsql,vexpression);
dbms_lob.append(vsql,''||chr(10)||'');
else
vexpression := ' a.'||out_string||' = b.'||out_string||' ' ;
dbms_lob.append(vsql,vexpression);
dbms_lob.append(vsql,''||chr(10)||'');      
end if; 
end loop; 
dbms_lob.append ( vsql, ')' );  
dbms_lob.append(vsql,''||chr(10)||'');      
dbms_lob.append ( vsql, ' where a.mode = ''DELETE'' '); 
dbms_lob.append ( vsql, ' ) '); 

execute immediate vsql;

-- Insert 

vsql := ' insert /*+ append */ into '||psourcetable||' a 
select ' ;
for item in c_tab_columns
loop
out_string := item.COLUMN_NAME;
if  c_tab_columns%rowcount = 1 
then
vexpression := ' b.'||out_string||' , ' ;
dbms_lob.append(vsql,vexpression);
dbms_lob.append(vsql,''||chr(10)||'');
elsif c_tab_columns%rowcount < item.tot_rows then
vexpression := ' b.'||out_string||' , ' ;
dbms_lob.append(vsql,vexpression);
dbms_lob.append(vsql,''||chr(10)||'');
else
vexpression := ' b.'||out_string||' ' ;
dbms_lob.append(vsql,vexpression);
dbms_lob.append(vsql,''||chr(10)||'');      
end if; 
end loop; 

dbms_lob.append(vsql,'from gtt_'||psourcetable||' b where b.mode = ''INSERT'' '); 

execute immediate vsql;

exception 
when others then 
vcode := sqlcode;
verrm := substr(sqlerrm, 1, 300);
vdml   := ' insert into control_process ( source_table, source_view , rows_processed , exit_code , error_message  ) 
values ( 
'''||psourcetable||''' , 
'''||psourceview||'''  , 
0, 
vcode, 
'''||verrm||'''
) ' ;

execute immediate vdml;
commit;
raise;
end;
/

有任何问题，请告诉我。