Oracle OCI, переменные связывания и запросы, такие как ID IN (1, 2, 3)

Сжатая версия:

Я ищу адаптацию C ++ OCI для следующей техники Java, где код может связывать массив чисел (размер массива может варьироваться) в не PL / SQL SELECT заявление, а затем использовать полученный массив в WHERE ID IN (...) проверка стиля

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

Оригинальный вопрос:

У нас есть приложение C ++, которое общается с Oracle через OCI. Мы пытаемся исправить старый код, который генерирует SQL-запросы, путем объединения текста; вместо этого мы хотим максимально использовать переменные связывания. Пришел один конкретный случай, для которого у нас нет хорошего решения.

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

Где (1, 4, 10, 30, 93) часть исходит от vector<int> или некоторый другой контейнер данных гибкого размера. Если бы мы знали, что всегда будет пять значений, мы могли бы сделать:

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

Но это может быть одна запись, или десять, или, может быть, даже ноль. Очевидно, что если мы строим запрос в виде строки, мы можем просто добавить столько чисел, сколько нам нужно, но цель состоит в том, чтобы избежать этого, если это возможно, и придерживаться только привязки переменных.

Есть ли хороший способ сделать это? Например, в OCI я могу связать массив и затем выбрать его из него?

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

куда :1 такое массив OCI? (Вероятно, синтаксис будет отличаться.) У кого-нибудь есть опыт работы с этим? Пример кода был бы находкой, поскольку я, как правило, борюсь с написанием необработанного OCI. Спасибо 🙂

РЕДАКТИРОВАТЬ: Я хотел бы сделать лучше, чем связывание в строке, которая анализируется процедурой PL / SQL, если это вообще возможно. Я уверен, что во многих случаях мы преодолеем ограничение в 4000 символов, и я также чувствую, что это просто обмен одним видом манипуляции со строками, который мне удобен, на другой, которым я не являюсь (и я не могу отлаживать так же легко). Если возможно, я бы хотел связать массив значений (или некоторую форму набора данных) в одну стандартную инструкцию SQL.

РЕДАКТИРОВАТЬ 2: Некоторые исследования обнаружили следующую ссылку, которая, кажется, делает то, что я хочу, но на Java: http://rafudb.blogspot.com/2011/10/variable-inlist.html Кто-нибудь знает, как адаптировать этот подход к C ++ OCI?

11

Решение

Этот пример демонстрирует подход с использованием типа коллекции, определенной в базе данных, для передачи списка параметров.
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;
}

Ты можешь использовать различные типы ODCIxxxList передать список чисел, дат или строк в Oracle через OCI или даже определить свой собственный тип в БД.

Пример скомпилирован с Visual Studio 10 Express и эта версия библиотек OCI.
Проверено на Oracle 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 и этот пример кода.

12

Другие решения

Это, безусловно, возможно, и нет необходимости использовать 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 ++ являются полезными, поэтому я извиняюсь, но мне не хватает примера кода. Хотя есть несколько вещей, которые стоит прочитать. Глава 12 Руководства программистов OCI по Объектные реляционные типы данных. Вероятно, было бы полезно узнать о Утилита Переводчика Типа Объекта, который:

используется для отображения типов объектов базы данных, типов больших объектов и именованных типов коллекций в объявления классов C ++

Пример 8-12 (объявление my_table) в many_types класс будет означать, что вы можете объявить его как vector<int>,

0

Вместо того чтобы динамически создавать оператор SQL для использования в предложении IN, попробуйте использовать глобальную временную таблицу, чтобы вставить значения, которые вы хотите в предложении IN. Чтобы это работало, вам нужно убедиться, что ваша таблица объявлена ​​как «on commit preserve row» и урезать вашу таблицу при входе в ваш блок кода.

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;
-1
По вопросам рекламы [email protected]