Oracle PL/SQL оператор WITH

В этом учебном пособии вы узнаете, как использовать оператор WITH в Oracle PL/SQL с синтаксисом и примерами.

Описание

Oracle PL/SQL оператор WITH позволяет дать блоку подзапроса имя/псевдоним, на которое можно ссылаться в нескольких местах основного SQL-запроса. Имя, присвоенное подзапросу, обрабатывается так, как если бы оно было встроенным представлением или таблицей. SQL оператор WITH по сути является заменой обычному подзапросу.

Синтаксис

Cинтаксис Oracle PL/SQL WITH с одним подзапросом:

WITH query_name AS (SELECT expressions FROM table_A)
SELECT column_list
FROM query_name [,table_name]
[WHERE conditions]

или

Cинтаксис Oracle PL/SQL WITH с с несколькими подзапросами:

WITH query_name_A AS
(SELECT expressions FROM table_A),
query_name_B AS
([SELECT expressions FROM query_name_A] | [SELECT expressions FROM table_B])
SELECT column_list
FROM query_name_A, query_name_B [,table_X | Join table_Z]
[WHERE conditions]

expressions — поля или расчеты подзапроса.
column_list — поля или расчеты основного запроса.
table_A, table_B, table_X, table_Z — таблицы или соединения для подзапросов.
query_name_A, query_name_B — псевдоним подзапроса. Если подзапросов несколько, то они перечисляются через запятую.
WHERE conditions — условия которые должны быть выполнены для основных запросов.

Примечание

  • Формально предложение WITH называется факторингом подзапроса.
  • Предложение SQL WITH используется, когда подзапрос выполняется несколько раз.
  • Подзапросы в WITH перечисляются через запятую.

Применение

  • SQL-предложение WITH было введено Oracle в базе данных Oracle 9i выпуск 2.
  • Начиная с Oracle Database 12c Release 1 (12.1) в операторе WITH можно определять функции и процедуры.

Примеры Oracle PL/SQL оператор WITH

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

Пример WITH с одним подзапросом

Рассмотрим пример sql оператора with c одним подзапросом, чтобы понять как использовать оператор with в Oracle PL/SQL.
Например:

В этом примере мы создали подзапрос с псевдонимом employee из таблицы Employees (сотрудников). Затем с помощью двух основных запросов и оператора UNION ALL запросили данные всех сотрудников у которых empno > 7900 и всех сотрудников у которых sal > 3000.

В следующем примере мы хотим узнать сколько людей в отделе для каждого сотрудника.

Пример WITH с двумя подзапросами

Для примера использования оператор WITH, создадим несколько таблиц с данными.

Например нам может понадобится выбрать отделы с заработной платой выше среднего. Для этого сначала в первом подзапросе dept_costs определим сумму зарплат по отделам. Затем во втором подзапросе avg_cost определим среднюю зарплату по отделам.
Например.

Пример WITH с функцией

Для примеров оператора WITH необходимо создать следующую тестовую таблицу.

В этом операторе WITH раздел объявления может использоваться для определения функций PL/SQL, как показано ниже.

С точки зрения разрешения имен функций, определенных в разделе объявлений PL/SQL оператора WITH, имеют приоритет над объектами с тем же именем, определенным на уровне схемы.

Пример WITH с процедурой

Мы также можем определить процедуры в разделе объявлений оператора WITH, даже если они не используются.

В действительности вы поместили бы процедуру в операторе WITH, только если бы планировали вызывать процедуру из функции в разделе объявлений.

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

Использование динамического SQL позволяет обойти это ограничение.

Поддержка этой функции с использованием статического SQL внутри PL/SQL ожидается в следующем релизе Oracle.

Преимущества производительности

Вся причина определения встроенного кода PL/SQL заключается в повышении производительности.
Создайте обычную функцию для использования в качестве сравнения.

Запустите следующий тест, который измеряет затраченное время и загрузку ЦП запроса, используя определение встроенной функции.

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

Пример PRAGMA UDF

В ряде презентаций, предшествовавших официальному выпуску 12c, выступавшие упоминали PRAGMA UDF(User Defined Function), которая предположительно дает вам преимущества производительности встроенного PL/SQL, в то же время позволяя вам определять объект PL/SQL вне оператора SQL. Следующий код переопределяет предыдущую обычную функцию для использования этой прагмы.

Как только функция скомпилирована, выполнение теста из предыдущего раздела для этой функции дает довольно интересные результаты.

Кажется, что автономная функция, использующая PRAGMA UDF, последовательно выполняет встроенную функцию.

У меня сложилось впечатление, что вызов функции, определенной с помощью PRAGMA UDF напрямую из PL / SQL, не удастся. Это не похоже на случайность.