查看“分类:Oracle”的源代码

create or replace procedure p_tab_for_ogg
as
v_cols         varchar2(4000);
v_sql          varchar2(10000);
type           t_cursor    is ref cursor;
c_cursor       t_cursor;
v_id      number;
tab_record          tab%rowtype;
tab_for_ogg_record  tab%rowtype;
v_set_col      varchar2(10000);
begin
  select listagg(column_name, ', ') within group (order by column_id) into v_cols
  from  user_tab_cols t
  where t.table_name=upper('tab');

  -- a minus b  1 insert
  insert into tab_for_ogg
    select * from tab
    where id in (select id from tab
                      minus
                      select id from tab_for_ogg);
  commit;
  -- b minus a  0 delete
  delete from tab_for_ogg
    where id in (select id from tab_for_ogg
                      minus
                      select id from tab);
  commit;

  -- a intersect b  2
  v_sql :='(select id from tab intersect select id from tab_for_ogg)
             minus
            (select id from (select '||v_cols||' from tab
                                    intersect
                                  select '||v_cols||' from tab_for_ogg))
                                  ';

  --dbms_output.put_line(v_sql);
  open c_cursor for v_sql;
  loop
     fetch c_cursor into v_id;
     exit when c_cursor%notfound;

     select * into tab_record from tab
     where id=v_id;

     select * into tab_for_ogg_record from tab_for_ogg
     where id=v_id;

     --字段( 不支持变量 )
    /* for c in (select column_name from  user_tab_cols t where t.table_name=upper('tab'))
     loop
         --dbms_output.put_line(c.column_name);
         v_str := 'tab_record.%c%';
         v_str := replace(v_str,'%c%',c.column_name);
         execute immediate v_str into v1;
         dbms_output.put_line(v1);
     end loop;*/
     v_set_col := '';
     --字段
     if nvl(tab_record.id,0) != nvl(tab_for_ogg_record.id,0) then
        v_set_col := v_set_col||'id,';
     end if;

     if nvl(tab_record.COMP_ID,0) != nvl(tab_for_ogg_record.COMP_ID,0) then
        v_set_col := v_set_col||'COMP_ID,';
     end if;

     if nvl(tab_record.NAME,0) != nvl(tab_for_ogg_record.NAME,0) then
        v_set_col := v_set_col||'NAME,';
     end if;
     if nvl(tab_record.DESCA,0) != nvl(tab_for_ogg_record.DESCA,0) then
        v_set_col := v_set_col||'DESCA,';
     end if;
     if nvl(tab_record.PARENT_id,0) != nvl(tab_for_ogg_record.PARENT_id,0) then
        v_set_col := v_set_col||'PARENT_id,';
     end if;
     if nvl(tab_record.PARENT_idS,0) != nvl(tab_for_ogg_record.PARENT_idS,0) then
        v_set_col := v_set_col||'PARENT_idS,';
     end if;
     if nvl(tab_record.PARENT_NAMES,0) != nvl(tab_for_ogg_record.PARENT_NAMES,0) then
        v_set_col := v_set_col||'PARENT_NAMES,';
     end if;
     if nvl(tab_record.DTYPE,0) != nvl(tab_for_ogg_record.DTYPE,0) then
       v_set_col := v_set_col||'DTYPE,';
     end if;
     if nvl(tab_record.DEPT_STYPE,0) != nvl(tab_for_ogg_record.DEPT_STYPE,0) then
        v_set_col := v_set_col||'DEPT_STYPE,';
     end if;
     if nvl(tab_record.BTYPE,0) != nvl(tab_for_ogg_record.BTYPE,0) then
        v_set_col := v_set_col||'BTYPE,';
     end if;
     if nvl(tab_record.SUB,0) != nvl(tab_for_ogg_record.SUB,0) then
        v_set_col := v_set_col||'SUB,';
     end if;
     if nvl(tab_record.PSTORE,0) != nvl(tab_for_ogg_record.PSTORE,0) then
        v_set_col := v_set_col||'PSTORE,';
     end if;
     if nvl(tab_record.MUSID,0) != nvl(tab_for_ogg_record.MUSID,0) then
        v_set_col := v_set_col||'MUSID,';
     end if;
     if nvl(tab_record.MPSID,0) != nvl(tab_for_ogg_record.MPSID,0) then
        v_set_col := v_set_col||'MPSID,';
     end if;
     if nvl(tab_record.CD,0) != nvl(tab_for_ogg_record.CD,0) then
        v_set_col := v_set_col||'CD,';
     end if;
     if nvl(tab_record.DE_TYPE,0) != nvl(tab_for_ogg_record.DE_TYPE,0) then
        v_set_col := v_set_col||'DE_TYPE,';
     end if;
     if nvl(tab_record.EDT,sysdate) != nvl(tab_for_ogg_record.EDT,sysdate) then
        v_set_col := v_set_col||'EDT,';
     end if;
     if nvl(tab_record.STATUS,0) != nvl(tab_for_ogg_record.STATUS,0) then
        v_set_col := v_set_col||'STATUS,';
     end if;
     if nvl(tab_record.CT,sysdate) != nvl(tab_for_ogg_record.CT,sysdate) then
        v_set_col := v_set_col||'CT,';
     end if;
     if nvl(tab_record.UT,sysdate) != nvl(tab_for_ogg_record.UT,sysdate) then
        v_set_col := v_set_col||'UT,';
     end if;
     if nvl(tab_record.HR_DID,0) != nvl(tab_for_ogg_record.HR_DID,0) then
        v_set_col := v_set_col||'HR_DID,';
     end if;
     if nvl(tab_record.COMPANY,0) != nvl(tab_for_ogg_record.COMPANY,0) then
        v_set_col := v_set_col||'COMPANY,';
     end if;
     if nvl(tab_record.STYPE,0) != nvl(tab_for_ogg_record.STYPE,0) then
        v_set_col := v_set_col||'STYPE,';
     end if;
     if nvl(tab_record.IS_VTD,0) != nvl(tab_for_ogg_record.IS_VTD,0) then
        v_set_col := v_set_col||'IS_VTD,';
     end if;
     if nvl(tab_record.DB_TYPE,0) != nvl(tab_for_ogg_record.DB_TYPE,0) then
        v_set_col := v_set_col||'DB_TYPE,';
     end if;
     if nvl(tab_record.DBSUB_TYPE,0) != nvl(tab_for_ogg_record.DBSUB_TYPE,0) then
        v_set_col := v_set_col||'DBSUB_TYPE,';
     end if;
     if nvl(tab_record.UQC,0) != nvl(tab_for_ogg_record.UQC,0) then
        v_set_col := v_set_col||'UQC,';
     end if;
     if nvl(tab_record.LEAF,0) != nvl(tab_for_ogg_record.LEAF,0) then
        v_set_col := v_set_col||'LEAF,';
     end if;
     if nvl(tab_record.MD,0) != nvl(tab_for_ogg_record.MD,0) then
        v_set_col := v_set_col||'MD,';
     end if;
     if nvl(tab_record.SOURCE,0) != nvl(tab_for_ogg_record.SOURCE,0) then
        v_set_col := v_set_col||'SOURCE,';
     end if;
     if nvl(tab_record.STORE,0) != nvl(tab_for_ogg_record.STORE,0) then
        v_set_col := v_set_col||'STORE,';
     end if;
     if nvl(tab_record.PRE_PId,0) != nvl(tab_for_ogg_record.PRE_PId,0) then
        v_set_col := v_set_col||'PRE_PId,';
     end if;
     if nvl(tab_record.PRE_PIdS,0) != nvl(tab_for_ogg_record.PRE_PIdS,0) then
        v_set_col := v_set_col||'PRE_PIdS,';
     end if;
     if nvl(tab_record.PRE_PNAMES,0) != nvl(tab_for_ogg_record.PRE_PNAMES,0) then
        v_set_col := v_set_col||'PRE_PNAMES,';
     end if;

     v_set_col := trim(',' from v_set_col);

     --update
     if v_set_col is not null then
       v_sql:='update tab_for_ogg set ('||v_set_col||')=(select '||v_set_col||' from tab where id='||v_id||') where id='||v_id;
       execute immediate v_sql;
       commit;
     end if;

   end loop;
   close c_cursor;
exception
when others then
    rollback;
end;