Oracle переменная привязки не разрешена в операциях определения данных

Обновлено: 06.07.2024

Я хочу написать повторно используемый код, и мне нужно объявить некоторые переменные в начале и повторно использовать их в скрипте, например:

Как я могу объявить переменную и повторно использовать ее в следующих инструкциях, таких как SQLDeveloper.

попытки

Используйте раздел DECLARE и вставьте следующий оператор SELECT в BEGIN и END; . Доступ к переменной с помощью &stupidvar .
Используйте ключевое слово DEFINE и получите доступ к переменной.
Использование ключевого слова VARIABLE и доступ к переменной.

Но во время попыток я получаю всевозможные ошибки (несвязанная переменная, синтаксическая ошибка, ожидаемая SELECT INTO . ).

Обратите внимание, что подход в принятом ответе @APC может использоваться без PL / SQL, например, в рабочем листе разработчика SQL в соответствии с вашим вопросом. Просто объявите переменную в одной строке (без точки с запятой), затем в строке exec, чтобы установить ее значение (заканчивая точкой с запятой), затем свой оператор select. Наконец, запустите его как сценарий (F5), а не как инструкцию (F9).

Есть несколько способов объявления переменных в скриптах SQL * Plus.

Первый - использовать VAR для объявления переменной привязки. Механизм присвоения значений переменной - это вызов EXEC:

VAR особенно полезен, когда мы хотим вызвать хранимую процедуру, которая имеет параметры OUT или функцию.

В качестве альтернативы мы можем использовать подстановочные переменные. Это хорошо для интерактивного режима:

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

Наконец, есть анонимный блок PL / SQL. Как видите, мы все еще можем назначать значения объявленным переменным в интерактивном режиме:

Все хорошо, за исключением того, что вы использовали термин «связываемая переменная». Объявление VAR создает переменную связывания, а ACCEPT или DEFINE создает переменную подстановки. @Ecropolis - да, в период использования SQL Plus по умолчанию. Используйте SET CONCAT, чтобы определить символ, который отделяет имя подстановочной переменной от буквенно-цифровых символов, следующих сразу за именем переменной. В PL / SQL или SQL используйте двойной канал || объединить. Если SQL является стандартным языком, то почему так трудно найти каноническую ссылку, которая работает везде? WTF . @jww - SQL - это стандарт, но он не всегда определяет точный синтаксис, поэтому разные продукты RDBMS могут реализовывать вещи по-разному; арифметика дат является хорошим примером. Кроме того, более старые продукты баз данных, такие как Oracle, часто вводили функции до того, как их охватывал Стандарт: например, иерархический синтаксис CONNECT BY. Но в этом случае мы обсуждаем SQL * Plus, который является клиентским инструментом и в любом случае не охвачен стандартом ANSI.

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

Спасибо за ваш ответ, но если я добавлю переменную в двойные кавычки, я получу ORA-01008: not all variables bound . Конечно! DEFINE num = 1; SELECT &num FROM dual; ведет к: ORA-01008: not all variables bound @ bl4ckb0l7 - Держу пари, вы пытаетесь сделать это не в SQL * Plus.

ключевое слово объявлять используется для объявления переменной

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

или чтобы выбрать что-то в эту переменную, которую вы используете INTO оператор, однако вам нужно обернуть оператор в, BEGIN а END также вы должны убедиться, что возвращается только одно значение, и не забывайте точки с запятой.

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

Ваша переменная может использоваться только в пределах BEGIN и END поэтому , если вы хотите использовать более чем один , вам придется сделать несколько BEGIN END оберток

Надеюсь, это сэкономит вам время

Если вы хотите объявить дату, а затем использовать ее в SQL Developer.

Просто хочу добавить ответ Мэйтаса . Может быть, это очевидно, но я долго искал, чтобы выяснить, что переменная доступна только внутри конструкции BEGIN-END , поэтому, если вам понадобится использовать ее в каком-то коде позже, вам нужно поместить этот код в BEGIN -END блок .

Обратите внимание, что эти блоки могут быть вложенными :

Вопрос в том, чтобы использовать переменную в скрипте, значит, для меня она будет использоваться в SQL * Plus.

Проблема в том, что вы пропустили кавычки, и Oracle не может преобразовать значение в число.

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

Если вы проверите, набрав DEFINE в SQL * Plus, он покажет, что переменная num имеет значение CHAR.

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

С цитатой, поэтому не заставляйте Oracle выполнять синтаксический анализ до числа, все будет в порядке:

Итак, чтобы ответить на исходный вопрос, это должно быть похоже на этот образец:

Есть другой способ сохранить переменную в SQL * Plus, используя значение столбца запроса .

COL [УМН] имеет new_value параметр в значение магазина из запроса по имени поля.

Переменные связывания - это технический момент, на который стоит обратить внимание в системах OLTP. Хорошая привязка переменных сделает SQL в системной базе данных OLTP очень быстрым, а эффективность использования памяти будет чрезвычайно высокой. Несвязанные переменные могут привести к перегруженности базы данных OLTP, ресурсы потребляются при разборе SQL, и система работает медленно.

Случай этого поста основан на Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Прежде чем вводить переменные связывания, нам нужно знать, как именно выполняется SQL?

Когда пользователь устанавливает соединение с базой данных, он отправляет в базу данных оператор SQL.После получения этого SQL Oracle сначала выполнит операцию Hash-функции для SQL, чтобы получить значение Hash, а затем перейдет в общий пул, чтобы выяснить, есть ли какие-либо SQL с совпадающим хеш-значением существует.

Если найден, Oracle будет напрямую использовать существующий план выполнения SQL для выполнения текущего SQL, а затем вернет результаты пользователю.

Если не найден, Oracle будет рассматривать это как новый SQL, который будет выполняться в следующем порядке:

1. Грамматический анализ

2. Семантический анализ

После того, как анализ синтаксиса пройден, Oracle проверит некоторые объекты и разрешения для этого SQL, чтобы выяснить, существует ли таблица, работающая на SQL, правильны ли столбцы в таблице и есть ли у пользователя разрешение на управление объектом.

3. Создайте план выполнения

Этот процесс Oracle составляет окончательный план выполнения SQL после ряда операций, таких как просмотр статистической информации об объекте операции, динамическая выборка и так далее.

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

4. Выполнить SQL

Oracle выполняет SQL в соответствии с планом выполнения, сгенерированным на предыдущем шаге, и возвращает результат пользователю.

Вышеуказанная работа, которую мы обычно называем сложным анализом, фактически потребляет системные ресурсы. SQL с таким же значением Hash уже существует в общем пуле, называется soft parse.

По сути, связывание переменных означает, что SQL, который изначально требовал от Oracle выполнения жесткого анализа, превращается в мягкий анализ, чтобы сократить время и ресурсы Oracle, затрачиваемые на анализ SQL.

Использовать ли переменные связывания для сравнения потребления ресурсов

Давайте посмотрим на потребление ресурсов связанных и несвязанных переменных для одного и того же SQL, выполненного 10000 раз.

Используйте переменные связывания

Открыть SQL_TRACE

Анализ Tkprof и резюме исходного файла трассировки

После получения файла трассировки на сервере Oracle используйте tkprof для анализа, суммирования и просмотра.

Давайте посмотрим на ключевые части после анализа и обобщения

Весь процесс плюс сгенерированный рекурсивный SQL, мы можем увидеть весь оператор:

ALL NON-RECURSIVE STATEMENTS + ALL RECURSIVE STATEMENTS

Время выполнения (истекшее): 0,48 + 0,10 = 0,58 (приблизительно только время выхода)
Процессорное время (процессор): 0,48 + 0,09 = 0,57
Время анализа (разбор): 8 + 2 = 10
Время выполнения (выполнить): 9 + 10017 = 10025

Не используйте переменные связывания

Выполнить сводку анализа tkprof

Просмотр ключевых частей xgj_var_unbind.txt

. Десять миллионов слов опущены в середине

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

Информация о том же исполнении под нашей статистикой:

ALL NON-RECURSIVE STATEMENTS + ALL RECURSIVE STATEMENTS

Время выполнения (прошедшее): 1,28 + 15,38 = 16,66
Процессорное время (процессор): 1,22 + 15,31
Время анализа (разбор): 3 + 20000
Время выполнения (выполнить): 4 + 20000

Для сравнения мы можем найти, что В системе OLTP Потребление ресурсов SQL с использованием связанных переменных намного меньше, чем у несвязанных переменных SQL. Чем больше выполнений SQL, тем более очевиден разрыв.

Ресурсы несвязанной переменной SQL в основном используются в сгенерированном рекурсивном SQL, который в основном используется при жестком разборе операторов SQL.

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

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

Если для создания базы данных вы используете графический инструмент Oracle DBCA, у вас должно сложиться впечатление: один шаг - попросить вас выбрать тип базы данных OLTP или OLAP. Фактически это показывает, что базы данных OLTP и OLAP сильно различаются: Oracle необходимо знать архитектуру системы, которую вы выбираете, чтобы можно было установить соответствующие значения параметров в соответствии с архитектурой системы, такой как параметры инициализации.

OLTP каштаны

данные:

сводный анализ tkprof

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

Как видно из файла трассировки, на этапе выборки при полном сканировании таблицы было прочитано более 42093 блоков данных, в то время как индексированные на этапе выборки были прочитаны только 308 блоков данных.

OLAP каштаны

Система OLAP намного сложнее в работе с SQL. Большую часть времени в базе данных OLAP выполняет какой-то отчет SQL. Эти SQL часто используют агрегированные запросы (например, group by), и набор результатов также очень велик. В этом случае Далее, индекс не является неизбежным выбором, и даже иногда производительность полного сканирования таблицы будет зависеть от индекса, даже если один и тот же SQL, если условия предиката отличаются, план выполнения может отличаться

данные

Мы используем следующую команду

Примечание: приведенная выше команда выполняется на клиенте plsql и может поддерживаться, но Команда autotrace, plsql, не очень хорошо поддерживается, поэтому я вошел на хост, где расположен сервер, и выполнил ее, конечно, я также могу работать через клиента sqlplus.

Из результатов видно, что, хотя отличается только предикат, оракул выбрал другой план выполнения, поскольку Oracle считает, что такой план имеет наименьшую стоимость.

в заключении

Системе OLAP вообще не нужно устанавливать переменные привязки, это будет иметь только отрицательные последствия, например, заставит SQL выбрать неправильный план выполнения, позволит Oracle выполнять жесткий анализ для каждого SQL и точно знать значение условия предиката. Это влияет на план выполнения. Выбор имеет решающее значение. Это связано с тем, что в системе OLAP затраты на жесткий анализ SQL незначительны. Ресурсы системы в основном используются для более крупных запросов SQL. По сравнению с запросом ресурсы, потребляемые при разборе SQL, явно незначительны, поэтому оптимальный План реализации становится особенно важным
В системе OLAP дайте Oracle точно знать значение условия предиката, которое напрямую определяет выбор плана выполнения SQL. Способ сделать это - не связывать переменные
В системе OLAP анализ индекса таблицы очень важен, потому что он является источником информации и предложением для Oracle составить правильный план выполнения для SQL.

Когда дело доходит до привязки переменных, мы должны упомянуть новую функцию, введенную в Oracle9i, которая называется bind peaking, как следует из названия, когда сложно выполнить синтаксический анализ оператора SQL, Oracle будет смотреть на значение текущего предиката SQL, чтобы сгенерировать наибольшее количество Хороший план исполнения.

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

Просмотр привязки в конечном итоге не решает проблему разных планов выполнения, вызванных разными предикатами, а может только сделать план выполнения более точным, когда SQL выполняется впервые. OLAP по-прежнему не должен использовать переменные связывания.

Для системы OLTP та же частота повторения SQL очень привлекательна: если оптимизатор Fan Lake анализирует SQL, он неизбежно потребляет ресурсы. Кроме того, результаты пользовательских запросов в системе OLTP, как правило, очень малы, в основном будут учитываться индексы. При проверке привязки был получен правильный план выполнения во время первого жесткого анализа. Последующий SQL выполняется в соответствии с этим планом, что может значительно повысить производительность системы. Это определяется характеристиками системы OLTP.

Связываемые переменные, они же prepared statements, они же подготовленные выражения (четко устоявшегося перевода обнаружить не удалось; будем использовать и тот, и тот) — это часть функциональности SQL-баз данных, предназначенная для отделения данных запроса и собственно выполняемого SQL-запроса. Например, у нас есть запрос:
insert into someTable(name) values(‘Вася’);
Что мы можем заметить, просто посмотрев на него? Во-первых, сам запрос insert обычно статичен и не меняется в разных запросах, в 90% случаев просто жестко вбит в коде или генерируется при помощи некоторого ORM; значение данных (в данном случае 'Вася') меняется постоянно и задается извне — из ввода пользователя или из других источников. Связываемые переменные позволяют задать запрос отдельно, а потом передавать данные в него отдельно, приблизительно так (псевдокод):

Так мы отдельно задаем запрос, вместо данных подставляя в него номера связываемых переменных (:1, :2. ) или просто вопросительные знаки. Далее вызываем запрос, указывая, какие именно данные надо подставить вместо указанных переменных.
Результат выполнения этого кода полностью аналогичен результату выполнения запроса insert into someTable(name) values(‘Вася’); , но есть несколько важных отличий, которые будут рассмотрены далее.

Преимущества и особенности связываемых переменных

При использовании связываемых переменных есть несколько преимуществ:
1. Очевидное преимущество — один и тот же подготовленный запрос можно использовать несколько раз для разных данных, тем самым сокращая код.
2. Запросы со связываемыми переменными лучше кэшируются сервером, сокращая время синтаксического разбора.
3. Запросы со связываемыми переменными обладают готовой встроенной защитой от SQL-инъекций.
Рассмотрим каждый пункт подробнее.
Первый пункт очевиден — при наборе данных можно использовать одно и тоже подготовленное выражение несколько раз:

Код генерации SQL-запроса сокращается, а вам любой разработчик скажет, что сокращение объемов кода — это сокращение вероятности ошибки в нем.

Для пояснения второго пункта следует рассказать подробнее, как именно сервер баз данных обрабатывает SQL-запрос. Первейшим этапом выполнения запроса является синтаксический разбор самого запроса, то есть сервер переводит запрос из SQL-языка в какой-то свой внутренний формат, чтобы определить, что именно хочет от сервера клиент. За синтаксическим разбором следует собственно выполнение — составление плана запроса, формирование индексов, сканирование таблиц и множество других неинтересных вещей. Надо отметить, что сам по себе синтаксический разбор — операция довольно «тяжелая» по времени выполнения (хотя бы по сравнению с поиском по индексу, например). Подавляющее большинство современных систем управления базами данных (увы, насколько я знаю, MySQL в данном случае к таковым не относится), «умеют» кэшировать результаты синтаксического разбора и заново использовать их. В этом случае становится очень выгодным, если есть возможность повторять один и тот же SQL-запрос не один раз — будет использоваться синтаксический кэш. Обратимся к примеру в пункте 1 — очевидно, что в данном случае синтаксический разбор выполняется один раз, хотя сам запрос — четыре раза. Если бы мы писали:

то в этом случае каждый раз запрос для сервера был бы новым (потому что анализируется полный текст запроса), и синтаксический разбор пришлось бы выполнить четырежды. Это еще не говоря о том, что такие запросы забивают «мусором» описанный синтаксический кэш.

Перейдем к третьему пункту. Почему же связываемые переменные — это гарантированная защита от SQL-инъекций (по крайней мере, того типа, который рассматривается в упомянутой статье)? Существует заблуждение (у меня оно точно было), что prepared statements – это просто синтаксическая «нашлепка» на команду sql_execute (mysql_real_query, например), которая просто
экранирует все указанные переменные, собирает в одну строку и просто вызывает команду sql_execute, избавляя программиста от некоторого ручного труда. Это не так. На самом деле prepared statement – отдельная возможность в любой вменяемой СУБД. Для этой возможности есть отдельные функции в библиотеке, отдельные места в бинарном протоколе между клиентской и серверной частью СУБД. Более того, собственно подготовленный запрос и данные, которые в нем используются, передаются на сервер отдельно. В клиентских библиотеках есть отдельные команды подготовки выражений (для примера можно посмотреть документацию MySQL C API, PostgreSQL C library).

Примечание: есть исключение — в PHP PDO связываемые переменные по умолчанию эмулируются именно описанным методом, то есть конструированием SQL-команды на клиентской стороне. Это лучше отключать (взято со StackOverflow):

Из этого следует важный вывод — поскольку данные передаются полностью отдельно от запроса, у этих данных нет никаких возможностей модифицировать запрос. Нет возможностей. Вообще никаких. (Экзотические атаки типа переполнения буфера здесь мы не рассматриваем — это совсем другой класс атак).
Данные не нужно экранировать, преобразовывать или как-то менять; они идут в базу данных в точно том виде, в каком нужны нам. Если нам передали строку Robert');drop table students; , не надо заботиться об экранировании, надо просто передать ее как связываемую переменную — ничего она нам не сделает, а так и будет просто лежать в базе данных, как самая обычная строка.

Комментарии к комментариям

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

Rhaps107
а в чем проблема с mysql_real_escape_string? В нём есть какие-то известные уязвимости?

Это мы уже разобрали — проблема с функцией mysql_real_escape_string в том, что ей вообще пользуются. Со связываемыми переменными ей не надо пользоваться. Это экономия на клиентской части (представьте, что функции надо «шерстить» мегабайтную строку, чтобы найти места, где все-таки поставить обратный слэш), а остальные преимущества уже расписаны в статье.

@m_z21
PDO и ORM не панацея. И с использованием pdo можно наделать подобных дыр, если нет понимания как работают sql-инъекции.

Каким боком сюда приплели ORM – непонятно. А вот PDO (и MySQLi) как раз панацея, поскольку SQL injection при их грамотном использовании невозможны, как уже и было описано.

@VolCh21
Выигрыш по потребляемым ресурсам (скорости, памяти), т. к. mysql_* является по сути просто биндингами к libmysql, а mysqli/pdo создают ненужный во многих случаях объектный слой?

И это фактическая ошибка. Команды типа mysqli::prepare — это тоже всего лишь биндинги к соответствующим функциям клиентской библиотеки MySQL. Если желаете убедиться, то можете сами посмотреть на исходные коды PHP. Соответственно, расходы на (якобы ненужный) объектный слой даже если и есть, то они минимальные. Да и экономия на объектном слое уж очень сильно напоминает «экономию на спичках».

Заключение

Надеюсь, мне удалось прояснить для кого-то такую несомненно важную тему, как связываемые переменные (prepared statements). Надеюсь, что многие хотя бы задумаются над тем, чтобы всегда использовать связываемые переменные при работе с БД. Я не претендую на абсолютную полноту и точность изложения, так что буду только рад, если у кого-то найдется что добавить, убавить или откорректировать в написанном.

Как я могу объявить переменную и повторно использовать ее в следующих инструкциях, таких как SQLDeveloper.

попытки

Используйте раздел DECLARE и вставьте следующий оператор SELECT в BEGIN и END; . Доступ к переменной с помощью &stupidvar .
Используйте ключевое слово DEFINE и получите доступ к переменной.
Использование ключевого слова VARIABLE и доступ к переменной.