Oracle Урок за PL/SQL Dynamic SQL: Незабавно изпълнение & DBMS_SQL

Какво е динамичен SQL?

Динамичен SQL е методология за програмиране за генериране и изпълнение на оператори по време на изпълнение. Използва се главно за писане на програми с общо предназначение и гъвкави програми, където SQL операторите се създават и изпълняват по време на изпълнение въз основа на изискването, например когато имената на таблици, списъците с колони или условията WHERE не са известни, докато програмата не се изпълни.

Начини за писане на динамичен SQL

PL/SQL предлага два начина за писане на динамичен SQL:

1. NDS – собствен динамичен SQL (използването на оператори EXECUTE IMMEDIATE и OPEN-FOR)
2. DBMS_SQL (доставен пакет)

Общото правило е просто: ако броят и типовете данни на входните и изходните променливи са известни по време на компилация, използвайте Native Dynamic SQL, защото е по-бърз и изисква по-малко код. Когато тази информация е известна само по време на изпълнение, използвайте пакета DBMS_SQL.

NDS (Native Dynamic SQL) – Незабавно изпълнение

Native Dynamic 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 с един ред; той записва върнатите стойности в променливи или запис. Всяка избрана колона се нуждае от съвместима с типа променлива.
• ИЗПОЛЗВАНЕ на клауза: Незадължително. Предоставя свързващи променливи. Режимът по подразбиране е 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 код. Това проверява синтаксиса и поддържа заявката готова за изпълнение.
• Стойности на BIND VARIABLE: Задайте стойностите на свързващите променливи, ако има такива.
• ДЕФИНИРАНЕ НА КОЛОНА: Дефинирайте всяка колона, използвайки нейната относителна позиция в оператора 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 по време на изпълнение, но са подходящи за различни ситуации:

• Използвайте Native Dynamic SQL (EXECUTE IMMEDIATE / OPEN-FOR) когато броят и типовете данни на входовете и изходите са известни по време на компилация. Това е по-бързо, по-лесно за четене и изисква по-малко код.
• Използвайте DBMS_SQL когато структурата е неизвестна до момента на изпълнение, например заявка, чийто брой избрани колони или променливи за свързване варира, известна като динамичен SQL метод-4, или оператор, твърде голям, за да се побере в една 32K променлива VARCHAR2.

Въпроси и Отговори