У меня есть таблица Oracle, которая содержит первичный ключ (назовем его key ) и поле value . В моем приложении PHP у меня есть список ключей, и я хочу извлечь все соответствующие значения из базы данных. Я мог бы сделать это, используя что-то вроде следующего PHP-кода:
$keyList = array('apple', 'orange', 'banana'); $conn = oci_pconnect(USERNAME, PASSWORD, URI); $stmt = oci_parse($conn, 'SELECT * FROM myTable WHERE value IN (' .explode(',', $keylist) .')'); oci_execute($stmt); while($row = oci_fetch_array($stmt, OCI_ASSOC)) { echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values }
Это должно работать, но $keyList может содержать до 200 наименований (возможно, даже больше). Что вызывает вопрос (ы):
Нецелесообразно передавать значения для условия IN качестве конкатенации строк. Первое, конечно, безопасность и правильность, но следующий момент – это производительность.
Каждый раз, когда вы вызываете механизм базы данных операторов, он анализирует его, строит план запроса и после этого выполняет действия, указанные в инструкции SQL.
Если вы каждый раз создаете текст запроса с нуля, все три этапа выполняются каждый раз.
Но если вы используете переменные bind всегда, то запрос выглядит одинаково, поэтому база данных использует кешированный план запросов, что ускоряет выполнение запроса. Даже вы можете вызывать oci_parse() только один раз и повторно использовать переменную $stmt с различным набором поставляемых параметров.
Итак, для лучшей производительности вы должны использовать переменную bind и заполнить ее массивом с помощью oci_bind_array_by_name .
Кроме того, получение результатов с помощью oci_fetch_all может выполняться быстрее, чем чтение набора результатов по строкам, но это зависит от логики обработки результатов.
Обновить
Кажется, что параметры передаваемых массивов работают только в том случае, если вы собираетесь выполнять PL / SQL-блок и не можете использовать его с операторами SQL. Но другая возможность – использовать коллекции для передачи списка значений параметров. Можно удовлетворять условиям вопроса даже массивами, но этот способ менее изящный.
Помимо различных способов запроса базы данных есть такие вещи, как системные настройки. В случае PHP есть некоторые параметры в файле php.ini который контролирует взаимодействие с Oracle. Один из них ( oci8.statement_cache_size ) связан с кешированием запросов и производительностью.
Примеры
Во всех примерах используется одна и та же настройка данных в Oracle.
Для передачи данных я выбираю предопределенный тип SYS.ODCIVarchar2List , но также можно определить настраиваемый тип с теми же характеристиками (продемонстрированный в примере настройки данных). Ниже приведен код для демонстрации настройки схемы данных и принципа использования коллекций в DML.
SQLFiddle
create table myTable(value varchar2(100), key varchar2(100)) / insert into myTable(value, key) select * from ( select 'apple', 'apple_one' from dual union all select 'apple', 'apple_two' from dual union all select 'banana', 'banana_one' from dual union all select 'orange', 'orange_one' from dual union all select 'orange', 'orange_two' from dual union all select 'potato', 'potato_one' from dual ) / create or replace type TCustomList as table of varchar2(4000) / create or replace package TestPackage as type TKeyList is table of varchar2(1000) index by binary_integer; function test_select(pKeyList in out TKeyList) return sys_refcursor; end; / create or replace package body TestPackage is function test_select(pKeyList in out TKeyList) return sys_refcursor is vParam sys.ODCIVarchar2List := sys.ODCIVarchar2List(); vCur sys_refcursor; vIdx binary_integer; begin vIdx := pKeyList.first; while(vIdx is not null) loop vParam.Extend; vParam(vParam.last) := pKeyList(vIdx); vIdx := pKeyList.next(vIdx); end loop; open vCur for select * from myTable where value in (select column_value from table(vParam)) ; return vCur; end; end; /
Запросы для демонстрации коллекций:
--select by value list select * from myTable where value in ( select column_value from table(Sys.ODCIVarchar2List('banana','potato')) ) / --same with custom type select * from myTable where value in ( select column_value from table(TCustomList('banana','potato')) ) / --same with demonstration of casting select * from myTable where value in ( select column_value from table(cast(TCustomList('banana','potato') as Sys.ODCIVarchar2List)) ) /
Пример 1 – вызов из PHP с помощью коллекций
<?php $keyList = array('apple', 'potato'); $conn = oci_pconnect("user_name", "user_password", "SERVER_TNS_NAME"); $stmt = oci_parse($conn, "SELECT * FROM myTable where value in (select column_value from table(:key_list))"); $coll = oci_new_collection($conn, 'ODCIVARCHAR2LIST','SYS'); for ($i=0; $i < count($keyList); $i++) { $coll->append($keyList[$i]); } oci_bind_by_name($stmt, 'key_list', $coll, -1, OCI_B_NTY); oci_execute($stmt); while($row = oci_fetch_array($stmt, OCI_ASSOC)) { echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values } echo "---\n"; $coll->free(); //-- Run statement another time with different parameters //-- without reparsing. $coll = oci_new_collection($conn, 'ODCIVARCHAR2LIST','SYS'); $coll->append('banana'); oci_bind_by_name($stmt, 'key_list', $coll, -1, OCI_B_NTY); oci_execute($stmt); while($row = oci_fetch_array($stmt, OCI_ASSOC)) { echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values } echo "---\n"; $coll->free(); oci_free_statement($stmt); oci_close($conn); ?>
Пример 2. Вызов из PHP с использованием массива и пакета
<?php $keyList = array('apple', 'potato'); $conn = oci_pconnect("user_name", "user_password", "SERVER_TNS_NAME"); $stmt = oci_parse($conn, "begin :cur := TestPackage.test_select(:key_list); end;"); $curs = oci_new_cursor($conn); oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR); oci_bind_by_name($stmt, "cur", $curs, -1, OCI_B_CURSOR); oci_execute($stmt); oci_execute($curs); while($row = oci_fetch_array($curs, OCI_ASSOC)) { echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values } echo "---\n"; //-- Run statement another time with different parameters //-- without reparsing. $keyList = array('banana'); oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR); oci_execute($stmt); oci_execute($curs); while($row = oci_fetch_array($curs, OCI_ASSOC)) { echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values } echo "---\n"; oci_free_statement($stmt); oci_close($conn); ?>
Пример 3 – вызов из PHP с использованием массива и анонимного блока
<?php $keyList = array('apple', 'potato'); $conn = oci_pconnect("user_name", "user_password", "SERVER_TNS_NAME"); $stmt = oci_parse($conn, " declare type TKeyList is table of varchar2(4000) index by binary_integer; pKeyList TKeyList := :key_list; vParam sys.ODCIVarchar2List := sys.ODCIVarchar2List(); vIdx binary_integer; begin -- Copy PL/SQL array to a type which allowed in SQL context vIdx := pKeyList.first; while(vIdx is not null) loop vParam.Extend; vParam(vParam.last) := pKeyList(vIdx); vIdx := pKeyList.next(vIdx); end loop; open :cur for select * from myTable where value in (select column_value from table(vParam)); end; "); $curs = oci_new_cursor($conn); oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR); oci_bind_by_name($stmt, "cur", $curs, -1, OCI_B_CURSOR); oci_execute($stmt); oci_execute($curs); while($row = oci_fetch_array($curs, OCI_ASSOC)) { echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values } echo "---\n"; //-- Run statement another time with different parameters //-- without reparsing. $keyList = array('banana'); oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR); oci_execute($stmt); oci_execute($curs); while($row = oci_fetch_array($curs, OCI_ASSOC)) { echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values } echo "---\n"; oci_free_statement($stmt); oci_close($conn); ?>
Ваш подход, вероятно, самый эффективный способ сделать это, если keyList пришел из Oracle в первую очередь (например, если это был результат другого запроса, тогда вы хотели бы объединить эти запросы).
Я определенно не выполнил бы один раз для каждого ключа в списке, так как круговые поездки, подобные этому, могут быть очень дорогостоящими. Что касается списка 200 предметов, вам придется экспериментировать, но я не думаю, что это будет проблемой.
Еще один очень важный момент : обязательно дезинформируйте данные в keyList иначе вы можете подвергать свое приложение SQL Injection.