Oracle 매개 변수 및 IN 절

Oracle 매개 변수 및 IN 절

시스템을 사용하여 IN 절에 매개 변수를 추가할 수 있는 방법이 있습니까?Data.Oracle Client.

예:

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

Oracle Command로 래핑할 수 있습니다.확장 방법:

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

를 사용하여 보다 쉽게 작업할 수 있습니다.ODP.NET:

1. 성을 합니다.TABLE데이터베이스에 입력:

   CREATE TYPE t_varchar2 AS TABLE OF VARCHAR2(4000);
   

2. 컬렉션 매개 변수를 만듭니다.

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

3. 매개 변수 채우기:

   param = new string[2] {"Ben", "Sam" };
   

4. 매개 변수를 다음 쿼리에 바인딩합니다.

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

나는 이것이 얼마 전에 물었지만 훌륭한 대답은 아니라는 것을 압니다.

저는 이와 같은 것을 할 것입니다 - 조잡한 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 + ")";

그래서 쿼리는 결국 in(:1,:2)을 갖게 되고 이들 각각은 개별적으로 바인딩됩니다.

여기에도 비슷한 질문이 있습니다. Oracle/c#: 여러 레코드를 반환하기 위해 select 문과 함께 바인딩 변수를 사용하는 방법은 무엇입니까?

아마도 다른 접근법을 사용할 것입니다.

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의 내용은 단일 매개 변수일 수 있습니다.따라서 모든 언어에서 사용할 수 있어야 합니다.

다음과 유사한 오라클 사용자 정의 데이터 유형을 사용할 수 있습니다.
http://www.c-sharpcorner.com/code/2191/.aspxhttp ://www.c-sharpcorner.com/code/2191/pass-collection-to-oracle-stored-procedure-from-net-layer.aspx

그리고 여기:
https://.com/a/31466114/1867157https ://stackoverflow.com/a/31466114/1867157

한 후을 부여합니다. 즉, Oracle과 같은 유형입니다.

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

그런 다음 두 개의 클래스를 만듭니다.

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
}

문자열 목록 사용자 지정TypeFactory.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)))

이렇게 하면 다음과 같은 질문을 할 수 있습니다.

선택 * 표 이름에서 사용자 이름 위치(''Ben'', ''Sam'');

이 두 이름은 하나의 단일 값으로 입력됩니다.

동적 목록을 얻는 방법을 알아보려면 asktom.oracle.com 의 이 스레드를 살펴보십시오.

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

오래된 질문이지만 제 코드를 공유하고 싶습니다.바인딩 매개 변수의 성능과 보안을 유지하면서 동적으로 생성된 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 + " )  ";

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

동일한 질문을 검색할 때 발견되었기 때문에 위의 내용이 실제로 달성되었다고 생각되지 않으므로 도움이 되는 답변을 추가하고자 합니다.

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

링크가 비활성화될 경우를 대비하여 여기에 답을 추가합니다.

Re: IN 절과 함께 바인드 변수 사용 2007년 12월 17일 오후 06: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);

문제가 오래된 것이기는 하지만, 저는 제 경우에 문제를 해결한 방법을 설명합니다.예는 Vb.NET에 있지만 저는 똑같이 이해된다고 생각합니다.일반적으로 해결책은 IN 문을 프로그램별로 각각의 매개 변수를 사용하여 일련의 OR 조건으로 변환하는 것이었습니다.

검색된 값이 쉼표로 구분된 문자열을 사용하는 것부터 시작하여 Oracle에서 사용하는 문자열 따옴표 없이 사용자가 정의된 Oracle Command(이 예에서 또는 Commando라고 함)를 가지고 있다고 가정합니다.제가 한 것은 검색된 값이 있는 문자열을 분할하여 필요한 만큼의 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

이런 방식으로 쿼리는 코드를 조정할 필요 없이 수신된 값의 수에 따라 자동으로 조정됩니다.

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에서 Oracle SP로 전달되는 매개 변수를 입력합니다.

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

솔루션에는 쉼표 문자나 작은따옴표, 큰따옴표를 사용할 수 없습니다.저는 당신이 임시 테이블을 사용하고 그 중에서 선택하는 것을 제안합니다.일반 명령 매개 변수를 사용하여 임시 테이블을 채웁니다.

사실, 저도 이 코드를 사용해 보겠습니다.

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;

언급URL : https://stackoverflow.com/questions/541466/oracleparameter-and-in-clause