Oracle/PLSQL динамический SQL

В этом учебном материале вы узнаете, как использовать динамический SQL в Oracle/PLSQL с синтаксисом и примерами.

Описание

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

Оператор EXECUTE IMMEDIATE

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 {host_array_name | collection_name}]
[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.
Описание команд в комментариях (--).

Пример процедуры динамического SQL, которая принимает имя таблицы и предложение WHERE

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

Указание режимов параметров для переменных связывания в строках динамического SQL

С предложением USING режимом по умолчанию является IN, поэтому вам не нужно указывать режим параметров для аргументов связывания ввода.
С предложением RETURNING INTO режим имеет значение OUT, поэтому вы не можете указать режим параметров для выходных аргументов связывания.
Вы должны указать режим параметров в более сложных случаях, таких как этот, где вы вызываете процедуру из динамического блока PL/SQL:

Пример:

Чтобы вызвать процедуру из динамического блока PL/SQL, необходимо указать режим IN OUT для аргумента связывания, связанного с формальным параметром deptno, следующим образом:

Построение динамического запроса с помощью динамического SQL

Для обработки динамического многострочного запроса вы используете три оператора: OPEN-FOR, FETCH и CLOSE.
Сначала вы открываете переменную курсора для многострочного запроса. Затем вы выбираете строки из набора результатов по одной за раз.
Когда все строки обработаны, вы закрываете (CLOSE) курсорную переменную.

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

Примеры динамического SQL для типов объектов и коллекций

Следующий пример иллюстрирует использование объектов и коллекций. Предположим, вы определили тип объекта Person и VARRAY тип Hobbies следующим образом:

Используя динамический SQL, вы можете создать пакет, который использует эти типы:

Из анонимного блока вы можете вызвать процедуры из пакета TEAMS:

Использование Bulk (множественного) SQL в динамическом SQL

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

Используя следующие команды, предложения и атрибут курсора, ваши приложения могут создавать объемные операторы SQL, а затем выполнять их динамически во время выполнения:

  • BULK FETCH
  • BULK EXECUTE IMMEDIATE
  • FORALL
  • COLLECT INTO
  • RETURNING INTO
  • %BULK_ROWCOUNT

Использование динамического SQL с Bulk SQL

Массовое (множественное) связывание позволяет Oracle связать переменную в операторе SQL с коллекцией значений.
Тип коллекции может быть любым типом коллекции Oracle/PLSQL (index-by table, nested table или varray).
Элементы коллекции должны иметь тип данных SQL, такой как CHAR, DATE или NUMBER.
Три оператора поддерживают динамическое массовое связывание: EXECUTE IMMEDIATE, FETCH и FORALL.

EXECUTE IMMEDIATE

  • Вы можете использовать предложение BULK COLLECT INTO с оператором EXECUTE IMMEDIATE, чтобы хранить значения из каждого столбца набора результатов запроса в отдельной коллекции.
  • Вы можете использовать предложение RETURNING BULK COLLECT INTO с оператором EXECUTE IMMEDIATE, чтобы сохранить результаты оператора INSERT, UPDATE или DELETE в наборе коллекций.

FETCH

  • Вы можете использовать предложение BULK COLLECT INTO с оператором FETCH для хранения значений из каждого столбца курсора в отдельной коллекции.

FORALL

  • Вы можете совместить EXECUTE IMMEDIATE инструкцию с RETURNING BULK COLLECT INTO внутри инструкции FORALL. Вы можете хранить результаты всех операторов INSERT, UPDATE или DELETE в наборе коллекций.
  • Вы можете передать индексированные элементы коллекции в инструкцию EXECUTE IMMEDIATE через предложение USING.
  • Вы не можете объединить индексированные элементы непосредственно в строковый аргумент для EXECUTE IMMEDIATE; например, вы не можете создать коллекцию имен таблиц и написать оператор FORALL, где каждая итерация применяется к другой таблице.

Примеры динамический SQL с предложением BULK COLLECT INTO

Вы можете связать определенные переменные в динамическом запросе, используя предложение BULK COLLECT INTO. Как показано в следующем примере, вы можете использовать это предложение в массовом выражении FETCH или в массовом выражении EXECUTE IMMEDIATE:

Пример динамический SQL с предложением RETURNING BULK COLLECT INTO

Только операторы INSERT, UPDATE и DELETE могут иметь выходные переменные связывания. Вы массово связываете их в EXECUTE IMMEDIATE с предложением RETURNING BULK COLLECT INTO.
Например:

Пример динамический SQL внутри оператора FORALL

Чтобы связать входные переменные в операторе SQL, вы можете использовать оператор FORALL и предложение USING, как показано ниже. Оператор SQL не может быть запросом.
Например:

Рекомендации по динамическому SQL

В этом разделе показано, как в полной мере использовать преимущества динамического SQL и как избежать некоторых распространенных ошибок.

Когда использовать или пропустить точку с запятой с помощью динамического SQL

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

Повышение производительности динамического SQL с помощью переменных связывания

Когда код ваших операторов INSERT, UPDATE, DELETE, и SELECT находится непосредственно в PL/SQL, PL/SQL превращает переменные в переменные связывания автоматически, чтобы операторы эффективно работали с SQL. Когда вы создаете такие операторы в динамическом SQL, вам нужно указать переменные связывания самостоятельно, чтобы получить ту же производительность.

В приведенном ниже примере Oracle открывает отдельный курсор для каждого отдельного значения emp_id. Это может привести к конфликту ресурсов и снижению производительности, поскольку каждый оператор анализируется и кэшируется.

Вы можете улучшить производительность, используя переменную связывания, которая позволяет Oracle повторно использовать один и тот же курсор для разных значений emp_id:

Передача имен объектов схемы в качестве параметров

Предположим, вам нужна процедура, которая принимает имя любой таблицы базы данных, а затем удаляет эту таблицу из вашей схемы. Вы должны создать строку с оператором, который включает имена объектов, а затем использовать EXECUTE IMMEDIATE для выполнения оператора:

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

Использование дублирующих заполнителей с динамическим SQL

Заполнители в динамическом операторе SQL связаны с аргументами связывания в предложении USING по позиции, а не по имени. Если вы укажете последовательность заполнителей, например :a, :a, :b, :b, вы должны включить в предложение USING четыре элемента. Например, учитывая динамическую строку:

тот факт, что имя x повторяется, не имеет значения. Вы можете кодировать соответствующее предложение USING с четырьмя различными переменными связывания:

Если динамический оператор представляет PL/SQL блок, то правила для дублирующих заполнителей различны. Каждый уникальный заполнитель отображается на один элемент в предложении USING. Если один и тот же заполнитель появляется два или более раз, все ссылки на это имя соответствуют одному аргументу связывания в предложении USING.
В следующем примере все ссылки на заполнитель x связаны с первым аргументом связывания a, а второй уникальный заполнитель y связан со вторым аргументом связывания b.
Например:

Использование атрибутов курсора с динамическим SQL

SQL атрибуты курсора %FOUND, %ISOPEN, %NOTFOUND и %ROWCOUNT работают в динамическом SQL при выдаче INSERT, UPDATE, DELETE или однорядного предложения SELECT:

Аналогично, при добавлении к имени переменной курсора, атрибуты курсора возвращают информацию о выполнении многострочного запроса:

Передача NULL в динамический SQL

Литерал NULL не допускается в предложении USING. Чтобы обойти это ограничение, замените ключевое слово NULL неинициализированной переменной:

Использование связей базы данных с динамическим SQL

Подпрограммы PL/SQL могут выполнять динамические операторы SQL, которые используют ссылки на базы данных для ссылки на объекты в удаленных базах данных:

Цели удаленных вызовов процедур (RPC) могут содержать динамические операторы SQL. Например, предположим, что следующая автономная функция, которая возвращает количество строк в таблице, находится в базе данных Чикаго:

Из анонимного блока вы можете вызвать функцию удаленно, как показано ниже:

Использование прав Invoker с динамическим SQL

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

Например, эта процедура может удалить любой объект базы данных:

Допустим, эта процедура является частью схемы HR. Без этого условия AUTHID процедура всегда удаляла бы объекты в схеме HR, независимо от того, кто ее вызывает. Даже если вы передадите полное имя объекта, эта процедура не будет иметь прав для внесения изменений в другие схемы.

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

Как избежать тупиков с помощью динамического SQL

В некоторых ситуациях выполнение оператора определения данных SQL приводит к тупику. Например, приведенная ниже процедура вызывает взаимоблокировку, поскольку она пытается удалить саму себя. Для того чтобы избежать тупиков, никогда не пытайтесь выполнить команды ALTER или DROP подпрограммы или пакета в то время как вы все еще используете его.