Oracle触发器中如何将子查询结果存入变量复用在IN子句

以下查询是触发器中IN子句的子查询：

select RECEIPT_USER from ABCD.GENERIC_FF_EVNT_LAST
WHERE RECEIPT_USER is not null
group by RECEIPT_USER
having max(load_Date) > add_months(SYSDATE,-48)

现有触发器简化代码 #

create or replace TRIGGER ABCD.T_EVNTS_UPSERT
FOR INSERT OR UPDATE ON ABCD.EVNTS
COMPOUND TRIGGER

  Type r_evnts_type Is Record (
      shpmt_unts_id  ABCD.evnts.shpmt_unts_id%Type,
      evnts_id       ABCD.evnts.evnts_id%Type,
      evnt_date      ABCD.evnts.evnt_date%Type,
      last_updt_user ABCD.evnts.db_rw_last_updt_usr%Type
  );
  Type rt_evnts_type Is Table Of r_evnts_type Index By Pls_Integer;
  --v_USER_LIST ABCD.GENERIC_FF_EVNT_LAST.RECEIPT_USER%TYPE;
  
  i            Pls_integer;
  rt_1         rt_evnts_type;  
  rt_2         rt_evnts_type;
  rt_3         rt_evnts_type;
  rt_4         rt_evnts_type;
  rt_5         rt_evnts_type;
  rt_6         rt_evnts_type;
  rt_7         rt_evnts_type;
  rt_8         rt_evnts_type;
  rt_9         rt_evnts_type;
  rt_10        rt_evnts_type;

Before Each Row Is
Begin
    --无关代码
End Before Each Row;


AFTER EACH ROW IS
BEGIN
     --表类型数据已填充
END AFTER EACH ROW;


AFTER STATEMENT IS
BEGIN      
    --此类代码有数十处

    If (rt_1.Exists(1)) Then
    ForAll i In 1 .. rt_1.Last
        UPDATE ABCD.SHPMT_UNTS SU
        SET SU.CONUS_ARRIVAL_DT = rt_1(i).EVNT_DATE,
            SU.CONUS_ARRIVAL_EVENTID = rt_1(i).EVNTS_ID,
            SU.CONUS_FLAG = '1'
        WHERE SU.SHPMT_UNTS_ID = rt_1(i).SHPMT_UNTS_ID
          AND (SU.CONUS_DEPARTURE_EVENTID IS NULL or rt_1(i).last_updt_user in
          (select RECEIPT_USER from ABCD.GENERIC_FF_EVNT_LAST
            WHERE RECEIPT_USER is not null
            group by RECEIPT_USER
            having max(load_Date) > add_months(SYSDATE,-48))); <--- 该子查询重复出现数十次，代码冗长且可读性差
    End If;

    ---If (rt_2.Exists(1)) Then
    ---If (rt_3.Exists(1)) Then

End After Statement;
    
END t_evnts_upsert;

我希望将该子查询的结果存入某个变量/游标中，然后在IN子句中复用，避免重复执行此子查询。

已尝试方案 #

方法1：使用游标 #

定义游标：

cursor user_list is
select RECEIPT_USER from ABCD.GENERIC_FF_EVNT_LAST
WHERE RECEIPT_USER is not null
group by RECEIPT_USER
having max(load_Date) > add_months(SYSDATE,-48);

尝试在WHERE子句中使用：

WHERE SU.SHPMT_UNTS_ID = rt_conus_ar(i).SHPMT_UNTS_ID
      AND (SU.CONUS_DEPARTURE_EVENTID IS NULL or rt_conus_ar(i).last_updt_user in user_list.RECEIPT_USER)

此方法无效。

方法2：存入单个变量 #

定义变量：

v_USER_LIST ABCD.GENERIC_FF_EVNT_LAST.RECEIPT_USER%TYPE;

然后执行Select INTO v_USER_LIST.......，但该方案也无效。

请问是否有办法将子查询结果存入某种变量中，并在IN子句中使用？

你需要使用PL/SQL集合类型存储子查询的多行结果，然后在SQL语句中通过MEMBER OF操作符或TABLE()函数引用这个集合，具体步骤如下：

1. 定义集合类型 #

在触发器的声明部分，定义与RECEIPT_USER类型匹配的集合：

-- 定义用户集合类型，直接匹配原字段类型
TYPE t_user_list IS TABLE OF ABCD.GENERIC_FF_EVNT_LAST.RECEIPT_USER%TYPE;
-- 声明集合变量
v_user_list t_user_list;

2. 批量加载数据到集合 #

在AFTER STATEMENT块的最开头，一次性将子查询结果存入集合：

AFTER STATEMENT IS
BEGIN      
    -- 一次性加载用户列表到集合
    SELECT RECEIPT_USER
    BULK COLLECT INTO v_user_list
    FROM ABCD.GENERIC_FF_EVNT_LAST
    WHERE RECEIPT_USER IS NOT NULL
    GROUP BY RECEIPT_USER
    HAVING MAX(load_Date) > ADD_MONTHS(SYSDATE, -48);

    -- 复用集合的UPDATE代码
    If (rt_1.Exists(1)) Then
    ForAll i In 1 .. rt_1.Last
        UPDATE ABCD.SHPMT_UNTS SU
        SET SU.CONUS_ARRIVAL_DT = rt_1(i).EVNT_DATE,
            SU.CONUS_ARRIVAL_EVENTID = rt_1(i).EVNTS_ID,
            SU.CONUS_FLAG = '1'
        WHERE SU.SHPMT_UNTS_ID = rt_1(i).SHPMT_UNTS_ID
          AND (SU.CONUS_DEPARTURE_EVENTID IS NULL 
               OR rt_1(i).last_updt_user MEMBER OF v_user_list); -- 用MEMBER OF判断归属
    End If;

    -- 其他数十处类似代码都可替换为上述写法
    -- If (rt_2.Exists(1)) Then
    -- ...同样使用v_user_list...

End After Statement;

3. 替代写法：使用TABLE()函数 #

如果MEMBER OF不符合需求，也可以用IN结合TABLE()函数：

AND (SU.CONUS_DEPARTURE_EVENTID IS NULL 
     OR rt_1(i).last_updt_user IN (SELECT column_value FROM TABLE(v_user_list)))

注意事项 #

• 若Oracle版本低于11g，需先在数据库级别创建集合类型，再在触发器中引用：

  CREATE OR REPLACE TYPE t_user_list IS TABLE OF VARCHAR2(100); -- 匹配RECEIPT_USER的实际类型
  

• BULK COLLECT INTO可以高效批量加载多行数据，避免逐行处理的性能损耗。

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