PL/SQL - кэширование двух наборов результатов в коллекции и объединение их вместе?

У меня есть две очень большие таблицы, и мне нужно обработать небольшой набор результатов из этих таблиц. Однако обработка выполняется в нескольких функциях, каждая из которых должна выполнить некоторое объединение, чтобы правильно отформатировать данные.

Мне определенно нужно как-то кэшировать исходный набор результатов, чтобы его можно было повторно использовать в функциях. Что я хотел бы сделать, так это поместить первый набор результатов в одну коллекцию, второй набор результатов в другую коллекцию, а затем манипулировать этими коллекциями с помощью запросов SQL, как если бы они были настоящими таблицами SQL.

Можете подсказать, как это можно сделать?


sql database collections oracle plsql
person Dario    schedule 24.02.2010    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Вам нужно создать тип уровня схемы (не внутри пакета) в виде вложенной таблицы. Вы можете заполнить их, а затем использовать в своих запросах как обычные таблицы, используя оператор "table()".

Эта ссылка объясняет, что вам нужно. Быстрый пример

create type foo as table of number;-- or a record type, data%rowtype, whatever
...
myfoo1 foo := foo (1,2,3);
myfoo2 foo := foo(3,4,5)

select column_value
into bar
from table(foo1) join table(foo2) using (column_value)
person Samuel    schedule 24.02.2010

arrow_upward
5
arrow_downward

Звучит как работа для временных таблиц:

CREATE GLOBAL TEMPORARY TABLE table_name (...) ON ...

У ON есть два варианта с разными последствиями:

  1. ON COMMIT DELETE ROWS указывает, что временная таблица будет специфичной для транзакции. Данные сохраняются в таблице до момента окончания транзакции. Если вы завершаете транзакцию, база данных усекает таблицу (удаляет все строки). Предположим, если вы выполните коммит или запустите ddl, данные во временной таблице будут потеряны. Это опция по умолчанию.

  2. ON COMMIT PRESERVE ROWS указывает, что временная таблица будет зависеть от сеанса. Данные сохраняются в таблице до момента окончания сеанса. Если вы завершаете сеанс, база данных усекает таблицу (удаляет все строки). Предположим, вы ввели exit в SQL*Plus, тогда данные во временной таблице будут потеряны.

Ссылка:

... но возможно, вам не нужно использовать временные таблицы. Производные таблицы/встроенные представления/подзапросы (возможно, конвейерные) могут делать то, что вы хотите, но информация расплывчата, поэтому я не могу рекомендовать конкретный подход.

person OMG Ponies    schedule 24.02.2010

arrow_upward
3
arrow_downward

Если ваши коллекции объявлены с типом SQL, вы можете использовать их в операторах SQL с функцией TABLE(). В Oracle 10g мы можем объединять коллекции с помощью оператора MULTISET UNION. В следующем коде показаны примеры обоих методов...

SQL> declare
  2      v1 sys.dbms_debug_vc2coll;
  3      v2 sys.dbms_debug_vc2coll;
  4      v3 sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll();
  5  begin
  6      select ename
  7      bulk collect into v1
  8      from emp;
  9      select dname
 10      bulk collect into v2
 11      from dept;
 12
 13      -- manipulate connects using SQL
 14
 15      for r in ( select * from table(v1)
 16                 intersect
 17                 select * from table(v2)
 18               )
 19      loop
 20          dbms_output.put_line('Employee '|| r.column_value ||' has same name as a department');
 21      end loop;
 22
 23      -- combine two collections into one
 24
 25      dbms_output.put_line('V3 has '|| v3.count() ||' elements');
 26      v3 := v1 multiset union v2;
 27      dbms_output.put_line('V3 now has '|| v3.count() ||' elements');
 28  end;
 29  /
Employee SALES has same name as a department
V3 has 0 elements
V3 now has 23 elements

PL/SQL procedure successfully completed.

SQL>

Существует ряд других подходов, которые вы можете использовать. Как правило, лучше использовать SQL, а не PL/SQL, поэтому предложение OMG Ponies о временных таблицах может оказаться уместным. Это действительно зависит от точных деталей ваших потребностей в обработке.

person APC    schedule 24.02.2010