C# OracleParameter 和 IN 子句

OracleParameter and IN Clause

提问by rc.

Is there a way to add a parameter to an IN clause using System.Data.OracleClient.

有没有办法使用 System.Data.OracleClient 将参数添加到 IN 子句。

For example:

例如：

string query = "SELECT * FROM TableName WHERE UserName IN (:Pram)";
OracleCommand command = new OracleCommand(query, conn);
command.Parameters.Add(":Pram", OracleType.VarChar).Value = "'Ben', 'Sam'";

回答by Yas

That way your query will be:

这样你的查询将是：

SELECT * FROM TableName WHERE UserName IN ('''Ben'', ''Sam''');

Those two names will be input as one single value.

这两个名称将作为一个值输入。

Have a look at this thread from asktom.oracle.com to find out how to get a dynamic in list.

查看来自 asktom.oracle.com 的此线程以了解如何在列表中获取动态。

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

回答by Quassnoi

You can do it more easily with ODP.NET:

您可以使用ODP.NET以下方法更轻松地做到这一点：

1. Create a TABLEtype in your database:

   CREATE TYPE t_varchar2 AS TABLE OF VARCHAR2(4000);
   

2. Create a collection parameter:

   OracleParameter param = new OracleParameter();
   param.OracleDbType = OracleDbType.Varchar2;
   param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
   

3. Fill the parameter:

   param = new string[2] {"Ben", "Sam" };
   

4. Bind the parameter to the following query:

   SELECT * FROM TableName WHERE UserName IN (TABLE(CAST(:param AS t_varchar2)));
   

1. TABLE在您的数据库中创建一个类型：

   CREATE TYPE t_varchar2 AS TABLE OF VARCHAR2(4000);
   

2. 创建集合参数：

   OracleParameter param = new OracleParameter();
   param.OracleDbType = OracleDbType.Varchar2;
   param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
   

3. 填写参数：

   param = new string[2] {"Ben", "Sam" };
   

4. 将参数绑定到以下查询：

   SELECT * FROM TableName WHERE UserName IN (TABLE(CAST(:param AS t_varchar2)));
   

回答by Big

Actually, I would also try this code :

其实，我也想试试这个代码：

string query = "SELECT * FROM TableName WHERE UserName IN (:Pram)";
param = new string[2] {"Ben", "Sam" };
OracleCommand command = new OracleCommand(query, conn);
command.ArrayBindCount = param.Length;
command.Parameters.Add(":Pram", OracleType.VarChar).Value = param;

回答by Adam Butler

I know this was asked a while ago but not a brilliant answer.

我知道这是前一段时间问过的，但不是一个很好的答案。

I would do something like this - please excuse the crude psudo code

我会做这样的事情 - 请原谅粗略的伪代码

string args[] = {'Ben', 'Sam'};
string bindList = "";
for(int ii=0;ii<args.count;++ii)
{
  if(ii == 0)
  {
   bindList += ":" + ii;
  }
  else
  {
   bindList += ",:" + ii;
  }
  OracleParameter param = new OracleParameter();
  param.dbType = types.varchar;
  param.value = args[ii];
  command.Parameters.Add(param);
}

query = "select * from TableName where username in(" + bindList + ")";

So then query ends up having in(:1,:2) and each of these are bound separately.

然后查询最终有 in(:1,:2) 并且每个都单独绑定。

There is also a similar question here: Oracle/c#: How do i use bind variables with select statements to return multiple records?

这里也有一个类似的问题：Oracle/c#: How do i use bind variables with select statements to return multiple records?

回答by argy

You can wrap it in OracleCommandExtension method:

您可以将其包装在 OracleCommandExtension 方法中：

public static class OracleCommandExtension
{
    public static OracleCommand AddParameterCollection<TValue>(this OracleCommand command, string name, OracleType type, IEnumerable<TValue> collection)
    {
        var oraParams = new List<OracleParameter>();
        var counter = 0;
        var collectionParams = new StringBuilder(":");
        foreach (var obj in collection)
        {
            var param = name + counter;
            collectionParams.Append(param);
            collectionParams.Append(", :");
            oraParams.Add(new OracleParameter(param, type) { Value = obj });
            counter++;
        }
        collectionParams.Remove(collectionParams.Length - 3, 3);
        command.CommandText = command.CommandText.Replace(":" + name, collectionParams.ToString());
        command.Parameters.AddRange(oraParams.ToArray());
        return command;
    }
}

回答by namford

I came across it when searching for the same question, so I'd like to add an answer that I found helpful since I don't believe the above really achieve it:

我在搜索同一问题时遇到了它，所以我想添加一个我认为有用的答案，因为我不相信上述内容真的能实现它：

http://forums.asp.net/t/1195359.aspx/1?Using%20bind%20variable%20with%20an%20IN%20clause

http://forums.asp.net/t/1195359.aspx/1?Using%20bind%20variable%20with%20an%20IN%20clause

I'll add the answer here as well in case the link becomes invalid:

如果链接无效，我也会在此处添加答案：

Re: Using bind variable with an IN clause Dec 17, 2007 06:56 PM|LINK

You must add each value separately. Something like this (writing on a Mac, so I couldn't test it)

string sql = "select id, client_id as ClientID, acct_nbr as AcctNbr from acct where acct_nbr in ( %params% )";
        OracleConnection conn = new OracleConnection(DBConnection);
        OracleCommand cmd = new OracleCommand();


        List<string> params=new List<string>();

        foreach(string acctNbr in AcctNbrs.Split(','))
        {
            string paramName=":acctNbr" + params.Count.Tostring();
            params.Add(paramName)
            OracleParameter parms = new OracleParameter(paramName, OracleType.VarChar);
            parms.Value = acctNbr;
            cmd.Parameters.Add(parms);

        }

        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql.Replace("%params%",params.ToArray().Join(","));
        cmd.Connection = conn;

        OracleDataAdapter da = new OracleDataAdapter(cmd);
        da.Fill(ds);

回复：在 IN 子句中使用绑定变量 2007 年 12 月 17 日下午 06:56|LINK

您必须分别添加每个值。像这样的东西（在 Mac 上编写，所以我无法测试）

string sql = "select id, client_id as ClientID, acct_nbr as AcctNbr from acct where acct_nbr in ( %params% )";
        OracleConnection conn = new OracleConnection(DBConnection);
        OracleCommand cmd = new OracleCommand();


        List<string> params=new List<string>();

        foreach(string acctNbr in AcctNbrs.Split(','))
        {
            string paramName=":acctNbr" + params.Count.Tostring();
            params.Add(paramName)
            OracleParameter parms = new OracleParameter(paramName, OracleType.VarChar);
            parms.Value = acctNbr;
            cmd.Parameters.Add(parms);

        }

        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql.Replace("%params%",params.ToArray().Join(","));
        cmd.Connection = conn;

        OracleDataAdapter da = new OracleDataAdapter(cmd);
        da.Fill(ds);

回答by Edgar Carvalho

Old question but I would like to share my code. Just a simple method to create a string that you can concatenate to a dynamic generated sql, without loosing the performance and security of bind parameters:

老问题，但我想分享我的代码。只是创建一个字符串的简单方法，您可以将其连接到动态生成的 sql，而不会失去绑定参数的性能和安全性：

    /// <summary>
    /// 1 - Given an array of int, create one OracleParameter for each one and assigin value, unique named using uniqueParName
    /// 2 - Insert the OracleParameter created into the ref list.
    /// 3 - Return a string to be used to concatenate to the main SQL
    /// </summary>
    /// <param name="orclParameters"></param>
    /// <param name="lsIds"></param>
    /// <param name="uniqueParName"></param>
    /// <returns></returns>
    private static string InsertParameters(ref List<OracleParameter> orclParameters, int[] lsIds, string uniqueParName)
    {
        string strParametros = string.Empty;

        for (int i = 0; i <= lsIds.Length -1; i++)
        {
            strParametros += i == 0 ? ":" + uniqueParName + i : ", :" + uniqueParName + i;

            OracleParameter param = new OracleParameter(uniqueParName + i.ToString(), OracleType.Number);
            param.Value = lsIds[i];
            orclParameters.Add(param);
        }
        return strParametros;
    }

And use like this:

并像这样使用：

List<OracleParameter> parameterList = new List<OracleParameter>();
int[] idAr = new int[] { 1, 2, 3, 4};
string idStr = InsertParameters(ref parameterList, idAr, "idTest");
string SQL = " SELECT name FROM tblTest WHERE idTest in ( " + idStr + " )  ";

回答by Rohini

Its very simple in ORACLE.

在 ORACLE 中它非常简单。

following steps:

以下步骤：

1.create default type in oracle

1.在oracle中创建默认类型

CREATE OR REPLACE TYPE t_varchar_tab AS TABLE OF VARCHAR2(4000);

2.create function in oracle for seperating given string like "a,b,c" into ''a','b','c''

2.在oracle中创建函数，用于将给定的字符串如“a,b,c”分隔成''a','b','c''

CREATE OR REPLACE FUNCTION in_list(p_in_list  IN  VARCHAR2)ETURNt_varchar_tab

AS

  l_tab   t_varchar_tab := t_varchar_tab();

  l_text  VARCHAR2(32767) := p_in_list || ',' ;

  l_idx   NUMBER;

BEGIN

  LOOP

    l_idx := INSTR(l_text, ',');

    EXIT WHEN NVL(l_idx, 0) = 0;

    l_tab.extend;

    l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));

    l_text := SUBSTR(l_text, l_idx + 1);

  END LOOP;


  RETURN l_tab;

END;

3:Then use following query to extract data from table

3：然后使用以下查询从表中提取数据

SELECT * FROM TABLE_NAME EMP WHERE  IN (SELECT * FROM TABLE(in_list(i_input1)));

4.Input parameter passing from c#.net to oracle SP like

4.输入参数从c#.net传递到oracle SP之类的

 cmd.Parameters.Add("i_input1", OracleType.VarChar, 50).Value = "S1,S2";

回答by raduV

SELECT * FROM Clients 
WHERE id IN ( 
SELECT trim(regexp_substr(str, '[^,]+', 1, level)) strRows 
FROM (SELECT :Pram as str from dual ) t   
CONNECT BY instr(str, ',', 1, level -1) >0);

回答by John Smith

The solution should not contain the comma character nor single quotes, double quotes. I suggest that you use a temp table and then select from that. Populate the temp table using regular command parameters.

解决方案不应包含逗号字符，也不应包含单引号、双引号。我建议您使用临时表，然后从中进行选择。使用常规命令参数填充临时表。