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.