Oracle：如何使用键列表高效地选择行

Question

我有一个Oracle表，其中包含一个主键（我们称之为键）和一个值字段。在我的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个项目长（甚至更多）。这就提出了问题：

• 这是最有效/稳健的方法吗
• 还是最好使用某种预先准备好的语句从数据库中选择一个值，并对列表中的每个键执行一次

Answer 1

您的方法可能是最有效的方法，除非＜code＞keyList＜/code＞首先来自Oracle（例如，如果它是另一个查询的结果，那么您希望合并这些查询）。

我绝对不会为列表中的每个键执行一次，因为这样的往返可能非常昂贵。至于一个200条长的列表，您必须进行实验，但我认为这不是问题。

还有一点非常重要：请务必清理 keyList 中的数据，否则可能会向 SQL 注入公开您的应用。

Answer 2

将< code>IN条件的值作为字符串串联传递不是一种好的做法。首先当然是安全性和正确性，但下一点是性能。< br >每次调用语句时，数据库引擎都会对其进行分析，生成查询计划，然后执行SQL语句中指定的操作。< br >如果每次都从头开始构建查询文本，那么每次都会执行所有三个阶段。< br >但是，如果您始终使用绑定变量，查询看起来是一样的，因此数据库使用缓存的查询计划来加快查询执行。甚至可以只调用< code>oci_parse()一次，并使用不同参数集重用< code>$stmt变量。< br >因此，为了获得最佳性能，您必须使用绑定变量，并使用< code > OCI _ bind _ array _ by _ name 用数组填充它。

另外，使用oci_fetch_all检索结果可能比逐行读取结果集执行得更快，但这取决于处理结果的逻辑。

更新

似乎传递数组参数仅在您要执行PL / SQL块并且不能将其与SQL语句一起使用时才有效。但另一种可能性是使用集合来传递参数值列表。即使使用数组，也可以满足问题的条件，但这种方式不太优雅。
除了查询数据库的不同方法外，还有系统设置之类的东西。在PHP的情况下，php中有一些参数.ini文件控制与Oracle的交互。其中之一（oci8.statement_cache_size）与查询缓存和性能有关。

例子

所有示例都使用Oracle中相同的数据设置。< br >为了传递数据，我选择了预定义的< code>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);
?>