복붙노트

[SQL] IN 문을 오라클 매개 변수?

SQL

IN 문을 오라클 매개 변수?

내가 수정할 필요가 C # .NET 응용 프로그램을 얻었다. 순간 쿼리를 효과적으로 수행합니다

select * from contract where contractnum = :ContractNum

(매우 단지 우리가를 사용하고 보여주기 위해 단순화 = 하나 개의 매개 변수)

즉, 매개 변수는 C # 응용 프로그램에 Settings.Settings 파일에서 읽어와 그 안에 하나의 문자열을 가지고 있습니다. 나는 내가 SQL을 변경할 수 있습니다 파악 그래서 여러 계약을 포함하도록 수정해야합니다 :

select * from contract where contractnum in (:ContractNum)

그러나 그 결과를 반환하지 않습니다, 나는 매개 변수에 문자열을 포맷하는 방법에 상관없이.

내가 매개 변수와 함께 IN을 할 수있는 오라클을 얻을 수있는 방법이 있나요?

해결법

  1. ==============================

    1.지원이 절에서 단독으로 분리하는 쉼표를 포함하는 단일 문자열 변수를 평가하는 DB를 찾을 아직.

    지원이 절에서 단독으로 분리하는 쉼표를 포함하는 단일 문자열 변수를 평가하는 DB를 찾을 아직.

    옵션은 쉼표는 다음이에 가입 할 수 있도록 변수 내용, 행으로 설정되어 구분 있도록 변수를 하위 문자열에 있습니다. 또는 명령문이 실행되기 전에 SPROC의 문자열로 구성된 SQL 문이다 동적 SQL을 사용합니다.

  2. ==============================

    2.당신의 dataProvider로 ODP.NET을 사용할 때 매개 변수 (바인드 변수)와 같은 수의 오라클 모음을 사용할 수 있습니다. 이것은 Oracle 서버 9, 10 또는 11 ODP.net 릴리스> = 11.1.0.6.20와 함께 작동합니다.

    당신의 dataProvider로 ODP.NET을 사용할 때 매개 변수 (바인드 변수)와 같은 수의 오라클 모음을 사용할 수 있습니다. 이것은 Oracle 서버 9, 10 또는 11 ODP.net 릴리스> = 11.1.0.6.20와 함께 작동합니다.

    오라클에 대한 Devart .NET 데이터 공급자를 사용할 때 비슷한 솔루션이 가능합니다.

    이제 contractnum의 3, 4와 계약을 선택할 수 있습니다.

    우리는 우리의 쿼리에 계약 번호의 배열을 전송하는 오라클 유형을 사용해야합니다.

    우리가 이미 사전 정의 된 오라클 입력 사용하는 경우 우리는 우리 자신의 오라클의 유형을 정의 할 필요가 없기 때문에 MDSYS.SDO_ELEM_INFO_ARRAY이 사용됩니다. 당신은 최대 1048576 개 숫자 MDSYS.SDO_ELEM_INFO_ARRAY을 채울 수 있습니다.

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

    하나 개는 생략합니다 / * + 카디널리티 (10 탭) * / 힌트 contract.contractnum 때의 지수가 사용되지 않는다. 나는이 열이 인덱싱 할 수 있도록 contractnum가 기본 키입니다 가정합니다.

    여기를 참조하십시오 : http://forums.oracle.com/forums/thread.jspa?messageID=3869879#3869879

  3. ==============================

    3.당신은 IN 연산자와 함께 사용할 수있는 테이블에 문자열을 변환하는 파이프 라인 기능을 사용할 수 있습니다. 예를 들어 (10gR2의 테스트) :

    당신은 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)))
  4. ==============================

    4.IN 문 매개 변수를 사용하기 위해이 구조를 사용할 수 있습니다 :

    IN 문 매개 변수를 사용하기 위해이 구조를 사용할 수 있습니다 :

    select * from contract where contractnum
in (select column_value from table (:ContractNum))

    여기서 ContractNum 사용자 정의 어레이 형이다.

  5. ==============================

    5.나는이 오래된 질문 알고 있지만, 선택한 답변이 내 문제가 해결되지 않는 한 몇 가지 중 하나이며 나는 단지 내가 나의 여행에서 발견 무엇을 내려 놓을 게요 그래서이 주제에 대한 또 다른 스레드를 시작하지 않으 누군가 도움이 될 것을 희망한다.

    나는이 오래된 질문 알고 있지만, 선택한 답변이 내 문제가 해결되지 않는 한 몇 가지 중 하나이며 나는 단지 내가 나의 여행에서 발견 무엇을 내려 놓을 게요 그래서이 주제에 대한 또 다른 스레드를 시작하지 않으 누군가 도움이 될 것을 희망한다.

    내가 SQL 서버처럼, 많은 오라클 작동하지 않습니다하지만, 그것은 그 전달하는 것 같다 테이블 반환하면 해당 UDT 당신이 (내가 잘못 될 수 있습니다) 실행 권한이있는에 (사용자 정의 테이블)이 필요 매개 변수입니다. 다른 답변의 사용을 제안하는 것이이 방법 내장 SYS UDT 일부화물에 와서 나는 정말로 무언가에 테이블을 통과 할 수 있는지 여부를 알아낼 수있는 PL / SQL 현재 버전에서 저장 프로 시저 ODP.net의.

    둘째, 문자열 구문 분석 솔루션은 모든 분명한 이유에 대한 kludge는 (실행 계획을 캐시 할 수 없거나 오라클이 그것을 호출간에, 잘 등의 크기를 조절하지 않습니다)입니다.

    나는 더 적은 ASP.net 포럼에서 (명백한의 눈부신 플래시에 의해 명중되기 전에 내가 읽기 전용 권한이없는 한 어떤에 마트에 테이블 반환 매개 변수를 사용하여 IN-절을 시도하지 않고 많은 시간을 보냈다 그래서 ). 오라클 밝혀하여 XML '기본적으로'쿼리 그래서 대신에 (즉, 당신이 필요로하는 모든 경우) 당신은 XML 목록을 전달할 수있는 값의 배열을 전달을 지원합니다. 다시 말하지만, 제가 틀릴 수도 있지만 합법적 인 바인드 매개 변수로 취급됩니다이은 (vb.net, ADO.net, ODP.net는 NuGet 패키지를 사용)를 사용하는 것이 얼마나 간단한 예입니다 :

        Dim xe As New XElement("l", New XElement("i", "ITEM-A"), New XElement("i", "ITEM-B"))
    Using conn As New OracleConnection(myConnectionString)
        conn.Open()
        Using cmd As OracleCommand = conn.CreateCommand()
            cmd.CommandType = CommandType.Text
            Dim query As String
            query = "  SELECT s.FOO, q.BAR " & vbCrLf
            query &= " FROM TABLE1 s LEFT OUTER JOIN " & vbCrLf
            query &= "      TABLE2 q ON q.ID = s.ID " & vbCrLf
            query &= " WHERE (COALESCE(q.ID, 'NULL') NOT LIKE '%OPTIONAL%') AND "
            query &= "       (s.ID IN ("
            query &= "                      SELECT stid "
            query &= "                      FROM XMLTable('/l/i' PASSING XMLTYPE(:stid) COLUMNS stid VARCHAR(32) PATH '.')"
            query &= "                 )"
            query &= "        )"
            cmd.CommandText = query
            Dim parameter As OracleParameter = cmd.Parameters.Add("stid", OracleDbType.NVarchar2, 4000)
            parameter.Value = xe.ToString
            Using r As OracleDataReader = cmd.ExecuteReader
                While r.Read()
                    //Do something
                End While
            End Using
        End Using
        conn.Close()

    이런 식으로 일에 대한 부적절한 거기에 아무것도 경우에 더하십시오 코멘트 그래서주의 깊게 연구 솔루션보다 관측이다.

    내 페이징을 볼 수 있었다, 그래서이 방법 (2000 NVARCHAR 경우)를 사용하여 4000 자 제한이 분명히 있습니다. 당신이 가서 경우에 당신이 얻는 정보 오류 메시지는

    6. from https://stackoverflow.com/questions/1625649/oracle-parameters-with-in-statement by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 당신은 어떻게 당신이 저장된 프로 시저에서 반환 데이터 집합의 테이블 이름을 지정할 수 있습니다?  (0) 2020.05.13
[SQL] T-SQL 트림 & NBSP (및 기타 영숫자가 아닌 문자)  (0) 2020.05.13
[SQL] 액세스 2013 SQL에 TRANSFORM 및 PIVOT  (0) 2020.05.13
[SQL] 분할 쉼표 오라클 SQL 쿼리를 통해 행의 열 값을 분리  (0) 2020.05.13
[SQL] 세 이하의 문자와 단어에 대한 MySQL의 전체 텍스트 검색  (0) 2020.05.13
,

티스토리툴바