Почему Вы желаете сделать это?
Вы не можете изменить значение *p, так как это - станд. константы:: строка. Если бы Вы действительно изменяли его, то Вы могли бы повредить инварианты контейнера путем изменения порядка сортировки элементов.
, Если у Вас нет других требований, чтобы Вы не давали здесь, затем необходимо просто сделать копию строки.
Еще предстоит найти базу данных, которая поддерживает оценку одной строковой переменной, содержащей запятые, для разделения в качестве единственного предложения IN
.
Вы можете подстроку переменной преобразовать содержимое переменных, разделенных запятыми, в строки, чтобы затем можно было присоединиться к ней. Или использовать динамический SQL,
вы можете использовать конвейерную функцию для преобразования строки в таблицу, которую можно использовать с оператором IN
. Например (проверено с 10gR2):
SQL> select * from table(demo_pkg.string_to_tab('i,j,k'));
COLUMN_VALUE
-----------------
i
j
k
со следующим пакетом:
SQL> CREATE OR REPLACE PACKAGE demo_pkg IS
2 TYPE varchar_tab IS TABLE OF VARCHAR2(4000);
3 FUNCTION string_to_tab(p_string VARCHAR2,
4 p_delimiter VARCHAR2 DEFAULT ',')
5 RETURN varchar_tab PIPELINED;
6 END demo_pkg;
7 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY demo_pkg IS
2 FUNCTION string_to_tab(p_string VARCHAR2,
3 p_delimiter VARCHAR2 DEFAULT ',')
4 RETURN varchar_tab PIPELINED IS
5 l_string VARCHAR2(4000) := p_string;
6 l_first_delimiter NUMBER := instr(p_string, p_delimiter);
7 BEGIN
8 LOOP
9 IF nvl(l_first_delimiter,0) = 0 THEN
10 PIPE ROW(l_string);
11 RETURN;
12 END IF;
13 PIPE ROW(substr(l_string, 1, l_first_delimiter - 1));
14 l_string := substr(l_string, l_first_delimiter + 1);
15 l_first_delimiter := instr(l_string, p_delimiter);
16 END LOOP;
17 END;
18 END demo_pkg;
19 /
Package body created
Ваш запрос будет выглядеть так:
select *
from contract
where contractnum in (select column_value
from table(demo_pkg.string_to_tab(:ContractNum)))
Вы можете использовать коллекцию чисел Oracle как параметр (переменную связывания), когда используете ODP.NET в качестве провайдера данных. Это работает с Oracle server 9, 10 или 11 и выпуском ODP.net> = 11.1.0.6.20.
Аналогичное решение возможно при использовании поставщика данных Devart .NET для Oracle.
Давайте выберем контракты с номерами контрактов 3 и 4.
Мы должны использовать тип Oracle для передачи массива номеров контрактов в наш запрос.
MDSYS.SDO_ELEM_INFO_ARRAY
используется, потому что, если мы используем этот уже предопределенный тип Oracle, нам не нужно определять наш собственный тип Oracle. Вы можете заполнить MDSYS.SDO_ELEM_INFO_ARRAY
не более чем 1048576 числами.
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
[OracleCustomTypeMappingAttribute("MDSYS.SDO_ELEM_INFO_ARRAY")]
public class NumberArrayFactory : IOracleArrayTypeFactory
{
public Array CreateArray(int numElems)
{
return new Decimal[numElems];
}
public Array CreateStatusArray(int numElems)
{
return null;
}
}
private void Test()
{
OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();
b.UserID = "sna";
b.Password = "sna";
b.DataSource = "ora11";
using (OracleConnection conn = new OracleConnection(b.ToString()))
{
conn.Open();
using (OracleCommand comm = conn.CreateCommand())
{
comm.CommandText =
@" select /*+ cardinality(tab 10) */ c.* " +
@" from contract c, table(:1) tab " +
@" where c.contractnum = tab.column_value";
OracleParameter p = new OracleParameter();
p.OracleDbType = OracleDbType.Array;
p.Direction = ParameterDirection.Input;
p.UdtTypeName = "MDSYS.SDO_ELEM_INFO_ARRAY";
//select contract 3 and 4
p.Value = new Decimal[] { 3, 4 };
comm.Parameters.Add(p);
int numContracts = 0;
using (OracleDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
numContracts++;
}
}
conn.Close();
}
}
}
Индекс на contract.contractnum не используется, если опущена подсказка / * + мощность (вкладка 10) * /. Я предположил, что contractnum является первичным ключом, поэтому этот столбец будет проиндексирован.
См. Также здесь: http://forums.oracle.com/forums/thread.jspa?messageID=3869879#3869879