C# 2010，ODP.net，调用存储过程传递数组

我有一个需要 4 个输入的 PL/SQL 存储过程。这些输入之一是关联数组（Oracle 类型：PLS_INTEGER 索引的 VARCHAR2(1) 表）。

我想要一个 C# 程序，使用正确的输入（包括关联数组）调用此存储过程。

我将 ODP.net 11.2 与 Visual C# 2010 Express 和 Oracle 11gR2 结合使用。

我找不到任何关于如何从 C# 将数组传递到 pl/sql 过程的好示例。有人能给我举个例子吗？按照 Oracle 文档，我确实遇到错误，指出参数的数量或类型错误。

我的 C# 代码：

        OracleCommand cmd = new OracleCommand("begin sdg_test.sdg_test2(:1); end;", conn);

        OracleParameter Param1 = cmd.Parameters.Add("1", OracleDbType.Varchar2);

        Param1.Direction = ParameterDirection.Input;

        Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

        Param1.Value = new string[22] { "Y", "Y", "N", "Y", "N", "Y", "Y", "Y", "Y", "Y", "N", "Y", "N", "Y", "Y", "Y", "Y", "Y", "N", "Y", "N", "Y" };

        Param1.Size = 22;
        Param1.ArrayBindSize = new int[22] { 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 };

        cmd.ExecuteNonQuery();

        conn.Close();
        conn.Dispose();

我的程序所做的只是记录一条消息。我只是想让这个概念发挥作用。

您可以从（更简单的方法）开始：

List<int> idList = yourObjectList;
List<int> nameList = yourObjectList;

using (OracleConnection oraconn = new OracleConnection())
{
    oraconn.ConnectionString = "Your_Connection_string";

    using (OracleCommand oracmd = new OracleCommand())
    {
        oracmd.Connection = oraconn;

        oracmd.CommandType = CommandType.StoredProcedure;
        oracmd.CommandText = "Your_Procedura_name";
        oraconn.Open();

        // To use ArrayBinding, you need to set ArrayBindCount   
        oracmd.BindByName = true;
        oracmd.ArrayBindCount = idList.Count;

        // Instead of single values, we pass arrays of values as parameters   
        oracmd.Parameters.Add("ids", OracleDbType.Int32, oyourObjectList.ToArray(), ParameterDirection.Input);
        oracmd.Parameters.Add("names", OracleDbType.Varchar2, oyourObjectList.ToArray(), ParameterDirection.Input);

        oracmd.ExecuteNonQuery();
        oraconn.Close();
    }
}

然后，在数据库中添加包/过程：

PROCEDURE Your_Procedure_name(
      name IN VARCHAR2,
      id IN NUMBER
      )    IS     
BEGIN

    INSERT INTO your_table VALUES( id, name);
END Your_Procedure_name;

另一种选择是：

using (OracleConnection oraconn = new OracleConnection())
{
    oraconn.ConnectionString = "Your_Connection_string";

    using (OracleCommand cmd = new OracleCommand())
    {

        cmd.Connection = oraconn;

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Your_Procedure_name";
        oraconn.Open();


        OracleParameter idParam = new OracleParameter("i_idList", OracleDbType.Int32, ParameterDirection.Input);
        idParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        idParam.Value = idList.ToArray();
        idParam.Size = idList.Count;

        OracleParameter nameParam = new OracleParameter("i_nameList", OracleDbType.Varchar2, ParameterDirection.Input);
        nameParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        nameParam.Value = nameList.ToArray();
        nameParam.Size = nameList.Count;

        // You need this param for output
        cmd.Parameters.Add("ret", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
        cmd.Parameters.Add(idParam);
        cmd.Parameters.Add(nameParam);

        conn.Open();

        //If you need to read results ...
        using (OracleDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                ...
            }
        }
        conn.Close();
    }
}

但它更复杂，因为您需要为存储过程定义新类型，例如

TYPE integer_list IS TABLE OF Your_table.id_column%TYPE INDEX BY BINARY_INTEGER;
// same for names

创建一个模式级别类型，例如

create or replace TYPE T_ID_TABLE is table of number; 

然后在存储过程中使用它们，例如

PROCEDURE Your_Procedure_name(
      v_ret IN OUT SYS_REFCURSOR,
      i_idList integer_list,
      i_nameList string_list)
  IS  
  begin
    -- Store passed object id list to array
 idList T_ID_TABLE := T_ID_TABLE(); 
  ...
  begin

    -- Store passed object id list to array
    idList.Extend(i_idList.Count);
    FOR i in i_idList.first..i_idList.last loop
     idList(i) := i_idList(i);
    END LOOP;    

    ...
END Your_Procedure_name;