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