Что такое динамический sql oracle

Обновлено: 07.07.2024

Ошибки, возникающие при работе с СУБД, можно разделить на следующие группы:

ошибки, генерируемые системой (например, нехватка памяти или повторяющееся значение индекса);

ошибки, генерируемые приложением (например, невыполнение каких-либо условий и проверок).

В языке PL/SQL ошибки всех видов интерпретируются как исключительные ситуации, или исключения.

Исключения могут быть системными и пользовательскими :

- неименованные исключения - имеют только номера (ORA-02292)

Определяются программистом в приложении.
Имеют номер в диапазоне от -20999 до -20000 и текстовое описание.
Инициируются с помощью RAISE_APPLICATION_ERROR

Условия, которые могут привести к неожиданным последствиям или некорректным результатам

Использование значения VARCHAR2 для поля с типом NUMBER в операторе INSERT

Условия, которые не влияют на производительность, но усложняют чтение кода

Конфигурирование производится посредством установки значения параметра PLSQL_WARNINGS.

Посредством установки параметра PLSQL_WARNINGS можно:

Во всех ALTER-операторах значение параметра PLSQL_WARNINGS задается в следующем виде:

Несколько примеров настройки режима выдачи предупреждений

Включение всех предупреждений внутри сессии (полезно при разработке):

ALTER PROCEDURE loc_var COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE', 'ERROR:06002';

Для просмотра текущего значения PLSQL_WARNINGS следует обратиться к представлению ALL_PLSQL_OBJECT_SETTINGS.

Обработка исключений в PL/SQL

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

Независимо от того, как и по какой причине было инициировано конкретное исключение, оно обрабатывается одним и тем же обработчиком в разделе исключений.

Любая ошибка может быть обработана только одним обработчиком.

Для обработки исключений в блоке PL/SQL предназначается необязательный раздел EXCEPTION :

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

Обработчик WHEN OTHERS должен быть последним обработчиком в блоке, иначе возникнет ошибка компиляции. Этот обработчик не является обязательным. Если он отсутствует, то все необработанные исключения передадутся в родительский блок, либо в вызывающую хост-систему.

В одном предложении WHEN, можно объединить несколько исключений, используя оператор OR:

Также в одном о6ра6отчике можно ком6инировать имена пользовательских и системных исключений:

WHEN balance_too_low OR zero_divide OR dbms_ldap.invalid_session THEN

Создание собственных исключений

Внутри приложения можно определять свои собственные (пользовательские) исключения.

Сделать это можно в разделе объявлений блока РL/SQL следующим образом:

Для того, чтобы инициировать исключение, необходимо воспользоваться оператором RAISE:

После этого выполнение программы переходит в раздел EXCEPTION на соответствующий обработчик:

Для того, чтобы присвоить ошибке номер и создать для нее текстовое описание, следует воспользоваться процедурой RAISE_APPLICATION_ERROR :

Связываем исключение с кодом ошибки

Предположим, у нас есть программа, при выполнении которой может сгенерироваться ошибка, связанная с данными, например ОRА-01843: not a valid month.

Для перехвата этой ошибки в код программы потребуется поместить такой обработчик:

Конкретную ошибку Oracle можно привязать к именованному исключению с помощью директивы компилятора EXCEPTION_INIT:

Теперь имя ошибки говорит само за себя и никакие литеральные номера ошибок, которые трудно запомнить, не понадобятся.

Установив такую связь, можно инициировать исключение по имени и использовать это имя в предложении WHEN обработчика ошибок.

Именованные системные исключения

B Oracle для некоторых системных исключений определены стандартные имена, которые заданы с помощью директивы компилятора EXCEPTION_INIT во встроенных пакетах.

Наиболее важные и широко применяемые из них определены в пакете STANDARD.

То обстоятельство, что этот пакет используется по умолчанию, означает, что на определенные в нем исключения можно ссылаться без указания в качестве префикса имени пакета.
Например, если необходимо обработать в программе исключение NO_DАТА_FOUND, то это можно сделать посредством любого из двух операторов:

Инициирование исключений

Программно инициировать исключение можно посредством оператора RAISE или процедуры RAISE_АРРLICATIОN_ERROR.

Оператор RAISE

С помощью оператора RAISE можно инициировать как собственные, так и системные исключения.

Инициирование исключения, определенного в текущем блоке, а также инициирование системных исключений, объявленных в пакете STANDARD

Если исключение объявлено в любом другом пакете, отличном от STANDARD, имя исключения нужно уточнять именем пакета

Не требует указывать имя исключения, но используется только в предложении WHEN раздела исключений. Этой формой оператора следует пользоваться, когда в обработчике исключений нужно повторно инициировать то же самое исключение

Использование процедуры RAISE_APPLICATION_ERROR

PROCEDURE RAISE_APPLICATION_ERROR( num BINARY_INTEGER,

Использование функций обработки ошибок

Предложение WHEN OTHERS используется для перехвата исключений, не указанных в предложениях WHEN. Однако в этом обработчике тоже нужна информация о том, какая именно ошибка произошла. Для ее получения можно воспользоваться функцией SQLCODE , возвращающей номер возникшей ошибки (значение 0 указывает, что в стеке ошибок нет ни одной ошибки).

Функция возвращает отформатированную строку со стеком вызовов в приложении PL/SQL.

она возвращает до 2000 символов (SQLERRM возвращает 512 символов)

этой функции нельзя в качестве аргумента передать код ошибки

Она возвращает отформатированную строку с содержимым стека программ и номеров строк. Ее выходные данные позволяют отследить строку, в которой изначально была инициирована ошибка.

Продолжение работы после возникновения исключения

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

Эскалация необработанного исключения

Инициированное исключение обрабатывается в соответствии с определенными правилами. Сначала PL/SQL ищет обработчик исключения в текущем блоке (анонимном блоке, процедуре или функции). Если такового не нашлось, исключение передается в родительский блок. Затем PL/SQL пытается обработать исключение, инициировав его еще раз в родительском блоке. И так в каждом внешнем по отношению к другому блоке до тех пор, пока все они не будут исчерпаны. После этого PL/SQL возвращает необработанное исключение в среду приложения, из которого был выполнен самый внешний блок PL/SQL.

На что стоит обратить внимание

Если при выполнении нескольких DML-операций в SQL-среде возникает исключительная ситуация, то все операции, предшествующие ошибочному оператору, считаются выполненными корректно и не откатываются.

Если те же самые DML-операции обернуть в блок BEGIN . END - тогда при возникновении исключительной ситуации на очередном DML-операторе все предыдущие успешно (!) выполненные операции откатываются. Откат происходит к моменту начала выполнения блока.
Т.е. блок либо выполняется целиком, либо не выполняется совсем.

Если обработчик завершается с повторной инициацией исключительной ситуации (напр., WHEN OTHERS then raise ), то все изменения, проделанные в блоке, откатываются.

Если же выход из блока происходит через обработку исключительной ситуации и повторной инициации исключительной ситуации не происходит (напр., WHEN OTHERS then null ), то блок считается исполненным успешно и отката изменений, которые внутри него произошли, не будет (. ). То есть результат работы операторов, предшествующих ошибочному оператору, останется в БД.
Поэтому, если по бизнес-логике такого не нужно, то в обработчике исключения надо явно делать ROLLBACK.

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

Статическими называются жестко закодированные инструкции и операторы, которые не изменяются с момента компиляции программы .
Инструкции динамического SQL формируются, компилируются и вызываются непосредственно во время выполнения программы .
Следует отметить, что такая гибкость языка открывает перед программистами огромные возможности и позволяет писать универсальный код многократного использования.

На практике NDS в подавляющем большинстве является более предпочтительным решением.

Инструкции NDS

NDS представлен в языке РL/SQL единственной инструкцией EXECUTE IMMEDIATE , немедленно выполняющей заданную SQL инструкцию, а также расширением инструкции OPEN FOR , позволяющей выполнять сложные динамические запросы.

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

Инструкция EXECUTE IMMEDIATE

Инструкция ЕХЕСUТЕ IMMEDIATE , используемая для выполнения необходимой SQL-инструкции, имеет следующий синтаксис:

USING - предложение, определяющее параметры SQL-инструкции и используемое как в динамическом SQL, так и в динамическом РL/SQL (способ передачи параметра дается только в РL/SQL, причем по умолчанию для него установлен режим передачи IN).

Инструкция ЕХЕСUТЕ IMMEDIATE может использоваться для выполнения любой SQL-инструкции или PL/SQL-блока, за исключением многострочных запросов.

Если SQL-строка заканчивается точкой с запятой, она интерпретируется как блок РL/SQL. В противном случае воспринимается как DML- или DDL-инструкция.

Строка может содержать формальные параметры, но с их помощью не могут быть заданы имена объектов схемы, скажем, такие, как имена столбцов таблицы.

Хранимую процедуру, выполняющую любую инструкцию DDL, можно создать так:
CREATE OR REPLACE PROCEDURE execDDL(ddl_string in varchar2) is
BEGIN
EXECUTE IMMEDIATE ddl_string;
END;

Инструкция OPEN FOR

: хост_переменная_курсор - переменная-курсор, объявленная в хост-среде PL/SQL;

Режимы использования параметров

При передаче значений параметров SQL-инструкции можно использовать один из трех режимов:

- IN (только чтение, задан по умолчанию);
- OUT (только запись);
- IN OUT (чтение и запись).

Когда выполняется динамический запрос, все параметры SQL-инструкции, за исключением параметра в предложении RETURNING, должны передаваться в режиме IN:

DECLARE
v_emp_name1 VARCHAR2(50) := 'Марина';
v_emp_name2 VARCHAR2(50) := 'Иванова';
v_emp_name VARCHAR2(50);
v_id_emp NUMBER := 1666;
BEGIN
EXECUTE IMMEDIATE 'update ADM.EMPLOYEE ' ||
'set emp_name1 = :v_emp_name1, ' ||
'emp_name2 = :v_emp_name2 ' ||
'where id_emp = :v_id_emp ' ||
'returning emp_name1 into :val'
USING IN v_emp_name1, IN v_emp_name2, IN v_id_emp, OUT v_emp_name;
dbms_output.put_line(v_emp_name);
END;
/

Дублирование формальных параметров

При выполнении динамической SQL-инструкции связь между формальными и фактическими параметрами устанавливается в соответствии с их позициями. Однако интерпретация одноименных параметров зависит от того, какой код, SQL или PL/SQL, выполняется с помощью оператора EXECUTE IMMEDIATE:

При выполнении динамической SQL-инструкции (DML- или DDL-строки, не оканчивающейся точкой с запятой) параметр подстановки нужно задать для каждого формального параметра, даже если их имена повторяются.

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

Передача значений NULL

При попытке передать NULL в качестве параметра подстановки:

Дело в том, что NULL типа данных не имеет и поэтому не может являться значением одного из типов данных SQL.

1. Можно использовать неинициализированную переменную.

2. Можно преобразовать NULL в типизированное значение: USING TO_NUMBER(NULL) ;

Использование пакета DBMS_SQL

Пакет DBMS_SQL предоставляет возможность использования в PL/SQL динамического SQL для выполнения DML- или DDL-операций.

Выполнение одного динамического оператора с использованием пакета DBMS_SQL состоит, как правило, из следующих шагов:

Связывание текста динамического оператора с курсором и его синтаксический анализ и разбор;

Связывание входных аргументов с переменными, содержащими реальные значения;

Термином «динамический SQL» обозначаются команды SQL, которые конструируются и вызываются непосредственно во время выполнения программы. Статическими называются жестко закодированные команды SQL, которые не изменяются с момента компиляции программы. «Динамическим PL/SQL» называют целые блоки кода PL/SQL, которые строятся динамически, а затем компилируются и выполняются.

Пожалуй, написание динамических команд SQL и динамических программ PL/SQL было самым интересным делом из всего, что я когда-либо делал на языке PL/SQL. Конструирование и динамическое выполнение обеспечивает невероятную гибкость. У разработчика появляется возможность создавать обобщенный код с широким спектром применения. Несмотря на это, динамический SQL следует применять лишь там, где это необходимо; решения со статическим SQL всегда являются предпочтительными, потому что динамические решения более сложны, создают больше проблем с отладкой и тестированием, обычно медленнее работают и усложняют сопровождение. Что же можно делать с динамическими конструкциями SQL и PL/SQL ? Лишь несколько идей:

Выполнение команд DDL. Со статическим кодом SQL в PL/SQL могут выполняться только запросы и команды DML. А если вы захотите создать таблицу или удалить индекс? Используйте динамический SQL!
Поддержка специализированных запросов и требований к обновлению веб-приложений. К интернет-приложениям часто предъявляется одно стандартное требование: пользователь должен иметь возможность выбрать столбцы, которые он желает видеть, и изменить порядок просмотра данных (конечно, пользователь может и не понимать, что именно при этом происходит).
Оперативное изменение бизнес-правил и формул. Вместо того чтобы жестко фиксировать бизнес-правила в коде, можно разместить соответствующую логику в таблицах. Во время выполнения программа генерирует и выполняет код PL/SQL, необходимый для применения правил.

Начиная с Oracle7, поддержка динамического SQL осуществлялась в виде встроенного пакета DMBS_SQL . В Oracle8i для этого появилась еще одна возможность — встроенный динамический SQL (Native Dynamic SQL, NDS). NDS интегрируется в язык PL/SQL; пользоваться им намного удобнее, чем DBMS_SQL . Впрочем, для некоторых ситуаций

лучше подходит DBMS_SQL . На практике в подавляющем большинстве случаев NDS является более предпочтительным решением.

Команды NDS в PL/SQL

Главным достоинством NDS является его простота. В отличие от пакета DBMS_SQL, для работы с которым требуется знание десятка программ и множества правил их использования, NDS представлен в PL/SQL единственной новой командой EXECUTE IMMEDIATE , которая немедленно выполняет заданную команду SQL, а также расширением существующей команды OPEN FOR , позволяющей выполнять многострочные динамические запросы.

Команды EXECUTE IMMEDIATE и OPEN FOR не будут напрямую доступны в Oracle Forms Builder и Oracle Reports Builder до тех пор, пока версия PL/SQL этих инструментов не будет обновлена до Oracle8i и выше. Для более ранних версий придется создавать хранимые программы, скрывающие вызовы этих конструкций; эти хранимые программы могут выполняться в клиентском коде PL/SQL.

Команда EXECUTE IMMEDIATE

Команда EXECUTE IMMEDIATE используется для немедленного выполнения заданной команды SQL. Она имеет следующий синтаксис:

Здесь строка_SQL — строковое выражение, содержащее команду SQL или блок PL/SQL; переменная — переменная, которой присваивается содержимое поля, возвращаемого запросом; запись — запись, основанная на пользовательском типе или типе %ROWTYPE , принимающая всю возвращаемую запросом строку; аргумент — либо выражение, значение которого передается команде SQL или блоку PL/SQL, либо идентификатор, являющийся входной и/или выходной переменной для функции или процедуры, вызываемой из блока PL/SQL. Секция INTO используется для однострочных запросов. Для каждого значения столбца, возвращаемого запросом, необходимо указать переменную или поле записи совместимого типа. Если INTO предшествует конструкция BULK COLLECT , появляется возможность выборки множественных строк в одну или несколько коллекций. Секция USING предназначена для передачи аргументов строке SQL. Она используется с динамическим SQL и PL/SQL, что и позволяет задать режим параметра. Этот режим актуален только для PL/SQL и секции RETURNING . По умолчанию для параметров используется режим IN (для команд SQL допустима только эта разновидность аргументов).

Команда execute immediate может использоваться для выполнения любой команды SQL или блока PL/SQL. Строка может содержать формальные параметры, но они не могут связываться с именами объектов схемы (например, именами таблиц или столбцов).

При выполнении команды DDL в программе также происходит закрепление операции. Если вы не хотите, чтобы закрепление, обусловленное DDL, отражалось на текущих изменениях в других частях приложения, поместите динамическую команду DDL в процедуру автономной транзакции. Пример такого рода приведен в файле auton_ddl.sql.

При выполнении команды исполняющее ядро заменяет в SQL-строке формальные параметры (идентификаторы, начинающиеся с двоеточия — например, :salary_value )

фактическими значениями параметров подстановки в секции USING . Не допускается передача литерала NULL — вместо него следует указывать выражение соответствующего типа, результат вычисления которого может быть равен NULL .

NDS поддерживает все типы данных SQL. Переменные и параметры команды могут быть коллекциями, большими объектами (LOB), экземплярами объектных типов, документами XML и т. д. Однако NDS не поддерживает типы данных, специфические для PL/SQL, такие как BOOLEAN , ассоциативные массивы и пользовательские типы записей. С другой стороны, секция INTO может содержать запись PL/SQL, количество и типы полей которой соответствуют значениям, выбранным динамическим запросом. Рассмотрим несколько примеров.

Проще не бывает, верно?

О Создание хранимой процедуры, выполняющей любую команду DDL:

При наличии процедуры exec_ddl тот же индекс может быть создан следующим образом:

Получение количества строк в произвольной таблице для заданного предложения WHERE :

Таким образом, нам больше не понадобится писать команду SELECT COUNT (*) ни в SQI*Plus , ни в программах PL/SQL . Она заменяется следующим блоком кода:

Изменение числового значения в любом столбце таблицы employees:

Безусловно, для такой гибкости объем кода получился совсем небольшим! В этом примере показано, как используется подстановка: после разбора команды UPDATE ядро PL/SQL заменяет в ней формальные параметры (:the_value, :lo и :hi) значениями переменных. Также обратите внимание, что в этом случае атрибут курсора SQL%ROWCOUNT используется точно так же, как при выполнении статических команд DML. Выполнение разных блоков кода в одно время в разные дни. Имя каждой программы строится по схеме ДЕНЬ_set_sd^edule . Все процедуры получают четыре аргумента: при вызове передается код работника employee_id и час первой встречи, а процедура возвращает имя работника и количество встреч в заданный день. Задача решается с использованием динамического PL/SQL:

Привязка значения BOOLEAN , специфического для PL/SQL , командой EXECUTE IMMEDIATE (новая возможность 12c):

Как видите, команда EXECUTE IMMEDIATE позволяет исключительно легко выполнять динамические команды SQL и блоки PL/SQL с удобным синтаксисом.

Команда OPEN FOR

Команда OPEN FOR изначально не была включена в PL/SQL для NDS ; она появилась в Oracle7 и предназначалась для работы с курсорными переменными. Затем ее синтаксис был расширен для реализации многострочных динамических запросов. При использовании пакета DBMS_SQL реализация многострочных запросов получается очень сложной: приходится производить разбор и подстановку, отдельно определять каждый столбец, выполнять команду, выбирать сначала строки, а затем — последовательно значения каждого столбца. Код получается весьма громоздким.

Для динамического SQL разработчики Oracle сохранили существующий синтаксис OPEN, но расширили его вполне естественным образом:

Здесь курсорная переменная — слаботипизированная курсорная переменная; хост_ переменная — курсорная переменная, объявленная в хост-среде PL/SQL, например в программе OCI (Oracle Call Interface); cmpoка SQL — команда SELECT , подлежащая динамическому выполнению.

Курсорные переменные рассматривались в этом блоге. Здесь мы подробно расскажем об их использовании с NDS .

В следующем примере объявляется тип REF CURSOR и основанная на нем переменная- курсор, а затем с помощью команды OPEN FOR открывается динамический запрос:

После того как запрос будет открыт командой OPEN FOR , синтаксис выборки записи, закрытия курсорной переменной и проверки атрибутов курсора ничем не отличается от синтаксиса статических курсорных переменных и явных курсоров.

Давайте поближе познакомимся с командой OPEN FOR . При выполнении OPEN FOR ядро PL/SQL :

связывает курсорную переменную с командой SQL, заданной в строке запроса;
вычисляет значения параметров и заменяет ими формальные параметры в строке запроса;
выполняет запрос;
идентифицирует результирующий набор;
устанавливает курсор на первую строку результирующего набора;
обнуляет счетчик обработанных строк, возвращаемый атрибутом %rowcount . Обратите внимание: параметры подстановки, заданные в секции USING , вычисляются только при открытии курсора. Это означает, что для передачи тому же динамическому запросу другого набора параметров нужно выполнить новую команду OPEN FOR .

Для выполнения многострочного запроса (то есть запроса, возвращающего набор строк) необходимо:

объявить тип ref cursor (или использовать встроенный тип sys_refcursor );
объявить на его основе курсорную переменную;
открыть курсорную переменную командой OPEN FOR ;
с помощью команды fetch по одной извлечь записи результирующего набора;
при необходимости проверить значения атрибутов ( %found, %notfound, %rowcount, %isopen) ;
закрыть курсорную переменную обычной командой Как правило, после завершения работы с курсорной переменной следует явно закрыть ее.

Следующая простая программа выводит значения поля заданной таблицы в строках, отбираемых с помощью секции WHERE (столбец может содержать числа, даты или строки, файл showcol.sp):

Примерный результат выполнения этой процедуры выглядит так:

Столбцы даже можно комбинировать:

Выборка в переменные или записи

Команда FETCH в процедуре showcol из предыдущего раздела осуществляет выборку в отдельную переменную. Также возможна выборка в серию переменных:

Работа с длинным списком переменных в списке FETCH может быть громоздкой и недостаточно гибкой; вы должны объявить переменные, поддерживать синхронизацию этого набора значений в команде FETCH и т. д. Чтобы упростить жизнь разработчика, NDS позволяет осуществить выборку в запись, как показано в следующем примере:

Конечно, во многих ситуациях выполнение команды SELECT * нежелательно; если ваша таблица содержит сотни столбцов, из которых вам нужны два-три, эта команда крайне неэффективна. Лучше создать тип записи, соответствующий разным требованиям. Эти структуры лучше всего разместить в спецификации пакета, чтобы их можно было использовать во всем приложении. Вот один из таких пакетов:

С таким пакетом приведенный выше код можно переписать следующим образом:

Секция USING в OPEN FOR

Как и в случае с командой EXECUTE IMMEDIATE , при открытии курсора можно передать аргументы. Для запроса можно передать только аргументы IN. Аргументы также повышают эффективность SQL, упрощая написание и сопровождение кода. Кроме того, они могут радикально сократить количество разобранных команд, хранящихся в общей памяти SGA, а это повышает вероятность того, что уже разобранная команда будет находиться в SGA в следующий раз, когда она вам потребуется.

Вернемся к процедуре showcol. Эта процедура получает полностью обобщенную секцию WHERE . Допустим, действуют более специализированные требования: я хочу вывести (или иным образом обработать) всю информацию столбцов для строк, содержащих столбец даты со значением из некоторого диапазона. Другими словами, требуется обеспечить поддержку запроса:

а также запроса:

Также нужно проследить за тем, чтобы компонент времени столбца даты не учитывался в условии WHERE .

Заголовок процедуры выглядит так:

Теперь команда OPEN FOR содержит два формальных параметра и соответствующую секцию USING :

Команда построена таким образом, что при отсутствии конечной даты секция WHERE возвращает строки, у которых значение в столбце даты совпадает с заданным значением dt1 . Остальной код процедуры showcol остается неизменным, не считая косметических изменений в выводе заголовка.

Следующий вызов новой версии showcol запрашивает имена всех работников, принятых на работу в 1982 году:

О четырех категориях динамического SQL

Итак, мы рассмотрели две основные команды, используемые для реализации динамического SQL в PL/SQL . Теперь пришло время сделать шаг назад и рассмотреть четыре разновидности (категории) динамического SQL , а также команды NDS , необходимые для реализации этих категорий. Категории и соответствующие команды NDS перечислены в табл. 1.

Таблица 1. Четыре категории динамического SQL

тип	описание	Команды NDS
Категория 1	Без запросов; только команды DDL и команды UPDATE, INSERT, MERGE и DELETE без параметров	EXECUTE IMMEDIATE без секций USING и INTO
Категория 2	Без запросов; только команды DDL и команды UPDATE, INSERT, MERGE и DELETE с фиксированным количеством параметров	EXECUTE IMMEDIATE с секцией USING
Категория 3 (одна строка)	Запросы (SELECT) с фиксированным количеством столбцов и параметров, с выборкой одной строки данных	EXECUTE IMMEDIATE с секциями USING и INTO
Категория 3 (несколько строк)	Запросы (SELECT) с фиксированным количеством столбцов и параметров, с выборкой одной или нескольких строк данных	EXECUTE IMMEDIATE с секциями USING и BULK COLLECT INTO или OPEN FOR с динамической строкой
Категория 4	Команда, в которой количество выбранных столб- цов (для запроса) или количество параметров неизвестно до стадии выполнения	Для категории 4 необходим пакет DBMS_SQL

Категория 1

Следующая команда DDL является примером динамического SQL категории 1:

Команда UPDATE также относится к динамическому SQL категории 1, потому что единственным изменяемым аспектом является имя таблицы — параметры отсутствуют:

Категория 2

Если заменить оба жестко фиксированных значения в предыдущей команде DML формальными параметрами (двоеточие, за которым следует идентификатор), появляется динамический SQL категории 2:

Секция USING содержит значения, которые будут подставлены в строку SQL после разбора и перед выполнением.

Категория 3

Команда динамического SQL категории 3 представляет собой запрос с фиксированным количеством параметров (или вообще без них). Вероятно, чаще всего вы будете создавать команды динамического SQL именно этого типа. Пример:

Здесь я запрашиваю всего два столбца из таблицы employees и сохраняю их значения в двух локальных переменных из секции INTO . Также используется один параметр. Так как значения этих компонентов являются статическими на стадии компиляции, я использую динамический SQL категории 3.

Категория 4

Наконец, рассмотрим самый сложный случай: динамический SQL категории 4. Возьмем предельно обобщенный запрос:

На момент компиляции кода я понятия не имею, сколько столбцов будет запрашиваться из таблицы employees . Возникает проблема: как написать команду FETCH INTO , которая будет обеспечивать подобную изменчивость? Есть два варианта: либо вернуться к DBMS_SQL для написания относительно тривиального (хотя и объемистого) кода, либо переключиться на исполнение динамических блоков PL/SQL .

К счастью, ситуации, требующие применения категории 4, встречаются редко.

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

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

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

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

NDS — собственный динамический SQL
DBMS_SQL

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

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

Синтаксис

Приведенный выше синтаксис показывает команду EXECUTE IMMEDIATE.
Предложение INTO является необязательным и используется только в том случае, если динамический SQL содержит оператор выбора, который выбирает значения. Тип переменной должен совпадать с типом переменной оператора выбора.
Предложение USING является необязательным и используется только в том случае, если динамический SQL содержит какую-либо переменную связывания.

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

Объяснение кода:

Строка кода 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.

Вывод

Объяснение кода:

Строка кода 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 для выполнения во время выполнения.

Oracle/PLSQL оператор EXECUTE IMMEDIATE подготавливает (анализирует) и немедленно выполняет динамический SQL-запрос или анонимный PL/SQL блок.
Основным аргументом EXECUTE IMMEDIATE является строка, содержащая SQL-запрос для выполнения. Вы можете создать строку, используя конкатенацию, или использовать предопределенную строку.
Динамическая строка может содержать любой оператор SQL (без последней точки с запятой), за исключением многострочных запросов или любой PL/SQL блок (с последней точкой с запятой).
Строка dynamic_string также может содержать заполнители, произвольные имена, которым предшествует двоеточие, для аргументов связывания bind_argument . В этом случае вы указываете, какие переменные PL/SQL соответствуют заполнителям, с помощью операторов INTO, USING и RETURNING INTO. Во время выполнения аргументы связывания заменяют соответствующие заполнители в динамической строке. Каждый заполнитель должен быть связан с аргументом связывания в предложении USING и/или предложении RETURNING INTO.

Синтаксис

Синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в переменную или строку:

EXECUTE IMMEDIATE dynamic_string
[ INTO <[define_variable[, define_variable] . | record_name>]
[USING [IN | OUT | IN OUT] bind_argument ]
returning_clause;

или синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в коллекцию

EXECUTE IMMEDIATE dynamic_string
[[ BULK COLLECT] INTO ]
[USING [IN | OUT | IN OUT] bind_argument]
returning_clause;

Параметры или аргументы

dynamic_string Строковый литерал, переменная или выражение, представляющее один оператор SQL или блок PL/SQL. Он должен иметь тип CHAR или VARCHAR2, а не NCHAR или NVARCHAR2. BULK COLLECT Сохраняет значения результатов в одной или нескольких коллекциях для более быстрых запросов, чем циклы с операторами FETCH. INTO Используется только для однострочных запросов, в этом разделе указываются переменные или записи, в которые извлекаются значения столбцов. Для каждого значения, полученного запросом, в предложении INTO должна быть соответствующая тип-совместимая переменная или поле. define_variable Переменная, в которой сохраняется значение выбранного столбца. record_name Пользовательская запись или запись %ROWTYPE, в которой сохраняется выбранная строка. bind_argument Выражение, значение которого передается в динамический оператор SQL, или переменная, в которой сохраняется значение, возвращаемое динамическим оператором SQL. collection_name Объявленная коллекция, в которую извлекаются значения select_item из dynamic_string . Для каждого select_item должна быть соответствующая, совместимая с типом коллекция в списке. host_array_name Массив (объявленный в хост-среде PL/SQL и переданный PL/SQL как переменная связывания), в который извлекаются значения select_item. Для каждого select_item должен быть соответствующий, совместимый с типом массив в списке. Массивы хоста должны начинаться с двоеточия. USING По умолчанию - IN. Определяет список входных и/или выходных аргументов привязки. returning_clause Возвращает значения из вставленных строк, устраняя необходимость SELECT строки после. Вы можете извлечь значения столбца в переменные или в коллекции. Вы не можете использовать предложение RETURNING для удаленной или параллельной вставки. Если инструкция не влияет ни на какие строки, значения переменных, указанных в предложении RETURNING, не определены.

Примеры:

Некоторые примеры динамического SQL

Рассмотрим несколько примеров использования Oracle/PLSQL оператора EXECUTE IMMEDIATE, чтобы понять как использовать EXECUTE IMMEDIATE в Oracle/PLSQL.
Описание команд в комментариях (--).

Читайте также: