Так или иначе эта проблема появляется постоянно, возможно, решений у нее много, но я привожу то, которым обычно пользуюсь. Граничные условия такие:
— Имеется соединение с базой, в терминах Oracle это SESSION, все действия с данными происходят в ее рамках
— У вас имеется некий код PL/SQL, который не является неразрывным, т.е. имеют место блоки с Dynamic SQL, которые могут вносить изменения в данные о которых на следующих этапах выполнения вы ничего не знаете. Если вам не понятно, то, что тут написано, смотрите пример пилотного проекта ниже.
Oracle имеет временные таблицы, они практически бесполезны, кто думает не так, это их проблемы, я спорить тут не буду.
Приведенный ниже подход позволяет работать в рамках сессии Oracle с любым количеством переменных. Кто еще не понял, объясняю: Oracle имеет внутренний язык кроме SQL, они называют его PL/SQL и это совершенно разные вещи, то, что я описываю ниже относится именно к PL/SQL.
Основная идея, главный посыл: Oracle имеет в своем инструментарии такие вещи, как: PACKAGE и TYPE, это объекты базы данных, о них говорить можно много, но нас в данный момент интересует такое их свойство, как наличие доступных извне переменных. Объект TYPE я рассматривать не буду в данной статье, это отдельная тема разговора.
Объект: PACKAGE.
Выполните следующий код в системе, которая выполнение таких кодов разрешает (это может быть PL/SQL Developer, SQL Developer, TOAD и т.д. и т.п.):
CREATE OR REPLACE PACKAGE pack_temp IS
TYPE tempR IS RECORD (
N1 NUMBER,
N2 NUMBER,
N3 NUMBER,
N4 NUMBER,
N5 NUMBER,
C1 VARCHAR2(1000),
C2 VARCHAR2(1000),
C3 VARCHAR2(1000),
C4 VARCHAR2(1000),
C5 VARCHAR2(1000),
D1 DATE,
D2 DATE,
D3 DATE,
D4 DATE,
D5 DATE
);
TYPE tempT IS TABLE OF tempR INDEX BY BINARY_INTEGER;
TYPE tempVar IS TABLE OF tempT INDEX BY VARCHAR2(255);
tempV tempVar;
END pack_temp;
В переводе на русский язык здесь написано следующее: создается заголовок пакета (его декларативная часть, больше вам ничего не надо), в пакете описывается запись из 15 полей по 5 полей на каждый тип: численный, символьный и тип DATETIME (до милисекунд), на основе этого типа записи декларируется таблица, которая индексируется через BINARY_INTEGER, такой тип необходим для того, чтобы с ним поддерживались операции BULK COLLECT, затем на основе этого типа создается еще один тип с типом индекса VARCHAR2(255), это техника для работы с хэш-массивами. В итоге декларируется переменная пакета на основе типа tempVar. Такой подход позволит вам иметь сколько угодно внутренних таблиц формата записи tempR к которым вы сможете обращаться через заданное имя.
Вот пример заполнения одного экземпляра таблицы через BULK COLLECT:
BEGIN
SELECT num_rows,
blocks,
1,
1,
1,
table_name,
tablespace_name,
'A',
'A',
'A',
last_analyzed,
SYSDATE,
SYSDATE,
SYSDATE,
SYSDATE
BULK COLLECT INTO pack_temp.tempV('tables')
FROM all_all_tables
WHERE owner = 'SYS';
END;
При таком заполнении, к сожалению, необходимо в конструкции SELECT полностью выдерживать структуру записи массива, иначе будет получена ошибка несоответствия запроса структуре его сохранения.
После того, как запрос отработает, в переменную пакета tempV будет записана таблица, ее можно получить разными способами, вот обычный блок PL/SQL:
BEGIN
DBMS_OUTPUT.enable;
FOR i IN 1..50 LOOP
DBMS_OUTPUT.put_line(
i || ' | ' ||
pack_temp.tempV('tables')(i).N1 || ' | ' ||
pack_temp.tempV('tables')(i).N2 || ' | ' ||
pack_temp.tempV('tables')(i).C1 || ' | ' ||
pack_temp.tempV('tables')(i).C2 || ' | ' ||
TO_CHAR(pack_temp.tempV('tables')(i).D1, 'DD/MM/YYYY HH24:MI:SS')
);
END LOOP;
END;
Вот блок c Dynamic SQL:
BEGIN
EXECUTE IMMEDIATE '
BEGIN
DBMS_OUTPUT.enable;
FOR i IN 51..100 LOOP
DBMS_OUTPUT.put_line(
i || '' | '' ||
pack_temp.tempV(''tables'')(i).N1 || '' | '' ||
pack_temp.tempV(''tables'')(i).N2 || '' | '' ||
pack_temp.tempV(''tables'')(i).C1 || '' | '' ||
pack_temp.tempV(''tables'')(i).C2 || '' | '' ||
TO_CHAR(pack_temp.tempV(''tables'')(i).D1, ''DD/MM/YYYY HH24:MI:SS'')
);
END LOOP;
END;
';
END;
Выше показан пример заполнения таблицы с помощью BULK COLLECT, с точки зрения скорости выполнения, этот способ наиболее оптимальный, но он не единственный, если скорость вас не интересует, то идеальным подходом может быть использование курсоров, вот так, например:
DECLARE
i NUMBER := 0;
BEGIN
pack_temp.tempV('tables').DELETE;
FOR cr IN (
SELECT * FROM all_all_tables WHERE owner = 'SYS'
) LOOP
i := i + 1;
pack_temp.tempV('tables')(i).N1 := cr.num_rows;
pack_temp.tempV('tables')(i).N2 := cr.blocks;
pack_temp.tempV('tables')(i).C1 := cr.table_name;
pack_temp.tempV('tables')(i).C2 := cr.tablespace_name;
pack_temp.tempV('tables')(i).D1 := cr.last_analyzed;
END LOOP;
END;
Обратите внимание на оператор DELETE, он важен, если вы будете обновлять таблицу несколько раз за сессию, то необходимо делать ее очистку, если этого требует логика, операция BULK COLLECT очищает таблицу автоматически и в этом случае DELETE выполнять не нужно.
Курсор удобен тем, что позволяет заполнить те поля, которые необходимы, не придерживаясь формата структуры записи таблицы.
Вот пример использования курсора при заполнении в Dynamic SQL:
BEGIN
EXECUTE IMMEDIATE '
DECLARE
i NUMBER := 0;
BEGIN
pack_temp.tempV(''tables'').DELETE;
FOR cr IN (
SELECT * FROM all_all_tables WHERE owner = ''SYS''
) LOOP
i := i + 1;
pack_temp.tempV(''tables'')(i).N1 := cr.num_rows;
pack_temp.tempV(''tables'')(i).N2 := cr.blocks;
pack_temp.tempV(''tables'')(i).C1 := cr.table_name;
pack_temp.tempV(''tables'')(i).C2 := cr.tablespace_name;
pack_temp.tempV(''tables'')(i).D1 := cr.last_analyzed;
END LOOP;
END;
';
END;
Хочу обратить ваше внимание на ограничения в приведенном примере. Константа [tables] в приведенных кодах взята с потолка, т.е. это всего лишь значение индекса хэш-массива, что позволяет вам иметь таких таблиц сколько угодно с любыми названиями, лишь бы память сессии позволяла. Основным ограничением является структура полей записи временной таблицы, в примере имеется по 5 полей разных типов, если вам нужно больше, просто добавьте сколько считаете необходимым.