Oracle OCI编程流程
这是在做OCI开发时写的一些测试的代码，基本流程很简单如下：>>创建环境句柄 OCIEnvCreate(&envhp,OCI_DEFAULT,(dvoid *)0, 0,0,0,0,(dvoid **)0);
>>申请错误句柄 OCIHandleAlloc((dvoid *) envhp,(dvoid **)&errhp, OCI_HTYPE_ERROR,(size_t)0,(dvoid **)0);
>>申请服务器句柄 OCIHandleAlloc((dvoid *)envhp,(dvoid **)&srvhp, OCI_HTYPE_SERVER,(size_t)0,(dvoid **)0);
>>申请服务环境句柄 OCIHandleAlloc((dvoid *)envhp,(dvoid **)&svchp, OCI_HTYPE_SVCCTX,(size_t)0,(dvoid **)0);
>>设置服务环境的服务器属性 OCIAttrSet((dvoid *)svchp,OCI_HTYPE_SVCCTX, (dvoid *)srvhp,(ub4)0,OCI_ATTR_SERVER,(OCIError *)errhp);
>>连接数据库，建立会话 status = OCILogon(envhp,errhp,&svchp,(const OraText*)user,(ub4)strlen((char *)user), (text *)pass_word,(ub4)strlen((char *)pass_word),(text *)servername,(ub4)strlen((char *)servername));
>>申请语句句柄 OCIHandleAlloc((dvoid *)envhp,(dvoid **)&stmthp, OCI_HTYPE_STMT,(size_t)0,(dvoid **)0);
>>准备sql语句 OCIStmtPrepare(stmthp,errhp,(const OraText*)sqlstat, (ub4)strlen((char *) sqlstat),OCI_NTV_SYNTAX,OCI_DEFAULT));
>>定义输出变量 OCIDefineByPos(stmthp,&defhp,errhp,1,&sum, sizeof(sum),SQLT_INT,(sb2 *)0,0,(ub2 *)0,OCI_DEFAULT) );
>>执行sql语句 OCIStmtExecute(svchp,stmthp,errhp,(ub4)0, (ub4)0,(const OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
>>取结果OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT);
>>断开连接 OCILogoff(svchp,errhp);
几个需要注意的地方：
1.输出变量（存储过程参数除外）用OCIDefineByPos
2.在调用存储过程时要用OCIBindByPos绑定所有参数
3.Oracle存储过程返回结果集不像mysql和sqlserver，需要用cursor OUT出来，在程序中接收cursor让很多人很迷惑，cursor需要用OCIStmt来绑定，然后和正常STMT一样来读取结果
如果还有不懂的地方，下面是示例代码：
#include "OCI.h" #include <conio.h>#include <stdio.h>#include <string>#include <Windows.h>using namespace std;const char user = "alien"; const char pass_word = "alienchang"; const char servername = "orcl"; void read();void procedure();typedef sword (*FuncOCIEnvCreate)(OCIEnv**,ub4, void*, void* (*malocfp)(void*, size_t),void* (*ralocfp)(void*, void*,size_t),void (*mfreefp)(void*, void*),size_t, void**);void error_proc(dvoid *errhp, sword status) { text errbuf; sb4 errcode; switch (status) { case OCI_SUCCESS: // printf("OCI_success\n"); break; case OCI_SUCCESS_WITH_INFO: printf("OCI error: OCI_success_with_info\n"); break; case OCI_NEED_DATA: printf("OCI error: OCI_need_data\n"); break; case OCI_NO_DATA: printf("OCI error: OCI_no_data\n"); break; case OCI_ERROR: (void)OCIErrorGet((dvoid *)errhp,(ub4)1,NULL,&errcode, errbuf,(ub4)sizeof(errbuf),OCI_HTYPE_ERROR); printf("错误号：%d\n错误信息：%s\n",errcode,errbuf); break; case OCI_INVALID_HANDLE: printf("OCI error: OCI_invalid_handle\n"); break; case OCI_STILL_EXECUTING: printf("OCI error: OCI_still_executing\n"); break; default: break; } } void main () {procedure();return ; } void procedure(){OCIEnv *envhp = NULL;OCIError *errhp = NULL;OCIServer *srvhp = NULL;OCISvcCtx *svchp = NULL;OCIStmt *stmthp = NULL;OCIStmt *curstmthp = NULL;OCIBind *bndlp;OCIDefine *defhp;char account = "D5SJ53WE06";int number = 0;sword status = 0;ub2 type = 0;char* sqlstat = "begin say(:v_text, :v_num); end;";//"begin pak_rc.get(:v_account, :v_cursor); end;"; OCIEnvCreate(&envhp, OCI_DEFAULT, 0, 0, 0, 0, 0, (dvoid**)0);OCIHandleAlloc(envhp, (dvoid**)&errhp, OCI_HTYPE_ERROR, 0, (dvoid**)0);error_proc(errhp, OCIHandleAlloc((dvoid*)envhp, (dvoid**)&srvhp,OCI_HTYPE_SERVER, 0, (dvoid**)0));error_proc(errhp, OCIHandleAlloc((dvoid*)envhp, (dvoid**)&svchp,OCI_HTYPE_SVCCTX, 0, (dvoid**)0));status = OCILogon(envhp, errhp, &svchp, (const OraText*)user, (ub4)strlen(user),(const OraText*)pass_word, (ub4)strlen(pass_word), (const OraText*)servername, (ub4)strlen(servername));if ( OCI_SUCCESS == status )printf("Connect Successsfully!\n");else{printf("Connect Failed!");error_proc(errhp, status);return;}error_proc(errhp, OCIHandleAlloc((dvoid*)envhp, (dvoid**)&stmthp,OCI_HTYPE_STMT, 0, (dvoid**)0));error_proc(errhp, OCIHandleAlloc((dvoid*)envhp, (dvoid**)&curstmthp,OCI_HTYPE_STMT, 0, (dvoid**)0));error_proc(errhp, OCIStmtPrepare(stmthp, errhp, (const OraText*)sqlstat,(ub4)strlen(sqlstat), OCI_NTV_SYNTAX, OCI_DEFAULT));/*error_proc(errhp, OCIDefineByPos(stmthp, &defhp, errhp, 2, &curstmthp,0, SQLT_RSET, (ub2*)0, 0, (ub2*)0, OCI_DEFAULT));char test;*/int pnum = 0;OCIParam *param;/*error_proc(errhp, OCIAttrGet ((dvoid *)stmthp, (ub4)OCI_HTYPE_STMT,(dvoid *)&pnum, (ub4 *)0, (ub4)OCI_ATTR_PARAM_COUNT,errhp));*//*error_proc(errhp, OCIParamGet (stmthp, OCI_HTYPE_STMT, errhp,(dvoid **)¶m, 1));error_proc(errhp, OCIAttrGet (param, OCI_DTYPE_PARAM,(dvoid *)&type, (ub4 *)0, OCI_ATTR_DATA_TYPE, errhp));*/printf("%d type: %d\n", pnum, type);printf("%d: %d %d: %d", account, &account, &curstmthp, curstmthp);error_proc(errhp, OCIBindByPos(stmthp, &bndlp, errhp, 1, &account, strlen(account) + 1,SQLT_STR, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));error_proc(errhp, OCIBindByPos(stmthp, &bndlp, errhp, 2, &number, sizeof(number),SQLT_INT, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));/*error_proc(errhp, OCIBindByPos(stmthp, &bndlp, errhp, 2, &curstmthp, sizeof(curstmthp),SQLT_RSET, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));*//*error_proc(errhp, OCIDefineByPos(stmthp, &defhp, errhp, 1,(dvoid *)test,(sb4)sizeof(test), SQLT_STR, (ub2*)0, 0, (ub2*)0, OCI_DEFAULT));*/if ( (status = OCIStmtExecute(svchp,stmthp,errhp,(ub4)1, (ub4)0,(const OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_COMMIT_ON_SUCCESS)) && status != OCI_SUCCESS_WITH_INFO ){error_proc(errhp, status);printf("-----ora_search,error in OCIStmtexecute-----\n"); return;}printf("string: %s; number: %d\n", account, number);getch(); int num = 0;/*error_proc(errhp, OCIAttrGet ((dvoid *)curstmthp, (ub4)OCI_HTYPE_STMT,(dvoid *)&num, (ub4 *)0, (ub4)OCI_ATTR_PARAM_COUNT,errhp));printf("%d\n", num);char c_account, pwd;error_proc(errhp, OCIDefineByPos(curstmthp, &defhp, errhp,1, c_account, sizeof(account), SQLT_STR, (ub2)0, 0, 0,OCI_DEFAULT)); error_proc(errhp, OCIDefineByPos(curstmthp, &defhp, errhp,2, pwd, sizeof(pwd), SQLT_STR, (ub2*)0, 0, 0,OCI_DEFAULT)); error_proc(errhp,OCIStmtFetch(curstmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT));do{printf("status: %d Account:%s PWD:%s\n", status, c_account, pwd);status = OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT);}while( OCI_SUCCESS == status );*///断开连接 error_proc(errhp,OCILogoff(svchp,errhp)); OCIHandleFree(errhp, OCI_HTYPE_ERROR); }void read(){OCIEnv *envhp = NULL; //环境句柄 OCIError *errhp = NULL; //错误句柄 OCIServer *srvhp = NULL; //服务器句柄 OCISvcCtx *svchp = NULL; //服务环境句柄 OCIStmt *stmthp = NULL; //语句句柄 OCIBind *bnd1p = (OCIBind *)0; //结合句柄 OCIDefine *defhp; //定义句柄 sword status; int sum; //命中记录数 char account;char* sqlstat = "select id,account from hx_account"; int li_ret; li_ret = 0; // char *summary_element=" rec_ctrl_id,title,first_author,book_search_no,publisher"; //创建环境句柄 OCIEnvCreate(&envhp,OCI_DEFAULT,(dvoid *)0, 0,0,0,0,(dvoid **)0);//申请错误句柄 OCIHandleAlloc((dvoid *) envhp,(dvoid **)&errhp, OCI_HTYPE_ERROR,(size_t)0,(dvoid **)0); //申请服务器句柄 error_proc(errhp,OCIHandleAlloc((dvoid *)envhp,(dvoid **)&srvhp, OCI_HTYPE_SERVER,(size_t)0,(dvoid **)0)); //申请服务环境句柄 error_proc(errhp,OCIHandleAlloc((dvoid *)envhp,(dvoid **)&svchp, OCI_HTYPE_SVCCTX,(size_t)0,(dvoid **)0)); //设置服务环境的服务器属性 error_proc(errhp,OCIAttrSet((dvoid *)svchp,OCI_HTYPE_SVCCTX, (dvoid *)srvhp,(ub4)0,OCI_ATTR_SERVER,(OCIError *)errhp)); //连接数据库，建立会话 status = OCILogon(envhp,errhp,&svchp,(const OraText*)user,(ub4)strlen((char *)user), (text *)pass_word,(ub4)strlen((char *)pass_word),(text *)servername,(ub4)strlen((char *)servername)); if (status==OCI_SUCCESS) printf("成功连接melinets数据库，已为%s用户建立会话.\n",user); else { printf("连接失败connect fail!\n"); printf("-----ora_search,error in OCIlogon-----\n"); error_proc(errhp,status); return; } //申请语句句柄 error_proc(errhp,OCIHandleAlloc((dvoid *)envhp,(dvoid **)&stmthp, OCI_HTYPE_STMT,(size_t)0,(dvoid **)0)); //准备sql语句 error_proc(errhp,OCIStmtPrepare(stmthp,errhp,(const OraText*)sqlstat, (ub4)strlen((char *) sqlstat),OCI_NTV_SYNTAX,OCI_DEFAULT)); //-------定义输出变量 error_proc(errhp, OCIDefineByPos(stmthp,&defhp,errhp,1,&sum, sizeof(sum),SQLT_INT,(sb2 *)0,0,(ub2 *)0,OCI_DEFAULT) ); error_proc(errhp, OCIDefineByPos(stmthp,&defhp,errhp,2,&account, sizeof(account),SQLT_STR,(sb2 *)0,0,(ub2 *)0,OCI_DEFAULT) ); // ora_search(&li_ret); // printf("li_ret is %d\n",li_ret); //执行sql语句 if ((status=OCIStmtExecute(svchp,stmthp,errhp,(ub4)0, (ub4)0,(const OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT)) && status != OCI_SUCCESS_WITH_INFO) { //失败则退出 error_proc(errhp,status); printf("-----ora_search,error in OCIStmtexecute-----\n"); return; } getch(); error_proc(errhp,OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT)); do{string strValue;strValue = account;printf("ID:%dAccount:%s\n", sum, strValue.c_str());status = OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT);}while( OCI_SUCCESS == status );//断开连接 error_proc(errhp,OCILogoff(svchp,errhp)); }
页:
[1]