基于%ROWTYPE扩展集合实现双向MINUS数据集比对方案咨询

最优实现方案（按优先级从高到低排列） #

1. PL/SQL集合内存处理（零额外表创建，满足只读一次视图要求）

你已经定义了带mode的扩展RECORD类型，直接基于该类型创建嵌套表集合，无需创建任何GTT即可完成数据比对和拆分：

-- 定义扩展行类型和集合类型
TYPE my_type IS RECORD ( mode varchar2(10), row_data some_table%ROWTYPE );
TYPE my_type_tab IS TABLE OF my_type;

l_diff_data my_type_tab;
BEGIN
  -- 用FULL OUTER JOIN替代双向MINUS，仅读取一次aView、一次aTable，性能更优
  SELECT 
    CASE WHEN a.主键列 IS NULL THEN 'INSERT' ELSE 'DELETE' END,
    CASE WHEN a.主键列 IS NULL THEN 
      -- 视图行转目标表%ROWTYPE
      CAST(MULTISET(SELECT b.* FROM DUAL) AS some_table%ROWTYPE)
    ELSE 
      CAST(MULTISET(SELECT a.* FROM DUAL) AS some_table%ROWTYPE)
    END
  BULK COLLECT INTO l_diff_data
  FROM aTable a
  FULL OUTER JOIN aView b ON a.主键列 = b.主键列
  WHERE a.主键列 IS NULL OR b.主键列 IS NULL;

  -- 直接在内存中拆分INSERT/DELETE行处理，无需落地到临时表
  FOR i IN 1..l_diff_data.COUNT LOOP
    IF l_diff_data(i).mode = 'INSERT' THEN
      -- 执行插入逻辑
      INSERT INTO aTable VALUES l_diff_data(i).row_data;
    ELSE
      -- 执行删除逻辑
      DELETE FROM aTable WHERE 主键列 = l_diff_data(i).row_data.主键列;
    END IF;
  END LOOP;
END;
/

如果要严格保留MINUS比对逻辑（比对全字段而非仅主键），可以把视图结果先批量收集到单独的aView%ROWTYPE集合中，再用集合操作符MULTISET EXCEPT做双向比对，全程仅读取一次视图。

2. 通用ANYDATA类型GTT方案（仅需2张全局临时表适配所有25张表）

无需为每张表创建单独的GTT，预定义2张通用GTT即可适配所有表的变更存储：

-- 预创建通用GTT，所有表复用
CREATE GLOBAL TEMPORARY TABLE gtt_insert_diff (
  op_mode VARCHAR2(10),
  row_data ANYDATA
) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE gtt_delete_diff (
  op_mode VARCHAR2(10),
  row_data ANYDATA
) ON COMMIT DELETE ROWS;

插入时将行数据转换为ANYDATA类型存储，读取时再转换为对应表的%ROWTYPE即可：

-- 插入示例
INSERT ALL
WHEN mode='INSERT' THEN INTO gtt_insert_diff VALUES(mode, ANYDATA.CONVERTOBJECT(row_data))
WHEN mode='DELETE' THEN INTO gtt_delete_diff VALUES(mode, ANYDATA.CONVERTOBJECT(row_data))
SELECT * FROM 双向比对视图;

-- 读取示例
DECLARE
  l_row some_table%ROWTYPE;
  l_any ANYDATA;
BEGIN
  SELECT row_data INTO l_any FROM gtt_insert_diff WHERE ROWNUM=1;
  -- 转换为目标表行类型
  IF l_any.GETOBJECT(l_row) = DBMS_TYPES.SUCCESS THEN
    -- 处理行数据
    NULL;
  END IF;
END;
/

3. 动态私有临时表方案（Oracle 18c+支持，零预定义表）

18c及以上版本支持会话级私有临时表，无需提前预定义任何临时表，执行比对逻辑时动态创建对应表结构的临时表即可，执行结束自动清理，完全无需维护50张预定义GTT。

内容的提问来源于stack exchange，提问作者Jakub P