Параметр OracleParameter и IN

Question

Параметр OracleParameter и IN

Есть ли способ добавить параметр в предложение IN с помощью System.Data.OracleClient.

Например:

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

24

c# .net oracle

Источник

user19067 12 фев '09 в 14:25

12 ответов

Другие вопросы по тегам c# .net oracle

user495803 03 ноя '10 в 10:52 2010-11-03 10:52 · Answer 1 · 2010-11-03 10:52

Вы можете обернуть его в метод 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;
    }
}

user55159 12 фев '09 в 15:12 2009-02-12 15:12 · Answer 2 · 2009-02-12 15:12

Вы можете сделать это легче с ODP.NET:

Создать TABLE введите в вашей базе данных:
```
CREATE TYPE t_varchar2 AS TABLE OF VARCHAR2(4000);
```

Создайте параметр коллекции:

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

Заполните параметр:
```
param = new string[2] {"Ben", "Sam" };
```

Привязать параметр к следующему запросу:

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

user2192239 31 янв '18 в 11:28 2018-01-31 11:28 · Answer 3 · 2018-01-31 11:28

Возможно, используя другой подход

SELECT * FROM SCOTT.EMP WHERE EMPNO IN (SELECT TO_NUMBER(X.COLUMN_VALUE) FROM XMLTABLE('7788,7900') X);

или же

SELECT * FROM SCOTT.EMP WHERE ENAME IN (SELECT X.COLUMN_VALUE.GETSTRINGVAL() FROM XMLTABLE('"SCOTT", "JAMES"') X);

Где содержимое XMLTABLE может быть единственным параметром. Следовательно, его можно использовать на любом языке.

user417721 23 сен '10 в 21:35 2010-09-23 21:35 · Answer 4 · 2010-09-23 21:35

Я знаю, что это спросили некоторое время назад, но не блестящий ответ.

Я хотел бы сделать что-то вроде этого - пожалуйста, извините сырой псевдо-код

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 + ")";

Итак, запрос в конечном итоге имеет в (:1,:2), и каждый из них связаны в отдельности.

Здесь также есть похожий вопрос: Oracle / C#: как использовать переменные связывания с операторами select для возврата нескольких записей?

user1867157 19 май '16 в 16:31 2016-05-19 16:31 · Answer 5 · 2016-05-19 16:31

Вы можете использовать пользовательский тип данных Oracle, подобный следующему:
http://www.c-sharpcorner.com/code/2191/pass-collection-to-oracle-stored-procedure-from-net-layer.aspx

и здесь:
/questions/249664/oracle-v-c-svyazyivat-peremennyie-i-zaprosyi-takie-kak-id-in-1-2-3/249674#249674

Сначала создайте тип в Oracle и дайте ему разрешения:

CREATE TYPE MYSCHEMA.VARCHAR2_TAB_T AS TABLE OF VARCHAR2(4000);
GRANT EXECUTE ON MYSCHEMA.VARCHAR2_TAB_T TO MYROLE

Затем создайте 2 класса:

StringListCustomType.cs

public class StringListCustomType : IOracleCustomType, INullable
{
    public const string Name = "MYSCHEMA.VARCHAR2_TAB_T";

    [OracleArrayMapping()]
    public string[] Array;

    #region IOracleCustomType
    public OracleUdtStatus[] StatusArray { get; set; }

    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        object objectStatusArray = null;
        Array = (string[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
        StatusArray = (OracleUdtStatus[])objectStatusArray;
    }

    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, 0, Array, StatusArray);
    }
    #endregion

    #region INullable
    public bool IsNull { get; set; }

    public static StringListCustomType Null
    {
        get
        {
            StringListCustomType obj = new StringListCustomType();
            obj.IsNull = true;
            return obj;
        }
    }
    #endregion
}

StringListCustomTypeFactory.cs

[OracleCustomTypeMapping(StringListCustomType.Name)]
public class StringListCustomTypeFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    #region IOracleCustomTypeFactory
    IOracleCustomType IOracleCustomTypeFactory.CreateObject()
    {
        return new StringListCustomType();
    }
    #endregion

    #region IOracleArrayTypeFactory
    Array IOracleArrayTypeFactory.CreateArray(int numElems)
    {
        return new string[numElems];
    }

    Array IOracleArrayTypeFactory.CreateStatusArray(int numElems)
    {
        return new OracleUdtStatus[numElems];
    }
    #endregion
}

Затем вы можете добавить такой параметр:

dbParameter = new OracleParameter();
dbParameter.ParameterName = "myparamname";
dbParameter.UdtTypeName = StringListCustomType.Name;
dbParameter.OracleDbType = OracleDbType.Array;

if (myarray != null)
{
    StringListCustomType newArray = new StringListCustomType();
    newArray.Array = myarray;
    dbParameter.Value
}
else
{
    dbParameter.Value = StringListCustomType.Null;
}

Ваш запрос будет выглядеть так:

SELECT * 
  FROM MYSCHEMA.MYTABLE 
 WHERE MYVARCHARFIELD IN (SELECT COLUMN_VALUE 
                            FROM TABLE(CAST(:myparamname AS MYSCHEMA.VARCHAR2_TAB_T)))

user900640 28 июл '15 в 05:44 2015-07-28 05:44 · Answer 6 · 2015-07-28 05:44

Старый вопрос, но я хотел бы поделиться своим кодом. Просто простой способ создать строку, которую вы можете объединить в динамически сгенерированный 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;
    }

И используйте так:

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 + " )  ";

user5192852 05 авг '15 в 08:01 2015-08-05 08:01 · Answer 7 · 2015-08-05 08:01

Это очень просто в ORACLE.

следующие шаги:

1. создать тип по умолчанию в оракуле

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

2.создать в оракуле функцию для разделения заданной строки типа "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: Затем используйте следующий запрос для извлечения данных из таблицы

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

4. Передача входного параметра из C#.net в SP Oracle

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

user65055 12 фев '09 в 14:33 2009-02-12 14:33 · Answer 8 · 2009-02-12 14:33

Таким образом, ваш запрос будет:

SELECT * FROM TableName, ГДЕ UserName IN ('''Ben'', ''Sam''');

Эти два имени будут введены как одно единственное значение.

Загляните в эту ветку на сайте asktom.oracle.com, чтобы узнать, как получить динамику в списке.

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

1

Источник

user65055 12 фев '09 в 14:33

user714881 08 авг '13 в 10:24 2013-08-08 10:24 · Answer 9 · 2013-08-08 10:24

Я столкнулся с этим при поиске того же вопроса, поэтому я хотел бы добавить ответ, который мне показался полезным, так как я не верю, что вышеизложенное действительно достигает этого:

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

Я также добавлю ответ здесь, если ссылка станет недействительной:

Re: Использование переменной связывания с предложением IN 17 декабря 2007 г. 18: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);

user13385625 16 июн '20 в 22:18 2020-06-16 22:18 · Answer 10 · 2020-06-16 22:18

Хотя вопрос старый, я объясняю, как я решил его в моем случае. Пример приведен на Vb.NET, но я думаю, что он одинаково понятен. Решение, в общем, состояло в том, чтобы преобразовать оператор IN в серию условий ИЛИ с соответствующими параметрами, все программно.

Начиная с наличия строки с искомыми значениями, разделенными запятыми, БЕЗ строковых кавычек, которые Oracle будет использовать, и при условии, что у вас есть определенная OracleCommand, которую я назвал oraCommando в этом примере. Я собрал строку запроса, разделив строку с искомыми значениями, создав столько сравнений OR, сколько необходимо, и присвоив им значения с соответствующими параметрами. Особое внимание следует уделить присвоению имени параметра в сборке строки запроса, чтобы не оставлять пробелов между именем и числом, которое ставится в конце, чтобы все они были разными именами.

strCommand & = " UserName = :userName" & puntParam & " "

Вот пример кода:

dim param as string = "Ben, Sam"
dim strCommand as string = "SELECT * FROM TableName WHERE"
dim puntParam as integer = 0
for each paramAnali as string in split (param, ",")
    puntParam + = 1
    if puntParam> 1 then
        strCommand & = "or"
    end if
    strCommand & = "UserName =: userName" & puntParam.ToString () & ""

    Dim paramNew As New OracleParameter With {
      .ParameterName = "userName" & puntParam.ToString (),
      .OracleDbType = OracleDbType.Varchar2,
      .Direction = ParameterDirection.Input,
      .Value = Trim (paramAnali)}

    oraCommando.Parameters.Add (paramNew)

next

Также, чтобы не было проблем с привязкой параметров, команде Oracle необходимо дать указание выполнять "привязку" по именам.

oraCommando.BindByName = True

Таким образом, запрос автоматически подстраивается под количество полученных значений без необходимости корректировать код.

user3497700 13 окт '15 в 09:06 2015-10-13 09:06 · Answer 11 · 2015-10-13 09:06

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

0

Источник

user3497700 13 окт '15 в 09:06

user2411533 07 апр '16 в 17:33 2016-04-07 17:33 · Answer 12 · 2016-04-07 17:33

Решение не должно содержать запятую, ни одинарные кавычки, ни двойные кавычки. Я предлагаю вам использовать временную таблицу, а затем выбрать из этого. Заполните временную таблицу, используя обычные параметры команды.

-1

Источник

user2411533 07 апр '16 в 17:33

user164892 28 авг '09 в 13:53 2009-08-28 13:53 · Answer 13 · 2009-08-28 13:53

На самом деле, я бы тоже попробовал этот код:

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;

-2

Источник

user164892 28 авг '09 в 13:53