Oracle环境下避免track_table重复插入的技术方案咨询

嘿，刚好我之前处理过类似的Oracle数据导入追踪需求，给你几个不用逐字段比对的高效方案，按需选就行：

这个方案适合数据量较大的场景，效率很高，核心思路是先筛选出临时表中不存在于data_table的TID，执行MERGE完成插入/更新后，批量把这些新TID的记录插入track_table。

首先假设你已经把文件数据导入到临时表temp_table（这一步你现有系统应该已经实现了），然后用以下PL/SQL块：

DECLARE
  -- 定义存储TID的集合类型
  TYPE tid_collection IS TABLE OF data_table.TID%TYPE;
  new_tid_list tid_collection;
BEGIN
  -- 第一步：提前筛选出所有需要插入的新TID
  SELECT tt.TID
  BULK COLLECT INTO new_tid_list
  FROM temp_table tt
  WHERE NOT EXISTS (
    SELECT 1 FROM data_table dt 
    WHERE dt.TID = tt.TID
  );

  -- 第二步：执行MERGE完成数据的插入/更新
  MERGE INTO data_table dt
  USING temp_table tt
  ON (dt.TID = tt.TID)
  WHEN MATCHED THEN
    UPDATE SET 
      dt.col1 = tt.col1,  -- 替换成你实际需要更新的字段
      dt.col2 = tt.col2
  WHEN NOT MATCHED THEN
    INSERT (TID, col1, col2)  -- 替换成你的目标表字段
    VALUES (tt.TID, tt.col1, tt.col2);

  -- 第三步：批量插入追踪记录，只针对新插入的TID
  FORALL i IN 1..new_tid_list.COUNT
    INSERT INTO track_table (DATE, TID, TEXT)
    VALUES (SYSDATE, new_tid_list(i), '新记录导入插入');
END;
/

这个方法的好处是全程批量操作，避免逐行处理的性能损耗，而且只通过TID判断是否需要追踪，完全不用比对其他字段。

如果不想修改现有存储过程的逻辑，这个方案最省心——给data_table加一个插入触发器，只有当新记录插入时才自动向track_table写入追踪信息，更新操作不会触发。

创建触发器的代码如下：

CREATE OR REPLACE TRIGGER trg_data_table_insert_track
AFTER INSERT ON data_table
FOR EACH ROW
BEGIN
  -- 插入追踪记录，TEXT字段可以根据需求自定义内容
  INSERT INTO track_table (DATE, TID, TEXT)
  VALUES (SYSDATE, :NEW.TID, '新记录导入插入');
END;
/

⚠️ 注意：如果除了这个导入系统之外，还有其他操作会向data_table插入数据，这些操作也会触发这个触发器写入追踪记录。如果需要只追踪导入系统的操作，可以给data_table加一个额外的标识字段（比如import_flag），在导入时设为Y，触发器里判断:NEW.import_flag = 'Y'再插入追踪记录。

如果你的导入数据量很小，逐行处理的性能可以接受，也可以用这个更直观的逻辑：对临时表的每条记录，先判断TID是否存在于data_table，不存在则同时插入data_table和track_table，存在则只更新。

DECLARE
  v_tid_exists NUMBER;
BEGIN
  FOR rec IN (SELECT * FROM temp_table) LOOP
    -- 判断当前TID是否已存在
    SELECT COUNT(1) INTO v_tid_exists 
    FROM data_table 
    WHERE TID = rec.TID;

    IF v_tid_exists = 0 THEN
      -- 不存在则插入数据+追踪记录
      INSERT INTO data_table (TID, col1, col2)
      VALUES (rec.TID, rec.col1, rec.col2);
      
      INSERT INTO track_table (DATE, TID, TEXT)
      VALUES (SYSDATE, rec.TID, '新记录导入插入');
    ELSE
      -- 存在则只更新数据
      UPDATE data_table 
      SET col1 = rec.col1, col2 = rec.col2
      WHERE TID = rec.TID;
    END IF;
  END LOOP;
END;
/

这个方案逻辑简单，容易调试，但数据量大时性能不如批量操作的方案1。

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