Oracle在IN子句中使用变量

q35jwt9p 于 2023-02-15 发布在 Oracle

关注(0)|答案(1)|浏览(293)

以下查询将用作触发器内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
    --Not relevant
End Before Each Row;

AFTER EACH ROW IS
BEGIN
     --the data of tabletype gets populated
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN      
    --There are 10s of these

    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))); <--- long and less readable because there are 10s of these
    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子句中使用它？

oracle

来源：https://stackoverflow.com/questions/75392221/oracle-use-variable-in-in-clause

1条答案

按热度按时间

jyztefdp1#

一般来说你可以做到。

declare
   type rt_evnts_type is table of VARCHAR2(100);
   rt_events rt_evnts_type;
begin

   select RECEIPT_USER 
   bulk collect into rt_events 
   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 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 rt_events 
      );

end;

也可以使用表达式SELECT COLUMN_VALUE FROM TABLE(rt_events)
注意，我没有测试上面的代码。我不是100%肯定，也许你必须定义类型为数据库对象，以便使用它的SQL查询，即。

CREATE TYPE rt_evnts_type is table of VARCHAR2(100);

您可以回顾一下您的数据设计，为什么要创建rt的10个不同变量？
一个更通用的方法是：

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,
  rt_type NUMBER
);
Type rt_evnts_type Is Table Of r_evnts_type;
rt_evnts rt_evnts_type;

... 

   WHERE CONUS_ARRIVAL_EVENTID =ANY
      (select EVNTS_ID
       from TABLE(rt_evnts)
       where rt_type = 1) 

  FOR r in 1..5 LOOP
   ...
   WHERE CONUS_ARRIVAL_EVENTID =ANY
      (select EVNTS_ID
       from TABLE(rt_evnts)
       where rt_type = r) 
  END LOOP;

多重集运算符和多重集条件可以使您的生活更轻松。

赞(0）回复(0）举报 2023-02-15

我来回答

Oracle在IN子句中使用变量

1条答案

相关问题

热门标签

最新问答