c# - 带有 IN 语句的 Oracle 参数？

Question

有一个我需要修改的 c#.net 应用程序。目前的查询有效地做到了这一点:

select * from contract where contractnum = :ContractNum

(非常简单，只是为了表明我们使用了一个 = 和一个参数)

该参数是从 C# 应用程序的 Settings.Settings 文件中读取的，其中包含一个字符串。我需要修改它以包含多个契约(Contract)，所以我想我可以将 SQL 更改为:

select * from contract where contractnum in (:ContractNum)

但是无论我如何格式化参数中的字符串，它都不会返回任何结果。

有没有办法让 oracle 执行带有参数的 IN？

Answer 1

当您使用 ODP.NET 作为数据提供者时，您可以使用 Oracle 数字集合作为参数(绑定(bind)变量)。这适用于 Oracle 服务器 9、10 或 11 和 ODP.net 版本 >= 11.1.0.6.20。

当您使用 Devart 的 .NET dataprovider for Oracle 时，类似的解决方案是可能的。

让我们选择 contractnum 为 3 和 4 的合约。

我们必须使用 Oracle 类型将一组契约(Contract)号传输到我们的查询中。

使用

MDSYS.SDO_ELEM_INFO_ARRAY 是因为如果我们使用这个已经预定义的 Oracle 类型，我们就不必定义我们自己的 Oracle 类型。您可以使用最多 1048576 个数字填充 MDSYS.SDO_ELEM_INFO_ARRAY。

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

[OracleCustomTypeMappingAttribute("MDSYS.SDO_ELEM_INFO_ARRAY")]
public class NumberArrayFactory : IOracleArrayTypeFactory
{
  public Array CreateArray(int numElems)
  {
    return new Decimal[numElems];
  }

  public Array CreateStatusArray(int numElems)
  {
    return null;
  }
}

private void Test()
{
  OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();
  b.UserID = "sna";
  b.Password = "sna";
  b.DataSource = "ora11";
  using (OracleConnection conn = new OracleConnection(b.ToString()))
  {
    conn.Open();
    using (OracleCommand comm = conn.CreateCommand())
    {
      comm.CommandText =
      @" select  /*+ cardinality(tab 10) */ c.*  " +
      @" from contract c, table(:1) tab " +
      @" where c.contractnum = tab.column_value";

      OracleParameter p = new OracleParameter();
      p.OracleDbType = OracleDbType.Array;
      p.Direction = ParameterDirection.Input;
      p.UdtTypeName = "MDSYS.SDO_ELEM_INFO_ARRAY";
      //select contract 3 and 4
      p.Value = new Decimal[] { 3, 4 };
      comm.Parameters.Add(p);

      int numContracts = 0;
      using (OracleDataReader reader = comm.ExecuteReader())
      {
        while (reader.Read())
        {
           numContracts++;
        }
      }
      conn.Close();
    }
  }
}

当省略 hint/*+ cardinality(tab 10) */时，不使用 contract.contractnum 上的索引。我假定 contractnum 是主键，因此该列将被编入索引。

另见此处:http://forums.oracle.com/forums/thread.jspa?messageID=3869879#3869879