Oracle Учебное пособие по динамическому SQL PL/SQL: немедленное выполнение и DBMS_SQL

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

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

Dynamic SQL — это методология программирования для создания и выполнения операторов во время выполнения. В основном он используется для написания универсальных и гибких программ, в которых операторы SQL будут создаваться и выполняться во время выполнения в зависимости от требований.

Способы написания динамического SQL

PL/SQL предоставляет два способа написания динамического SQL.

  1. NDS – собственный динамический SQL
  2. СУБД_SQL

NDS (собственный динамический SQL) – немедленное выполнение

Собственный динамический SQL — это более простой способ написания динамического SQL. Он использует команду EXECUTE IMMEDIATE для создания и выполнения SQL во время выполнения. Но чтобы использовать этот способ, необходимо заранее знать тип данных и количество переменных, которые будут использоваться во время выполнения. Он также обеспечивает лучшую производительность и меньшую сложность по сравнению с DBMS_SQL.

Синтаксис 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • Приведенный выше синтаксис показывает команду EXECUTE IMMEDIATE.
  • Предложение INTO является необязательным и используется только в том случае, если динамический SQL содержит оператор выбора, который извлекает значения. Тип переменной должен совпадать с типом переменной оператора 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 содержит переменную связывания, в которой условие «:empno».
  • Строка кода 9: Выполнение заключенного в рамку текста SQL (выполняется в строке кода 8) с помощью команды NDS «EXECUTE IMMEDIATE».
  • Переменные в предложении 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, мы должны открыть курсор.
  • АНАЛИЗ SQL: Следующий шаг — анализ динамического SQL. Этот процесс просто проверит синтаксис и подготовит запрос к выполнению.
  • ПРИВЯЗКА ПЕРЕМЕННОЙ Значения: Следующий шаг — присвоение значений переменным привязки, если таковые имеются.
  • ОПРЕДЕЛИТЬ КОЛОННУ: Следующий шаг — определить столбец, используя их относительные позиции в операторе выбора.
  • ВЫПОЛНИТЬ: Следующий шаг — выполнить анализируемый запрос.
  • ПОЛУЧИТЬ ЗНАЧЕНИЯ: Следующий шаг — получить выполненные значения.
  • ЗАКРЫТЬ КУРСОР: После получения результатов курсор должен закрыться.

Пример 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) зарплата (4) менеджер. Итак, исходя из этой позиции, мы определяем целевую переменную.
  • Строка кода 18: выполнение запроса с использованием DBMS_SQL.EXECUTE. Возвращает количество обработанных записей.
  • Строка кода 19–33: Извлечение записей с помощью цикла и их отображение.
  • Строка кода 20: DBMS_SQL.FETCH_ROWS выберет одну запись из обработанных строк. Его можно вызывать неоднократно для получения всех строк. Если он не может получить строки, он вернет 0, выйдя из цикла.

Резюме

В этом разделе мы обсудили динамический SQL и способы его выполнения. Мы также рассмотрели различные этапы выполнения динамического SQL обоими способами. Мы также видели примеры, в которых один и тот же сценарий обрабатывается как способами NDS, так и DBMS_SQL для выполнения выполнения во время выполнения.