Динамический 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.