Oracle PL/SQL procedure - store select results between selects

Question

I have a PL/SQL procedure that is used for testing only. It checks for differences between select results. The problem is that one of the selects (SELECT A) takes a long time to process and its results are used in 5 other selects. So it has to run 5 times.

I don't have much experience with PL/SQL. I would like to somehow store results of (SELECT A) so that it has to run only once. I hope it increases performance of the whole procedure.

All selects (SELECT A, SELECT B, SELECT C, SELECT D) are rows of 3 columns that need to be compared.

I can't use execute immediate.

Currently the code looks like this (with simplified SQLs):

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;

Answer 1

How about CTE?

This is a dummy example, I hope you'll get the idea.

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  /

Output:

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>