Oracle如何将子查询结果存入变量供触发器调用

实现方案 #

你不需要单独将查询结果存入变量，触发器内可以直接绑定触发事件对应的当前行ID，通过INSERT INTO ... SELECT语法直接将匹配的行写入table3，性能更高也更简洁。

前置准备

确保table3的表结构和你要插入的table1字段结构匹配，若table3未创建可参考以下语句：

-- 示例：创建和table1结构一致的table3，可根据实际需求调整字段
create table table3
(
    ID integer,
    reg_number varchar(9),
    primary_number varchar(9),
    act varchar(1)
);

触发器实现（以PostgreSQL为例，MySQL/Oracle仅需修改行引用语法）

MySQL行引用语法和PostgreSQL一致，用NEW.id指代当前触发行ID，Oracle用:NEW.id。

1. 创建触发器函数

CREATE OR REPLACE FUNCTION sync_table1_to_table3()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO table3
  SELECT * FROM table1 WHERE id IN (
    WITH children AS (
      SELECT id pid, primary_number pan FROM table1
      WHERE id = NEW.id  -- 替换原固定ID为触发器当前触发行的ID
        AND primary_number IS NOT NULL
    ),
    prime AS (
      SELECT id pid FROM table1 p
      INNER JOIN children c ON c.pan = p.reg_number
    ),
    sibs AS (
      SELECT secondary_number sec FROM table2 c
      *-- 注意原查询中关联字段写的是person_id，你提供的table2实际字段为table1_id，请按实际修正*
      INNER JOIN prime ON prime.pid = c.table1_id
    ),
    sibids AS (
      SELECT id pid FROM table1 p
      INNER JOIN sibs s ON s.sec = p.reg_number
    )
    SELECT pid FROM children
    UNION
    SELECT pid FROM prime
    UNION
    SELECT pid FROM sibids
  )
  -- 避免重复插入数据，PostgreSQL加此行，MySQL可改为INSERT IGNORE INTO table3
  ON CONFLICT (ID) DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到table1的act字段变更事件

CREATE TRIGGER trg_after_table1_act_change
-- 仅在act字段插入/更新时触发
AFTER INSERT OR UPDATE OF act ON table1
FOR EACH ROW
EXECUTE FUNCTION sync_table1_to_table3();

如需临时存储结果集的方案

如果你确实需要临时存储关联ID做其他逻辑处理，可通过数组变量存储：

DECLARE
  -- 定义数组变量存储关联的table1 ID集合
  related_ids integer[];
BEGIN
  SELECT array_agg(pid) INTO related_ids FROM (
    WITH children AS (
      SELECT id pid, primary_number pan FROM table1
      WHERE id = NEW.id AND primary_number IS NOT NULL
    ),
    prime AS (
      SELECT id pid FROM table1 p INNER JOIN children c ON c.pan = p.reg_number
    ),
    sibs AS (
      SELECT secondary_number sec FROM table2 c INNER JOIN prime ON prime.pid = c.table1_id
    ),
    sibids AS (
      SELECT id pid FROM table1 p INNER JOIN sibs s ON s.sec = p.reg_number
    )
    SELECT pid FROM children UNION SELECT pid FROM prime UNION SELECT pid FROM sibids
  ) t;
  -- 后续可直接使用related_ids变量进行其他操作
END;

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