具有 Oracle 数组绑定的 ExecuteReader

Question

我试图通过使用数组绑定到 OracleParameter 来提高 Oracle SQL 查询的性能。

这基本上就是我想要做的：

                List<string> IDValList = new List<string>();
                IDValList.Add( "IDOne" );
                IDValList.Add( "IDTwo" );

                List<int> sizes = new List<int>();
                foreach( string id in IDValList )
                {
                    sizes.Add( id.Length );
                }

                using( OracleCommand cmd = new OracleCommand( "select col1, col2, col3 from table where col4 in ( :idArray )", _conn ) )
                {
                    cmd.CommandType = System.Data.CommandType.Text;

                    OracleParameter arrayParam = new OracleParameter( "idArray", OracleDbType.Varchar2 );
                    arrayParam.Direction = System.Data.ParameterDirection.Input;
                    arrayParam.Value = IDValList.ToArray();
                    arrayParam.ArrayBindSize = sizes.ToArray();

                    cmd.ArrayBindCount = IDValList.Count;
                    cmd.Parameters.Add( arrayParam );

                    using( OracleDataReader dr = cmd.ExecuteReader() )
                    {
                        while( dr.Read() )
                        {
                           // now read the row...

它可以编译并运行，但我总是只返回第一个 ID 的一行。就像忽略参数数组中的其余值一样。

有趣的是，参数的 ArrayBindStatus 对于所有值都是成功的。

我缺少什么？或者这不适用于 OracleReader？

谢谢

编辑：基本上，我正在尝试遵循这个示例，但我希望能够使用 DataReader 从查询中读取结果数据集。

http://www.oracle.com/technology/oramag/ oracle/09-sep/o59odpnet.html

Answer 1

它会起作用：

  using
  ( OracleCommand cmd = 
    new OracleCommand("select col1, col2, col3 from table where col4 in (" + string.Join(",", IDValList) + ")", _conn ) )

因为现在您只需传递列表中的第一个元素。

Answer 2

有多种方法可以执行您正在搜索的“列表中的变量”。利用绑定参数。

这是基于以下几点：
http://asktom.oracle .com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

坦白说，我尝试尽可能频繁地使用绑定变量，如果不仅仅是为了提高性能，而是为了额外的性能安全级别。

完成

虽然剥猫皮的方法有很多，但这（虽然很冗长）应该在 Oracle 中
--创建你的类型
创建或替换类型 varcharTableType 作为 varchar2 的表 (255)；

--create your function
function in_varchar( p_string in varchar2 ) return varcharTableType  
    as
        l_string        long default p_string || ',';
        l_data          varcharTableType := varcharTableType();
        n               number;
    begin
      loop
          exit when l_string is null;
          n := instr( l_string, ',' );
         l_data.extend;
         l_data(l_data.count) := 
                 ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
         l_string := substr( l_string, n+1 );
    end loop;

    RETURN L_DATA;
  END in_varchar;

现在在 .net 中修改您的查询

 col4 in ( select COLUMN_VALUE from table(in_varchar(:idArray )) )

（顺便说一句，我从我在 Oracle 论坛上回答的先前帖子中复制了大部分代码：
http://forums.oracle.com/forums/thread.jspa ?messageID=4299793�

这实际上可以做到这一点，因此您不必使用数组绑定，只需确保这是一个逗号分隔的字符串： :idArray = "A,B,C"

另一种选择是将 select 语句返回到引用游标数组中：

        /* example table
         * 
Create  Table Zzztab(Deptno Number, Deptname Varchar2(50) , Loc Varchar2(50) , State Varchar2(2) , Idno Number(10)) ;
/
insert into Zzztab(Deptno , Deptname  , Loc  , State , Idno)
values (0,'Zero','US','NY',0);
insert into Zzztab(Deptno , Deptname  , Loc  , State , Idno)
values (1,'One','CA','ON',1);
insert into Zzztab(Deptno , Deptname  , Loc  , State , Idno)
values (2,'Three','IS',null,2);
insert into Zzztab(Deptno , Deptname  , Loc  , State , Idno)
values (3,'Four','BD',null,3);
         */
    string connectStr = GetConnectionString();

    // Initialize array of data
    String[] myArrayDeptName = { "Zero", "Three", "Four" };

    OracleConnection connection = new OracleConnection(connectStr);
    OracleCommand command = new OracleCommand();
    command.Connection = connection;
    command.CommandType = CommandType.Text ;
    command.CommandText = "begin open :cur for SELECT DEPTNO, DEPTNAME FROM ZZZTAB WHERE DEPTNAME = :DEPT; end;";

    command.ArrayBindCount = myArrayDeptName.Length ;
    command.BindByName = true;

    OracleParameter cur = new OracleParameter("cur", OracleDbType.RefCursor );
    cur.Direction = ParameterDirection.Output;
    cur.Value = myArrayDeptName;
    command.Parameters.Add(cur);

    // deptname parameter
    OracleParameter deptNameParam = new OracleParameter("DEPT", OracleDbType.Varchar2);
    deptNameParam.Direction = ParameterDirection.Input;
    deptNameParam.Value = myArrayDeptName;
    command.Parameters.Add(deptNameParam);

     try
    {
        connection.Open();
        command.ExecuteNonQuery();

        foreach (Oracle.DataAccess.Types.OracleRefCursor  rc in (Oracle.DataAccess.Types.OracleRefCursor[])cur.Value)
        { ...  fill in an join the datatables

您可以使用完全相同的逻辑，只需让列返回到它们自己的数组中。