Учебники

23) Динамический SQL в PL / SQL

Март 11, 2020

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

Динамический 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 содержит оператор выбора, который выбирает значения. Тип переменной должен совпадать с типом переменной оператора выбора.
  • Предложение USING является необязательным и используется только в том случае, если динамический SQL содержит какую-либо переменную связывания.

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

Динамический SQL в PL / SQL 

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 содержит переменную связывания, в которой условие ‘: 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» предоставляет значения переменной bind в запросе SQL (: emp_no).
  • Строка кода 10-13 : отображение выбранных значений.

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

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

  • OPEN CURSOR : динамический SQL будет выполняться так же, как курсор. Таким образом, чтобы выполнить инструкцию SQL, мы должны открыть курсор.
  • PARSE SQL : следующий шаг — анализ динамического SQL. Этот процесс будет просто проверять синтаксис и держать запрос готовым к выполнению.
  • Переменные BIND Значения : Следующим шагом является назначение значений для переменных связывания, если таковые имеются.
  • ОПРЕДЕЛИТЬ КОЛОННУ : Следующий шаг — определить столбец, используя его относительные позиции в операторе выбора.
  • ВЫПОЛНИТЬ : Следующий шаг — выполнить проанализированный запрос.
  • FETCH VALUES : Следующий шаг — выборка выполненных значений.
  • ЗАКРЫТЬ КУРСОР : После того, как результаты получены, курсор должен быть закрыт.

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

Динамический SQL в PL / 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);

Динамический SQL в PL / 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 для выполнения во время выполнения.

 

100
Share: