Soft — Consulting

Oracle имеет механизм для работы с временными таблицами, но он очень сильно уступает, например, временным таблицам в MS SQL. Недостатками являются то, что временные таблицы Oracle имеют заранее заданную структуру и постоянно хранятся в базе, по сути временными в таких таблицах являются только данные, которые сбрасываются после закрытии сессии или при операции COMMIT. Частично решить вопрос со структурой можно, если использовать некую универсальную структуру записи, например, какое-то количество полей разных типов. Если же имеется необходимость в наличии нескольких временных таблиц, то их придется создавать в схеме данных с разными именами. Если во временных таблицах предполагается хранить небольшие объемы данных, то можно использовать для этого инструменты, которые есть в языке PL/SQL Oracle.
Что мы предполагаем получить:
1. Хранение данных во временной таблице с универсальной структурой записи (5 числовых полей, 5 символьных полей, 5 полей формата DATE).
2. Возможность иметь неограниченное количество временных таблиц.
3. Возможность использования временных таблиц в SQL-запросах.

Для этого нам понадобится специальный тип Oracle, который называется PACKAGE (программный пакет), вот его заголовок:

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 tempTable IS TABLE OF tempR;

  TYPE tempTables IS TABLE OF tempTable INDEX BY VARCHAR2(255);
  
  tempTs tempTables;
  
  FUNCTION getData (nm VARCHAR2) return tempTable PIPELINED;

END pack_temp;

Тело пакета выглядит так:

CREATE OR REPLACE PACKAGE BODY pack_temp IS

FUNCTION getData (nm VARCHAR2) return tempTable PIPELINED IS
BEGIN	
  FOR i IN 1..tempTs(nm).COUNT LOOP
    PIPE ROW (tempTs(nm)(i));
  END LOOP;
END;

END;

Функция getData не совсем обычная, такой тип (PIPELINED) появился в 9-ой версии Oracle, что позволило функциям возвращать RECORDSETS, что MS SQL или SYBASE, а также масса всяких прочих игрушечных баз данных умели делать до этого момента уже лет 10.
Вот пример заполнения таблицы:

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.tempTs('tables')
    FROM all_all_tables
   WHERE owner = 'SYS';
END;

При таком заполнении, к сожалению, необходимо в конструкции SELECT полностью выдерживать структуру записи временной таблицы, иначе будет получена ошибка несоответствия запроса структуре его сохранения.
После того, как таблица заполнена, она может использоваться в обычных SQL-запросаx, вот пример получения RECORDSET:

SELECT * FROM TABLE(pack_temp.getData('tables'))

Вы можете иметь сколько угодно таких временных таблиц, так как они хранятся в хэш-массиве tempTs пакета pack_temp. В данном примере показан способ заполнения таблицы с помощью BULK COLLECT, он не единственный, заполнить можно с помощью курсора или просто прямым обращением к коллекции, например так:

BEGIN
  pack_temp.tempTs('tables').DELETE;
  pack_temp.tempTs('tables').EXTEND;
  pack_temp.tempTs('tables')(pack_temp.tempTs('tables').COUNT).n1 := 5;
  pack_temp.tempTs('tables')(pack_temp.tempTs('tables').COUNT).d1 := SYSDATE;  
  pack_temp.tempTs('tables').EXTEND;
  pack_temp.tempTs('tables')(pack_temp.tempTs('tables').COUNT).n1 := 10;
  pack_temp.tempTs('tables')(pack_temp.tempTs('tables').COUNT).d1 := SYSDATE + 1;  
END;

Для сброса данных в таблице используется оператор DELETE, подробнее о работе с коллекциями смотрите в документации Oracle, там очень хорошо все расписано.