C#2010，ODP.net，调用存储过程传递数组 [英] C# 2010, ODP.net, call stored procedure passing array

问题描述

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

I have a PL/SQL stored procedure that takes 4 inputs. One of those inputs is an associative array (Oracle Type: Table of VARCHAR2(1) index by PLS_INTEGER).

我想拥有一个C#程序，以适当的输入(包括关联数组)调用此存储过程.

I want to have a C# program that calls this stored procedure with the proper inputs including the associative array.

我正在将ODP.net 11.2与Visual C#2010 Express和Oracle 11gR2一起使用.

I am using ODP.net 11.2 with Visual C# 2010 Express and Oracle 11gR2.

我找不到如何从C#将数组传递给pl/sql过程的任何好例子.有人可以给我一个例子吗?完全遵循Oracle Documentation会给我错误，提示参数数目或类型错误.

I cannot find any good examples of how to pass an array to a pl/sql procedure from C#. Can anybody give me an example? Following Oracle Documentation exactly gives me error saying Wrong number or type of arguments.

我的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();

我所要做的只是记录一条消息.我只是想使这个概念起作用.

All my procedure does is log a message. I am just trying to get this concept to work.

推荐答案

您可以从(简单的方法)开始:

You can start from (simpler way):

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();
    }
}

然后，在db中添加包/过程:

Then, add package / procedure in db:

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();
    }
}

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

But it is more complicated, since you need to define new types for the stored procedure, like

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

创建类似架构级别的类型

create a schema-level type like

create or replace TYPE T_ID_TABLE is table of number; 

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

And then use them in the Stored Procedure, like

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;

这篇关于C#2010，ODP.net，调用存储过程传递数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！