четверг, 3 февраля 2011 г.

Особенности Oracle: данные выборки при joine таблиц

Все кто пишет SQL запросы так или инче знает, что данные основной выборки можно использовать где угодно, кроме секции from.
Вот такой запрос выполнится с ошибкой:
SELECT t1.id, t1.name 
FROM t1, 
(SELECT id FROM tmp WHERE tmp.sec_id = t1.id) as t2
Но оказывается это ограничение можно обойти в ORACLE. Достаточно оформить подзапрос в функцию, возвращающую таблицу и все отработает без проблем.

Пример:
Есть таблица, содеражщая 2 столбца: имя, число. Нам нужно вывести данные, продублировав поле "имя" число раз, заданное в поле "число". (Понятно, что данный пример можно сделать рекурсивным запросом, но факт такой возможности может не раз пригодится). Запрос:
SELECT t1.num, t1.name FROM t1, 
table (virtual_date_table(t1.num, sysdate, sysdate))
Функция, возвращающая таблицу:
CREATE OR REPLACE 
FUNCTION       admin.virtual_date_table(p_num_rows in INTEGER, p_start_date IN DATE, p_end_date IN DATE)
RETURN virtual_date_table_type
IS
  l_data virtual_date_table_type := virtual_date_table_type();
  l_step NUMBER := 1;
BEGIN
  l_step := (p_end_date - p_start_date) / p_num_rows;
  FOR i IN 1 .. p_num_rows
  LOOP
    l_data.extend;
    l_data(l_data.count) := p_start_date + i * l_step;
  END LOOP;

  RETURN l_data;
END;
/
Результат выборки:
num | name
1   | value1
3   | value3
3   | value3
3   | value3


С 12 версии есть стандартный вариант: Create a lateral inline view in a query