Oracle Учебное пособие по динамическому SQL PL/SQL: немедленное выполнение и DBMS

⚡ Умное резюме

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

⚙️ SQL во время выполнения: Динамический SQL генерирует и выполняет операторы, когда имена таблиц или столбцов заранее неизвестны.
⚡ Встроенный динамический SQL: Функция EXECUTE IMMEDIATE создает и выполняет SQL-запросы быстро, с минимальным количеством кода.
🔁 ОТКРЫТО ДЛЯ: Обрабатывает динамические запросы с несколькими строками, которые команда EXECUTE IMMEDIATE не может выполнить самостоятельно.
🧩 DBMS_SQL: Подходит для операторов, количество столбцов или типы которых неизвестны до момента выполнения.
🔐 Привязка переменных: Предложение USING передает значения позиционно и блокирует SQL-инъекции.
🤖 Помощь ИИ: Инструменты искусственного интеллекта создают динамические SQL-запросы и выявляют риски внедрения кода в процессе анализа.

Читать

Что такое динамический SQL?

Dynamic SQL SQL-запрос — это методология программирования, позволяющая генерировать и выполнять SQL-запросы во время выполнения. Она в основном используется для написания универсальных и гибких программ, где SQL-запросы создаются и выполняются во время выполнения в зависимости от требований, например, когда имена таблиц, списки столбцов или условия WHERE неизвестны до запуска программы.

Способы написания динамического SQL

PL/SQL предоставляет два способа написания динамического SQL-запроса:

NDS – собственный динамический SQL (Операторы EXECUTE IMMEDIATE и OPEN-FOR)
СУБД_SQL (в комплекте)

Общее правило простое: если количество и типы данных входных и выходных переменных известны на этапе компиляции, используйте нативный динамический SQL, поскольку он быстрее и требует меньше кода. Если эта информация известна только во время выполнения, используйте пакет DBMS_SQL.

NDS (собственный динамический SQL) – немедленное выполнение

Динамический SQL — это более простой способ написания динамических SQL-запросов. Он использует команду EXECUTE IMMEDIATE для создания и выполнения SQL-запроса во время выполнения. Для использования этого подхода необходимо заранее знать тип данных и количество переменных, используемых во время выполнения. Он также обеспечивает лучшую производительность и меньшую сложность по сравнению с DBMS_SQL.

Синтаксис

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

dynamic_sql_string: Строковое выражение (VARCHAR2 или CHAR, но не NVARCHAR2/NCHAR), содержащее одно SQL-заявление или блок PL/SQL.
Вставка: Необязательный параметр. Используется только в том случае, если динамический SQL-запрос представляет собой однострочный SELECT; он сохраняет возвращаемые значения в переменные или запись. Для каждого выбранного столбца требуется переменная, совместимая по типу.
Предложение USING: Необязательно. Предоставляет переменные для привязки. Режим по умолчанию — IN; OUT и IN OUT используются для получения значений обратно.
Возврат к пункту: Используется с операторами DML, содержащими предложение RETURNING, для захвата значений затронутых строк в качестве аргументов привязки.

Пример 1: В этом примере мы получаем данные из таблицы emp для emp_no '1001' с помощью оператора NDS с переменной привязки.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

Результат

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Объяснение:

Строки 2-6: Объявление переменных.
Линия 8: Формулировка SQL-запроса во время выполнения. SQL-запрос содержит переменную привязки ':empno' в условии WHERE.
Строки 9-11: Выполнение SQL-запроса с помощью команды EXECUTE IMMEDIATE. Переменные в предложении INTO содержат полученные значения, а предложение USING предоставляет значение для переменной привязки :empno.
Строки 12-15: Отображение полученных значений.

Использование динамического SQL для DDL

Статический PL/SQL не может напрямую выполнять операторы DDL, такие как CREATE, ALTER или DROP. Оператор EXECUTE IMMEDIATE решает эту проблему, формируя оператор в виде строки, что также удобно, когда имя объекта указывается во время выполнения:

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

Имена объектов (таблиц, столбцов, схем) нельзя передавать в качестве переменных привязки, поэтому их необходимо объединять в строку. Всегда проверяйте такие входные данные, например, с помощью DBMS_ASSERT.SIMPLE_SQL_NAME, чтобы избежать SQL-инъекций.

DBMS_SQL для динамического SQL

PL/SQL предоставляет пакет DBMS_SQL для работы с динамическим SQL-запросом, когда структура оператора неизвестна до момента выполнения. Процесс создания и выполнения динамического SQL-запроса включает следующие шаги:

ОТКРЫТЬ КУРСОРА: Динамический SQL выполняется как... курсорДля выполнения SQL-запроса необходимо сначала открыть курсор.
АНАЛИЗ SQL: Анализ динамического SQL-запроса. Это проверяет синтаксис и поддерживает запрос в состоянии готовности к выполнению.
ПРИВЯЗАТЬ ЗНАЧЕНИЯ ПЕРЕМЕННЫХ: Присвойте значения переменным привязки, если таковые имеются.
ОПРЕДЕЛИТЬ СТОЛБЕЦ: Определите каждый столбец, используя его относительное положение в операторе SELECT.
ВЫПОЛНЯТЬ: Выполните проанализированный запрос.
ПОЛУЧЕНИЕ ЗНАЧЕНИЙ: Получите выполненные значения.
ЗАКРЫТЬ КУРСОР: После получения результатов закройте курсор.

Пример 1: В этом примере мы получаем данные из таблицы emp для emp_no '1001' с помощью оператора DBMS_SQL. Блок EXCEPTION закрывает курсор даже в случае возникновения ошибки.

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

Результат

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Объяснение:

Строки 1-8: Объявление переменной.
Линия 10: Формулировка SQL-запроса.
Линия 11: Открытие курсора с помощью функции DBMS_SQL.OPEN_CURSOR, которая возвращает идентификатор открытого курсора.
Линия 12: После открытия курсора выполняется разбор SQL-запроса.
Линия 13: Вместо ':empno' присваивается значение привязки '1001'.
Строки 14-17: Определение столбцов по их относительному положению: (1) emp_name, (2) emp_no, (3) salary, (4) manager.
Линия 18: Выполнение запроса с помощью DBMS_SQL.EXECUTE возвращает количество обработанных записей.
Строки 19-32: Получение записей в цикле. Функция FETCH_ROWS возвращает 0, когда строк не осталось, что приводит к выходу из цикла.
Блок ИСКЛЮЧЕНИЯ: Гарантирует закрытие курсора, чтобы открытые курсоры не перенаправляли данные в случае возникновения ошибки.

NDS против DBMS_SQL: когда какой использовать?

Оба подхода выполняют SQL-запросы во время выполнения, но подходят для разных ситуаций:

Используйте встроенный динамический SQL (EXECUTE IMMEDIATE / OPEN-FOR). Когда количество и типы данных входных и выходных данных известны на этапе компиляции, это быстрее, проще для чтения и требует меньше кода.
Используйте DBMS_SQL Когда структура неизвестна до момента выполнения, например, запрос, в котором количество выбранных столбцов или переменных привязки изменяется, известный как динамический SQL метода 4, или оператор, слишком большой, чтобы поместиться в одной переменной VARCHAR2 размером 32 КБ.

Часто задаваемые вопросы (FAQ)

Привязывание переменных передает пользовательский ввод как данные, никогда как исполняемый код. Предложение USING предоставляет значения позиционно, поэтому вредоносный текст не может изменить структуру оператора. Всегда привязывайте ненадежные входные данные, а не объединяйте их.

№ Oracle Привязывает только значения данных, а не имена объектов. Для защиты от инъекций объединяйте идентификаторы в строку SQL и проверяйте их с помощью DBMS_ASSERT.SIMPLE_SQL_NAME.

Оператор EXECUTE IMMEDIATE извлекает только одну строку. Для получения большого количества строк откройте REF CURSOR с помощью оператора OPEN-FOR, затем пройдите циклом FETCH до тех пор, пока не будет найдено %NOTFOUND, и закройте курсор.

Добавьте предложение RETURNING к командам INSERT, UPDATE или DELETE, а затем используйте предложение RETURNING INTO команды EXECUTE IMMEDIATE, чтобы сохранить значения затронутых строк в качестве аргументов привязки.

Динамический SQL добавляет накладные расходы на синтаксический анализ, поскольку операторы компилируются во время выполнения. Повторное использование переменных привязки позволяет Oracle совместное использование курсоров и сокращение сложных операций синтаксического анализа,ping Производительность близка к статическому SQL.

Строка должна быть типа VARCHAR2 или CHAR. Национальные типы символов, такие как NVARCHAR2 и NCHAR, не допускаются. Для текста размером более 32 КБ DBMS_SQL принимает набор фрагментов типа VARCHAR2.

Да. Искусственные интеллекты, такие как GitHub Copilot, создают блоки EXECUTE IMMEDIATE и DBMS_SQL на основе простых подсказок, предлагают заполнители для переменных привязки и объясняют каждый пункт, хотя разработчику все равно следует просмотреть результат.

Сканеры кода на основе ИИ выявляют конкатенированный пользовательский ввод и рекомендуют проверки переменных привязки или DBMS_ASSERT. Они выделяют рискованные шаблоны во время проверки, помогаютping Команды выявляют уязвимости внедрения кода до развертывания.

Oracle Учебное пособие по динамическому SQL PL/SQL: немедленное выполнение и DBMS_SQL