Как повторно использовать большой запрос, не повторяя его?

Если у меня есть два запроса, которые я назову horrible_query_1 и ugly_query_2, и я хочу выполнить следующие две минус-операции над ними:

(horrible_query_1) minus (ugly_query_2)
(ugly_query_2) minus (horrible_query_1)

Или, может быть, у меня есть terribly_large_and_useful_query, а результат, который он создает, я хочу использовать как часть нескольких будущих запросов.

Как избежать копирования и вставки одних и тех же запросов в нескольких местах? Как я могу "не повторять себя" и следовать принципам СУХОЙ. Возможно ли это в SQL?

Я использую Oracle SQL. Перспективные решения SQL предпочтительнее, но если мне нужно использовать специальную функцию Oracle (включая PL/SQL), которая работает нормально.

Ответ 1

create view horrible_query_1_VIEW as 
 select .. ...
  from .. .. ..

create view ugly_query_2_VIEW as 
 select .. ...
  from .. .. ..

Тогда

(horrible_query_1_VIEW) minus (ugly_query_2_VIEW)

(ugly_query_2_VIEW) minus (horrible_query_1_VIEW)

Или, может быть, с with clause:

with horrible_query_1 as (
  select .. .. ..
    from .. .. ..
) ,
ugly_query_2 as (
  select .. .. ..
     .. .. ..
)
(select * from horrible_query_1 minus select * from ugly_query_2    ) union all
(select * from ugly_query_2     minus select * from horrible_query_1)

Ответ 2

Если вы хотите повторно использовать текст SQL запросов, то определение представлений - лучший способ, как описано выше.

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

Если вам нужно сохранить данные дольше, чем сеанс, вы можете рассмотреть материализованные представления.

Ответ 3

Пробовали ли вы использовать подсказки RESULT_CACHE в ваших запросах? Кроме того, вы могли бы

ALTER SESSION SET RESULT_CACHE_MODE=FORCE

и посмотрите, помогает ли она.

Ответ 4

Поскольку вы используете Oracle, я бы создал функции Pipelined TABLE. Функция принимает параметры и возвращает объект (который вы должны создать) а затем вы выбираете * или даже определенные столбцы из него с помощью функции TABLE() и можете использовать ее с предложением WHERE или с JOIN. Если вам нужна единица повторного использования (функция), вы не ограничены только возвращаемыми значениями (то есть скалярной функцией), вы можете написать функцию, возвращающую строки или записи. что-то вроде этого:

FUNCTION RETURN_MY_ROWS(Param1 IN type...ParamX IN Type)
            RETURN PARENT_OBJECT PIPELINED
            IS
            local_curs cursor_alias; --you need a cursor alias if this function is in a Package
            out_rec ROW_RECORD_OF_CUSTOM_OBJECT:=ROW_RECORD_OF_CUSTOM_OBJECT(NULL, NULL,NULL) --one NULL for each field in the record sub-object
        BEGIN
         OPEN local_curs FOR
          --the SELECT query that you're trying to encapsulate goes here
          -- and it can be very detailed/complex and even have WITH () etc..
        SELECT * FROM baseTable WHERE col1 = x;

   -- now that you have captured the SELECT into a Cursor
   -- here you put a LOOP to take what in the cursor and put it in the 
   -- child object (that holds the individual records)
          LOOP
         FETCH local_curs --opening the ref-cursor
          INTO out_rec.COL1, 
               out_rec.COL2,
               out_rec.COL3;
        EXIT WHEN local_curs%NOTFOUND;
         PIPE ROW(out_rec); --piping out the Object
        END LOOP;
        CLOSE local_curs;  -- always do this
        RETURN;  -- we're now done
  END RETURN_MY_ROWS;

после того, как вы это сделали, вы можете использовать его так

SELECT * FROM TABLE(RETURN_MY_ROWS(val1, val2));

вы можете INSERT SELECT или даже CREATE TABLE из него, вы можете иметь его в соединениях.

еще две вещи:

--ROW_RECORD_OF_CUSTOM_OBJECT is something along these lines
CREATE or REPLACE TYPE ROW_RECORD_OF_CUSTOM_OBJECT AS OBJECT
(
     col1 type;
     col2 type;
      ...
     colx type;
 );

и PARENT_OBJECT - это таблица другого объекта (с определениями полей), который мы только что сделали

create or replace TYPE PARENT_OBJECT IS TABLE OF ROW_RECORD_OF_CUSTOM_OBJECT;

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

В двух словах, функция проста в записи, вам нужен дочерний объект (с полями) и родительский объект, в котором будет размещен этот дочерний объект с типом TABLE дочернего объекта, и вы откроете исходную базу -назначение SQL-запроса в SYS_REFCURSOR (которому может потребоваться псевдоним), если вы находитесь в пакете, и вы читаете из этого курсора из цикла в отдельные записи. Функция возвращает тип PARENT_OBJECT, но внутри него упаковывает под-объект записей со значениями из курсора.

Я надеюсь, что это сработает для вас (могут возникнуть проблемы с вашим администратором базы данных, если вы хотите создавать объекты OBJECT и Table) */

Ответ 5

Если вы работаете со значениями, вы можете писать функции. Здесь вы найдете информацию о том, как это сделать. Он в основном работает как запись функции на любом языке. Вы можете определить параметры и вернуть значения. Это дает вам возможность писать код только один раз. Вот как вы это делаете:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm