반응형
Oracle OCI, 바인딩 변수 및 IDIN(1, 2, 3)과 같은 쿼리
간결한 버전:
코드가 숫자 배열을 비PL/SQL로 바인딩할 수 있는 다음 Java 기술의 C++ OCI 적용을 찾고 있습니다.SELECT문장을 작성한 다음 결과 배열을 a에서 사용합니다.WHERE 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)일부는 a에서 유래합니다.vector<int>또는 다른 유연한 크기의 데이터 컨테이너.항상 5가지 가치가 될 것이라는 것을 알았다면 다음과 같은 일을 할 수 있었습니다.
SELECT * FROM MyTable WHERE ID IN (:1, :2, :3, :4, :5)
하지만 하나의 출품작일 수도 있고, 10개일 수도 있고, 심지어는 0개일 수도 있습니다.물론 쿼리를 문자열로 작성하는 경우 필요한 만큼 숫자를 추가할 수 있지만, 가능하면 이를 피하고 바인딩 변수만 고수하는 것이 목표입니다.
이것을 달성할 수 있는 좋은 방법이 있습니까?예를 들어 OCI에서는 배열을 바인딩한 다음 하위 선택할 수 있습니까?
SELECT * FROM MyTable WHERE ID IN (SELECT * FROM :1)
어디에:1OCI 배열입니까? (아마도 구문이 다를 것입니다.)이것에 대한 경험이 있는 사람이 있습니까?샘플 코드는 제가 raw OCI를 쓰는 데 어려움을 겪는 경향이 있기 때문에 신의 선물이 될 것입니다.감사합니다 :)
편집: 가능하다면 PL/SQL 프로시저로 파싱된 문자열로 바인딩하는 것보다 더 잘하고 싶습니다.저는 우리가 많은 경우에 4000자 제한을 날려버릴 것이라고 확신하며, 또한 그것은 단지 제가 편한 한 종류의 문자열 조작을 제가 아닌 다른 종류와 교환하는 것(그리고 그렇게 쉽게 디버깅할 수 없는 것)이라고 생각합니다.가능하면 값 배열(또는 데이터셋의 일부 형태)을 하나의 표준 SQL 문으로 묶고 싶습니다.
편집 2: 어떤 조사 결과 다음 링크가 나타났는데, 이 링크는 제가 원하는 대로 되고 있는 것으로 보이지만 자바에서는 다음과 같습니다. http://rafudb.blogspot.com/2011/10/variable-inlist.html 이 접근 방식을 C++ OCI에 어떻게 적용하는지 아는 사람이 있습니까?
이 예에서는 데이터베이스에 정의된 수집 유형을 사용하여 매개 변수 목록을 전달하는 방법을 보여 줍니다.
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;
}
다양한 ODCI xxxList 유형을 사용하여 OCI를 통해 숫자, 날짜 또는 문자열 목록을 Oracle로 전달하거나 DB에서 자신의 유형을 정의할 수도 있습니다.
Visual Studio 10 Express와 이 버전의 OCI 라이브러리로 컴파일된 예제.Oracle 11.2.0.3.0에 대해 테스트되었습니다.
갱신하다
아래는 동일한 작업을 수행하지만 일반적인 COCIxx 기능을 사용하는 응용 프로그램의 예시입니다.
//
// 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 설명서와 이 예제 코드에서 정보를 얻을 수 있습니다.
이것은 확실히 가능하며 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장개체 유형 변환기 유틸리티에 대해 알아두면 유용할 수 있습니다.
데이터베이스 개체 유형, LOB 유형 및 명명된 컬렉션 유형을 C++ 클래스 선언에 매핑하는 데 사용됩니다.
( 8-12 (의)my_tablemany_types다로 할 수 합니다.vector<int>.
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;
언급URL : https://stackoverflow.com/questions/18603281/oracle-oci-bind-variables-and-queries-like-id-in-1-2-3
반응형
'programing' 카테고리의 다른 글
| 웹 응용 프로그램을 상대 파일 시스템 경로에 게시 (0) | 2023.10.08 |
|---|---|
| "[네이티브 코드]"가 무슨 뜻입니까? (0) | 2023.10.08 |
| C++ 전처리기가 C 전처리기와 동일합니까? (0) | 2023.10.08 |
| java.sql.SQL 예외:Io 예외:소켓 읽기 시간 초과 대 닫힌 연결 (0) | 2023.10.08 |
| DBCP 연결 풀 로그인 시간 초과 (0) | 2023.10.08 |