복붙노트

[SQL] 오라클 OCI, ID 등 IN 바인드 변수 및 질의 (1, 2, 3)

SQL

오라클 OCI, ID 등 IN 바인드 변수 및 질의 (1, 2, 3)

간결 버전 :

나는 코드가 아닌 PL / SQL SELECT 문에 (배열의 크기가 다를 수 있음) 숫자의 배열을 바인딩 한 다음 WHERE에 결과 배열을 사용할 수있는 다음과 같은 자바 기술의 C ++ OCI 적응을 찾고 있어요 ID IN (...) 스타일의 체크.

http://rafudb.blogspot.com/2011/10/variable-inlist.html

원래 질문 :

우리는 OCI를 통해 오라클에 C ++ 응용 프로그램이하는 이야기가 있습니다. 우리는 텍스트를 연결하여 SQL 쿼리를 생성 된 코드를 해결하기 위해 노력하고 있습니다; 대신 우리는 가능한 한 바인드 변수를 사용하고 싶습니다. 하나의 특정 사건은 우리에게 좋은 솔루션을하지 않아도 올있다.

SELECT * FROM MyTable WHERE ID IN (1, 4, 10, 30, 93)

제 (1, 4, 10, 30, 93) 부분은 데이터 벡터 유연 또는 다른 크기의 용기 출처. 우리는 항상 다섯 개 값 줄 알았 경우에, 우리는 할 수있다 :

SELECT * FROM MyTable WHERE ID IN (:1, :2, :3, :4, :5)

그러나 그것은 하나 개의 항목, 또는 열, 또는 어쩌면 제로 수 있습니다. 우리가 문자열로 쿼리를 구축하는 경우 분명히, 우리는 단지 APPEND 많은 번호와 같은 우리가해야 할 수 있지만, 목표는 단지 ​​바인드 변수에 가능한 스틱 경우에 방지하는 것입니다.

이 작업을 수행 할 수있는 좋은 방법이 있나요? 예를 들어, OCI, 나는 배열하고 그것의 서브 - 선택을 결합 할 수있다?

SELECT * FROM MyTable WHERE ID IN (SELECT * FROM :1)

여기서 OCI 배열은 1? (아마 구문이 다른 것입니다.) 사람이 경험이 있습니까? 나는 원시 OCI를 작성하여 투쟁하는 경향이 샘플 코드는 신의 선물이 될 것입니다. 감사 :)

편집 : 나는 모든 가능의 경우 PL / SQL 절차에 의해 구문 분석되는 문자열에 바인딩보다 더 잘 할 싶습니다. 나는 우리가 많은 경우에 4000 자 제한을 불어 것이라고 확신하고 그냥 아니에요 또 다른 종류의 내가 편안 해요 있음을 문자열 조작의 종류를 거래처럼 나 또한 기분이 (나는 할 수 없습니다 ) 쉽게 디버깅합니다. 가능하면 나는 하나의 표준 SQL 문에 값의 배열 (또는 데이터 세트의 형태)를 결합하고 싶습니다.

수정 2 : 일부 조사는 내가 원하는 바로 일을 할 것 같습니다 다음 링크를 설정하지만, 자바 : http://rafudb.blogspot.com/2011/10/variable-inlist.html이 적응하는 방법을 사람의 노하우를합니까 C ++ OCI에 접근?

해결법

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

    1.이 예는 매개 변수의 목록을 전달하는 데이터베이스에 정의 된 콜렉션 유형을 사용과 접근 방식을 보여줍니다. SYS.ODCINumberList 모든 사용자가 사용할 수 표준 콜렉션 유형입니다. 쿼리, 샘플에 사용 된 단지 첫번째 100 개 정수 (테스트)를 선택하고 (...) IN 절에서 목록이 정수를 필터링 할 수 있습니다.

    이 예는 매개 변수의 목록을 전달하는 데이터베이스에 정의 된 콜렉션 유형을 사용과 접근 방식을 보여줍니다. SYS.ODCINumberList 모든 사용자가 사용할 수 표준 콜렉션 유형입니다. 쿼리, 샘플에 사용 된 단지 첫번째 100 개 정수 (테스트)를 선택하고 (...) IN 절에서 목록이 정수를 필터링 할 수 있습니다.

    #include "stdafx.h"
#include <iostream>
#include <occi.h>

using namespace oracle::occi;
using namespace std;

// Vector type to pass as parameter list
typedef vector<Number> ValueList;

int _tmain(int argc, _TCHAR* argv[])
{
  Environment *env;
  Connection *con;

  // Note that Environment must be initialized in OBJECT mode 
  // to use collection mapping features.
  env = Environment::createEnvironment(Environment::OBJECT);

  con = env->createConnection ("test_user", "test_password", "ORACLE_TNS_NAME");

  try {

    Statement *stmt = con->createStatement(
                 "select * from "
                 " (select level as col from dual connect by level <= 100)"
                 "where "
                 "  col in (select column_value from table(:key_list))"
               );

    cout << endl << endl << "Executing the block :" << endl 
         << stmt->getSQL() << endl << endl;

    // Create instance of vector trype defined above 
    // and populate it with numbers.
    ValueList value_list;
    value_list.push_back(Number(10));
    value_list.push_back(Number(20));
    value_list.push_back(Number(30));
    value_list.push_back(Number(40));

    // Bind vector to parameter #1 in query and treat it as SYS.ODCINumberList type. 
    setVector(stmt, 1, value_list, "SYS", "ODCINUMBERLIST");

    ResultSet *rs = stmt->executeQuery();

    while(rs->next())
      std::cout << "value: " << rs->getInt(1) << std::endl;

    stmt->closeResultSet(rs); 
    con->terminateStatement (stmt);

  } catch(SQLException ex) {
    cout << ex.what();
  }


  env->terminateConnection (con);
  Environment::terminateEnvironment (env);

    return 0;
}

    당신은 OCI를 통해 오라클 숫자, 날짜 또는 문자열 목록을 전달하거나 DB에 자신의 유형을 정의하기 위해 다양한 ODCIxxxList 유형을 사용할 수 있습니다.

    예 비주얼 스튜디오 10 Express 및 OCI 라이브러리의 버전으로 컴파일. 오라클 11.2.0.3.0에 대해 테스트.

    최신 정보

    다음은 일반 C OCIxxx 기능이 있지만, 같은 일을 예를 들어 응용 프로그램입니다.

    //
// OCI collection parameters binding - example application
//

#include "stdafx.h"
#include <iostream>
#include <oci.h>
#include <oro.h>

using namespace std;

// connection parameters
const char *db_alias         = "ORACLE_DB_ALIAS";
const char *db_user_name     = "test_user";
const char *db_user_password = "test_password";

// helper error checking procedure to shorten main code, returns true if critical error detected
// and prints out error information
bool check_oci_error(char *error_point, OCIError *errhp, sword status, OCIEnv *envhp);

int _tmain(int argc, _TCHAR* argv[]) {

  //----- CONNECTION INITIALIZATION PART ------------------------------------------------------

  sword rc;
  OCIEnv *myenvhp;       /* the environment handle */
  OCIServer *mysrvhp;    /* the server handle */
  OCIError *myerrhp;     /* the error handle */
  OCISession *myusrhp;   /* user session handle */
  OCISvcCtx *mysvchp;    /* the  service handle */

  /* initialize the mode to be the threaded and object environment */
  /* NOTE: OCI_OBJECT must be present to work with object/collection types */
  rc = OCIEnvCreate(&myenvhp, OCI_THREADED|OCI_OBJECT, (dvoid *)0, 0, 0, 0, (size_t) 0, (dvoid **)0);

  if( check_oci_error("OCIEnvCreate", NULL, rc, NULL) ) {
    return -1; 
  }

  /* allocate a server handle */
  rc = OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&mysrvhp, OCI_HTYPE_SERVER, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_SERVER)", NULL, rc, myenvhp) ) return -1;

  /* allocate an error handle */
  rc = OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&myerrhp, OCI_HTYPE_ERROR, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_ERROR)", NULL, rc, myenvhp) ) return -1;

  /* create a server context */
  rc = OCIServerAttach(mysrvhp, myerrhp, (text *)db_alias, strlen (db_alias), OCI_DEFAULT);
  if( check_oci_error("OCIServerAttach()", myerrhp, rc, myenvhp) ) return -1;

  /* allocate a service handle */
  rc = OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&mysvchp, OCI_HTYPE_SVCCTX, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_SVCCTX)", myerrhp, rc, myenvhp) ) return -1;

  /* set the server attribute in the service context handle*/
  rc = OCIAttrSet((dvoid *)mysvchp, OCI_HTYPE_SVCCTX, (dvoid *)mysrvhp, (ub4) 0, OCI_ATTR_SERVER, myerrhp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SVCCTX,OCI_ATTR_SERVER)", myerrhp, rc, myenvhp) ) return -1;

  /* allocate a user session handle */
  rc = OCIHandleAlloc((dvoid *)myenvhp, (dvoid **)&myusrhp,  OCI_HTYPE_SESSION, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_SESSION)", myerrhp, rc, myenvhp) ) return -1;

  /* set user name attribute in user session handle */
  rc = OCIAttrSet((dvoid *)myusrhp, OCI_HTYPE_SESSION, (dvoid *)db_user_name, strlen(db_user_name), OCI_ATTR_USERNAME, myerrhp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SESSION,OCI_ATTR_USERNAME)", myerrhp, rc, myenvhp) ) return -1;

  /* set password attribute in user session handle */
  rc = OCIAttrSet((dvoid *)myusrhp, OCI_HTYPE_SESSION, (dvoid *)db_user_password, strlen(db_user_password), OCI_ATTR_PASSWORD, myerrhp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SESSION,OCI_ATTR_PASSWORD)", myerrhp, rc, myenvhp) ) return -1;

  rc = OCISessionBegin(mysvchp, myerrhp, myusrhp, OCI_CRED_RDBMS, OCI_DEFAULT);
  if( check_oci_error("OCISessionBegin()", myerrhp, rc, myenvhp) ) return -1;

  /* set the user session attribute in the service context handle*/
  rc = OCIAttrSet( (dvoid *)mysvchp, OCI_HTYPE_SVCCTX, (dvoid *)myusrhp, (ub4) 0, OCI_ATTR_SESSION, myerrhp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SVCCTX,OCI_ATTR_SESSION)", myerrhp, rc, myenvhp) ) return -1;

  cout << endl << "Initialization done." << endl;

  //----- REGISTER TYPE INFORMATION ------------------------------------------------------

  // This section can be invoked once per session to minimize server roundtrips.

  char    *type_owner_name = "SYS";               
  char    *type_name       = "ODCINUMBERLIST";
  OCIType *type_tdo        = NULL;

  rc= OCITypeByName(
        myenvhp, myerrhp, mysvchp, 
        (CONST text *)type_owner_name, strlen(type_owner_name),
        (CONST text *) type_name, strlen(type_name),
        NULL, 0,
        OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, 
        &type_tdo
      );
  if( check_oci_error("OCITypeByName()", myerrhp, rc, myenvhp) ) return -1;

  //----- PREPARE PARAMETER INSTANCE ---------------------------------------------

  OCIArray *array_param = NULL;

  rc = OCIObjectNew(
         myenvhp, myerrhp, mysvchp, 
         OCI_TYPECODE_VARRAY, 
         type_tdo, NULL, OCI_DURATION_SESSION, TRUE,
         (void**) &array_param
       );
  if( check_oci_error("OCITypeByName()", myerrhp, rc, myenvhp) ) return -1;

  //----- FILL PARAMETER ---------------------------------------------------------

  OCINumber num_val;
  int       int_val;

  for(int i = 1; i <= 3; i++) {
    int_val = i*10;

    rc = OCINumberFromInt(myerrhp, &int_val, sizeof(int_val), OCI_NUMBER_SIGNED, &num_val);
    if( check_oci_error("OCINumberFromInt()", myerrhp, rc, myenvhp) ) return -1;

    rc = OCICollAppend(myenvhp, myerrhp, &num_val, NULL, array_param);
    if( check_oci_error("OCICollAppend()", myerrhp, rc, myenvhp) ) return -1;
  }


  //----- BIND PARAMETER VALUE AND EXECUTE STATEMENT ------------------------------

  OCIStmt   *mystmthp   = NULL;
  OCIDefine *col1defp   = NULL;
  double    col1value;  
  OCIBind   *bndp       = NULL;

  char      *query_text = "select * from "
                          " (select level as col from dual connect by level < 100)"
                          "where "
                          "  col in (select column_value from table(:key_list))";

  rc = OCIHandleAlloc(myenvhp, (void **)&mystmthp, OCI_HTYPE_STMT, 0, NULL); 
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_STMT)", myerrhp, rc, myenvhp) ) return -1;

  rc = OCIStmtPrepare( 
         mystmthp, myerrhp, 
         (const OraText *)query_text, strlen(query_text), 
         OCI_NTV_SYNTAX, OCI_DEFAULT
       );
  if( check_oci_error("OCIStmtPrepare()", myerrhp, rc, myenvhp) ) return -1;

  // result column
  rc =  OCIDefineByPos(mystmthp, &col1defp, myerrhp, 1, &col1value, sizeof(col1value), SQLT_BDOUBLE, NULL, NULL, NULL, OCI_DEFAULT);
  if( check_oci_error("OCIDefineByPos()", myerrhp, rc, myenvhp) ) return -1;

  // parameter collection
  rc = OCIBindByName(
         mystmthp, &bndp, myerrhp,
         (text *)":key_list", strlen(":key_list"), 
         NULL, 0,
         SQLT_NTY, NULL, 0, 0, 0, 0,
         OCI_DEFAULT
       );
  if( check_oci_error("OCIBindByName()", myerrhp, rc, myenvhp) ) return -1;

  rc = OCIBindObject(
         bndp, myerrhp, 
         type_tdo, (dvoid **) &array_param, 
         NULL, NULL, NULL
       );
  if( check_oci_error("OCIBindByName()", myerrhp, rc, myenvhp) ) return -1;

  // execute and fetch
  rc = OCIStmtExecute(mysvchp, mystmthp, myerrhp, 0, 0, NULL, NULL, OCI_DEFAULT);
  if( check_oci_error("OCIBindByName()", myerrhp, rc, myenvhp) ) return -1;

  rc = OCIStmtFetch2(mystmthp, myerrhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);

  while(rc != OCI_NO_DATA) {
    if( check_oci_error("OCIStmtFetch2()", myerrhp, rc, myenvhp) ) return -1;
    cout << "value: " << col1value << endl;
    rc = OCIStmtFetch2(mystmthp, myerrhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);
  }

  // free collection object parameter
  rc = OCIObjectFree(myenvhp, myerrhp, array_param, OCI_OBJECTFREE_FORCE);
  if( check_oci_error("OCIObjectFree()", myerrhp, rc, myenvhp) ) return -1;

  cout << endl << "Main test done." << endl;

  //------- FINALIZATION -----------------------------------------------------------
  rc= OCISessionEnd(mysvchp, myerrhp, myusrhp, OCI_DEFAULT);
  if( check_oci_error("OCISessionEnd()", myerrhp, rc, myenvhp) ) return -1;

  rc = OCIServerDetach(mysrvhp, myerrhp, OCI_DEFAULT);
  if( check_oci_error("OCIServerDetach()", myerrhp, rc, myenvhp) ) return -1;

  OCIHandleFree(myenvhp, OCI_HTYPE_ENV);

  cout << endl << "Finalization done." << endl;

  return 0;
}

// helper error checking procedure to shorten main code, returns true if critical error detected
// and prints out error information
bool check_oci_error(char *error_point, OCIError *errhp, sword status, OCIEnv *envhp) { 

  text errbuf[1024];
  sb4  errcode;
  bool ret_code = true;

  switch (status) { 
    case OCI_SUCCESS:
        ret_code = false;
      break;
    case OCI_SUCCESS_WITH_INFO:
        OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
        cout << error_point << " Error: OCI_SUCCESS_WITH_INFO; Info: " << errbuf << endl;
        ret_code = (errcode == 436 || errcode == 437 || errcode == 438 || errcode == 439);
      break;
    case OCI_NEED_DATA:
        cout << error_point << " Error: OCI_NEED_DATA"<< endl;
      break;
    case OCI_NO_DATA:
        cout << error_point << " Error: OCI_NO_DATA"<< endl;
      break;
    case OCI_ERROR:
        OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
        cout << error_point << " Error: " << errbuf << endl;
      break;
    case OCI_INVALID_HANDLE:
        cout << error_point << " Error: OCI_INVALID_HANDLE" << endl;
      break;
    case OCI_STILL_EXECUTING:
        cout << error_point << " Error: OCI_STILL_EXECUTE"<< endl;
      break;
    case OCI_CONTINUE:
        cout << error_point << " Error: OCI_CONTINUE" << endl;
      break;
    default:
        cout << error_point << " Error: UNKNOWN(" << status << ")" << endl;
      break;
  }

  if( ret_code && (envhp != NULL) ) OCIHandleFree(envhp, OCI_HTYPE_ENV);

  return ret_code;

}

    추신 당신은 Oracle 설명서 및 예제 코드에서 정보를 얻을 수 있습니다.

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

    2.이것은 확실히 가능하며 PL / SQL을 사용할 필요가 없습니다. 당신은 당신이 사용할 수있는 데이터베이스 내의 객체를 생성하는거야 최초의 필요성을 제시 한대로 번호를 전달하는 것을 가정 :

    이것은 확실히 가능하며 PL / SQL을 사용할 필요가 없습니다. 당신은 당신이 사용할 수있는 데이터베이스 내의 객체를 생성하는거야 최초의 필요성을 제시 한대로 번호를 전달하는 것을 가정 :

    create or replace type t_num_array as table of number;

    그런 다음 다음과 같이 테이블을 사용하여 테이블을 조회 할 수 있습니다 :

    select *
  from my_table
 where id in (select * from table(t_num_array(1,2,3)) )

    당신은 여전히 ​​같은 문제를 남겨; 당신은 어떻게 문에 변수를 알 수없는 수를 결합 하는가? 하지만 당신은 지금 넣어에 바인딩 구조를 가지고있다.

    이반은 문서 미안하지만 난 간단한 예제 코드를 해요 그래서 혼란 조금 C의 내 지식 ++ 형편없는입니다 있습니다 바로 것을 확실히합니다. 하지만 가치가 독서보다 더 많은 것 몇 가지가있다. 객체 관계형 데이터 유형에 OCI 프로그래머 가이드의 12 장. 아마 개체 유형 번역기 유틸리티에 대해 알고 도움이 될 것입니다 :

    many_types 클래스의 예 8-12 (MY_TABLE의 선언)는 벡터 로 선언 할 수 있다는 것을 의미한다.

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

    3.대신 동적 IN 절에서 사용하는 SQL 문을 구축, 당신의 IN 절에서 원하는 값을 삽입하는 글로벌 임시 테이블을 사용해보십시오. 이 작업을 수행하려면, 당신은 당신의 코드 블록으로 항목에 테이블을 절단 "에 대한 행을 보존 커밋"로 확인 테이블이 선언되어 있는지 확인해야합니다.

    대신 동적 IN 절에서 사용하는 SQL 문을 구축, 당신의 IN 절에서 원하는 값을 삽입하는 글로벌 임시 테이블을 사용해보십시오. 이 작업을 수행하려면, 당신은 당신의 코드 블록으로 항목에 테이블을 절단 "에 대한 행을 보존 커밋"로 확인 테이블이 선언되어 있는지 확인해야합니다.

    start database transaction;

truncate temporary_table;

for each value in array
    insert into temporary_table;
end for each

open cursor 'select * from mytable where id in (select id from temporary_table)';

end database transaction;

    4. from https://stackoverflow.com/questions/18603281/oracle-oci-bind-variables-and-queries-like-id-in-1-2-3 by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 데이터베이스 데이터와의 TableView를 채우기 위해  (0) 2020.05.25
[SQL] 데이터베이스 MySQL을 PDO 연결 [중복]  (0) 2020.05.25
[SQL] ORA-22905 - SELECT 문을 사용하여 테이블 유형을 쿼리 할 때  (0) 2020.05.25
[SQL] 어떻게 SQL 쿼리를 속도를? 인덱스?  (0) 2020.05.25
[SQL] 오라클에서 각 그룹에 대한 최신 행을 선택  (0) 2020.05.25
,

티스토리툴바