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

Содержание:

Anonim

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

Динамический SQL - это методология программирования для создания и выполнения операторов во время выполнения. Он в основном используется для написания универсальных и гибких программ, в которых операторы SQL будут создаваться и выполняться во время выполнения в зависимости от требований.

В этом руководстве вы узнаете:

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

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

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

  1. NDS - собственный динамический SQL
  2. DBMS_SQL

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

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

Синтаксис 

EXECUTE IMMEDIATE()[INTO][USING ]
  • Приведенный выше синтаксис показывает команду EXECUTE IMMEDIATE.
  • Предложение INTO является необязательным и используется только в том случае, если динамический SQL содержит оператор выбора, который выбирает значения. Тип переменной должен соответствовать типу переменной оператора 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 '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 будет выполняться так же, как и курсор. Итак, чтобы выполнить оператор SQL, мы должны открыть курсор.
  • PARSE SQL : Следующим шагом является анализ динамического SQL. Этот процесс просто проверит синтаксис и сохранит готовность запроса к выполнению.
  • Значения BIND VARIABLE : Следующим шагом является присвоение значений для переменных связывания, если они есть.
  • DEFINE COLUMN : Следующим шагом является определение столбца, используя их относительные положения в операторе select.
  • ВЫПОЛНИТЬ : следующий шаг - выполнить проанализированный запрос.
  • ПОЛУЧИТЬ ЗНАЧЕНИЯ : Следующим шагом является получение выполненных значений.
  • ЗАКРЫТЬ КУРСОР : после получения результатов курсор должен быть закрыт.

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

Резюме

В этом разделе мы обсудили динамический SQL и способы выполнения ДИНАМИЧЕСКОГО SQL. Мы также видели разные этапы выполнения динамического SQL обоими способами. Мы также видели примеры, в которых один и тот же сценарий обрабатывается как в NDS, так и в DBMS_SQL для выполнения во время выполнения.