Oracle OCI，绑定变量和查询（如ID IN（1，2，3） [英] Oracle OCI, bind variables, and queries like ID IN (1, 2, 3)

问题描述

Succinct版本：

我正在寻找以下Java技术的C ++ OCI调整，其中代码能够绑定数组数组（数组大小可以变化）转换为非PL / SQL SELECT 语句，然后将结果数组用于 WHERE ID IN ...）样式检查。

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

原始问题：

我们有一个C ++应用程序，通过OCI与Oracle通信。我们正在尝试修复旧的代码，通过连接文本生成SQL查询;而是我们希望尽可能多地使用绑定变量。一个特殊的情况是，我们没有一个好的解决方案。

  SELECT * FROM MyTable WHERE ID IN ，4，10，30，93）
  

，10,30,93）部分来自向量< 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个整数（ test ）然后使用 IN（...）子句中的列表过滤整数。

  #includestdafx.h
 #include< iostream> 
 #include< occi.h> 
 
使用命名空间oracle :: occi; 
 using namespace std; 
 
 //作为参数列表传递的向量类型
 typedef vector< Number> ValueList; 
 
 int _tmain（int argc，_TCHAR * argv []）
 {
环境* env; 
 Connection * con; 
 
 //注意，Environment必须在OBJECT模式下初始化
 //以使用集合映射功能。 
 env = Environment :: createEnvironment（Environment :: OBJECT）; 
 
 con = env-> createConnection（test_user，test_password，ORACLE_TNS_NAME）; 
 
 try {
 
语句* stmt = con> createStatement（
select * from
 by level <= 100）
其中
col（从表（：key_list）中选择column_value）
）; 
 
 cout<< endl<< endl<< 执行块：< endl 
<< stmt-> getSQL（）<< endl<< endl; 
 
 //创建上面定义的向量trype的实例
 //并用数字填充它。 
 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））; 
 
 //将向量绑定到查询中的参数＃1，并将其视为SYS.ODCINumberList类型。 
 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甚至在DB中定义您自己的类型。 p>

使用Visual Studio 10 Express和此版本的OCI图书馆。
根据Oracle 11.2.0.3.0测试。

更新

下面是使用简单C OCIxxx函数做同样的事情的示例应用程序。

  // 
 // OCI集合参数绑定 - 示例应用程序
 // 
 
 #includestdafx.h
 #include< iostream> 
 #include< oci.h> 
 #include< oro.h> 
 
 using namespace std; 
 
 //连接参数
 const char * db_alias =ORACLE_DB_ALIAS; 
 const char * db_user_name =test_user; 
 const char * db_user_password =test_password; 
 
 //帮助程序错误检查程序缩短主代码，如果检测到严重错误则返回true 
 //并输出错误信息
 bool check_oci_error（char * error_point，OCIError * errhp ，sword status，OCIEnv * envhp）; 
 
 int _tmain（int argc，_TCHAR * argv []）{
 
 // ----- CONNECTION INITIALIZATION PART ----------- ------------------------------------------- 
 $ b $剑剑
 OCIEnv * myenvhp; / *环境句柄* / 
 OCIServer * mysrvhp; / *服务器句柄* / 
 OCIError * myerrhp; / *错误句柄* / 
 OCISession * myusrhp; / *用户会话句柄* / 
 OCISvcCtx * mysvchp; / *服务句柄* / 
 
 / *初始化模式为线程和对象环境* / 
 / *注意：OCI_OBJECT必须存在才能使用对象/集合类型* 
 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; 
} 
 
 / *分配服务器句柄* / 
 rc = OCIHandleAlloc（（dvoid *）myenvhp，（dvoid **）& mysrvhp，OCI_HTYPE_SERVER，0， **）0）; 
 if（check_oci_error（OCIHandleAlloc（OCI_HTYPE_SERVER），NULL，rc，myenvhp））return -1; 
 
 / *分配错误句柄* / 
 rc = OCIHandleAlloc（（dvoid *）myenvhp，（dvoid **）& myerrhp，OCI_HTYPE_ERROR，0，（dvoid **） ; 
 if（check_oci_error（OCIHandleAlloc（OCI_HTYPE_ERROR），NULL，rc，myenvhp））return -1; 
 
 / *创建服务器上下文* / 
 rc = OCIServerAttach（mysrvhp，myerrhp，（text *）db_alias，strlen（db_alias），OCI_DEFAULT）; 
 if（check_oci_error（OCIServerAttach（），myerrhp，rc，myenvhp））return -1; 
 
 / *分配服务句柄* / 
 rc = OCIHandleAlloc（（dvoid *）myenvhp，（dvoid **）& mysvchp，OCI_HTYPE_SVCCTX，0，（dvoid **） ; 
 if（check_oci_error（OCIHandleAlloc（OCI_HTYPE_SVCCTX），myerrhp，rc，myenvhp））return -1; 
 
 / *设置服务上下文句柄中的服务器属性* / 
 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; 
 
 / *分配用户会话句柄* / 
 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; 
 
 / *在用户会话句柄中设置用户名属性* / 
 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; 
 
 / *在用户会话句柄中设置密码属性* / 
 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; 
 
 / *设置服务上下文句柄中的用户会话属性* / 
 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<< 初始化完成。 << endl; 
 
 // -----注册类型信息-------------------------------- ---------------------- 
 
 //此部分可以在每个会话中调用一次，以最小化服务器往返。 
 
 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），
 （type_name），
 NULL，0，
 OCI_DURATION_SESSION，OCI_TYPEGET_HEADER，
& type_tdo 
）; 
 if（check_oci_error（OCITypeByName（），myerrhp，rc，myenvhp））return -1; 
 
 // -----准备参数实例-------------------------------- ------------- 
 
 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; 
} 
 
 
 // -----参数值和执行语句-------------------- ---------- 
 
 OCIStmt * mystmthp = NULL; 
 OCIDefine * col1defp = NULL; 
 double col1value; 
 OCIBind * bndp = NULL; 
 
 char * query_text =select * from
（选择级别作为col，从双重连接按级别<100）
其中
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; 
 
 //结果列
 rc = OCIDefineByPos（mystmthp，& col1defp，myerrhp，1，& col1value，sizeof（col1value），SQLT_BDOUBLE，NULL，NULL，OCI_DEFAULT）; 
 if（check_oci_error（OCIDefineByPos（），myerrhp，rc，myenvhp））return -1; 
 
 //参数集合
 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; 
 
 //执行和获取
 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）; 
} 
 
 //自由收集对象参数
 rc = OCIObjectFree（myenvhp，myerrhp，array_param，OCI_OBJECTFREE_FORCE）; 
 if（check_oci_error（OCIObjectFree（），myerrhp，rc，myenvhp））return -1; 
 
 cout<< endl<< 主要测试完成。 << 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<< 完成。 << endl; 
 
 return 0; 
} 
 
 //帮助程序错误检查程序缩短主代码，如果检测到严重错误则返回true 
 //并输出错误信息
 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<< 错误：UNKNOWN（<< status<<）< endl; 
 break; 
} 
 
 if（ret_code&（envhp！= NULL））OCIHandleFree（envhp，OCI_HTYPE_ENV）; 
 
 return ret_code; 
 
} 
  

您可以从Oracle文档和此示例代码中获取信息。

Succinct Version:

I'm looking for a C++ OCI adaptation of the following Java technique, where code is able to bind an array of numbers (the array size can vary) into a non-PL/SQL SELECT statement and then use the resulting array in a WHERE ID IN (...) style check.

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

Original Question:

We have a C++ app which talks to Oracle via OCI. We're trying to fix old code which generates SQL queries by concatenating text; instead we want to use bind variables as much as possible. One particular case has come up that we don't have a good solution for.

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

Where the (1, 4, 10, 30, 93) part comes from a vector<int> or some other flexibly-sized container of data. If we knew it would always be five values, we could do:

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

But it might be one entry, or ten, or maybe even zero. Obviously, if we are building up the query as a string, we can just append as many numbers as we need, but the goal is to avoid that if possible and stick to just bind variables.

Is there a good way to accomplish this? For instance, in OCI, can I bind an array and then sub-select out of it?

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

Where :1 is an OCI array? (Probably the syntax would differ.) Does anyone have experience with this? Sample code would be a godsend as I tend to struggle with writing raw OCI. Thanks :)

EDIT: I'd like to do better than binding in a string which is parsed by a PL/SQL procedure, if at all possible. I am confident that we would blow out the 4000 character limit in many cases, and I also feel like that's just trading one kind of string manipulation that I'm comfortable with, for another kind that I'm not (and I can't debug as easily). If possible I'd like to bind an array of values (or some form of dataset) into one standard SQL statement.

EDIT 2: Some investigation turned up the following link which seems to be doing just what I want, but in Java: http://rafudb.blogspot.com/2011/10/variable-inlist.html Does anyone know how to adapt this approach to C++ OCI?

解决方案

This example demonstrates approach with using collection type, defined in database to pass list of parameters.
SYS.ODCINumberList is standard collection type available for all users. Query, used in sample just select first 100 integers ( test ) and then filter this integers with list in IN(...) clause.

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

You can use various ODCIxxxList types to pass list of numbers, dates or strings to Oracle via OCI or even define your own type in DB.

Example compiled with Visual Studio 10 Express and this version of OCI libraries. Tested against Oracle 11.2.0.3.0 .

Update

Below is example application which does same thing but with plain C OCIxxx functions.

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

}

P.S. You can get info from Oracle documentation and this example code.

这篇关于Oracle OCI，绑定变量和查询（如ID IN（1，2，3）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！