Prepared statements. Подстановка массива значений.

[RXL]

Вопрос не сколько практический, сколько риторический: кто-нибудь видел в каком-либо API возможность подставлять в оператор переменный набор значений? Вопрос без определенной СУБД.

Например,
исходный оператор: SELECT * FROM tab t WHERE t.val IN (:VALUES);
получается: SELECT * FROM tab t WHERE t.val IN (1, 2, 3, 4);

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

[Алексей++]

наверное, нужно попользовать  containing  (где то Слава когда то про это показал)

Код:

text='~1~2~3~4~'

получается: SELECT * FROM tab t WHERE :text containing '~'||t.val||'~';

работает так - поскольку тильда - это точно символ, которые не встретится в номере, то ища строку, скажем '~4~' в text , найдём именно её, а не , скажем , '~44~' (насколько я понял, ищется любое первое вхождение строки)

[HandKot]

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

[McZim]

не встречал.

[RXL]

Леш, этот метод автоматом исключает использование индексов с участием данного поля, что сильно сужает область применения. Хочется более универсальный метод.

Я решил это чисто программно: используя свой формат имен подстановки и прогон с заменой перед prepare(). Только такой метод тоже сокращает универсальность, т.к. для данного поля теряется гибкость prepared statement и вообще это вызывает побочные явления.
Например, в Oracle, при различных значениях подставляемого списка, это будут разные операторы с собственным планом выполнения (Oracle кеширует планы выполнения в системных таблицах, чем существенно ускоряет быстрые запросы).
В MySQL есть свои prepared statements и, у меня есть подозрение, в более современных API (для версий >= 4.1) это может скрыто использоваться.

В одном из API PHP — в PDO — есть тип подстановки PDO::PARAM_STM — параметр-курсор. Но, пока ни один драйвер это не поддерживает.

В Oracle я бы решил это через PIPE-функцию и превращением в IN в поздапрос:
IN (SELECT * FROM TABLE(my_function(:STRING)))

В MySQL так не получится - там нет ни массивов, ни возврата курсоров, ни PIPE-функций. Есть возможность вернуть рекордсет (и не один) из процедуры, но для в подзапросов это использовать нет возможности.

В других СУБД опять же будут свои методы. Или не будут. А хочется универсальности.

[HandKot]

вернуть рекордсет из ХП и его закинуть в темповую таблицу, а потом её приджойнить или использовать с оператором IN. Тогда и индексы сработают

В MSSQL тоже нет ни массивов, ни списков и в предложенной статье рассматриваются различные варианты обхождения данной проблемы (должно работать на любых платформах)

кстати, в MSSQL есть ещё динамический SQL (не знаю насчет MySQL)

[Dimka]

D MSSQL последних версий есть тип xml со всеми вытекающими отсюда возможностями. Но в конце концов всё сведётся к некой функции, которая на входе получает строчку, а на выходе даёт таблицу.

[RXL]

HandKot, что временные таблицы, что сделать подстановку программно, до передачи в prepare() — равносильно. Но зв исключением если список очень длинный (сотни-тысячи значений), и в проверке по нему участвуют такого же порядка количество строк, а во временной таблице используется PK. В противном случае подставить статический список из десятков значений и обработать в запросе сотню строк будет быстрее. Порядки называю примерно — не мерил.

Вообще, я (выше) говорил о том, что универсальности нет. Ведь смысл prepared statements в том, что запрос парсится однажды, а выполниться может несколько раз за сессию и с разными параметрами. Там чуть быстрее, здесь чуть быстрее — с миру по нитке и выигрываем существенный кусок в производительности.

[Sla]

RXL, а нельзя в качестве :value передать строку select'а ?
типа :value := 'select 1,2,3,4 from dual';

sory, проверить не на чем

[RXL]

Sla, нет - подставляется исключительно одиночное значение. Это не компановка строки, а подстановка параметров в уже пропарсеный запрос.

Если не понятно:

Код:

/* Пример для PDO */

$query = 'SELECT * FROM tab t WHERE t.id = :ID AND t.name = :NAME';
$statement = $connection->prepare($query);
/* Теперь запрос пропарсен и с SQL дело не имеем. */

/* Первый запрос. */
$parameters = array(
    ':ID' => 10,
    ':NAME' => 'abc',
);
$statement->execute($parameters);
$rs1 = $statement->fetchAll();
$statement->closeCursor();

/* Второй запрос. */
$parameters = array(
    ':ID' => 33,
    ':NAME' => 'zyx',
);
$statement->execute($parameters);
$rs2 = $statement->fetchAll();
$statement->closeCursor();