OCI调用oracle入门程序

来源：互联网发布：歌在飞网络歌手编辑：程序博客网时间：2024/05/23 17:39

http://blog.csdn.net/evgd2288/article/details/6607035

OCI调用oracle入门程序

2009-01-04 11:48

参考下面网址的例子做了一些小小的修改,并加上注释-_-!
http://blog.chinaunix.net/u/28499/showart_1006536.html

start

/* cu1.c gcc -o test -I/home/oracle/include -L/home/oracle/lib -lclntsh cu1.c -D_DEBUG -Wall -g oracle的库文件和头文件路径根据个人电脑设置调整即可 */ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <oci.h> typedef struct { OCIEnv *EnvHP; //OCI环境句柄 OCIServer *SrvHP; //OCI服务器句柄 OCISvcCtx *SvcHP; //OCI服务器上下文句柄 OCIError *ErrHP; //OCI错误句柄 OCISession *SessionHP; //OCI对话句柄 OCIStmt *StmtHP; //OCI语句句柄 } MY_OCI_CONTEXT_T; int oci_init(MY_OCI_CONTEXT_T* ociCtx_p); static int select_data(MY_OCI_CONTEXT_T* ociCtx_p,int pid); void oci_clean(MY_OCI_CONTEXT_T* ociCtx_p); //tns名 char* pConnectChar = "tnsname"; //用户名和密码 char* pUsernameChar = "username"; char* pPasswordChar = "password"; int main() { int i; int errNo = 0; text errInfo[1024]; MY_OCI_CONTEXT_T ociCtx; i = oci_init(&ociCtx); //初始化链接 printf("i = %d\n",i); select_data(&ociCtx,1110); //执行sql语句 if( i == 1 ) { oci_clean(&ociCtx); } else { (void)OCIErrorGet ((dvoid*) ociCtx.ErrHP, (ub4) 1, (text *) NULL, &errNo, errInfo, (ub4)sizeof(errInfo)-1, (ub4) OCI_HTYPE_ERROR); printf("ErroNO=%d\nerrInfor=%s\n", errNo, errInfo); } return 0; } int oci_init(MY_OCI_CONTEXT_T* ociCtx_p) { sword sr; //OCI环境初始化在8i以后，可用OCIEnvCreate一个函数就可以初始化环境了，相当于OCIInitialize+ OCIEnvInit sr=OCIEnvInit( (OCIEnv **) &ociCtx_p->EnvHP, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 ); //创建OCI环境 sr=OCIEnvCreate(&ociCtx_p->EnvHP,OCI_DEFAULT,0,0,0,0,0,0); //判断是否成功 if(sr!=OCI_SUCCESS) { return -1; } //申请错误句柄ErrHP sr= OCIHandleAlloc( (dvoid *) ociCtx_p->EnvHP, (dvoid **) &ociCtx_p->ErrHP, OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0); /* server contexts */ if(sr!=OCI_SUCCESS) { return -1; } //申请服务器句柄SrvHP sr=OCIHandleAlloc( (dvoid *)ociCtx_p->EnvHP, (dvoid **) &ociCtx_p->SrvHP, OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0); if(sr!=OCI_SUCCESS) { return -1; } //申请服务器上下文句柄SvcHP sr=OCIHandleAlloc( (dvoid *)ociCtx_p->EnvHP, (dvoid **) &ociCtx_p->SvcHP, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0); if(sr!=OCI_SUCCESS) { return -1; } //多用户方式连接数据库服务器,设置链接的服务器名pConnectChar sr= OCIServerAttach(ociCtx_p->SrvHP,ociCtx_p->ErrHP, (text *)pConnectChar, strlen(pConnectChar), 0); if(sr!=OCI_SUCCESS) { printf("connect erro!!!!!!!!!!!!\n"); return -1; } /* set attribute server context in the service context */ //设置SvcHP句柄属性为：OCI_HTYPE_SVCCTX (void) OCIAttrSet( (dvoid *)ociCtx_p->SvcHP, OCI_HTYPE_SVCCTX, (dvoid *)ociCtx_p->SrvHP, (ub4) 0, OCI_ATTR_SERVER, (OCIError *) ociCtx_p->ErrHP); //申请对话句柄SessionHP sr= OCIHandleAlloc((dvoid *)ociCtx_p->EnvHP, (dvoid **)&ociCtx_p->SessionHP, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0); if(sr!=OCI_SUCCESS) { return -1; } //设置用户名pUsernameChar (void) OCIAttrSet((dvoid *)ociCtx_p->SessionHP, (ub4) OCI_HTYPE_SESSION, (dvoid *)pUsernameChar, (ub4) strlen((char *)pUsernameChar), (ub4) OCI_ATTR_USERNAME, ociCtx_p->ErrHP); //设置SessionHP句柄用户登录密码pPasswordChar (void) OCIAttrSet((dvoid *)ociCtx_p->SessionHP, (ub4) OCI_HTYPE_SESSION, (dvoid *) pPasswordChar, (ub4) strlen((char *)pPasswordChar), (ub4) OCI_ATTR_PASSWORD, ociCtx_p->ErrHP); //建立一个会话连接 sr=OCISessionBegin (ociCtx_p->SvcHP, ociCtx_p->ErrHP, ociCtx_p->SessionHP, OCI_CRED_RDBMS, (ub4) OCI_DEFAULT); if(sr!=OCI_SUCCESS) { return -1; } //设置SvcHP句柄属性为：OCI_HTYPE_SVCCTX (void) OCIAttrSet((dvoid *)ociCtx_p->SvcHP, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)ociCtx_p->SessionHP, (ub4) 0, (ub4) OCI_ATTR_SESSION, ociCtx_p->ErrHP); //申请语句句柄StmtHP sr=OCIHandleAlloc( (dvoid *)ociCtx_p->EnvHP, (dvoid **) &ociCtx_p->StmtHP, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0); if(sr!=OCI_SUCCESS) { return -1; } printf("#############Connect db sccessfully!################\n"); return 1; } //查询数据函数示例代码的sql语句有两列输出 static int select_data(MY_OCI_CONTEXT_T* ociCtx_p,int pid) { char sqlcmd[128]=""; sword retcode; OCIDefine *defcolp[3]; sword indp[3]; int pointId = 1; int pointId1 = 1; char pointName[30]; char pointDesc[60]; //生成sql语句 // sprintf(sqlcmd,"select c_point_id,c_point_name,C_POINT_DESC from t_point where c_point_id=%d",pid); strcpy(sqlcmd,"select * from T1"); //准备SQL语句 retcode = OCIStmtPrepare (ociCtx_p->StmtHP, ociCtx_p->ErrHP, (unsigned char *)sqlcmd, (ub4)strlen((char *)sqlcmd), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT ); if(retcode!=OCI_SUCCESS) { printf("error OCIStmtPrepare \n"); } //sql语句有两列输出，有几列就定义几次 //定义输出变量0 retcode=OCIDefineByPos(ociCtx_p->StmtHP, &defcolp[0], ociCtx_p->ErrHP, (ub4)1, (dvoid *)&pointId, (sb4)sizeof(int), SQLT_INT , (dvoid *)&indp[0], (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT); if(retcode!=OCI_SUCCESS) { printf("error OCIDefineByPos 0 \n"); } //定义输出变量1 retcode=OCIDefineByPos(ociCtx_p->StmtHP, &defcolp[0], ociCtx_p->ErrHP, (ub4)2, (dvoid *)&pointId1, (sb4)sizeof(int), SQLT_INT , (dvoid *)&indp[1], (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT); if(retcode!=OCI_SUCCESS) { printf("error OCIDefineByPos 1 \n"); } pointName[29] = '\0'; //执行SQL语句 retcode=OCIStmtExecute(ociCtx_p->SvcHP, ociCtx_p->StmtHP, ociCtx_p->ErrHP, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT ); //查错 if(retcode!=OCI_SUCCESS&&retcode!=OCI_NO_DATA) { int errNo = 0; retcode = OCIErrorGet ((dvoid*) ociCtx_p->ErrHP, (ub4) 1, (text *) NULL, &errNo, (text*)pointName, (ub4)sizeof(pointName)-1, (ub4) OCI_HTYPE_ERROR); printf("error OCIStmtExecute No=%d Info=%s \n", errNo, pointName); return -1; } //依次读取数据 printf("第一列=%d\t第二列=%d\n",pointId, pointId1); while( OCIStmtFetch(ociCtx_p->StmtHP, ociCtx_p->ErrHP, (ub4)1, (ub4)OCI_FETCH_NEXT, (ub4)OCI_DEFAULT) != OCI_NO_DATA ) { printf("第一列=%d\t第二列=%d\n",pointId, pointId1); } return 1; } void oci_clean(MY_OCI_CONTEXT_T* ociCtx_p) { printf("\n ########## clean up ############ \n"); //结束于数据库服务器的对话 if (OCISessionEnd(ociCtx_p->SvcHP, ociCtx_p->ErrHP, ociCtx_p->SessionHP, (ub4) 0)) printf("FAILED: OCISessionEnd()\n"); //断开与数据库服务器链接 if (OCIServerDetach(ociCtx_p->SrvHP, ociCtx_p->ErrHP, (ub4) OCI_DEFAULT)) printf("FAILED: OCIServerDetach()\n"); printf("Detached from server.\n"); printf("Freeing handles ...\n"); if (ociCtx_p->StmtHP) OCIHandleFree((dvoid *) ociCtx_p->StmtHP, (ub4) OCI_HTYPE_STMT); if (ociCtx_p->ErrHP) OCIHandleFree((dvoid *) ociCtx_p->ErrHP, (ub4) OCI_HTYPE_ERROR); if (ociCtx_p->SrvHP) OCIHandleFree((dvoid *) ociCtx_p->SrvHP, (ub4) OCI_HTYPE_SERVER); if (ociCtx_p->SvcHP) OCIHandleFree((dvoid *) ociCtx_p->SvcHP, (ub4) OCI_HTYPE_SVCCTX); if (ociCtx_p->SessionHP) OCIHandleFree((dvoid *) ociCtx_p->SessionHP, (ub4) OCI_HTYPE_SESSION); if (ociCtx_p->EnvHP) OCIHandleFree((dvoid *) ociCtx_p->EnvHP, (ub4) OCI_HTYPE_ENV); }

数据列的属性，执行sqlplus输出，编译后程序输出分别如下：

SQL> desc t1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
A                                                  NUMBER(4)
B                                         NOT NULL NUMBER(4)

SQL> select * from t1; A B ---------- ---------- 0 3 2 3 20 3 21 3 8 1 9 1 30 32 -1 2 1 4 3 6 5 8 A B ---------- ---------- 7 10 22 2 26 2 14 rows selected.

下面是编译执行程序得到的结果

[mgqw@localhost oracle]$ gcc -o test -I/home/oracle/include -L/home/oracle/lib -lclntsh cu1.c -D_DEBUG -Wall -g cu1.c: 在函数‘select_data’中: cu1.c:168: 警告：未使用的变量‘pointDesc’ [mgqw@localhost oracle]$ ./test #############Connect db sccessfully!################ i = 1 第一列=0 第二列=3 第一列=2 第二列=3 第一列=20 第二列=3 第一列=21 第二列=3 第一列=8 第二列=1 第一列=9 第二列=1 第一列=30 第二列=32 第一列=-1 第二列=2 第一列=1 第二列=4 第一列=3 第二列=6 第一列=5 第二列=8 第一列=7 第二列=10 第一列=22 第二列=2 第一列=26 第二列=2 ########## clean up ############ Detached from server. Freeing handles ...

0 0