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

Автор: Фіона Браун Фіона Браун
Hours оновлений

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

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

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

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

  1. NDS – рідний динамічний SQL
  2. DBMS_SQL

NDS (власний динамічний SQL) – Виконати негайно

Власний динамічний SQL — це простіший спосіб написання динамічного SQL. Він використовує команду "EXECUTE IMMEDIATE" для створення та виконання SQL під час виконання. Але щоб використовувати цей спосіб, тип даних і номер змінної, яка буде використовуватися під час виконання, повинні бути відомі раніше. Це також забезпечує кращу продуктивність і меншу складність у порівнянні з DBMS_SQL.

синтаксис 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • Наведений вище синтаксис показує команду EXECUTE IMMEDIATE.
  • Речення INTO є необов’язковим і використовується, лише якщо динамічний SQL містить оператор select, який отримує значення. Тип змінної має збігатися з типом змінної оператора select.
  • Речення USING є необов’язковим і використовується, лише якщо динамічний SQL містить будь-яку змінну прив’язки.

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

NDS - Виконати негайно

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
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_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

Вихід

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

Пояснення коду:

  • Рядок коду 2-6: Оголошення змінних.
  • Рядок коду 8: кадрування SQL під час виконання. SQL містить змінну прив’язки в умові where ':empno'.
  • Рядок коду 9: Виконання кадрованого тексту SQL (що виконується в рядку коду 8) за допомогою команди NDS "EXECUTE IMMEDIATE"
  • Змінні в реченні INTO (lv_emp_name, ln_emp_no, ln_salary, ln_manager) використовуються для зберігання отриманих значень із запиту SQL (emp_name, emp_no, salary, manager)
  • Речення 'USING' надає значення змінній прив'язки в SQL-запиті (:emp_no).
  • Рядок коду 10-13: Відображення отриманих значень.

DBMS_SQL для динамічного SQL

PL/SQL надає пакет DBMS_SQL, який дозволяє працювати з динамічним SQL. Процес створення та виконання динамічного SQL містить наступний процес.

  • ВІДКРИТИ КУРСОР: динамічний SQL виконуватиметься так само, як a курсор. Отже, щоб виконати оператор SQL, ми повинні відкрити курсор.
  • PARSE SQL: наступним кроком є ​​розбір динамічного SQL. Цей процес лише перевірить синтаксис і збереже запит готовим до виконання.
  • BIND VARIABLE Значення: наступним кроком є ​​призначення значень для змінних прив’язки, якщо такі є.
  • ВИЗНАЧИТИ СТОВПЦЬ: наступним кроком є ​​визначення стовпця за допомогою їх відносних позицій у операторі select.
  • ВИКОНАТИ: наступним кроком є ​​виконання проаналізованого запиту.
  • ОТРИМКА ЗНАЧЕНЬ: Наступним кроком є ​​отримання виконаних значень.
  • ЗАКРИТИ КУРСОР: Після отримання результатів курсор повинен бути закритий.

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

DBMS_SQL для динамічного SQL

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;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
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);

DBMS_SQL для динамічного SQL

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,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||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_ClIRSOR(ln_cursor_id);

END:
/

Вихід

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

Пояснення коду:

  • Рядок коду 1-9: Оголошення змінної.
  • Рядок коду 10: створення оператора SQL.
  • Рядок коду 11: відкриття курсору за допомогою DBMS_SQL.OPEN_CURSOR. Він поверне ідентифікатор відкритого курсора.
  • Рядок коду 12: після того, як курсор відкрито, SQL аналізується.
  • Рядок коду 13: Змінна прив’язки «1001» призначає ідентифікатор курсору замість «:empno».
  • Рядок коду 14-17: Визначення імені стовпця на основі їхнього відносного положення в операторі SQL. У нашому випадку відносна позиція: (1) emp_name, (2) emp_no (3) salary (4) manager. Отже, виходячи з цієї позиції, ми визначаємо цільову змінну.
  • Рядок коду 18: Виконання запиту за допомогою DBMS_SQL.EXECUTE. Він повертає кількість оброблених записів.
  • Рядок коду 19-33: Отримання записів за допомогою циклу та їх відображення.
  • Рядок коду 20: DBMS_SQL.FETCH_ROWS отримає один запис із оброблених рядків. Його можна викликати багаторазово, щоб отримати всі рядки. Якщо він не може отримати рядки, він поверне 0, таким чином виходячи з циклу.

Підсумки

У цьому розділі ми обговорили динамічний SQL і способи виконання DYNAMIC SQL. Ми також бачили різні етапи виконання динамічного SQL обома способами. Ми також бачили приклади, в яких той самий сценарій обробляється способами NDS і DBMS_SQL для виконання під час виконання.