Есть ли способ добавить параметр в предложение 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'";
Вы можете сделать это более легко с помощью 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)));
Вы можете обернуть его в методе 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;
}
}
Я знаю, что это было задано некоторое время назад, но не блестящий ответ.
Я бы сделал что-то вроде этого - пожалуйста, извините грубый код psudo
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/С#: Как использовать переменные bind с операторами select для возврата нескольких записей?
Вы можете использовать пользовательский тип данных Oracle, похожий на здесь:
http://www.c-sharpcorner.com/code/2191/pass-collection-to-oracle-stored-procedure-from-net-layer.aspx
и здесь:
fooobar.com/questions/199835/...
Сначала создайте тип в 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)))
Таким образом, ваш запрос будет выглядеть следующим образом:
SELECT * FROM TableName WHERE UserName IN ('''Ben'', ''Sam''');
Эти два имени будут введены как одно значение.
Посмотрите этот поток с asktom.oracle.com, чтобы узнать, как получить динамический список.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425
Я столкнулся с этим при поиске того же вопроса, поэтому я хотел бы добавить ответ, который нашел полезным, потому что я не верю, что это действительно так:
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);
Старый вопрос, но я хотел бы поделиться своим кодом. Просто простой способ создания строки, которую вы можете объединить с динамическим сгенерированным 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 + " ) ";
Это очень просто в ORACLE.
следующие шаги:
1.Создать тип по умолчанию в oracle
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. Ввод параметра, передаваемого из С#.net в oracle SP как
cmd.Parameters.Add("i_input1", OracleType.VarChar, 50).Value = "S1,S2";
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);
Возможно, используя другой подход
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 может быть единственным параметром. Следовательно, он должен использоваться на любом языке.
Решение не должно содержать запятую или одинарные кавычки, двойные кавычки. Я предлагаю вам использовать временную таблицу, а затем выбрать ее. Заполните таблицу temp с помощью регулярных параметров команды.
Собственно, я бы тоже попробовал этот код:
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;