oracle 差異表資料插入

create or replace procedure pro_check_data is
  v_objectId     varchar2(100);
  v_objectId1     varchar2(100);
  v_objectId2     varchar2(100);
  v_objectName   varchar2(100);
  v_relaObjectId varchar2(100);
  v_priObjectId  varchar2(100);
  v_Sql1         varchar2(1024);
  v_Sql2         varchar2(1024);
  v_Sql3         varchar2(1024);
  v_Sql4         varchar2(1024);
  v_sqlupdate    varchar2(1024);

  v_size integer;

  v_size1 integer;
  v_size2 integer;

  -- 找出差異資料複製
  Cursor cur_policy is
    select object_id, object_name
      from cat_sdv4.tbl_med_object
     order by object_id desc;
  curPolicyInfo cur_policy%rowtype;
  --更新資料引用
  Cursor cur_update is
    select distinct t.relate_obj_id, t.prima_obj_id
      FROM tbl_med_object_relation t
     order by t.relate_obj_id, t.prima_obj_id;
  curUpdateInfo cur_update%rowtype;

begin
  open cur_policy;
  Loop
    Fetch cur_policy
      into curPolicyInfo;
    Exit when cur_policy%notfound;
  
    v_objectId   := curPolicyInfo.object_id;
    v_objectName := curPolicyInfo.object_name;
    select count(*)
      into v_size
      from tbl_med_object
     where object_id = v_objectId
        or object_name = '' || v_objectName || '';
    if (v_size = 0) then
    
      v_Sql1 := 'insert into tbl_med_object SELECT t.* FROM cat_sdv4.tbl_med_object t where t.object_id=' ||
                v_objectId;
      v_Sql2 := 'insert into tbl_med_object_properties SELECT t.* FROM cat_sdv4.tbl_med_object_properties t where t.object_id=' ||
                v_objectId;
      v_Sql3 := 'insert into tbl_med_object_relation SELECT t.* FROM cat_sdv4.tbl_med_object_relation t where t.prima_obj_id=' ||
                v_objectId || ' or t.relate_obj_id=' || v_objectId;
      v_Sql4 := 'insert into tbl_med_rule_content SELECT t.* FROM cat_sdv4.tbl_med_rule_content t where t.object_id=' ||
                v_objectId;
      execute immediate v_Sql1;
      execute immediate v_Sql2;
      execute immediate v_Sql3;
      execute immediate v_Sql4;
      commit;
    
    end if;
  end loop;
  if cur_policy%isopen then
    --close cursor
    close cur_policy;
  end if;

  --對資料引用進行更新
  v_relaObjectId := '';

  open cur_update;
  Loop
    Fetch cur_update
      into curUpdateInfo;
    Exit when cur_update%notfound;
  
    v_relaObjectId := curUpdateInfo.Relate_Obj_Id;
    v_priObjectId  := curUpdateInfo.Prima_Obj_Id;
    select count(*)
      into v_size1
      from tbl_med_object
     where object_id = v_relaObjectId;
  
    select count(*)
      into v_size2
      from tbl_med_object
     where object_id = v_priObjectId;
  
    --不存在則需更新改資料引用
    if (v_size1 = 0) then
      --先去cat_sdv4使用者下查詢名稱
      --根據名稱查詢object_id
      select t.object_id
        into v_objectId1
        FROM tbl_med_object t
       where t.object_name =
             (select t.object_name
                FROM cat_sdv4.tbl_med_object t
               where t.object_id = v_relaObjectId);
      --更新引用
      v_sqlupdate := 'update tbl_med_object_relation t set t.relate_obj_id=' ||
                     v_objectId1 || ' where t.relate_obj_id=' ||
                     v_relaObjectId;
    end if;
  
    if (v_size2 = 0) then
      --先去cat_sdv4使用者下查詢名稱
      --根據名稱查詢object_id
      select t.object_id
        into v_objectId2
        FROM tbl_med_object t
       where t.object_name =
             (select t.object_name
                FROM cat_sdv4.tbl_med_object t
               where t.object_id = v_priObjectId);
      --更新引用
      v_sqlupdate := 'update tbl_med_object_relation t set t.prima_obj_id=' ||
                     v_objectId2 || ' where t.prima_obj_id=' ||
                     v_priObjectId;
    end if;
  
    execute immediate v_sqlupdate;
    commit;
  
  end Loop;
  if cur_update%isopen then
    --close cursor
    close cur_update;
  end if;
end pro_check_data;