C语言与OCI一起操作oracle

2024-06-12 08:38
文章标签 oracle 语言 操作 一起 oci

本文主要是介绍C语言与OCI一起操作oracle，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一，oci简介：
OCI(Oracle Call Intedace，即0racle调用层接口)是Oracle公司提供的由头文件和库函数等组成的一个访问Oracle数据库的应用程序编程接口（application programming interface API），它允许开发人员在第三代编程语言（包括C, C++, COBOL 与 FORTRAN）中通过SQL（Structure Query Language）来操纵Oracle数据库，而且OCI在一定程度上支持第三代编程语言（诸如C, C++, COBOL 与 FORTRAN）的数据类型、语法等等。OCI的显著特点是全面支持Oracle的面向对象技术，同时OCI还具有如下的一些特点：
1)高度控制应用程序的执行；
2)允许开发人员应用已熟悉的第三代程序设计语言来应用OCI；
3）可以内嵌到C代码中
4)支持动态SQL；
5)几乎所有的Oracle的开发工具都支持OCI；
6)通过回调技术（callbacks）来实现动态绑定与定义；
7)通过OCI的描述函数可以获取Oracle数据库的各种参数；
8)增强了数组在DML(data manipulation language)语言中的应用；
OCI接口支持Windows NT和Windows 95／98/2000/XP操作系统，它所支持的C语言编译器包括Borland C++和MiroSoft VisualC++等。在使用0CI开发Oralce数据库应用程序之前，应首先安装这些操作系统和C语言编译工具。在选择安装OCI开发工具包后，Oracle安装程序将0CI文件拷贝到oracle主目录内的以下子目录中：
头文件oci.h在
/home/oracle_11/app/oracle/product/11.2.0/db_1/rdbms/public/oci.h
二，简单的说一下oci的工作步骤
oci编程所需要的一些数据
typedef struct
{
OCIEnv* phOCIEnv ;//OCI环境句柄
OCIError* phOCIErr;//OCI错误句柄
OCISvcCtx* phOCISvctx;//服务上下文句柄
OCIServer* phOCIServe; /服务器上下文句柄/
OCIStmt* phOCIstmt; //语句句柄
OCISession * phSession; //会话句柄
char* DBName;//数据库服务名
char* UserName;//数据库用户名
char* Pwd;//数据库密码
}OCIHP;
2.1，分配和初始化一些句柄(如图)
这里写图片描述

2.2，分配初始化好句柄以后进行连接数据库如图
这里写图片描述
2.3，连接数据库以后就要执行一些sql语句，步骤如下
数据库连接好后可以执行SQL语句：一条SQL语句在OCI应用程序中的执行步骤一般如下：(1)准备SQL语句。(2)在SQL语句中绑定需要输入到SQL语句中的变量。(3)执行SQL语句。(4)获取SQL中的输出描述。(5)定义输出变量。(6)获取数据。具体过程及过程中调用的函数如下图所示。对于SQL中的定义语句（如CREATE，DROP）和控制语句（如GRANT，REVOKE），由于没有数据的输入输出，只需要图2中第一步和第三步即可。操作语句（如INSERT，DELETE，UPDATE）则需要执行前三步。而查询语句（如SELECT）不仅可能有数据输入，而且也有数据的输出，因此需要执行六个步骤。
这里写图片描述

三，常用函数解析可以到这里下载
http://download.csdn.net/detail/u011573853/9328969

四，案例，本人写了一个很浅显的案例，实现了增删改查操作，使用绑定参数和不绑定两种方式完成的，适合我这样的新手看，高手飘过核心代码如下
全部代码可以到此下载
http://download.csdn.net/detail/u011573853/9328997

//插入数据(不绑定参数的)
void Oci_insert(OCIHP* ph,char *sql)
{printf("sql =%s\n",sql);char sErrorMsg[1024];sb4 sb4ErrorCode;ub4 ub4RecordNo = 1;int nRet = 0;//准备SQL语句nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql,  (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);return ;}   		printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)1, (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} printf("%s\n","执行SQL语句 OK");Oci_commit( ph);return ;
}//绑定参数插入
void Oci_insert_bang(OCIHP* ph)
{char sErrorMsg[1024];sb4 sb4ErrorCode;ub4 ub4RecordNo = 1;int nRet = 0;OCIBind* bhp[10];  char id[5]="11";char sname[15]="liuyupei";int age=20;char sex[]="v";char sql[]="insert into stu(id,sname,age,sex) values(:Vhid,:Vhname,:Vhage,:Vhsex)";//准备SQL语句nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql,  (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);return ;}   //绑定输入参数变量/*//把id 和:Vhid绑定在一起if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[0], ph->phOCIErr, (text *) ":Vhid",strlen(":Vhid"), (ub1 *)id , strlen(id)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","1参数绑定失败");return ;}//把sname 和:Vhname绑定在一起if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[1], ph->phOCIErr, (text *) ":Vhname",strlen(":Vhname"), (ub1 *)sname , strlen(sname)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","2参数绑定失败");return ;}//把age 和:Vhage绑定在一起 if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[2], ph->phOCIErr, (text *) ":Vhage",-1, (ub1 *)&age , (sword)4, SQLT_INT, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","3参数绑定失败");return ;}//把sex 和:Vhsex绑定在一起 if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[3], ph->phOCIErr, (text *) ":Vhsex",strlen(":Vhsex"), (ub1 *)sex , strlen(sex)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","4参数绑定失败");return ;}*///第二种绑定的方法OCIBindByPos(ph->phOCIstmt, &bhp[0], ph->phOCIErr, 1,(dvoid *)id, sizeof(id), SQLT_STR,(dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT); OCIBindByPos(ph->phOCIstmt, &bhp[1], ph->phOCIErr, 2,(dvoid *)sname, sizeof(sname), SQLT_STR,(dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT); OCIBindByPos(ph->phOCIstmt, &bhp[2], ph->phOCIErr, 3,(dvoid *)&age, sizeof(int), SQLT_INT,(dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT); OCIBindByPos(ph->phOCIstmt, &bhp[3], ph->phOCIErr, 4,(dvoid *)sex, sizeof(sex), SQLT_STR, (dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT); printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)1, (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} printf("%s\n","执行SQL语句 OK");Oci_commit( ph);return ;
}

//更新数据不绑定
void Oci_update(OCIHP* ph,char *sql)
{printf("sql =%s\n",sql);char sErrorMsg[1024];sb4 sb4ErrorCode;ub4 ub4RecordNo = 1;int nRet = 0;//准备SQL语句nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql,  (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;}   		printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)1, (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} printf("%s\n","执行SQL语句 OK");Oci_commit( ph);return ;
}//更新数据绑定参数
void Oci_update_bang(OCIHP* ph)
{char sErrorMsg[1024];sb4 sb4ErrorCode;ub4 ub4RecordNo = 1;int nRet = 0;OCIBind* bhp[10];  char id[5]="11";char sname[15]="liweieieieei";
;char sql[]="update stu set sname=':Vhname' where id=':Vhid'";//准备SQL语句nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql,  (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);return ;}   //绑定输入参数变量/*//把id 和:Vhid绑定在一起if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[0], ph->phOCIErr, (text *) ":Vhid",strlen(":Vhid"), (ub1 *)id , strlen(id)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","1参数绑定失败");return 0;}//把sname 和:Vhname绑定在一起if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[1], ph->phOCIErr, (text *) ":Vhname",strlen(":Vhname"), (ub1 *)sname , strlen(sname)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","2参数绑定失败");return 0;}*///第二种绑定的方法OCIBindByPos(ph->phOCIstmt, &bhp[0], ph->phOCIErr, 2,(dvoid *)id, sizeof(id), SQLT_STR,(dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT); OCIBindByPos(ph->phOCIstmt, &bhp[1], ph->phOCIErr, 1,(dvoid *)sname, sizeof(sname), SQLT_STR,(dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT); printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)1, (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} printf("%s\n","执行SQL语句 OK");Oci_commit( ph);return ;
}//查询
void Oci_select(OCIHP* ph)
{int nRet = 0;ub4 ub4RecordNo = 1;OCIDefine * bhp[10];  char id[20];char sname[30];int age;char sex[20];char sErrorMsg[1024];sb4 sb4ErrorCode;//char sname[10] ={0};b2 sb2aIndid[30]; //指示器变量，用于取可能存在空值的字char sql[]="select id,sname,age,sex  from stu  ";nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql, (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); //准备SQL语句if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);return ;}   //获取数据长度ub2 datalen = 0;//绑定输出参数if(OCIDefineByPos(ph->phOCIstmt,&bhp[0],ph->phOCIErr, 1, (dvoid *)&id, (ub4)sizeof(id),SQLT_STR/*LBI long binary type */, &sb2aIndid[0], &datalen, NULL,   OCI_DEFAULT) !=0){//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg,sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);OCIHandleFree(ph->phOCIstmt, OCI_HTYPE_STMT);printf("%s\n","1参数绑定失败");return ;}if(OCIDefineByPos(ph->phOCIstmt,&bhp[1],ph->phOCIErr, 2,(dvoid *)&sname, (ub4)sizeof(sname),SQLT_STR/*LBI long binary type */, &sb2aIndid[1], &datalen, NULL, OCI_DEFAULT) !=0){//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*)sErrorMsg,sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);OCIHandleFree(ph->phOCIstmt, OCI_HTYPE_STMT);printf("%s\n","2参数绑定失败");return ;}if(OCIDefineByPos(ph->phOCIstmt,&bhp[2],ph->phOCIErr, 3,(dvoid *)&age, (ub4)4,SQLT_INT/*LBI long binary type */, NULL, &datalen, NULL,  OCI_DEFAULT) !=0){//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);OCIHandleFree(ph->phOCIstmt, OCI_HTYPE_STMT);printf("%s\n","3参数绑定失败");return ;}if(OCIDefineByPos(ph->phOCIstmt,&bhp[3],ph->phOCIErr, 4,(dvoid *)&sex, (ub4)sizeof(sex),SQLT_STR/*LBI long binary type */, &sb2aIndid[3], &datalen, NULL, OCI_DEFAULT) !=0){//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*)sErrorMsg,sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);OCIHandleFree(ph->phOCIstmt, OCI_HTYPE_STMT);printf("%s\n","4参数绑定失败");return ;}//获取执行语句类型ub2 stmt_type;OCIAttrGet ((dvoid *)ph->phOCIstmt, (ub4)OCI_HTYPE_STMT, (dvoid *)&stmt_type, (ub4 *)0, (ub4)OCI_ATTR_STMT_TYPE, ph->phOCIErr);printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)(stmt_type==OCI_STMT_SELECT?1:0), (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} // 利用游标提取信息int rows_fetched;do{printf("id=%s,sname=%s,age=%d,sex =%s\n",id,(sb2aIndid[1]==-1?"NULL":sname),age,sex);printf("%d\n",sb2aIndid[0]);printf("%d\n",sb2aIndid[1]);}while(OCIStmtFetch2(ph->phOCIstmt, ph->phOCIErr, 1, OCI_FETCH_NEXT, OCI_FETCH_NEXT, OCI_DEFAULT) != OCI_NO_DATA);// 获得记录条数OCIAttrGet((CONST void *)ph->phOCIstmt, OCI_HTYPE_STMT, (void *)&rows_fetched, (ub4 *)sizeof(rows_fetched),OCI_ATTR_ROW_COUNT, ph->phOCIErr);printf("总共记录数 %d\n",rows_fetched);
}//删除数据
void Oci_delete(OCIHP* ph,char *sql)
{printf("sql =%s\n",sql);char sErrorMsg[1024];sb4 sb4ErrorCode;ub4 ub4RecordNo = 1;int nRet = 0;//准备SQL语句nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql,  (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;}   		printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)1, (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} printf("%s\n","执行SQL语句 OK");Oci_commit( ph);return ;
}
简单的makefile：//插入数据(不绑定参数的)
void Oci_insert(OCIHP* ph,char *sql)
{printf("sql =%s\n",sql);char sErrorMsg[1024];sb4 sb4ErrorCode;ub4 ub4RecordNo = 1;int nRet = 0;//准备SQL语句nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql,  (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);return ;}   		printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)1, (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} printf("%s\n","执行SQL语句 OK");Oci_commit( ph);return ;
}//绑定参数插入
void Oci_insert_bang(OCIHP* ph)
{char sErrorMsg[1024];sb4 sb4ErrorCode;ub4 ub4RecordNo = 1;int nRet = 0;OCIBind* bhp[10];  char id[5]="11";char sname[15]="liuyupei";int age=20;char sex[]="v";char sql[]="insert into stu(id,sname,age,sex) values(:Vhid,:Vhname,:Vhage,:Vhsex)";//准备SQL语句nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql,  (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);return ;}   //绑定输入参数变量/*//把id 和:Vhid绑定在一起if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[0], ph->phOCIErr, (text *) ":Vhid",strlen(":Vhid"), (ub1 *)id , strlen(id)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","1参数绑定失败");return ;}//把sname 和:Vhname绑定在一起if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[1], ph->phOCIErr, (text *) ":Vhname",strlen(":Vhname"), (ub1 *)sname , strlen(sname)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","2参数绑定失败");return ;}//把age 和:Vhage绑定在一起 if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[2], ph->phOCIErr, (text *) ":Vhage",-1, (ub1 *)&age , (sword)4, SQLT_INT, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","3参数绑定失败");return ;}//把sex 和:Vhsex绑定在一起 if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[3], ph->phOCIErr, (text *) ":Vhsex",strlen(":Vhsex"), (ub1 *)sex , strlen(sex)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","4参数绑定失败");return ;}*///第二种绑定的方法OCIBindByPos(ph->phOCIstmt, &bhp[0], ph->phOCIErr, 1,(dvoid *)id, sizeof(id), SQLT_STR,(dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT); OCIBindByPos(ph->phOCIstmt, &bhp[1], ph->phOCIErr, 2,(dvoid *)sname, sizeof(sname), SQLT_STR,(dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT); OCIBindByPos(ph->phOCIstmt, &bhp[2], ph->phOCIErr, 3,(dvoid *)&age, sizeof(int), SQLT_INT,(dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT); OCIBindByPos(ph->phOCIstmt, &bhp[3], ph->phOCIErr, 4,(dvoid *)sex, sizeof(sex), SQLT_STR, (dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT); printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)1, (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} printf("%s\n","执行SQL语句 OK");Oci_commit( ph);return ;
}

//更新数据不绑定
void Oci_update(OCIHP* ph,char *sql)
{printf("sql =%s\n",sql);char sErrorMsg[1024];sb4 sb4ErrorCode;ub4 ub4RecordNo = 1;int nRet = 0;//准备SQL语句nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql,  (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;}   		printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)1, (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} printf("%s\n","执行SQL语句 OK");Oci_commit( ph);return ;
}//更新数据绑定参数
void Oci_update_bang(OCIHP* ph)
{char sErrorMsg[1024];sb4 sb4ErrorCode;ub4 ub4RecordNo = 1;int nRet = 0;OCIBind* bhp[10];  char id[5]="11";char sname[15]="liweieieieei";
;char sql[]="update stu set sname=':Vhname' where id=':Vhid'";//准备SQL语句nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql,  (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);return ;}   //绑定输入参数变量/*//把id 和:Vhid绑定在一起if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[0], ph->phOCIErr, (text *) ":Vhid",strlen(":Vhid"), (ub1 *)id , strlen(id)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","1参数绑定失败");return 0;}//把sname 和:Vhname绑定在一起if ((nRet= OCIBindByName(ph->phOCIstmt, &bhp[1], ph->phOCIErr, (text *) ":Vhname",strlen(":Vhname"), (ub1 *)sname , strlen(sname)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS){printf("%s\n","2参数绑定失败");return 0;}*///第二种绑定的方法OCIBindByPos(ph->phOCIstmt, &bhp[0], ph->phOCIErr, 2,(dvoid *)id, sizeof(id), SQLT_STR,(dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT); OCIBindByPos(ph->phOCIstmt, &bhp[1], ph->phOCIErr, 1,(dvoid *)sname, sizeof(sname), SQLT_STR,(dvoid*)0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT); printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)1, (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} printf("%s\n","执行SQL语句 OK");Oci_commit( ph);return ;
}//查询
void Oci_select(OCIHP* ph)
{int nRet = 0;ub4 ub4RecordNo = 1;OCIDefine * bhp[10];  char id[20];char sname[30];int age;char sex[20];char sErrorMsg[1024];sb4 sb4ErrorCode;//char sname[10] ={0};b2 sb2aIndid[30]; //指示器变量，用于取可能存在空值的字char sql[]="select id,sname,age,sex  from stu  ";nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql, (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); //准备SQL语句if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);return ;}   //获取数据长度ub2 datalen = 0;//绑定输出参数if(OCIDefineByPos(ph->phOCIstmt,&bhp[0],ph->phOCIErr, 1, (dvoid *)&id, (ub4)sizeof(id),SQLT_STR/*LBI long binary type */, &sb2aIndid[0], &datalen, NULL,   OCI_DEFAULT) !=0){//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg,sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);OCIHandleFree(ph->phOCIstmt, OCI_HTYPE_STMT);printf("%s\n","1参数绑定失败");return ;}if(OCIDefineByPos(ph->phOCIstmt,&bhp[1],ph->phOCIErr, 2,(dvoid *)&sname, (ub4)sizeof(sname),SQLT_STR/*LBI long binary type */, &sb2aIndid[1], &datalen, NULL, OCI_DEFAULT) !=0){//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*)sErrorMsg,sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);OCIHandleFree(ph->phOCIstmt, OCI_HTYPE_STMT);printf("%s\n","2参数绑定失败");return ;}if(OCIDefineByPos(ph->phOCIstmt,&bhp[2],ph->phOCIErr, 3,(dvoid *)&age, (ub4)4,SQLT_INT/*LBI long binary type */, NULL, &datalen, NULL,  OCI_DEFAULT) !=0){//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);OCIHandleFree(ph->phOCIstmt, OCI_HTYPE_STMT);printf("%s\n","3参数绑定失败");return ;}if(OCIDefineByPos(ph->phOCIstmt,&bhp[3],ph->phOCIErr, 4,(dvoid *)&sex, (ub4)sizeof(sex),SQLT_STR/*LBI long binary type */, &sb2aIndid[3], &datalen, NULL, OCI_DEFAULT) !=0){//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*)sErrorMsg,sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);OCIHandleFree(ph->phOCIstmt, OCI_HTYPE_STMT);printf("%s\n","4参数绑定失败");return ;}//获取执行语句类型ub2 stmt_type;OCIAttrGet ((dvoid *)ph->phOCIstmt, (ub4)OCI_HTYPE_STMT, (dvoid *)&stmt_type, (ub4 *)0, (ub4)OCI_ATTR_STMT_TYPE, ph->phOCIErr);printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)(stmt_type==OCI_STMT_SELECT?1:0), (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} // 利用游标提取信息int rows_fetched;do{printf("id=%s,sname=%s,age=%d,sex =%s\n",id,(sb2aIndid[1]==-1?"NULL":sname),age,sex);printf("%d\n",sb2aIndid[0]);printf("%d\n",sb2aIndid[1]);}while(OCIStmtFetch2(ph->phOCIstmt, ph->phOCIErr, 1, OCI_FETCH_NEXT, OCI_FETCH_NEXT, OCI_DEFAULT) != OCI_NO_DATA);// 获得记录条数OCIAttrGet((CONST void *)ph->phOCIstmt, OCI_HTYPE_STMT, (void *)&rows_fetched, (ub4 *)sizeof(rows_fetched),OCI_ATTR_ROW_COUNT, ph->phOCIErr);printf("总共记录数 %d\n",rows_fetched);
}//删除数据
void Oci_delete(OCIHP* ph,char *sql)
{printf("sql =%s\n",sql);char sErrorMsg[1024];sb4 sb4ErrorCode;ub4 ub4RecordNo = 1;int nRet = 0;//准备SQL语句nRet=	OCIStmtPrepare(ph->phOCIstmt, ph->phOCIErr, (text*)sql,  (ub4) strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); if(nRet){printf("%s\n","准备SQL语句错误");printf("OCIStmtPrepare() error:%d\n",nRet);if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;}   		printf("%s\n","kaishi执行SQL语句");nRet = OCIStmtExecute( ph->phOCISvctx, ph->phOCIstmt, ph->phOCIErr, (ub4)1, (ub4)0, (OCISnapshot *) NULL,(OCISnapshot *) NULL, (ub4) OCI_DEFAULT); //执行SQL语句if(nRet){printf("%s\n","执行SQL语句错误");printf("OCIStmtExecute() error:%d\n",nRet);//获取错误信息if (OCIErrorGet(ph->phOCIErr, ub4RecordNo++, NULL, &sb4ErrorCode, (OraText*) sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS) printf("error msg:%s\n", sErrorMsg);return ;} printf("%s\n","执行SQL语句 OK");Oci_commit( ph);return ;
}简单的makefile：gcc myocitext.c  -o myocitext -I$ORACLE_HOME/rdbms/demo -I$ORACLE_HOME/rdbms/public -lclntsh应注意库lclntsh的位置本人也是新手，下面有不错的资料
http://www.cnblogs.com/ychellboy/archive/2010/04/16/1713884.html
http://kulong0105.blog.163.com/blog/static/174406191201162145944574/

这篇关于C语言与OCI一起操作oracle的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1053722。

相关文章

MySQL游标和触发器的操作流程

MySQL游标和触发器的操作流程

《MySQL游标和触发器的操作流程》本文介绍了MySQL中的游标和触发器的使用方法,游标可以对查询结果集进行逐行处理,而触发器则可以在数据表发生更改时自动执行预定义的操作,感兴趣的朋友跟随小编一起看看... 目录游标游标的操作流程1. 定义游标2.打开游标3.利用游标检索数据4.关闭游标例题触发器触发器的基
阅读更多...
在C#中分离饼图的某个区域的操作指南

在C#中分离饼图的某个区域的操作指南

《在C#中分离饼图的某个区域的操作指南》在处理Excel饼图时,我们可能需要将饼图的各个部分分离出来,以使它们更加醒目,Spire.XLS提供了Series.DataFormat.Percent属性,... 目录引言如何设置饼图各分片之间分离宽度的代码示例：从整个饼图中分离单个分片的代码示例：引言在处理
阅读更多...
Python列表的创建与删除的操作指南

Python列表的创建与删除的操作指南

《Python列表的创建与删除的操作指南》列表（list）是Python中最常用、最灵活的内置数据结构之一,它支持动态扩容、混合类型、嵌套结构,几乎无处不在,但你真的会创建和删除列表吗,本文给大家介绍... 目录一、前言二、列表的创建方式1. 字面量语法（最常用）2. 使用list()构造器3. 列表推导式
阅读更多...
Go异常处理、泛型和文件操作实例代码

Go异常处理、泛型和文件操作实例代码

《Go异常处理、泛型和文件操作实例代码》Go语言的异常处理机制与传统的面向对象语言（如Java、C#）所使用的try-catch结构有所不同,它采用了自己独特的设计理念和方法,：本文主要介绍Go异... 目录一：异常处理常见的异常处理向上抛中断程序恢复程序二：泛型泛型函数泛型结构体泛型切片泛型 map三：文
阅读更多...
C语言逗号运算符和逗号表达式的使用小结

C语言逗号运算符和逗号表达式的使用小结

《C语言逗号运算符和逗号表达式的使用小结》本文详细介绍了C语言中的逗号运算符和逗号表达式,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习... 在Ｃ语言中逗号“，”也是一种运算符，称为逗号运算符。 其功能是把两个表达式连接其一般形式为：表达
阅读更多...
Go语言实现桥接模式

Go语言实现桥接模式

《Go语言实现桥接模式》桥接模式是一种结构型设计模式,它将抽象部分与实现部分分离,使它们可以独立地变化,本文就来介绍一下了Go语言实现桥接模式,感兴趣的可以了解一下... 目录简介核心概念为什么使用桥接模式？应用场景案例分析步骤一：定义实现接口步骤二：创建具体实现类步骤三：定义抽象类步骤四：创建扩展抽象类步
阅读更多...
GO语言实现串口简单通讯

GO语言实现串口简单通讯

《GO语言实现串口简单通讯》本文分享了使用Go语言进行串口通讯的实践过程,详细介绍了串口配置、数据发送与接收的代码实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要... 目录背景串口通讯代码代码块分解解析完整代码运行结果背景最近再学习 go 语言，在某宝用5块钱买了个
阅读更多...
MySQL基本表查询操作汇总之单表查询+多表操作大全

MySQL基本表查询操作汇总之单表查询+多表操作大全

《MySQL基本表查询操作汇总之单表查询+多表操作大全》本文全面介绍了MySQL单表查询与多表操作的关键技术,包括基本语法、高级查询、表别名使用、多表连接及子查询等,并提供了丰富的实例,感兴趣的朋友跟... 目录一、单表查询整合（一）通用模版展示（二）举例说明（三）注意事项（四）Mapper简单举例简单查询
阅读更多...
Nginx概念、架构、配置与虚拟主机实战操作指南

Nginx概念、架构、配置与虚拟主机实战操作指南

《Nginx概念、架构、配置与虚拟主机实战操作指南》Nginx是一个高性能的HTTP服务器、反向代理服务器、负载均衡器和IMAP/POP3/SMTP代理服务器,它支持高并发连接,资源占用低,功能全面且... 目录Nginx 深度解析：概念、架构、配置与虚拟主机实战一、Nginx 的概念二、Nginx 的特点
阅读更多...
MySQL 数据库进阶之SQL 数据操作与子查询操作大全

MySQL 数据库进阶之SQL 数据操作与子查询操作大全

《MySQL数据库进阶之SQL数据操作与子查询操作大全》本文详细介绍了SQL中的子查询、数据添加（INSERT）、数据修改（UPDATE）和数据删除（DELETE、TRUNCATE、DROP）操作... 目录一、子查询：嵌套在查询中的查询1.1 子查询的基本语法1.2 子查询的实战示例二、数据添加：INSE
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2