Bind variables oracle что это
Переменные связи ( bind-переменные ) служат для передачи значений в СУБД. Эти переменные могут использоваться во фразе WHERE для вычисления условия, в операторах INSERT и DELETE для определения устанавливаемых значений.
Переменные связи , так же как и INTO-переменные, перед применением должны быть предварительно объявлены. Переменные связи при указании их в SQL-операторе предваряются символом "двоеточие".
Курсоры
Под курсором , как правило, понимают получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. Курсор - это объект, связанный с определенной областью памяти. Существуют явные и неявные курсоры .
Явный курсор имеет имя и перед использованием должен быть объявлен. Неявный курсор создается автоматически и его нельзя повторно открыть без перекомпиляции оператора запроса.
Объявление курсора выполняется оператором DECLARE CURSOR , в котором фраза FOR определяет запрос, ассоциируемый с данным курсором .
создает курсор c1 на базе таблицы tbl1. При объявлении курсора выполнения запроса не происходит. Выполнение запроса и создание курсора инициируется оператором OPEN CURSOR .
создаст курсор , выполнив определенный в нем оператор SELECT .
Приложение получает доступ к данным курсора при последовательном извлечении строк результирующего набора в переменные приложения.
Для извлечения данных из курсора используется оператор FETCH
извлекает значения текущей строки курсора в INTO-переменные .
Для освобождения памяти, выделенной под курсор , его следует закрыть, выполнив оператор CLOSE CURSOR .
Обработка NULL-значений
Для работы с NULL-значениями предусмотрены индикаторные переменные, которые могут использоваться для:
- определения извлекаемого NULL-значения;
- внесения NULL-значения в таблицу;
- фиксирования усекаемых строк.
Если в результате выполнения оператора FETCH или оператора SELECT (возвращающего одну строку) извлекаемые данные принимают значение NULL , то, во-первых, считается, что SQL-оператор выполнен с ошибкой, а во-вторых, в INTO-переменную будет записано значение, отличное от NULL (зависит от типа переменной). Для предотвращения таких ситуаций применяются индикаторные переменные, указываемые после INTO-переменной через символ двоеточия (или INDICATOR :). Если индикаторная переменная принимает отрицательное значение, значит, столбец содержит значение NULL . По умолчанию до выполнения оператора индикаторной переменной присваивается значение 0.
Если в операторе INSERT или UPDATE требуется указать, что используемая переменная связи может содержать значение NULL , то после этой переменной через двоеточие следует записать индикаторную переменную. В этом случае при отрицательном значении индикаторной переменной в базу данных будет занесено значение NULL .
При фиксировании усекаемых строк в индикаторную переменную записывается первоначальная длина строки, а сама строка записывается в переменную основного языка с усечением.
Позиционированные операторы
Для обновления курсора в операторах DELETE и UPDATE может использоваться фраза WHERE CURRENT OF , определяющая, что действие относится к текущей строке курсора . Такой оператор называется позиционированным , и к нему предъявляются следующие требования:
- и курсор , и оператор должны использовать только одну и ту же таблицу;
- в запросе, используемом для создания курсора , не должно быть фраз UNION и ORDER BY ;
- курсор должен удовлетворять критериям обновляемого курсора (например, не применять агрегирующие функции).
Позиционированный оператор DELETE удобно использовать для удаления из таблицы группы строк, предварительно выбранных в курсор .
Обработка ошибок
Стандартом SQL-92 определено две переменных, которые позволяют получать информацию о выполняемом SQL-операторе:
- переменная SQLSTATE имеет тип char(5) и содержит информацию о классе (два старших символа) и подклассе (3 младших символа), описывающих состояние выполненного SQL-оператора;
- переменная SQLCODE имеет целочисленный тип и содержит код завершения последнего выполненного SQL-оператора.
Как и другие переменные, используемые во встроенном SQL , переменные SQLSTATE и SQLCODE предварительно должны быть объявлены. Однако переменная SQLCODE может быть создана по умолчанию, если нет объявления другой переменной, получающей информацию о завершении выполнения SQL-оператора.
После выполнения SQL-оператора данные о статусе и коде выполнения автоматически записываются СУБД в эти переменные.
Статус выполнения SQL-оператора может быть определен как:
- успешное завершение. Соответствует в SQLSTATE коду '00000' (класс '00' ). SQLCODE в этом случае тоже равна 0;
- успешное завершение с предупреждением. Класс состояния '02' в SQLSTATE определяет предупреждение 'NOT FOUND' ; класс состояния '01' указывает предупреждение, более точно специфицируемое подклассом;
- завершение с ошибкой. Классы '03' и последующие в SQLSTATE описывают различные ошибочные ситуации (подклассы специфицируют как стандартные ситуации, так и определяемые приложением).
Предупреждение 'NOT FOUND' указывает, что SQL-оператор не содержал ошибки, но не вернул ожидаемого результата. Например, сформированный результирующий набор не содержит ни одной строки, или оператор UPDATE не изменил ни одной строки.
Переменные SQLCODE и SQLSTATE очень часто используются в операторе while для завершения цикла и для выхода из него в случае возникновения ошибки.
Встроенный SQL поддерживает оператор WHENEVER , определяющий действия, которые будут выполнены при возникновении описанной ситуации. Этот оператор следует указывать до выполнения того SQL-оператора, чья обработка ошибок будет "перехватываться".
Оператор WHENEVER влияет на все выполняемые SQL-операторы.
Оператор WHENEVER определяет или метку, на которую будет выполнен переход при возникновении ошибки, или действие типа CONTINUE (продолжение выполнения), или процедуру обработки ошибок.
1. При первом разборе происходит полный разбор запроса (hard parse)
План запроса помещается в глобальный кэш БД с определенным sql_id
2. При повторном выполнении происходит частичный разбор (soft parse)
Происходит только синтаксический разбор, проверки прав доступа и проверки bind переменных. Что для разных вариаций sql_id создает дочерние child_sql_id
Из-за такого механизма работы Oracle вытекает частая проблема oltp систем, где существует огромное число маленьких запросов, отличающихся друг от друга только фильтрами или параметрами. Это приводит к быстрому вытеснению планов из кэша и их последующему повторному hard parse.
В итоге может оказаться, что большую часть времени БД занимается разбором запросов, а не собственно их выполнением.
Отсюда вывод: по возможности используйте bind переменные в вариациях одного запроса, замен константных фильтров, т.к. это даст нам только один план запроса (child_sql_id) при разных значениях переменных на равномерно распределенном столбце.
Я не зря сказал ранее "на равномерно распределенном столбце", т.к. с bind переменными есть проблема: по умолчанию Oracle не знает какие данные будут переданы в запрос и из-за этого может сгенерить неверный план запроса.
Посмотрим на примере по умолчанию. Создадим таблицу с неравномерно распределенным столбцом "n" (9 строк со значением = 1, и 1млн-9 строк со значением 2):
Столбец не имеет гистограмм, но есть статистика по уникальным значениям. Построим план запроса с bind переменной = 1:
Oracle закономерно ожидает в результате половину таблицу и выбирает full scan, хотя мы то знаем, что тут был бы лучше Index scan.
К счастью с 11 версии Oracle может заглядывать в значения bind переменных и подбирать под них нужные планы.
Для этого соберем гистограмму с 2 вершинами и повторим эксперимент:
Oracle сгенерировал новый child_sql_id под новое значение bind переменной и выбрал правильный доступ по индексу.
Данный план был закеширован в глобальную память и если прямо сейчас выполнить заново с параметром 2, то мы получим тотже план (child number 2).
Замечу что на этом этапе уже надо смотреть план уже выполненного запроса, т.к. oracle не умеет показывать план и заглядывать в bind переменные, но при реальном выполнении запроса значения bind переменных смотрятся.
но oracle пометит этот запрос на пересмотр, т.к. план совсем не сошелся с реальными данными и при последующем применении сгенерирует новый child_sql_id (child number 3) под нашу bind переменную:
Из всего этого можно сделать вывод, что вопреки частому заблуждению, Oracle умеет генерировать правильные планы по bind переменным, но делает это не сразу, а при повторном вызове и при наличии гистограммы.
Второе: реальный план запроса с bind переменными можно узнать только во время или после выполнения запроса, т.к. "explain plan" не подсматривает в bind переменные.
Cardinality feedback
Ora Blog
Oracle мониторит и исправляет следующии "estimated rows" оценки на основе реальных "actual rows"
* Single table cardinality (after filter predicates are applied)
* Index cardinality (after index filters are applied)
* Cardinality produced by a group by or distinct operator
Dynamic Sampling
Ora Blog
Применимо для запросов со сложными предикатами фильтрации, которые дают существенную ошибку оптимизатора.
Включение dynamic sampling в зависимости от параметра пробирует от 32 блоков таблицы на предикаты фильтрации и определяем реальный лучший план.
Связываемые переменные, они же 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). Надеюсь, что многие хотя бы задумаются над тем, чтобы всегда использовать связываемые переменные при работе с БД. Я не претендую на абсолютную полноту и точность изложения, так что буду только рад, если у кого-то найдется что добавить, убавить или откорректировать в написанном.
По словам очевидцев, почти безобидный запрос периодически выполняется дольше таймаута приложения (магическим образом установленным ровно в 55 секунд):
13.10.2012
Параметризованные обзоры и Cardinality Feedback в Oracle 11.2
При использовании в запросе [текстовых] констант значения предикатов точно определены, и для построении максимально точного плана выполнения доступны обычная и расширенная (extended statistics) статистика распределения значений столбцов
При использовании связанных переменных для построения плана значения доступны через bind peeking и, в дальнейшем при необходимости, через технологию Bind-Aware Cursor Sharing
Комментарии к записи Параметризованные обзоры и Cardinality Feedback в Oracle 11.2 отключены29.08.2012
Операция filter(NULL IS NOT NULL) и запросы со связанными переменными
При выполнении запроса с NULL-евыми значениями связанных переменных условия типа COLUMN1 = :VAR1 превращаются в COLUMN1 = NULL и по определению становятся невыполнимыми (в том смысле, что запрос с таким условием в WHERE не возвращает строк), но при построении плана / выполнении запроса оптимизатор не всегда использует эту возможность сэкономить ресурсы
Например, при выполнении практическоого запроса (судя по тексту, выполняющему проверку на совпадение введённого в поле формы текста с ID либо логином клиента) с пустым значением переменной:
04.06.2012
Как форсировать разбор SQL при каждом выполнении: новый параметр _disable_cursor_sharing в 11.2.0.3
Иногда (нечасто) возникает необходимость при каждом выполнении запроса со связанными переменными выполнять разбор этого запроса оптимизатором (hard parse) с целью генерации отдельного плана для каждого набора связанных переменных. Такая необходимость может возникать, например, в случае нечастого выполнения тяжёлых отчётов, для которых Oracle вполне в состоянии подобрать быстрый план, если бы в запросе не использовались связанные переменные и традиционный механизм повторного использования курсоров cursor sharing
05.08.2011
Использование связанных переменных с точки зрения оптимизатора
[ Note ] In addition to these generic limitations, each gateway can have additional limitations. Please consult the gateway documentation for individual gateways for a complete list of limitations of the product.
Heterogeneous Replication
Гетерогенная репликация может быть реализована при помощи материальных представлений.
[ Note ] There is another means of replicating information between Oracle and non-Oracle databases called Streams.
Ограничения репликации non Oracle-to-Oracle по сравнению с Oracle-to-Oracle:
- Only the non-Oracle system can be the master site. This is because materialized views can be created only on an Oracle server.
- Materialized views must use complete refresh. This is because fast refresh would require Oracle-specific functionality in the non-Oracle system.
- Not all types of materialized views can be created to reference tables on a non-Oracle system. Primary key and subquery materialized views are supported, but ROWID and OBJECT ID materialized views are not supported. This is because there is no SQL standard for the format and contents of ROWID, and non-Oracle systems do not implement Oracle objects.
- Совместимость типов данных, как правило совместимыми являются типы данных определённые стандартом ANSI SQL.
- Совместимость под запросов представления как правило обеспечивается переводом их на язык удаленной системы, однако иногда совместимость не может быть проверена до выполнения запроса.
Passthrough SQL (Сквозной SQL)
Passthrough SQL позволяет отправлять запросы напрямую удаленной базе, пропуская интерпретацию запроса в Oracle. Эта возможность полезна при выполнении запросов для которых не эквивалента в Oracle.
- Using the DBMS_HS_PASSTHROUGH Package;
- Considering the Implications of Using Passthrough SQL;
- Executing Passthrough SQL Statements.
Использование пакета DBMS_HS_PASSTHROUGH
Сквозные (Транзитивные) SQL операторы выполняются при помощи пакета DBMS_HS_PASSTHROUGH . Этот пакет является виртуальным. Концептуально он находится в удаленной базе. На самом деле запросы выполняемые через этот пакет перехватываются HS и распространяются на необходимое количество вызовов. Драйвер в свою очередь преобразует эти запросы в API удаленной базы. [ 45 DBMS_HS_PASSTHROUGH ]
Considering the Implications of Using Passthrough SQL
Executing Passthrough SQL Statements
Функции и процедуры пакета DBMS_HS_PASSTHROUGH, позволяющие выполнять транзитивные SQL запросы:
- OPEN_CURSOR
- CLOSE_CURSOR
- PARSE
- BIND_VARIABLE
- BIND_OUT_VARIABLE
- BIND_INOUT_VARIABLE
- EXECUTE_NON_QUERY
- EXECUTE_IMMEDIATE
- FETCH_ROW
- GET_VALUE
Executing non Queries
Для этого запроса следует использовать функцию EXECUTE_IMMEDIATE .
DECLARE
num_rows INTEGER ;
BEGIN
num_rows : = DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@salesdb ( 'CREATE TABLE dept1 (n SMALLINT, loc CHARACTER(10))' ) ;
END ;
Функция EXECUTE_IMMEDIATE возвращает количество строк затронутых выполнение SQL оператора, для DDL операторов функция возвращает ноль.
Using Bind Variables (Использование Связанных Переменных)
Для выполнения сквозного SQL ( Passthrough SQL ) вместе со связанными переменными необходимо:
Читайте также: