Підручник з динамічного SQL Oracle PL / SQL: Виконайте негайне & DBMS_SQL

Зміст:

Anonim

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

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

У цьому підручнику ви дізнаєтесь-

  • Способи написання динамічного SQL
  • NDS (Native Dynamic SQL) - Виконати негайно
  • DBMS_SQL для динамічного SQL

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

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

  1. NDS - власний динамічний SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) - Виконати негайно

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

Синтаксис 

EXECUTE IMMEDIATE()[INTO][USING ]
  • У наведеному вище синтаксисі показано команду EXECUTE IMMEDIATE.
  • Пункт INTO є необов'язковим і використовується лише в тому випадку, якщо динамічний SQL містить оператор select, який отримує значення. Тип змінної повинен відповідати типу змінної оператора select.
  • Пункт USING є необов'язковим і використовується лише в тому випадку, якщо динамічний SQL містить будь-яку змінну прив'язки.

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

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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 : XXXEmployee Number: 1001Salary: 15000Manager ED: 1000

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

  • Кодовий рядок 2-6 : Оголошення змінних.
  • Рядок коду 8 : Обрамлення SQL під час виконання. SQL містить змінну прив'язки, де умова ': empno'.
  • Рядок коду 9 : Виконання оформленого тексту SQL (що виконується в рядку коду 8) за допомогою команди NDS 'ВИКОНАЙТЕ НЕЗАБАЧНО
  • Змінні в реченні 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) використовуються для зберігання отриманих значень із запиту SQL (emp_name, emp_no, зарплата, менеджер)
  • Речення 'USING' надає значення змінної прив'язки в запиті SQL (: emp_no).
  • Кодовий рядок 10-13 : Відображення отриманих значень.

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

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

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

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

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:XXXEmployee Number:1001Salary:15000Manager ID:1000

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

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

Резюме

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