Oracle Підручник з PL/SQL Dynamic SQL: Execute Immediate & DBMS

⚡ Розумний підсумок

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

️ Виконання SQL: Динамічний SQL генерує та виконує оператори, коли імена таблиць або стовпців заздалегідь невідомі.
⚡ Вбудований динамічний SQL: EXECUTE IMMEDIATE швидко створює та виконує SQL з найменшим обсягом коду.
🔁 ВІДКРИТО ДЛЯ: Обробляє багаторядкові динамічні запити, які EXECUTE IMMEDIATE не може отримати самостійно.
🧩 СУБД_SQL: Підходить для операторів, кількість або типи стовпців яких невідомі до моменту виконання.
🔐 Зв'язані змінні: Речення USING передає значення позиційно та блокує SQL-ін'єкції.
🤖 Допомога AI: Інструменти штучного інтелекту визначають динамічний SQL-код та позначають ризики впровадження під час перевірки.

Детальніше

Що таке динамічний SQL?

Dynamic SQL — це методологія програмування для генерації та виконання операторів під час виконання. Вона використовується переважно для написання універсальних та гнучких програм, де SQL-оператори створюються та виконуються під час виконання на основі вимог, наприклад, коли імена таблиць, списки стовпців або умови WHERE невідомі до виконання програми.

Способи написання динамічного SQL

PL/SQL пропонує два способи написання динамічного SQL:

NDS – рідний динамічний SQL (оператори EXECUTE IMMEDIATE та OPEN-FOR)
DBMS_SQL (постачається в комплекті)

Загальне правило просте: якщо кількість та типи даних вхідних і вихідних змінних відомі під час компіляції, використовуйте Native Dynamic 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[, ...]];

динамічний_рядок_SQL: Рядковий вираз (VARCHAR2 або CHAR, а не NVARCHAR2/NCHAR), що містить один оператор SQL або блок PL/SQL.
Речення INTO: Необов'язково. Використовується лише тоді, коли динамічний SQL є однорядковим SELECT; він записує повернуті значення у змінні або запис. Кожен вибраний стовпець потребує сумісної з типом змінної.
Речення USING: Необов'язково. Забезпечує зв'язування змінних. Режим за замовчуванням — IN; OUT та IN OUT використовуються для отримання значень назад.
Речення RETURNING INTO: Використовується з операторами DML, що містять речення RETURNING, для захоплення значень рядків, на які вони впливають, у аргументи зв'язування.

Приклад 1: У цьому прикладі ми отримуємо дані з таблиці emp для emp_no '1001' за допомогою оператора NDS зі змінною bind.

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: Значення зв'язування '1001' призначається замість ':empno'.
Рядки 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 КБ.

Поширені запитання

Змінні зв'язування передають введені користувачем дані як дані, а не як виконуваний код. Речення 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 спільний доступ до курсорів та зменшення кількості складних парсингів, keeping продуктивність близька до статичного SQL.

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

Так. Помічники ШІ, такі як GitHub Copilot, створюють блоки EXECUTE IMMEDIATE та DBMS_SQL зі звичайних команд, пропонують заповнювачі для змінних зв'язку та пояснюють кожен пункт, хоча розробник все одно повинен переглянути результат.

Сканери коду на базі штучного інтелекту позначають об'єднані дані користувача та рекомендують перевірки змінних зв'язування або DBMS_ASSERT. Вони виявляють ризиковані шаблони під час перевірки, допомагаютьping команди виявляють недоліки ін'єкцій перед розгортанням.

Oracle Підручник з PL/SQL Dynamic SQL: Execute Immediate & DBMS_SQL