一、环境的配置
1、系统环境：要想使用OCI编程需要安装Oracle的客户端，而这个普通的客户端比较大，方便起见，可以安装即时客户端（Instantclient）作为Oracle的访问客户端。
具体的配置可以参考这里：
http://www.cnblogs.com/ychellboy/archive/2010/04/16/1713884.html
2、执行环境：
windows下的配置可以参考这里：
http://blog.csdn.net/sherlockhua/article/details/4353531
linux下的配置可以参考这里：
http://blog.csdn.net/sherlockhua/article/details/4353531
二、基本理论
1、首先要创建OCI环境，即创建和初始化OCI工作环境，其他的OCI函数需要OCI环境才能执行。
2、分配OCI环境句柄：它定义所有OCI函数的调用环境，是其他句柄的父句柄。( 由OCIEnvInit 或OCIEnvCreate 生成 ) 。
3、错误句柄：作为一些OCI函数的参数，用来记录这些OCI函数操作过程中所产生的错误，当有错误发生时，可用OCIErrorGet()来读取错误句柄中记录的错误信息。
4、服务器环境句柄：定义OCI调用的服务器操作环境，它包含服务器、用户会话和事务三种句柄。
5、服务器句柄：标识数据源，它转换为与服务器的物理连接。
6、用户会话句柄：定义用户角色和权限及OCI调用的执行环境。
7、事务句柄：定义执行SQL操作的事务环境，事务环境中包含用户的会话状态信息。
注意：Bind/Define 句柄在执行具体的SQL语句的时候，被隐含创建并连接到表达句柄（Statement Handle）上，当表达句柄释放时，它们也被隐含释放。所以在执行每一个sql语句时，先分配表达句柄，执行结束后，释放表达句柄，这样做保证不发生由于定位句柄和绑定变量句柄引起的内存泄漏。
三、操作步骤
OCI连接Oracle数据库的步骤比较复杂，除了分配设置各个基本句柄外，还要明确彼此之间的联系，大致流程如下：
1、创建环境句柄： OCIEnvCreate(&envhp, „);
2、创建一个指定环境的错误句柄： OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp,„);
3、创建一个指定环境的服务器句柄： OCIHandleAlloc((dvoid *)envhp, (dvoid **)&servhp,„);
4、建立到数据源的访问路径 ： OCIServerAttach(servhpp, errhpp,„);
5、创建一个指定环境的服务上下文句柄： (void) OCIHandleAlloc((dvoid *)envhpp,„);
6、为指定的句柄及描述符设置特定的属性： (void) OCIAttrSet((dvoid *)svchpp,„);
7、创建一个指定环境的用户连接句柄： (void) OCIHandleAlloc((dvoid *)envhpp,„);
8、为用户连接句柄设置登录名及密码： (void) OCIAttrSet((dvoid *)usrhpp,„);
9、认证用户建立一个会话连接： OCISessionBegin(svchpp, errhpp,„);
10、创建一个句子句柄： OCIHandleAlloc((dvoid *)envhpp,„);
11、准备 SQL 语句： OCIStmtPrepare(stmthpp, errhpp,„);
12、绑定输入变量： OCIBindByPos(stmtp &hBind, errhp,„);
13、绑定输出变量： OCIDefineByPos(stmthpp, &bhp1, errhpp,„);
14、获得 SQL 语句类型： OCIAttrGet ((dvoid *)stmthpp, (ub4)OCI_HTYPE_STMT,„);
15、执行 SQL 语句： OCIStmtExecute(svchpp, stmthpp,„);
17、释放一个会话： OCISessionEnd();
18、删除到数据源的访问 ： OCIServerDetach(servhpp, errhpp, OCI_DEFAULT);
19、释放句柄： OCIHandleFree((dvoid *) stmthpp, OCI_HTYPE_STMT);
四、示例
程序1
//============================================================================
// Name : CExercise.cpp
// Author : Haier
// Version : 0.1
// Copyright : Your copyright notice
// Description : Connect Oracle in C++, Ansi-style
//============================================================================ #include <oci.h>
#include <iostream>
#include <stdio.h>
#include <string>
#include <string.h>
#include <stdlib.h>
#include <ociapr.h>
#include <ocidem.h>
using namespace std; Lda_Def lda;
cda_def cda; dvoid oci_error(void)
{
text msg[600];
sword rv;
//取错误信息
rv=oerhms(&lda,cda.rc,msg,600);
//显示错误码和错误信息
printf("\n\n%.*s",rv,msg);
//显示发生错误的oci函数
printf("processing oci function %s\n",oci_func_tab[cda.fc]);
//关闭光标
if(oclose(&cda))
printf("error closing cursor!\n");
if(ologof(&lda))
printf("error logging off!\n"); exit(1);
} int main()
{
char szDbUser[]="so1@KFCS";
char szPassword[] ="1qaz!QAZ";
char pc_msg[256];
char sql[256] = "update so1.ins_prod_a set state='1' where bill_id='15238075968'"; if(olon( &lda,(OraText*)szDbUser, -1, (OraText*)szPassword, -1, 0))
{
oci_error();
} cout<<"Connect to Oracle DB Successed!"<<endl; if(oopen(&cda,&lda,(OraText*)0,-1,-1,(OraText*)0,-1))
{
oci_error();
} cout<<"Open Curse Successed!"<<endl; if(oparse(&cda,(OraText*)sql,-1,0,2))
{
oci_error();
} cout<<"Parse Sql Successed!"<<endl; if(oexn( &cda, 1, 0 ))
{
oci_error();
} cout<<"exec Sql Successed!"<<endl; if(oparse(&cda,(text*)"commit",-1,0,2))
{
oci_error();
} cout<<"Commit Successed!"<<endl; if(oclose(&cda))
{
printf("error closing cursor!/n");
exit(1);
} //结束事务，退出oracle
if(ologof(&lda))
{
printf("error logging off!/n");
exit(1);
} return 0;
}
运行
Connect to Oracle DB Successed!
Open Curse Successed!
Parse Sql Successed!
exec Sql Successed!
Commit Successed!
程序2
//============================================================================
// Name : CExercise.cpp
// Author : Haier
// Version : 0.1
// Copyright : Your copyright notice
// Description : Connect Oracle in C++ by oci, Ansi-style
//============================================================================ #include <iostream>
#include <stdio.h>
#include <string>
#include <string.h>
#include <stdlib.h>
#include <oci.h>
#include <ociapr.h>
#include <ocidem.h>
using namespace std; Lda_Def lda;
cda_def cda; dvoid oci_error(void)
{
text msg[600];
sword rv;
//取错误信息
rv=oerhms(&lda,cda.rc,msg,600);
//显示错误码和错误信息
printf("\n\n%.*s",rv,msg);
//显示发生错误的oci函数
printf("processing oci function %s\n",oci_func_tab[cda.fc]);
//关闭光标
oclose(&cda);
ologof(&lda);
exit(1);
} sword Login()
{
char username[20];
char password[20] ;
int times=0; do
{
if(++times>3)
{
return 1;
} cout<<"Enter user-name:";
cin>>username;
cout<<"Enter password:";
cin>>password; }while(olon( &lda,(OraText*)username, -1, (OraText*)password, -1, 0)); return 0;
} int main()
{ char bill_id[20];
char password[20];
char sql[256] = "select bill_id,password from so1.ins_prod_a where bill_id='15238075968'"; //登陆oracle
if(Login())
{
oci_error();
} //禁止自动提交
if(ocof(&lda))
{
oci_error();
} //打开curse
if(oopen(&cda,&lda,(OraText*)0,-1,-1,(OraText*)0,-1))
{
oci_error();
} //分析sql
if(oparse(&cda,(OraText*)sql,-1,0,2))
{
oci_error();
} //定义输入
if(odefin(&cda,1,(ub1 *)bill_id,sizeof(bill_id),5,-1,(sb2 *)0,(OraText *)0,0,-1,(ub2 *)0,(ub2 *)0))
{
oci_error();
} if(odefin(&cda,2,(ub1 *)password,sizeof(password),5,-1,(sb2 *)0,(OraText *)0,0,-1,(ub2 *)0,(ub2 *)0))
{
oci_error();
} //执行sql
if(oexn( &cda, 1, 0 ))
{
oci_error();
} //提取结果
if(ofen(&cda,1))
{
oci_error();
} //输出结果
cout<<"\n"<<bill_id<<"\t"<<password<<endl; //关闭curse
if(oclose(&cda))
{
printf("error closing cursor!/n");
exit(1);
} //结束事务，退出oracle
if(ologof(&lda))
{
printf("error logging off!/n");
exit(1);
} return 0;
}
运行
Enter user-name:so1@KFCS
Enter password:1qaz!QAZ 15238075968 W*_6oJ
程序3
//============================================================================
// Name : CExercise.cpp
// Author : Haier
// Version : 0.1
// Copyright : Your copyright notice
// Description : Connect Oracle in C++ by oci, Ansi-style
//============================================================================ #include <iostream>
#include <stdio.h>
#include <stdlib.h>
#include <ctype.h>
#include <string.h>
#include <oci.h>
#include <oratypes.h>
#include <ocidfn.h>
#include <ociapr.h>
#include <ocidem.h>
using namespace std; #define MAX_BINDS 12
#define MAX_ITEM_BUFFER_SIZE 32
#define MAX_SELECT_LIST_SIZE 12
#define MAX_SQL_IDENTIFIER 31
#define PARSE_NO_DELAY 0
#define PARSE_V7_LNG 2
#define RAWWIDTH 18 struct describe
{
sb4 dbsize;
sb2 dbtype;
sb1 buf[MAX_ITEM_BUFFER_SIZE];
sb4 buflen;
sb4 dsize;
sb2 precision;
sb2 scale;
sb2 nullok;
}; struct define
{
ub1 buf[MAX_ITEM_BUFFER_SIZE];
float flt_fuf;
sword int_buf;
sb2 indp;
sb2 col_retlen,col_retcode;
}; Lda_Def lda;
Cda_Def cda;
ub1 hda[256];
text errorMsg[600];
sword stmtLevel;
static text sqlStatement[2048];
static sword sqlFunction;
static sword numwidth = 8;
struct describe desc[MAX_SELECT_LIST_SIZE];
struct define def[MAX_SELECT_LIST_SIZE];
text bindValue[MAX_BINDS][MAX_ITEM_BUFFER_SIZE]; sword connect()
{
text username[20];
text password[20];
sword n; printf("Copyright (c) 1982, 2014, Oracle. All rights reserved.\n");
printf("Connected to Oracle Database:\n\n");
for(n=3; --n>=0; )
{
setbuf(stdout,NULL);
printf("Enter user-name: ");
gets((char*)username);
printf("Enter password: ");
gets((char*)password); if(orlon(&lda,hda,(OraText*)username,-1,(OraText*)password,-1,-1))
{
oerhms(&lda,lda.rc,(OraText*)errorMsg,sizeof(errorMsg));
printf("%sPlease try again.\n",errorMsg);
}
else
return TRUE; } printf("Connect failed.Exiting...\n");
return FALSE;
} sword getSqlStatement()
{
text sqlBuf[1024];
text *cp; while(1)
{
if(1==(++stmtLevel)){
*sqlStatement='\0';
printf("\nSQL>");
}else{
printf("%3d>",stmtLevel);
} fflush(stdin);
gets((char*)sqlBuf);
if(*sqlBuf=='\0'){
continue;
}else
{
if(!strncmp((char*)sqlBuf,"exit",4) || !strncmp((char*)sqlBuf,"quit",4))
{
return 1;
}
} if(stmtLevel>1)
{
strcat((char*)sqlStatement," ");
}
strcat((char*)sqlStatement,(char*)sqlBuf); cp = &sqlStatement[strlen((char*)sqlStatement)-1]; while(isspace(*cp))
{
cp--;
} if(*cp==';')
{
*cp='\0';
break;
} } return 0;
} void exitOracle(sword code)
{
if(oclose(&cda))
{
oerhms(&cda,cda.rc,(OraText*)errorMsg,sizeof(errorMsg));
printf("%s\n",errorMsg);
} if(ologof(&lda))
{
oerhms(&lda,lda.rc,(OraText*)errorMsg,sizeof(errorMsg));
printf("%s\n",errorMsg);
} exit(code);
} void oci_error(Cda_Def *cda)
{
char choice; oerhms(&lda,lda.rc,(OraText*)errorMsg,sizeof(errorMsg));
printf("%s\n",errorMsg); fprintf(stderr,"Do you want to continue ?[y/n]");
fscanf(stdin,"%c",&choice); if(toupper(choice)!='Y')
{
exitOracle(1);
} fputc('\n',stdout);
} sword bind(Cda_Def *cda,text *sqlStatement)
{
sword i,inLiteral,n;
text *cp,*ph; for(i=0,inLiteral=0,cp=sqlStatement; *cp && i<MAX_BINDS; cp++)
{
/* if(*cp=='\'')
{
inLiteral=-n;
}*/ if(*cp==':' && !inLiteral)
{
for(ph=(++cp),n=0; *cp && (isalnum(*cp) || *cp=='_') && n<MAX_SQL_IDENTIFIER; cp++,n++)
{ } *cp='\0';
printf("Enter value for %s:",ph);
gets((char*)&bindValue[i][0]); if(obndrv(cda,(OraText *)ph,-1,(ub1*)bindValue[i],-1,VARCHAR2_TYPE,-1,(sb2*)0,(OraText*)0,-1,-1))
{
oci_error(cda);
return -1;
} i++;
}
} return i;
} sword describeDefine(Cda_Def *cda)
{
sword col,deflen,deftype;
static ub1 *defptr; for(col=0; col<MAX_SELECT_LIST_SIZE; col++)
{
desc[col].buflen = MAX_ITEM_BUFFER_SIZE; if(odescr(cda,col+1,(sb4*)&desc[col].dbsize,(sb2*)&desc[col].dbtype,(sb1*)(int)desc[col].buf,(sb4*)&desc[col].buflen,(sb4*)desc[col].dsize,(sb2*)(int)desc[col].precision,(sb2*)(int)desc[col].scale,(sb2*)(int)desc[col].nullok))
{
if(cda->rc==VAR_NOT_IN_LIST)
{
break;
}else{
oci_error(cda);
return -1;
}
} switch(desc[col].dbtype)
{
case NUMBER_TYPE:
{
desc[col].dbsize = numwidth; if(desc[col].scale != 0)
{
defptr = (ub1*)&def[col].flt_fuf;
deflen = (sword)sizeof(float);
deftype= FLOAT_TYPE;
desc[col].dbtype = FLOAT_TYPE;
}else{
defptr = (ub1*)&def[col].int_buf;
deflen = (sword)sizeof(sword);
deftype= INT_TYPE;
desc[col].dbtype = INT_TYPE;
}
break;
}
case DATE_TYPE:
{
desc[col].dbsize = 9;
defptr = def[col].buf;
deflen = desc[col].dbsize > MAX_ITEM_BUFFER_SIZE ? MAX_ITEM_BUFFER_SIZE : desc[col].dbsize+1;
deftype= STRING_TYPE;
break;
}
case ROWID_TYPE:
{
desc[col].dbsize = 18;
defptr = def[col].buf;
deflen = desc[col].dbsize > MAX_ITEM_BUFFER_SIZE ? MAX_ITEM_BUFFER_SIZE : desc[col].dbsize+1;
deftype= STRING_TYPE;
break;
}
default:
{
defptr = def[col].buf;
deflen = desc[col].dbsize > MAX_ITEM_BUFFER_SIZE ? MAX_ITEM_BUFFER_SIZE : desc[col].dbsize+1;
deftype= STRING_TYPE;
break;
} } if(odefin(cda,col+1,(ub1*)defptr,deflen,deftype,-1,(sb2*)(int)def[col].indp,(OraText*)0,-1,-1,(ub2*)&def[col].col_retlen,(ub2*)(int)def[col].col_retcode))
{
oci_error(cda);
return -1;
}
} return col;
} void printHeader(sword ncols)
{
sword col,n,i; for(col=0; col<ncols; col++)
{
n=RAWWIDTH -desc[col].buflen; if(desc[col].dbtype==FLOAT_TYPE || desc[col].dbtype==INT_TYPE)
{
printf("%*.*s",desc[col].buflen,desc[col].buflen,desc[col].buf);
printf("%*c",n,' ');
}else{
printf("%*.*s",desc[col].buflen,desc[col].buflen,desc[col].buf);
printf("%*c",n,' ');
}
} fputc('\n',stdout); for(col=0; col<ncols; col++)
{
n=RAWWIDTH -desc[col].buflen; for(i=desc[col].buflen; i; i--)
{
fputc('-',stdout); } if(desc[col].dbtype==FLOAT_TYPE || desc[col].dbtype==INT_TYPE)
{
printf("%*c",n,' ');
}else
{
printf("%*c",n,' ');
} } fputc('\n',stdout); } void printRaw(Cda_Def *cda,sword ncols)
{
sword col,n; while(1)
{
fputc('\n',stdout); if(ofetch(cda))
{
if(cda->rc==NO_DATA_FOUND)
{
break;
}
if(cda->rc!=NULL_VALUE_RETURNED)
{
oci_error(cda);
}
} for(col=0; col<ncols; col++)
{ if(def[col].indp<0)
{
printf("%*c",desc[col].dbsize,' ');
}else{ switch(desc[col].dbtype)
{
case FLOAT_TYPE:
{
n = printf("%f",def[col].flt_fuf);
n = RAWWIDTH - n;
if(n)
{
printf("%*c",n,' ');
}
break;
}
case INT_TYPE:
{
n = printf("%d",def[col].int_buf);
n = RAWWIDTH - n;
if(n)
{
printf("%*c",n,' ');
}
break;
}
default:
{
n = printf("%s",def[col].buf);
n = RAWWIDTH - n;
if(n)
{
printf("%*c",n,' ');
}
break;
}
}
} }
}
} int main()
{
sword ncols; //登陆oracle
if(!connect())
{
exit(-1);
} //打开curse
if(oopen(&cda,&lda,(OraText*)0,-1,-1,(OraText*)0,-1))
{
oerhms(&lda,lda.rc,(OraText*)errorMsg,sizeof(errorMsg));
printf("%s,Please try again.\n",errorMsg);
exit(-1);
} while(1)
{
stmtLevel=0; //输入sql
if(getSqlStatement())
{
exitOracle(0);
} //分析sql
if(oparse(&cda,(OraText*)sqlStatement,-1,PARSE_NO_DELAY,PARSE_V7_LNG))
{
oci_error(&cda);
continue;
} sqlFunction = cda.ft; //绑定变量
if((ncols = bind(&cda,sqlStatement))==-1)
{
continue;
} if(sqlFunction==FT_SELECT)
{
if((ncols=describeDefine(&cda)) == -1)
{
continue;
}
} //执行sql
if(oexec(&cda))
{
oci_error(&cda);
continue;
} //若是查询，则显示查询结果;否则，提示事件
if(sqlFunction==FT_SELECT)
{
printHeader(ncols);
printRaw(&cda,ncols);
}else{
if(ocom(&lda))
{
oci_error(&lda);
continue;
}
} //显示处理行数
if(sqlFunction==FT_SELECT || sqlFunction==FT_UPDATE || sqlFunction==FT_DELETE || sqlFunction==FT_INSERT)
{
printf("\n\n%d row%c processed.\n",cda.rpc,cda.rpc==1 ? '\0' : 's');
}else{
printf("\n\nStatement processed.\n");
}
}
}
运行
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to Oracle Database: Enter user-name: so1@KFCS
Enter password: 1qaz!QAZ SQL>select * from res.res_head_imsi where hlr='G04';
HLR WRITE_TYPE MANAGE_STATUS BILL_ID STANDBYFLAG NULL_FLAG IS_CONFIRM RES_CODE RES_STATUS HEADIMSI TOTAL
--- ---------- ------------- ------- ----------- --------- ---------- -------- ---------- -------- ----- G04 1 0 13673526099 0 0 K09111 1 46000 1 1 row