Динамический SQL

📖 Описание

Динамический SQL в Oracle позволяет строить и выполнять SQL или PL/SQL во время работы программы: EXECUTE IMMEDIATE для отдельных команд, OPEN FOR и FETCH для наборов строк. Значения передаются через bind-переменные; имена объектов требуют отдельной проверки.

  • Динамический SQL делает ваши программы более гибкими, создавая и обрабатывая SQL предложения во время выполнения программ.
  • С динамическим SQL вы можете напрямую выполнять большинство типов операторов SQL, включая определение данных и операторы управления данными.
  • Вы можете строить запросы, в которых вы заранее не знаете имен таблиц, предложений WHERE и другой информации.

📌 Оператор EXECUTE IMMEDIATE

Oracle/PLSQL оператор EXECUTE IMMEDIATE подготавливает (анализирует) и немедленно выполняет динамический SQL-запрос или анонимный PL/SQL блок.
Основным аргументом EXECUTE IMMEDIATE является строка, содержащая SQL-запрос для выполнения. Вы можете создать строку, используя конкатенацию, или использовать предопределенную строку.
Динамическая строка может содержать поддерживаемый оператор SQL (без последней точки с запятой), включая запрос с несколькими результирующими строками через BULK COLLECT INTO, либо блок PL/SQL (с последней точкой с запятой).
Строка dynamic_string также может содержать заполнители, произвольные имена, которым предшествует двоеточие, для аргументов связывания bind_argument. В этом случае вы указываете, какие переменные PL/SQL соответствуют заполнителям, с помощью операторов INTO, USING и RETURNING INTO. Во время выполнения аргументы связывания заменяют соответствующие заполнители в динамической строке. Каждый заполнитель должен быть связан с аргументом связывания в предложении USING и/или предложении RETURNING INTO.


🧩 Синтаксис

Синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в переменную или строку:

EXECUTE IMMEDIATE dynamic_string
  [INTO variable1, variable2]
  [USING [IN | OUT | IN OUT] bind1, bind2]
  [RETURNING INTO output1, output2];

или синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в коллекцию

EXECUTE IMMEDIATE dynamic_query
  BULK COLLECT INTO collection1, collection2
  [USING bind1, bind2];

⚙️ Параметры и аргументы

dynamic_string
Строковый литерал, переменная или выражение, представляющее один оператор SQL или блок PL/SQL. В Oracle 19c поддерживаются CHAR, VARCHAR2 и CLOB; для национальных типов действуют ограничения.
BULK COLLECT
Сохраняет значения результатов в одной или нескольких коллекциях с уменьшением числа переходов между SQL и PL/SQL. Скорость зависит от задачи, а размер коллекций — от числа строк и памяти.
INTO
Используется только для однострочных запросов, в этом разделе указываются переменные или записи, в которые извлекаются значения столбцов. Для каждого значения, полученного запросом, в предложении INTO должна быть соответствующая тип-совместимая переменная или поле.
define_variable
Переменная, в которой сохраняется значение выбранного столбца.
record_name
Пользовательская запись или запись %ROWTYPE, в которой сохраняется выбранная строка.
bind_argument
Выражение, значение которого передается в динамический оператор SQL, или переменная, в которой сохраняется значение, возвращаемое динамическим оператором SQL.
collection_name
Объявленная коллекция, в которую извлекаются значения select_item из dynamic_string. Для каждого select_item должна быть соответствующая, совместимая с типом коллекция в списке.
host_array_name
Массив (объявленный в хост-среде PL/SQL и переданный PL/SQL как переменная связывания), в который извлекаются значения select_item. Для каждого select_item должен быть соответствующий, совместимый с типом массив в списке. Массивы хоста должны начинаться с двоеточия.
USING
По умолчанию - IN. Определяет список входных и/или выходных аргументов привязки.
returning_clause
Возвращает значения строк, затронутых поддерживаемым INSERT, UPDATE или DELETE, без отдельного SELECT. Вы можете извлечь значения столбца в переменные или в коллекции. Вы не можете использовать предложение RETURNING для удалённого или параллельного DML. Если инструкция не влияет ни на какие строки, значения переменных, указанных в предложении RETURNING, не определены.

Синтаксис выше схематический: INTO и RETURNING INTO выбираются по виду команды. SELECT одной строки требует INTO, нескольких — BULK COLLECT INTO или OPEN FOR. При EXECUTE IMMEDIATE без INTO/BULK COLLECT запрос SELECT не выполняется. Обычный INTO при отсутствии строк вызывает NO_DATA_FOUND, при нескольких — TOO_MANY_ROWS. Для SQL аргументы должны иметь SQL-совместимые типы; динамический вызов PL/SQL поддерживает также определённые PL/SQL-типы, включая BOOLEAN и объявленные в спецификации пакета типы. Имена объектов нельзя передать через USING.

💻 Общие учебные данные

В одной учебной схеме нужны CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE и квота. Примеры с объектными типами ниже дополнительно требуют CREATE TYPE. Создайте собственные таблицы:

CREATE TABLE demo_dyn_studios (
  studio_id NUMBER PRIMARY KEY,
  studio_name VARCHAR2(40 CHAR) NOT NULL,
  city VARCHAR2(40 CHAR)
);
CREATE TABLE demo_dyn_mentors (
  mentor_id NUMBER PRIMARY KEY,
  mentor_name VARCHAR2(40 CHAR) NOT NULL,
  studio_id NUMBER REFERENCES demo_dyn_studios(studio_id),
  joined_on DATE NOT NULL,
  fee NUMBER(8,2) NOT NULL,
  bonus NUMBER(8,2)
);
CREATE SEQUENCE demo_dyn_studio_seq START WITH 101;
INSERT ALL
  INTO demo_dyn_studios VALUES (7, 'River Studio', 'Tashkent')
  INTO demo_dyn_studios VALUES (8, 'Garden Studio', 'Samarkand')
SELECT 1 FROM dual;
INSERT ALL
  INTO demo_dyn_mentors VALUES (41, 'Aziza', 7, DATE '2026-11-14', 120, NULL)
  INTO demo_dyn_mentors VALUES (42, 'Temur', 7, DATE '2026-11-21', 150, 30)
  INTO demo_dyn_mentors VALUES (43, 'Zarina', 8, DATE '2026-11-28', 200, NULL)
SELECT 1 FROM dual;
COMMIT;

CREATE OR REPLACE PACKAGE demo_dyn_actions AS
  PROCEDURE increase_fee(p_id NUMBER, p_amount NUMBER);
END demo_dyn_actions;
/
CREATE OR REPLACE PACKAGE BODY demo_dyn_actions AS
  PROCEDURE increase_fee(p_id NUMBER, p_amount NUMBER) IS
  BEGIN
    UPDATE demo_dyn_mentors SET fee = fee + p_amount WHERE mentor_id = p_id;
  END;
END demo_dyn_actions;
/

📌 DDL, DML, запись и RETURNING

Динамический DDL выполняется отдельно до SAVEPOINT: CREATE TABLE фиксирует транзакцию.

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE demo_dyn_bonus_scratch (id NUMBER, amount NUMBER)';
END;
/

SAVEPOINT before_dynamic_basics;
DECLARE
  l_sql VARCHAR2(1000);
  l_mentor demo_dyn_mentors%ROWTYPE;
  l_fee NUMBER;
BEGIN
  l_sql := 'INSERT INTO demo_dyn_studios (studio_id, studio_name, city) VALUES (:1, :2, :3)';
  EXECUTE IMMEDIATE l_sql USING 99, 'Hill Studio', 'Bukhara';

  EXECUTE IMMEDIATE 'SELECT * FROM demo_dyn_mentors WHERE mentor_id = :id'
    INTO l_mentor USING 41;
  DBMS_OUTPUT.PUT_LINE(l_mentor.mentor_name || ': ' || l_mentor.fee);

  EXECUTE IMMEDIATE 'BEGIN demo_dyn_actions.increase_fee(:id, :amount); END;'
    USING 42, 25;
  EXECUTE IMMEDIATE
    'UPDATE demo_dyn_mentors SET fee = :fee WHERE mentor_id = :id RETURNING fee INTO :result'
    USING 135, 41 RETURNING INTO l_fee;
  DBMS_OUTPUT.PUT_LINE('returned fee=' || l_fee);

  EXECUTE IMMEDIATE 'DELETE FROM demo_dyn_studios WHERE studio_id = :id' USING 99;
END;
/
SELECT mentor_id, fee FROM demo_dyn_mentors ORDER BY mentor_id;
ROLLBACK TO before_dynamic_basics;

Вывод: Aziza: 120 и returned fee=135. До отката ставки 41/135, 42/175, 43/200; после — исходные 120, 150 и 200. Учебная таблица DEMO_DYN_BONUS_SCRATCH остаётся, поскольку создана до SAVEPOINT.

ALTER SESSION тоже можно передать строкой. Отдельная иллюстрация для сеанса с изначально выключенной SQL-трассировкой:

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE = TRUE';
  -- Здесь выполняется только нужная диагностическая работа.
  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE = FALSE';
END;
/

Это необязательный диагностический фрагмент; изменение настройки сеанса не откатывается ROLLBACK. Не включайте трассировку как обязательную часть остальных примеров.

💻 Имя таблицы и условие DELETE

Имя объекта не является значением bind-параметра. Принимая имя и условие, выберите допустимую форму запроса заранее. Процедура ниже принимает только одну учебную таблицу и один вариант условия; произвольный WHERE не исполняется. NULL-условие явно означает удаление всех строк именно этой таблицы.

CREATE OR REPLACE PROCEDURE demo_dyn_delete_rows (
  p_table VARCHAR2,
  p_condition VARCHAR2 DEFAULT NULL,
  p_studio NUMBER DEFAULT NULL
) AUTHID CURRENT_USER IS
  l_name VARCHAR2(128) := UPPER(TRIM(p_table));
  l_sql VARCHAR2(1000);
BEGIN
  IF l_name IS NULL OR l_name != 'DEMO_DYN_MENTORS' THEN
    RAISE_APPLICATION_ERROR(-20101, 'Table is not allowed');
  END IF;
  l_sql := 'DELETE FROM ' || DBMS_ASSERT.ENQUOTE_NAME(l_name);
  IF p_condition IS NULL THEN
    EXECUTE IMMEDIATE l_sql;
  ELSIF UPPER(TRIM(p_condition)) = 'STUDIO_ID = :ID' THEN
    EXECUTE IMMEDIATE l_sql || ' WHERE studio_id = :id' USING p_studio;
  ELSE
    RAISE_APPLICATION_ERROR(-20102, 'Condition is not allowed');
  END IF;
  DBMS_OUTPUT.PUT_LINE('deleted=' || SQL%ROWCOUNT);
END;
/

SAVEPOINT before_dynamic_delete;
BEGIN
  demo_dyn_delete_rows('demo_dyn_mentors', 'studio_id = :id', 7);
END;
/
-- deleted=2
ROLLBACK TO before_dynamic_delete;
BEGIN
  demo_dyn_delete_rows('demo_dyn_mentors');
END;
/
-- deleted=3
ROLLBACK TO before_dynamic_delete;

Для произвольных пользовательских фильтров нужен разбор и сборка из разрешённых полей и операторов. DBMS_ASSERT проверяет свойства имени, но не делает произвольное условие безопасным и не выдаёт права.

📌 Режимы IN, OUT и IN OUT

USING по умолчанию передаёт IN. RETURNING INTO принимает выходные переменные без слова OUT. При динамическом вызове процедуры режим должен соответствовать её параметру:

CREATE OR REPLACE PROCEDURE demo_dyn_create_studio (
  p_id IN OUT NUMBER,
  p_name IN VARCHAR2,
  p_city IN VARCHAR2
) IS
BEGIN
  p_id := demo_dyn_studio_seq.NEXTVAL;
  INSERT INTO demo_dyn_studios (studio_id, studio_name, city)
  VALUES (p_id, p_name, p_city);
END;
/

SAVEPOINT before_dynamic_mode;
DECLARE
  l_id NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'BEGIN demo_dyn_create_studio(:id, :name, :city); END;'
    USING IN OUT l_id, 'Lake Studio', 'Khiva';
  DBMS_OUTPUT.PUT_LINE('new studio=' || l_id);
END;
/
ROLLBACK TO before_dynamic_mode;

При первом обращении к новой последовательности l_id равен 101. При повторном запуске номер другой, даже после отката. Здесь процедура заменяет входное значение идентификатора; IN OUT сохранён для демонстрации режима связывания.

📌 Многострочный запрос: OPEN, FETCH, CLOSE

DECLARE
  l_cursor SYS_REFCURSOR;
  l_mentor demo_dyn_mentors%ROWTYPE;
BEGIN
  OPEN l_cursor FOR
    'SELECT * FROM demo_dyn_mentors WHERE studio_id = :id ORDER BY mentor_id'
    USING 7;
  LOOP
    FETCH l_cursor INTO l_mentor;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_mentor.mentor_name || ': ' || l_mentor.fee);
  END LOOP;
  CLOSE l_cursor;
EXCEPTION
  WHEN OTHERS THEN
    IF l_cursor%ISOPEN THEN CLOSE l_cursor; END IF;
    RAISE;
END;
/

После отката предыдущих опытов вывод: Aziza: 120, затем Temur: 150. Сначала выполняется FETCH и проверяется %NOTFOUND, затем обрабатывается полученная строка.

💻 Объектные типы и коллекции

Типы создаются на уровне схемы до пакета. Они используются и как типы столбцов, и как bind-значения. Имя таблицы ограничено одним учебным объектом.

CREATE TYPE demo_dyn_person AS OBJECT (full_name VARCHAR2(40 CHAR), age NUMBER);
/
CREATE TYPE demo_dyn_materials IS VARRAY(10) OF VARCHAR2(40 CHAR);
/
CREATE OR REPLACE PACKAGE demo_dyn_team_api AS
  PROCEDURE create_table(p_table VARCHAR2);
  PROCEDURE insert_row(p_table VARCHAR2, p_person demo_dyn_person, p_items demo_dyn_materials);
  PROCEDURE print_table(p_table VARCHAR2);
END demo_dyn_team_api;
/
CREATE OR REPLACE PACKAGE BODY demo_dyn_team_api AS
  FUNCTION checked_name(p_table VARCHAR2) RETURN VARCHAR2 IS
    l_name VARCHAR2(128) := UPPER(TRIM(p_table));
  BEGIN
    IF l_name IS NULL OR l_name != 'DEMO_DYN_TEAM' THEN
      RAISE_APPLICATION_ERROR(-20103, 'Team table is not allowed');
    END IF;
    RETURN DBMS_ASSERT.ENQUOTE_NAME(l_name);
  END;
  PROCEDURE create_table(p_table VARCHAR2) IS
  BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE ' || checked_name(p_table) ||
      ' (person_info demo_dyn_person, materials demo_dyn_materials)';
  END;
  PROCEDURE insert_row(p_table VARCHAR2, p_person demo_dyn_person, p_items demo_dyn_materials) IS
  BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO ' || checked_name(p_table) ||
      ' (person_info, materials) VALUES (:person, :items)' USING p_person, p_items;
  END;
  PROCEDURE print_table(p_table VARCHAR2) IS
    l_cursor SYS_REFCURSOR;
    l_person demo_dyn_person;
    l_items demo_dyn_materials;
  BEGIN
    OPEN l_cursor FOR 'SELECT t.person_info, t.materials FROM ' ||
      checked_name(p_table) || ' t ORDER BY t.person_info.full_name';
    LOOP
      FETCH l_cursor INTO l_person, l_items;
      EXIT WHEN l_cursor%NOTFOUND;
      IF l_person IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE(l_person.full_name || ', age=' || l_person.age);
      END IF;
      IF l_items IS NOT NULL THEN
        FOR i IN 1 .. l_items.COUNT LOOP
          DBMS_OUTPUT.PUT_LINE('  material=' || l_items(i));
        END LOOP;
      END IF;
    END LOOP;
    CLOSE l_cursor;
  EXCEPTION
    WHEN OTHERS THEN
      IF l_cursor%ISOPEN THEN CLOSE l_cursor; END IF;
      RAISE;
  END;
END demo_dyn_team_api;
/
BEGIN
  demo_dyn_team_api.create_table('DEMO_DYN_TEAM');
END;
/

SAVEPOINT before_dynamic_objects;
BEGIN
  demo_dyn_team_api.insert_row('DEMO_DYN_TEAM', demo_dyn_person('Aziza', 29),
    demo_dyn_materials('Clay', 'Glaze'));
  demo_dyn_team_api.insert_row('DEMO_DYN_TEAM', demo_dyn_person('Temur', 34),
    demo_dyn_materials('Paper', 'Ink'));
  demo_dyn_team_api.print_table('DEMO_DYN_TEAM');
END;
/
ROLLBACK TO before_dynamic_objects;

Печатаются Aziza, age=29 с Clay/Glaze и Temur, age=34 с Paper/Ink. Тело print_table действительно выводит значения. После отката строки исчезают, а таблица остаётся.

📌 Массовый SQL

BULK COLLECT и FORALL уменьшают число переходов между PL/SQL и SQL при обработке набора. FETCH ... BULK COLLECT INTO допускает LIMIT для размера партии; EXECUTE IMMEDIATE ... BULK COLLECT INTO выбирает весь результат в память. FORALL выполняет DML для элементов коллекции и не предназначен для SELECT.

Используются FETCH ... BULK COLLECT INTO, EXECUTE IMMEDIATE ... BULK COLLECT INTO, FORALL, RETURNING BULK COLLECT INTO и SQL%BULK_ROWCOUNT. Правила типов зависят от операции: SQL-объекты и коллекции схемы из предыдущего примера не следует путать с PL/SQL-типами параметров динамического вызова подпрограммы.

💻 BULK COLLECT: курсор и EXECUTE IMMEDIATE

DECLARE
  TYPE t_numbers IS TABLE OF NUMBER;
  TYPE t_names IS TABLE OF VARCHAR2(40 CHAR);
  l_ids t_numbers;
  l_names t_names;
  l_fees t_numbers;
  l_cursor SYS_REFCURSOR;
  l_total PLS_INTEGER := 0;
BEGIN
  OPEN l_cursor FOR 'SELECT mentor_id, mentor_name FROM demo_dyn_mentors ORDER BY mentor_id';
  LOOP
    FETCH l_cursor BULK COLLECT INTO l_ids, l_names LIMIT 2;
    EXIT WHEN l_ids.COUNT = 0;
    l_total := l_total + l_ids.COUNT;
    FOR i IN 1 .. l_ids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(l_ids(i) || ': ' || l_names(i));
    END LOOP;
  END LOOP;
  CLOSE l_cursor;
  EXECUTE IMMEDIATE 'SELECT fee FROM demo_dyn_mentors ORDER BY mentor_id'
    BULK COLLECT INTO l_fees;
  DBMS_OUTPUT.PUT_LINE('fetched=' || l_total || ', fees=' || l_fees.COUNT);
EXCEPTION
  WHEN OTHERS THEN
    IF l_cursor%ISOPEN THEN CLOSE l_cursor; END IF;
    RAISE;
END;
/

Строки 41: Aziza, 42: Temur, 43: Zarina выбираются партиями 2 и 1; итог fetched=3, fees=3. Для неограниченного набора выбирайте размер партий и освобождайте курсор при ошибках.

💻 RETURNING BULK COLLECT INTO

SAVEPOINT before_dynamic_returning;
DECLARE
  TYPE t_names IS TABLE OF VARCHAR2(40 CHAR);
  l_names t_names;
BEGIN
  EXECUTE IMMEDIATE
    'UPDATE demo_dyn_mentors SET bonus = :bonus WHERE studio_id = :studio
     RETURNING mentor_name INTO :names'
    USING 25, 7 RETURNING BULK COLLECT INTO l_names;
  DBMS_OUTPUT.PUT_LINE('returned=' || l_names.COUNT);
END;
/
SELECT mentor_id, bonus FROM demo_dyn_mentors ORDER BY mentor_id;
ROLLBACK TO before_dynamic_returning;

returned=2, в возвращённой коллекции Aziza и Temur; порядок RETURNING не задан. До отката бонусы 41/25, 42/25, 43/NULL. Затем возвращаются исходные NULL, 30, NULL. RETURNING относится к DML, в то время как OUT-параметры могут быть и у динамически вызванной PL/SQL-подпрограммы.

💻 FORALL с динамическим UPDATE

SAVEPOINT before_dynamic_forall;
DECLARE
  TYPE t_numbers IS TABLE OF NUMBER;
  TYPE t_names IS TABLE OF VARCHAR2(40 CHAR);
  l_ids t_numbers := t_numbers(41, 43, 99);
  l_names t_names;
BEGIN
  FORALL i IN 1 .. l_ids.COUNT
    EXECUTE IMMEDIATE
      'UPDATE demo_dyn_mentors SET fee = fee * 1.10 WHERE mentor_id = :id
       RETURNING mentor_name INTO :names'
      USING l_ids(i) RETURNING BULK COLLECT INTO l_names;
  DBMS_OUTPUT.PUT_LINE('returned=' || l_names.COUNT);
  FOR i IN 1 .. l_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('id=' || l_ids(i) || ', rows=' || SQL%BULK_ROWCOUNT(i));
  END LOOP;
END;
/
SELECT mentor_id, fee FROM demo_dyn_mentors ORDER BY mentor_id;
ROLLBACK TO before_dynamic_forall;

Вернутся два имени; число изменённых строк для 41, 43 и 99 равно 1, 1 и 0. Ставки до отката: 132, 150 и 220. Отсутствующий идентификатор 99 не является ошибкой UPDATE. Строка SQL в FORALL одна; переменные значения передаются через простые элементы коллекции в USING, а не заменой имени таблицы на каждой итерации.

📌 Рекомендации по динамическому SQL

Точка с запятой в SQL и PL/SQL

В строке отдельного SQL-оператора завершающей точки с запятой нет. В строке анонимного блока точка с запятой после END обязательна. Символ / — команда клиента, в строку не включается:

SAVEPOINT before_dynamic_semicolon;
BEGIN
  EXECUTE IMMEDIATE
    'UPDATE demo_dyn_mentors SET fee = fee WHERE mentor_id = :id' USING 41;
  EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''Workshop ready''); END;';
END;
/
ROLLBACK TO before_dynamic_semicolon;

Переменные связывания вместо конкатенации значений

Фрагмент ниже показывает нежелательный способ построения строки. Он не выполняется: преобразование значения в текст зависит от формата, а конкатенация непроверенного пользовательского текста открывает путь SQL-инъекции.

-- Антипаттерн: l_sql := 'DELETE FROM demo_dyn_mentors WHERE mentor_id = ' || p_id;
-- Значение следует передать через USING, как в процедуре ниже.

Одинаковый текст SQL с разными аргументами также даёт Oracle возможность повторно использовать разобранный курсор. Это не обещание одинакового плана или фиксированного ускорения.

CREATE OR REPLACE PROCEDURE demo_dyn_remove_mentor(p_id NUMBER)
AUTHID CURRENT_USER IS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM demo_dyn_mentors WHERE mentor_id = :id'
    USING p_id;
  DBMS_OUTPUT.PUT_LINE('deleted=' || SQL%ROWCOUNT);
END;
/
SAVEPOINT before_dynamic_remove;
BEGIN demo_dyn_remove_mentor(43); END;
/
ROLLBACK TO before_dynamic_remove;

Для исходных данных deleted=1; после отката Zarina снова присутствует.

Имя объекта как параметр

Имя таблицы нельзя заменить bind-переменной. Здесь разрешено удалять только отдельную учебную таблицу из первого примера. Проверка допустимого имени предшествует DBMS_ASSERT. DROP выполняет DDL с неявной фиксацией транзакции; откат DML его не отменяет.

CREATE OR REPLACE PROCEDURE demo_dyn_drop_table(p_table VARCHAR2)
AUTHID CURRENT_USER IS
  l_name VARCHAR2(128) := UPPER(TRIM(p_table));
BEGIN
  IF l_name IS NULL OR l_name <> 'DEMO_DYN_BONUS_SCRATCH' THEN
    RAISE_APPLICATION_ERROR(-20103, 'Only the scratch table is allowed');
  END IF;
  EXECUTE IMMEDIATE 'DROP TABLE ' || DBMS_ASSERT.ENQUOTE_NAME(l_name);
END;
/
BEGIN demo_dyn_drop_table('demo_dyn_bonus_scratch'); END;
/
SELECT COUNT(*) AS remaining FROM user_tables
WHERE table_name = 'DEMO_DYN_BONUS_SCRATCH';

При первом последовательном запуске remaining=0. Повторный DROP отсутствующей таблицы завершится ошибкой; процедура намеренно не скрывает её.

Повторяющиеся заполнители

В SQL позиции заполнителей значимы, даже если имена повторяются. Четырём позициям соответствуют четыре аргумента:

CREATE TABLE demo_dyn_bind_values (a NUMBER, b NUMBER, c NUMBER, d NUMBER);
SAVEPOINT before_dynamic_binds;
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO demo_dyn_bind_values VALUES (:x, :x, :y, :x)'
    USING 4, 4, 9, 4;
END;
/
SELECT a, b, c, d FROM demo_dyn_bind_values;
ROLLBACK TO before_dynamic_binds;
A B C D
4 4 9 4

Для анонимного PL/SQL-блока или CALL повторяющееся имя обозначает одну привязку. Ниже достаточно двух аргументов, по порядку первых появлений x и y:

CREATE OR REPLACE PROCEDURE demo_dyn_stats(
  p_a NUMBER, p_b NUMBER, p_c NUMBER, p_d NUMBER
) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('sum=' || (p_a + p_b + p_c + p_d));
END;
/
BEGIN
  EXECUTE IMMEDIATE 'BEGIN demo_dyn_stats(:x, :x, :y, :x); END;' USING 4, 9;
END;
/
-- sum=21

Атрибуты курсоров

После динамического DML доступны SQL%ROWCOUNT, SQL%FOUND и SQL%NOTFOUND. Считывайте их до следующего SQL-оператора. SQL%ISOPEN всегда FALSE: неявный курсор закрывается автоматически.

SAVEPOINT before_dynamic_attributes;
DECLARE
  l_count PLS_INTEGER;
  l_found BOOLEAN;
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM demo_dyn_mentors WHERE mentor_id > :id' USING 41;
  l_count := SQL%ROWCOUNT;
  l_found := SQL%FOUND;
  DBMS_OUTPUT.PUT_LINE('deleted=' || l_count);
  IF l_found THEN DBMS_OUTPUT.PUT_LINE('found=TRUE'); END IF;
END;
/
ROLLBACK TO before_dynamic_attributes;

Результат deleted=2, found=TRUE. Для переменной REF CURSOR используются её собственные атрибуты; ROWCOUNT накапливает извлечённые строки:

DECLARE
  TYPE t_ids IS TABLE OF NUMBER;
  l_ids t_ids;
  l_cursor SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR 'SELECT mentor_id FROM demo_dyn_mentors ORDER BY mentor_id';
  FETCH l_cursor BULK COLLECT INTO l_ids;
  DBMS_OUTPUT.PUT_LINE('fetched=' || l_cursor%ROWCOUNT);
  CLOSE l_cursor;
EXCEPTION
  WHEN OTHERS THEN
    IF l_cursor%ISOPEN THEN CLOSE l_cursor; END IF;
    RAISE;
END;
/
-- fetched=3

Передача NULL

Литерал NULL нельзя непосредственно указать как аргумент USING. Неинициализированная переменная подходящего типа передаёт SQL NULL:

SAVEPOINT before_dynamic_null;
DECLARE
  l_null NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'UPDATE demo_dyn_mentors SET bonus = :value WHERE mentor_id = :id'
    USING l_null, 42;
END;
/
SELECT mentor_id, bonus FROM demo_dyn_mentors WHERE mentor_id = 42;
ROLLBACK TO before_dynamic_null;

До отката у наставника 42 бонус NULL, после — исходные 30.

Права вызывающего пользователя

AUTHID CURRENT_USER означает проверку прав вызывающего пользователя во время выполнения. Оно не выдаёт привилегий и не заменяет проверку входных имён. Пример допускает только два конкретных учебных объекта и два вида DDL:

CREATE TABLE demo_dyn_drop_sample (id NUMBER);
CREATE VIEW demo_dyn_drop_view AS SELECT id FROM demo_dyn_drop_sample;
CREATE OR REPLACE PROCEDURE demo_dyn_drop_object(
  p_kind VARCHAR2, p_name VARCHAR2
) AUTHID CURRENT_USER IS
  l_kind VARCHAR2(10) := UPPER(TRIM(p_kind));
  l_name VARCHAR2(128) := UPPER(TRIM(p_name));
BEGIN
  IF l_kind IS NULL OR l_name IS NULL THEN
    RAISE_APPLICATION_ERROR(-20104, 'Object kind and name are required');
  END IF;
  IF NOT ((l_kind = 'VIEW' AND l_name = 'DEMO_DYN_DROP_VIEW') OR
          (l_kind = 'TABLE' AND l_name = 'DEMO_DYN_DROP_SAMPLE')) THEN
    RAISE_APPLICATION_ERROR(-20104, 'Object is not allowed');
  END IF;
  EXECUTE IMMEDIATE 'DROP ' || l_kind || ' ' || DBMS_ASSERT.ENQUOTE_NAME(l_name);
END;
/
BEGIN demo_dyn_drop_object('VIEW', 'demo_dyn_drop_view'); END;
/
BEGIN demo_dyn_drop_object('TABLE', 'demo_dyn_drop_sample'); END;
/

Сначала удаляется представление, затем таблица. Вызовите это отдельно от незавершённых изменений: DDL фиксирует транзакцию.

Не изменяйте выполняемый программный модуль

Не следует выполнять DROP или ALTER самой процедуры, пока она находится в стеке вызовов. Это может привести к ожиданию блокировки и ошибке. Следующий код — отрицательный пример; не создавайте и не вызывайте его для проверки на рабочей БД:

-- Антипаттерн, только для разбора:
CREATE OR REPLACE PROCEDURE demo_dyn_self_drop IS
BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE demo_dyn_self_drop';
END;
/

Удалять или пересоздавать такой объект следует отдельной командой после завершения его выполнения, с учётом других сеансов.

📌 Динамический SQL через связь базы данных

Этот самостоятельный сценарий требует двух изолированных Oracle БД и заранее настроенной связи DEMO_TRAINING_LINK. Она должна подключать к владельцу учебных объектов с необходимыми правами. Пароли и создание связи здесь не приводятся. Без такой среды примеры ниже не запускаются.

На удалённой учебной БД один раз создайте собственную таблицу и функцию:

CREATE TABLE demo_remote_studios (
  studio_id NUMBER PRIMARY KEY,
  studio_name VARCHAR2(40 CHAR)
);
INSERT INTO demo_remote_studios VALUES (91, 'Remote Clay');
COMMIT;
CREATE OR REPLACE FUNCTION demo_remote_count(p_table VARCHAR2)
RETURN NUMBER AUTHID CURRENT_USER IS
  l_name VARCHAR2(128) := UPPER(TRIM(p_table));
  l_count NUMBER;
BEGIN
  IF l_name IS NULL OR l_name <> 'DEMO_REMOTE_STUDIOS' THEN
    RAISE_APPLICATION_ERROR(-20105, 'Table is not allowed');
  END IF;
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || DBMS_ASSERT.ENQUOTE_NAME(l_name)
    INTO l_count;
  RETURN l_count;
END;
/

В локальной БД можно вызвать удалённую функцию динамическим PL/SQL-блоком. OUT относится к первому заполнителю:

DECLARE
  l_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'BEGIN :result := demo_remote_count@demo_training_link(:table_name); END;'
    USING OUT l_count, IN 'DEMO_REMOTE_STUDIOS';
  DBMS_OUTPUT.PUT_LINE('remote rows=' || l_count);
END;
/

При указанных исходных данных remote rows=1. Для удалённого DELETE значение идентификатора передаётся через USING. Имя связи не является bind-параметром; разрешена только одна заранее известная связь:

CREATE OR REPLACE PROCEDURE demo_remote_delete(
  p_link VARCHAR2, p_id NUMBER
) AUTHID CURRENT_USER IS
  l_link VARCHAR2(128) := UPPER(TRIM(p_link));
BEGIN
  IF l_link IS NULL OR l_link <> 'DEMO_TRAINING_LINK' THEN
    RAISE_APPLICATION_ERROR(-20106, 'Database link is not allowed');
  END IF;
  EXECUTE IMMEDIATE
    'DELETE FROM demo_remote_studios@' || l_link || ' WHERE studio_id = :id'
    USING p_id;
  DBMS_OUTPUT.PUT_LINE('deleted=' || SQL%ROWCOUNT);
END;
/
-- Начинайте в отдельном сеансе без других незавершённых изменений.
BEGIN demo_remote_delete('demo_training_link', 91); END;
/
SELECT COUNT(*) AS remaining FROM demo_remote_studios@demo_training_link;
ROLLBACK;
SELECT COUNT(*) AS restored FROM demo_remote_studios@demo_training_link;

Для исходной строки: deleted=1, remaining=0, после полного отката restored=1. Не добавляйте COMMIT в вызываемую функцию: контроль транзакции остаётся у вызывающего кода. Доступность сети, свойства database link и права проверяются отдельно; результат здесь описывает ожидаемую логику сценария.

✅ Что проверить перед запуском

  • Локальные примеры используют только учебные DEMO_DYN_* объекты в отдельной схеме. Для создания объектов нужны соответствующие привилегии и квота.
  • Сначала создайте исходные таблицы, затем выполняйте сценарии по порядку. DDL фиксирует транзакцию; SAVEPOINT относится только к следующему тестовому DML и его откату.
  • Для вывода включите DBMS Output в клиенте; в SQL*Plus/SQLcl используйте SET SERVEROUTPUT ON.
  • Связывайте значения, проверяйте имена по разрешённому списку; DBMS_ASSERT проверяет форму имени, но не выдаёт права и не определяет разрешённый бизнес-контекст.

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

Документация Oracle: Native Dynamic SQL, EXECUTE IMMEDIATE, SQL Injection, DBMS_ASSERT.