Oracle PL/SQL过程-在选择之间存储选择结果

4jb9z9bj 于 2023-10-16 发布在 Oracle

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

我有一个PL/SQL过程，仅用于测试。它检查选择结果之间的差异。问题是其中一个选择（SELECT A）需要很长时间来处理，其结果用于其他5个选择。所以它必须运行5次。
我对PL/SQL没有太多的经验。我想以某种方式存储（SELECT A）的结果，以便它只运行一次。我希望它能提高整个过程的性能。
所有选择（SELECT A、SELECT B、SELECT C、SELECT D）都是需要比较的3列行。

我不能使用 execute immediate。

当前代码看起来像这样（使用简化的SQL）：

create or replace package body TESTS AS
  res1 numeric;
  res2 numeric;
  res3 numeric;

  procedure compare_groups as

  begin
    for res in (
       select distinct id as user_id
       from users
    ) loop
      select count(*) into res1 from (
        (SELECT A where user_id = res.user_id) MINUS (SELECT B where user_id = res.user_id)
      );

      select count(*) into res2 from (
        (SELECT A where user_id = res.user_id) MINUS (SELECT C where user_id = res.user_id)
      );

      select count(*) into res3 from (
        (SELECT A where user_id = res.user_id) MINUS (SELECT D where user_id = res.user_id)
      );

      DBMS_OUTPUT.PUT_LINE('Res1: ' || res1);
      DBMS_OUTPUT.PUT_LINE('Res2: ' || res2);
      DBMS_OUTPUT.PUT_LINE('Res3: ' || res3);

    end loop;

  end compare_groups;

end TESTS;

oracle

来源：https://stackoverflow.com/questions/77016054/oracle-pl-sql-procedure-store-select-results-between-selects

1条答案

按热度按时间

axzmvihb1#

慢性创伤性脑炎呢？
这是一个简单的例子，我希望你能理解。

SQL> set serveroutput on
SQL> declare
  2    res1 numeric;
  3    res2 numeric;
  4    res3 numeric;
  5  begin
  6    for res in (select distinct user_id from t_users) loop
  7      with ta as
  8        (select * from a where user_id = res.user_id)
  9      select
 10        (select count(*) from (select * from ta
 11                               minus
 12                               select * from b where user_id = res.user_id
 13                              )
 14        ),
 15        (select count(*) from (select * from ta
 16                               minus
 17                               select * from c where user_id = res.user_id
 18                              )
 19        ),
 20        (select count(*) from (select * from ta
 21                               minus
 22                               select * from d where user_id = res.user_id
 23                              )
 24        )
 25      into res1, res2, res3
 26      from dual;
 27
 28      DBMS_OUTPUT.PUT_LINE('User ID = ' || res.user_id || ' --------------');
 29      DBMS_OUTPUT.PUT_LINE('Res1: ' || res1);
 30      DBMS_OUTPUT.PUT_LINE('Res2: ' || res2);
 31      DBMS_OUTPUT.PUT_LINE('Res3: ' || res3);
 32    end loop;
 33  end;
 34  /

输出量：

User ID = 99 --------------
Res1: 0
Res2: 0
Res3: 0
User ID = 10 --------------
Res1: 1
Res2: 3
Res3: 0
User ID = 20 --------------
Res1: 5
Res2: 2
Res3: 0
User ID = 30 --------------
Res1: 0
Res2: 0
Res3: 0
User ID = 40 --------------
Res1: 0
Res2: 0
Res3: 0

PL/SQL procedure successfully completed.

SQL>

赞(0）回复(0）举报 2023-10-16

我来回答

Oracle PL/SQL过程-在选择之间存储选择结果

1条答案

相关问题

热门标签

最新问答