分类:Oracle：修订间差异

来自牛奶河Wiki
跳到导航 跳到搜索
无编辑摘要
（清空全部内容）
标签：清空 手工回退
 
第1行： 第1行：
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;

2024年9月29日 (日) 09:52的最新版本

分类“Oracle”中的页面

以下5个页面属于本分类，共5个页面。

A

O

取自“https://mwbbs.eu.org/wiki/index.php?title=分类:Oracle&oldid=2762”