Параметры Oracle с оператором IN?

Получил c#.net приложение, которое я должен изменить. Запрос в данный момент эффективно делает это:

select * from contract where contractnum = :ContractNum

(очень упрощенный, только для показа мы используем = и один параметр),

Тот параметр читается в из Настроек. Файл настроек на приложении C# и имеет одну строку в нем. Я должен изменить его для включения нескольких контрактов, таким образом, я полагаю, что могу изменить SQL на:

select * from contract where contractnum in (:ContractNum)

но это не возвращает результатов, неважно, как я форматирую строку в параметре.

Существует ли способ, в котором я могу заставить оракула делать с параметром?

7
задан ΩmegaMan 16 September 2019 в 20:16
поделиться

3 ответа

Еще предстоит найти базу данных, которая поддерживает оценку одной строковой переменной, содержащей запятые, для разделения в качестве единственного предложения IN .

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

2
ответ дан 6 December 2019 в 14:05
поделиться

вы можете использовать конвейерную функцию для преобразования строки в таблицу, которую можно использовать с оператором 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)))
6
ответ дан 6 December 2019 в 14:05
поделиться

Вы можете использовать коллекцию чисел 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

7
ответ дан 6 December 2019 в 14:05
поделиться
Другие вопросы по тегам:

Похожие вопросы: