集合,该集合具有%ROWTYPE列的附加列(双向减号比较两个数据集)

我希望我正确地理解了你的困境,尽管我可能会错过一些东西。如果我没弄错的话,你需要对一组表进行操作,并根据减号双向生成要删除的记录和要插入的记录。

我会选择全局临时表,使用 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;
/

如果有任何问题,请告诉我。