WITH

📖 Описание

WITH в Oracle задаёт имя блоку подзапроса, на который можно ссылаться в основном запросе и последующих блоках WITH. Это помогает разделить вычисление на понятные шаги. WITH не создаёт постоянную таблицу или представление.

🧩 Синтаксис

Один именованный подзапрос:

WITH query_name AS (
  SELECT column_list FROM source_table WHERE condition
)
SELECT column_list FROM query_name;

Несколько блоков перечисляются через запятую; второй может использовать первый:

WITH query_a AS (
  SELECT column_list FROM source_table
), query_b AS (
  SELECT column_list FROM query_a WHERE condition
)
SELECT column_list FROM query_b;

query_a и query_b — имена подзапросов, column_list — нужные столбцы или выражения, condition — условие. Способ выполнения выбирает оптимизатор: WITH не гарантирует материализацию или единственное вычисление блока.

📌 Применение

Факторинг подзапросов доступен с семейства Oracle 9i. С Oracle 12.1 WITH также поддерживает объявления локальных функций и процедур. Ниже рассматриваются обычные подзапросы, повторное использование, агрегирование, локальные подпрограммы, динамический вызов и PRAGMA UDF.

💻 Данные для подзапросов

В учебной схеме с CREATE TABLE и квотой создайте собственные студии и наставников:

CREATE TABLE demo_with_studios (
  studio_id NUMBER PRIMARY KEY,
  studio_name VARCHAR2(40 CHAR) NOT NULL
);
CREATE TABLE demo_with_mentors (
  mentor_id NUMBER PRIMARY KEY,
  mentor_name VARCHAR2(40 CHAR) NOT NULL,
  joined_on DATE NOT NULL,
  monthly_fee NUMBER(8,2) NOT NULL,
  studio_id NUMBER NOT NULL REFERENCES demo_with_studios(studio_id)
);
INSERT ALL
  INTO demo_with_studios VALUES (7, 'River Studio')
  INTO demo_with_studios VALUES (8, 'Garden Studio')
  INTO demo_with_studios VALUES (9, 'Hill Studio')
  INTO demo_with_studios VALUES (10, 'Lake Studio')
SELECT 1 FROM dual;
INSERT ALL
  INTO demo_with_mentors VALUES (41, 'Aziza', DATE '2026-02-03', 6000, 7)
  INTO demo_with_mentors VALUES (42, 'Temur', DATE '2026-03-10', 8000, 7)
  INTO demo_with_mentors VALUES (43, 'Zarina', DATE '2026-04-14', 5000, 8)
  INTO demo_with_mentors VALUES (44, 'Behruz', DATE '2026-05-19', 7000, 8)
  INTO demo_with_mentors VALUES (45, 'Nilufar', DATE '2026-06-23', 9000, 9)
  INTO demo_with_mentors VALUES (46, 'Kamol', DATE '2026-07-28', 4000, 9)
SELECT 1 FROM dual;
COMMIT;

💻 Один подзапрос, два использования

WITH mentors AS (
  SELECT mentor_id, mentor_name, monthly_fee FROM demo_with_mentors
)
SELECT mentor_id, mentor_name, monthly_fee FROM mentors WHERE mentor_id >= 45
UNION ALL
SELECT mentor_id, mentor_name, monthly_fee FROM mentors WHERE monthly_fee > 8000
ORDER BY mentor_id;
MENTOR_ID MENTOR_NAME MONTHLY_FEE
45 Nilufar 9000
45 Nilufar 9000
46 Kamol 4000

Nilufar попадает в обе ветви и присутствует дважды: UNION ALL сохраняет повторы.

💻 Число наставников в студии

WITH studio_counts AS (
  SELECT studio_id, COUNT(*) AS mentor_count
  FROM demo_with_mentors GROUP BY studio_id
)
SELECT m.mentor_name, c.mentor_count
FROM demo_with_mentors m
JOIN studio_counts c ON c.studio_id = m.studio_id
ORDER BY m.mentor_id;

Для всех шести наставников MENTOR_COUNT равен 2. Студия 10 не появляется, потому что наставников в ней нет.

💻 Два подзапроса: общая ставка выше среднего

WITH studio_costs AS (
  SELECT s.studio_id, s.studio_name, SUM(m.monthly_fee) AS studio_total
  FROM demo_with_studios s
  JOIN demo_with_mentors m ON m.studio_id = s.studio_id
  GROUP BY s.studio_id, s.studio_name
), average_cost AS (
  SELECT AVG(studio_total) AS average_total FROM studio_costs
)
SELECT studio_name, studio_total
FROM studio_costs
WHERE studio_total > (SELECT average_total FROM average_cost)
ORDER BY studio_name;
STUDIO_NAME STUDIO_TOTAL
River Studio 14000

Суммы студий 7, 8 и 9 равны 14000, 12000 и 13000; среднее их сумм — 13000. Это не средняя ставка одного наставника. Пустая студия исключена внутренним соединением. Если нужно учитывать её как ноль, потребуется LEFT JOIN и соответствующая обработка NULL.

💻 WITH с функцией

Для следующих опытов подготовьте 12000 строк с количеством мест от 1 до 6. Небольшой набор подходит для проверки результата; это не готовый тест производительности конкретной системы.

CREATE TABLE demo_with_samples AS
SELECT LEVEL AS sample_id, MOD(LEVEL - 1, 6) + 1 AS seats
FROM dual CONNECT BY LEVEL <= 12000;

Локальная функция рассчитывает стоимость по 45 за место:

WITH
  FUNCTION local_fee(p_seats NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_seats * 45;
  END;
SELECT local_fee(seats) AS total_fee
FROM demo_with_samples
WHERE sample_id = 11
/

У строки 11 пять мест, TOTAL_FEE = 225. Локальное имя функции имеет приоритет перед одноимённой функцией схемы в этом запросе. В SQL*Plus/SQLcl запрос WITH с объявлениями подпрограмм завершается строкой /; внутренние точки с запятой относятся к PL/SQL.

💻 WITH с процедурой

Процедуру можно объявить, но само объявление её не вызывает:

WITH
  PROCEDURE trace_seats(p_seats NUMBER) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('seats=' || p_seats);
  END;
SELECT seats FROM demo_with_samples WHERE sample_id = 11
/

Результат SELECT — 5. Сообщение seats=5 не печатается, поскольку вызова процедуры нет. Для вызова из выражения SQL используйте функцию, которая обращается к процедуре:

WITH
  PROCEDURE trace_seats(p_seats NUMBER) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('seats=' || p_seats);
  END;
  FUNCTION local_fee(p_seats NUMBER) RETURN NUMBER IS
  BEGIN
    trace_seats(p_seats);
    RETURN p_seats * 45;
  END;
SELECT local_fee(seats) AS total_fee
FROM demo_with_samples WHERE sample_id = 11
/

Значение TOTAL_FEE — 225; при включённом DBMS Output вызов процедуры пишет seats=5. Не используйте число таких сообщений как гарантию числа вызовов функции: SQL может вычислять выражения по выбранному плану.

📌 Контекст PL/SQL и динамический запрос

Поддержка WITH с объявлениями подпрограмм в SQL-клиенте и внутри статического PL/SQL — разные вопросы. Следующий диагностический вариант не следует включать в основной сценарий: в контексте, не поддерживающем такой синтаксис, он приводит к ошибке компиляции (например, ORA-06550 вместе с ORA-00905). Текст и позиции диагностики зависят от версии.

BEGIN
  FOR r IN (
    WITH FUNCTION local_fee(p_seats NUMBER) RETURN NUMBER IS
      BEGIN RETURN p_seats * 45; END;
    SELECT local_fee(seats) AS total_fee
    FROM demo_with_samples WHERE sample_id = 11
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(r.total_fee);
  END LOOP;
END;
/

Рабочий способ передать SQL-парсеру весь запрос — динамический SQL. Здесь запрос постоянный, внешние строки к нему не добавляются:

DECLARE
  l_sql VARCHAR2(32767) := q'~
    WITH FUNCTION local_fee(p_seats NUMBER) RETURN NUMBER IS
      BEGIN RETURN p_seats * 45; END;
    SELECT local_fee(seats) FROM demo_with_samples WHERE sample_id = 11
  ~';
  l_cursor SYS_REFCURSOR;
  l_value NUMBER;
BEGIN
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor INTO l_value;
  IF l_cursor%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('total_fee=' || l_value);
  END IF;
  CLOSE l_cursor;
EXCEPTION
  WHEN OTHERS THEN
    IF l_cursor%ISOPEN THEN CLOSE l_cursor; END IF;
    RAISE;
  END;
/

Вывод: total_fee=225. В строке динамического SQL нет завершающей строки / и нет точки с запятой после SELECT; разделители внутри объявления функции сохранены. Обычный WITH без локальных подпрограмм также применяется в статическом SQL.

📌 Сравнение выполнения и PRAGMA UDF

Локальная функция удобна и не создаёт отдельный объект схемы. Скорость нельзя вывести из одного вида синтаксиса. Для сравнения создайте обычную функцию и отдельный вариант с PRAGMA UDF; нужна CREATE PROCEDURE:

CREATE OR REPLACE FUNCTION demo_fee_regular(p_seats NUMBER)
RETURN NUMBER IS
BEGIN
  RETURN p_seats * 45;
END;
/

CREATE OR REPLACE FUNCTION demo_fee_udf(p_seats NUMBER)
RETURN NUMBER IS
  PRAGMA UDF;
BEGIN
  RETURN p_seats * 45;
END;
/

PRAGMA UDF сообщает компилятору о преимущественных вызовах из SQL и может уменьшить накладные расходы. Она не гарантирует ускорения и не запрещает вызов из PL/SQL.

Следующий блок полностью выбирает результаты четырёх вариантов, включая обычное SQL-выражение. BULK COLLECT ограничен партиями по 500 строк. Сравнивайте строки и контрольную сумму прежде времени:

DECLARE
  PROCEDURE measure_query(p_label VARCHAR2, p_sql VARCHAR2) IS
    l_cursor SYS_REFCURSOR;
    TYPE t_values IS TABLE OF NUMBER;
    l_batch t_values;
    l_rows PLS_INTEGER := 0;
    l_total NUMBER := 0;
    l_time NUMBER := DBMS_UTILITY.GET_TIME;
    l_cpu NUMBER := DBMS_UTILITY.GET_CPU_TIME;
    l_elapsed NUMBER;
    l_cpu_elapsed NUMBER;
  BEGIN
    OPEN l_cursor FOR p_sql;
    LOOP
      FETCH l_cursor BULK COLLECT INTO l_batch LIMIT 500;
      EXIT WHEN l_batch.COUNT = 0;
      l_rows := l_rows + l_batch.COUNT;
      FOR i IN 1 .. l_batch.COUNT LOOP
        l_total := l_total + l_batch(i);
      END LOOP;
    END LOOP;
    CLOSE l_cursor;
    l_elapsed := MOD(DBMS_UTILITY.GET_TIME - l_time + 4294967296, 4294967296);
    l_cpu_elapsed := MOD(DBMS_UTILITY.GET_CPU_TIME - l_cpu + 4294967296, 4294967296);
    IF l_rows != 12000 OR l_total != 1890000 THEN
      RAISE_APPLICATION_ERROR(-20091, 'Unexpected benchmark result');
    END IF;
    DBMS_OUTPUT.PUT_LINE(p_label || ': rows=' || l_rows || ', sum=' || l_total);
    DBMS_OUTPUT.PUT_LINE('elapsed_hsecs=' || l_elapsed || ', cpu_hsecs=' || l_cpu_elapsed);
  EXCEPTION
    WHEN OTHERS THEN
      IF l_cursor%ISOPEN THEN CLOSE l_cursor; END IF;
      RAISE;
  END;
BEGIN
  FOR pass IN 1 .. 2 LOOP
    DBMS_OUTPUT.PUT_LINE('pass=' || pass);
    measure_query('SQL', 'SELECT seats * 45 FROM demo_with_samples');
    measure_query('WITH', q'~
      WITH FUNCTION local_fee(p_seats NUMBER) RETURN NUMBER IS
        BEGIN RETURN p_seats * 45; END;
      SELECT local_fee(seats) FROM demo_with_samples
    ~');
    measure_query('REGULAR', 'SELECT demo_fee_regular(seats) FROM demo_with_samples');
    measure_query('UDF', 'SELECT demo_fee_udf(seats) FROM demo_with_samples');
  END LOOP;
END;
/

Каждый вариант должен вернуть rows=12000 и sum=1890000: значения 1–6 встречаются по 2000 раз, (1+2+3+4+5+6) × 2000 × 45 = 1890000. Фактические времена здесь не приведены: этот материал не содержит результата запуска на конкретной Oracle.

hsecs — сотые доли секунды. GET_TIME измеряет прошедшее время, GET_CPU_TIME — процессорное. Для короткого опыта возможны нули из-за разрешения таймера. Сравнение включает разбор, выборку, передачу значений в PL/SQL и контрольную сумму. Первый проход может прогревать кэш; для выводов повторяйте серии, меняйте порядок вариантов и учитывайте планы, статистику и нагрузку. MOD учитывает переход 32-битного счётчика для коротких измерений; это не таймер для опыта длиной в полный цикл счётчика.

💻 Прямой вызов UDF из PL/SQL

DECLARE
  l_total NUMBER;
BEGIN
  l_total := demo_fee_udf(4);
  DBMS_OUTPUT.PUT_LINE('total_fee=' || l_total);
END;
/
-- total_fee=180

Включите DBMS Output в клиенте; в SQL*Plus/SQLcl используйте SET SERVEROUTPUT ON. Пример не требует автоматического удаления таблиц или предположения, что CTAS всегда собрал все необходимые статистики. Проверьте их отдельно при полноценном исследовании производительности.

См. документацию Oracle: SELECT.

Документация Oracle: UDF Pragma, DBMS_UTILITY, вызовы функций из SQL.