-
2021-05-21 05:17:26
最近在Unix上使用C对Oracle编程,可是我对Oracle不熟悉。现在遇到一个难题:怎样读取表中的列数。
如果有哪位大侠知道解决的方法,请回应。
先谢了。
|
OCIEnv *penv;
OCIError *perr;
OCISvcCtx *psvc;
int
describe_select_list(char *sql)
{
OCIStmt *pstmt;
OCIParam *pparam;
ub4 pos;
ub2 dtype;
ub2 dsize;
text *name;
ub4 name_length;
if (OCIHandleAlloc(penv, (dvoid **)&pstmt, OCI_HTYPE_STMT, 0, 0) == OCI_SUCCESS)
{
if (OCIStmtPrepare(pstmt, perr, (text *)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT) == OCI_SUCCESS)
{
if (OCIStmtExecute(psvc, pstmt, perr, 0, 0, 0, 0, OCI_DEFAULT) == OCI_SUCCESS)
{
OCIAttrGet((dvoid *)pstmt, OCI_HTYPE_STMT, &name_length, 0, OCI_ATTR_PARAM_COUNT, perr);
printf("found %d total columns.n", name_length);
pos = 1;
while (OCIParamGet(pstmt, OCI_HTYPE_STMT, perr, (dvoid *)&pparam, pos) == OCI_SUCCESS)
{
OCIAttrGet((dvoid *)pparam, OCI_DTYPE_PARAM, (dvoid *)&dtype, 0, OCI_ATTR_DATA_TYPE, perr);
OCIAttrGet((dvoid *)pparam, OCI_DTYPE_PARAM, (dvoid *)&dsize, 0, OCI_ATTR_DATA_SIZE, perr);
OCIAttrGet((dvoid *)pparam, OCI_DTYPE_PARAM, (dvoid *)&name, &name_length, OCI_ATTR_NAME, perr);
printf("%02d> column = '%s', type = %d, size = %d.n", pos, name, dtype, dsize);
pos++;
}
OCIHandleFree(pstmt, OCI_HTYPE_STMT);
return 0;
}
}
}
OCIHandleFree(pstmt, OCI_HTYPE_STMT);
show_error_message("describe_select_list");
return -1;
}
|
对Oracle的操作和对mysql的一样吗?
更多相关内容 -
oracle oci编程
2014-10-13 14:58:21OCI(Oracle Call Interface)是ORACLE公司开发的一个应用程序开发工具,是一个通过访问Oracle数据库的服务器,控制各类SQL语句的执行,进而创建应用程序的的应用程序接口(API). 压缩包里是我以前开发时看过的几个... -
C语言与OCI一起操作oracle
2015-12-05 16:40:43OCI(Oracle Call Intedace,即0racle调用层接口)是Oracle公司提供的由头文件和库函数等组成的一个访问Oracle数据库的应用程序编程接口(application programming interface API),它允许开发人员在第三代编程语言...一,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数据库
2022-04-16 18:41:30/* 在服务上下文句柄中设置服务器属性*/ (void)OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errhp); /* 分配一个用户会话句柄 */ (void)...sql语句
CREATE TABLE "SYS_LOG" ( "ID" NVARCHAR2(32) NOT NULL , "LOG_TYPE" NUMBER(11) , "LOG_CONTENT" NVARCHAR2(1000) , "OPERATE_TYPE" NUMBER(11) , "USERID" NVARCHAR2(32) , "USERNAME" NVARCHAR2(100) , "IP" NVARCHAR2(100) , "METHOD" NVARCHAR2(500) , "REQUEST_URL" NVARCHAR2(255) , "REQUEST_PARAM" NCLOB , "REQUEST_TYPE" NVARCHAR2(10) , "COST_TIME" NUMBER(20) , "CREATE_BY" NVARCHAR2(32) , "CREATE_TIME" DATE , "UPDATE_BY" NVARCHAR2(32) , "UPDATE_TIME" DATE ); INSERT INTO "SYS_LOG" VALUES ('1487d69ff97888f3a899e2ababb5ae48', '1', '用户名: admin,登录成功!', NULL, NULL, NULL, '127.0.0.1', NULL, NULL, NULL, NULL, NULL, 'jeecg-boot', TO_DATE('2019-01-22 14:21:17', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL); INSERT INTO "SYS_LOG" VALUES ('cc7fa5567e7833a3475b29b7441a2976', '1', '用户名: admin,登录成功!', NULL, NULL, NULL, '127.0.0.1', NULL, NULL, NULL, NULL, NULL, 'jeecg-boot', TO_DATE('2019-01-22 14:21:31', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL); INSERT INTO "SYS_LOG" VALUES ('asdqwe567e7833a3475b29b7441asdqw', '1', '用户名: cxx,登录成功!', NULL, NULL, NULL, '127.0.0.1', NULL, NULL, NULL, NULL, NULL, 'jeecg-boot', TO_DATE('2019-01-22 14:21:31', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL);
select查询
#define _CRT_SECURE_NO_WARNINGS //这个宏定义最好要放到.c文件的第一行 #include <stdio.h> #include <stdlib.h> #include <string.h> #include <oci.h> static text *username = (text *) "scott"; static text *password = (text *) "oracle"; /* Define SQL statements to be used in program. ,LOG_TYPE,LOG_CONTENT */ static text *selectlogbytype = (text *)"SELECT ID,LOG_CONTENT,LOG_TYPE FROM SYS_LOG WHERE LOG_TYPE = 1"; static OCIEnv *envhp; static OCIError *errhp; static void checkerr(/*_ OCIError *errhp, sword status _*/); static void cleanup(/*_ void _*/); static void myfflush(/*_ void _*/); int main(/*_ int argc, char *argv[] _*/); static sword status; int main() { //参数类型 //ub1 logid, logType,logContent; sb2 ind[3]; /* 指示符变量 */ OCIDescribe *dschndl1 = (OCIDescribe *)0, *dschndl2 = (OCIDescribe *)0, *dschndl3 = (OCIDescribe *)0; OCISession *authp = (OCISession *)0; /* 用户会话句柄 */ OCIServer *srvhp; /* 服务器句柄 */ OCISvcCtx *svchp; /* 服务句柄 */ OCIStmt *stmthp; OCIDefine *defnp = (OCIDefine *)0; OCIBind *bnd1p = (OCIBind *)0; /* the first bind handle */ OCIBind *bnd2p = (OCIBind *)0; /* the second bind handle */ OCIBind *bnd3p = (OCIBind *)0; /* the third bind handle */ OCIBind *bnd4p = (OCIBind *)0; /* the fourth bind handle */ OCIBind *bnd5p = (OCIBind *)0; /* the fifth bind handle */ OCIBind *bnd6p = (OCIBind *)0; /* the sixth bind handle */ sword errcode = 0; /* 将模式初始化为线程和对象环境 */ errcode = OCIEnvCreate((OCIEnv **)&envhp, (ub4)OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t)) 0, (void(*)(dvoid *, dvoid *)) 0, (size_t)0, (dvoid **)0); if (errcode != 0) { (void)printf("OCIEnvCreate failed with errcode = %d.\n", errcode); exit(1); } /* 分配一个错误句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0); /* 分配一个服务器句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0); /* 分配一个服务句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, (size_t)0, (dvoid **)0); //(void)OCIServerAttach(srvhp, errhp, (text *)"", strlen(""), 0); //连接远程服务器 (void)OCIServerAttach(srvhp, errhp, (text *)"82.156.213.852:1521/oracle", strlen("82.156.213.852:1521/oracle"), 0); /* 在服务上下文句柄中设置服务器属性*/ (void)OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errhp); /* 分配一个用户会话句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&authp, (ub4)OCI_HTYPE_SESSION, (size_t)0, (dvoid **)0); /* 在用户会话句柄中设置用户名属性 */ (void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION, (dvoid *)username, (ub4)strlen((char *)username), (ub4)OCI_ATTR_USERNAME, errhp); /* 在用户会话句柄中设置密码属性 */ (void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION, (dvoid *)password, (ub4)strlen((char *)password),(ub4)OCI_ATTR_PASSWORD, errhp); checkerr(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS,(ub4)OCI_DEFAULT)); /* 在服务上下文句柄中设置用户会话属性*/ (void)OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)authp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhp); checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0)); //定义变量的类型 ,LOG_TYPE,LOG_CONTENT text logId[50]; text logContent[100]; int logType; //准备sql语句 checkerr(errhp, OCIStmtPrepare(stmthp, errhp, selectlogbytype, (ub4)strlen((char *)selectlogbytype), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); //绑定输出列 checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 1, (ub1*)logId, sizeof(logId), SQLT_STR, &ind[0], (ub2 *)0,(ub2 *)0, OCI_DEFAULT)); checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp,2, (ub1*)logContent, sizeof(logContent), SQLT_STR, &ind[1], (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 3, (dvoid *)&logType, (sb4)sizeof(int), SQLT_INT, &ind[2], (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT)) ) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } else { //用do while是因为 先执行一次 do { printf("logId=%s,logContent=%s,logType=%d\n", logId, logContent, logType); } while ((OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) != OCI_NO_DATA); } } void checkerr(errhp, status) OCIError *errhp; sword status; { text errbuf[512]; sb4 errcode = 0; switch (status) { case OCI_SUCCESS: break; case OCI_SUCCESS_WITH_INFO: (void)printf("Error - OCI_SUCCESS_WITH_INFO\n"); break; case OCI_NEED_DATA: (void)printf("Error - OCI_NEED_DATA\n"); break; case OCI_NO_DATA: (void)printf("Error - OCI_NODATA\n"); break; case OCI_ERROR: (void)OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR); (void)printf("Error - %.*s\n", 512, errbuf); break; case OCI_INVALID_HANDLE: (void)printf("Error - OCI_INVALID_HANDLE\n"); break; case OCI_STILL_EXECUTING: (void)printf("Error - OCI_STILL_EXECUTE\n"); break; case OCI_CONTINUE: (void)printf("Error - OCI_CONTINUE\n"); break; default: break; } } /* * Exit program with an exit code. */ void cleanup() { if (envhp) (void)OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV); return; } void myfflush() { eb1 buf[50]; fgets((char *)buf, 50, stdin); } /* end of file cdemo81.c */
insert插入
#define _CRT_SECURE_NO_WARNINGS //这个宏定义最好要放到.c文件的第一行 #include <stdio.h> #include <stdlib.h> #include <string.h> #include <oci.h> static text *username = (text *) "scott"; static text *password = (text *) "oracle"; /* Define SQL statements to be used in program. ,LOG_TYPE,LOG_CONTENT */ static text *selectlogbytype = (text *)"SELECT ID,LOG_CONTENT,LOG_TYPE FROM SYS_LOG WHERE LOG_TYPE = 1"; static text *insertsql = (text *)"INSERT INTO SYS_LOG(ID, LOG_CONTENT,LOG_TYPE) VALUES (:logId, :logContent, :logType)"; static OCIEnv *envhp; static OCIError *errhp; static void checkerr(/*_ OCIError *errhp, sword status _*/); static void cleanup(/*_ void _*/); static void myfflush(/*_ void _*/); int main(/*_ int argc, char *argv[] _*/); static sword status; int main() { //参数类型 //ub1 logid, logType,logContent; sb2 ind[3]; /* 指示符变量 */ OCIDescribe *dschndl1 = (OCIDescribe *)0, *dschndl2 = (OCIDescribe *)0, *dschndl3 = (OCIDescribe *)0; OCISession *authp = (OCISession *)0; /* 用户会话句柄 */ OCIServer *srvhp; /* 服务器句柄 */ OCISvcCtx *svchp; /* 服务句柄 */ OCIStmt *inserthp, *stmthp; OCIDefine *defnp = (OCIDefine *)0; OCIBind *bnd1p = (OCIBind *)0; /* the first bind handle */ OCIBind *bnd2p = (OCIBind *)0; /* the second bind handle */ OCIBind *bnd3p = (OCIBind *)0; /* the third bind handle */ OCIBind *bnd4p = (OCIBind *)0; /* the fourth bind handle */ OCIBind *bnd5p = (OCIBind *)0; /* the fifth bind handle */ OCIBind *bnd6p = (OCIBind *)0; /* the sixth bind handle */ sword errcode = 0; /* 将模式初始化为线程和对象环境 */ errcode = OCIEnvCreate((OCIEnv **)&envhp, (ub4)OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t)) 0, (void(*)(dvoid *, dvoid *)) 0, (size_t)0, (dvoid **)0); if (errcode != 0) { (void)printf("OCIEnvCreate failed with errcode = %d.\n", errcode); exit(1); } /* 分配一个错误句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0); /* 分配一个服务器句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0); /* 分配一个服务句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, (size_t)0, (dvoid **)0); (void)OCIServerAttach(srvhp, errhp, (text *)"", strlen(""), 0); //连接远程服务器 //(void)OCIServerAttach(srvhp, errhp, (text *)"82.156.213.852:1521/oracle", strlen("82.156.213.852:1521/oracle"), 0); /* 在服务上下文句柄中设置服务器属性*/ (void)OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errhp); /* 分配一个用户会话句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&authp, (ub4)OCI_HTYPE_SESSION, (size_t)0, (dvoid **)0); /* 在用户会话句柄中设置用户名属性 */ (void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION, (dvoid *)username, (ub4)strlen((char *)username), (ub4)OCI_ATTR_USERNAME, errhp); /* 在用户会话句柄中设置密码属性 */ (void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION, (dvoid *)password, (ub4)strlen((char *)password), (ub4)OCI_ATTR_PASSWORD, errhp); checkerr(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT)); /* 在服务上下文句柄中设置用户会话属性*/ (void)OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)authp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhp); checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&inserthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0)); /**当我们绑定insert语句时,我们还需要分配存储空间 因此将在分配语句句柄时分配它;这将在语句消失且内容减少时获得释放碎片化。+2,以允许\\n和\\0**/ //insert的字段 sword insert_type; text *insert_id, *insert_content; sb4 idlen = 32; sb4 typelen = 11; sb4 contentlen = 50; checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&inserthp, OCI_HTYPE_STMT, (size_t)idlen + 2, (dvoid **)&insert_id)); insert_id = "asdww1111"; insert_content = "asdadffff2"; insert_type = 2; //准备insert sql语句 checkerr(errhp, OCIStmtPrepare(inserthp, errhp, insertsql, (ub4)strlen((char *)insertsql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); sb2 sal_ind, job_ind; sword empno, sal, deptno; /* Bind the placeholders in the INSERT statement. */ if ((status = OCIBindByName(inserthp, &bnd1p, errhp, (text *) ":logId", -1, (dvoid *)insert_id, idlen + 1, SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) || (status = OCIBindByName(inserthp, &bnd2p, errhp, (text *) ":logContent", -1, (dvoid *)insert_content, contentlen + 1, SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) || (status = OCIBindByName(inserthp, &bnd3p, errhp, (text *) ":logType", -1, &insert_type, (sword) sizeof(insert_type), SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT))) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } //执行insert语句 if ((status = OCIStmtExecute(svchp, inserthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT)) && status != 1) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } /* //定义变量的类型 ,LOG_TYPE,LOG_CONTENT text logId[50]; text logContent[100]; int logType; //准备select sql语句 checkerr(errhp, OCIStmtPrepare(stmthp, errhp, selectlogbytype, (ub4)strlen((char *)selectlogbytype), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); //绑定输出列 checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 1, (ub1*)logId, sizeof(logId), SQLT_STR, &ind[0], (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 2, (ub1*)logContent, sizeof(logContent), SQLT_STR, &ind[1], (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 3, (dvoid *)&logType, (sb4)sizeof(int), SQLT_INT, &ind[2], (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT)) ) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } else { //用do while是因为 先执行一次 do { printf("logId=%s,logContent=%s,logType=%d\n", logId, logContent, logType); } while ((OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) != OCI_NO_DATA); } */ /* Commit the change. */ if (status = OCITransCommit(svchp, errhp, 0)) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } } void checkerr(errhp, status) OCIError *errhp; sword status; { text errbuf[512]; sb4 errcode = 0; switch (status) { case OCI_SUCCESS: break; case OCI_SUCCESS_WITH_INFO: (void)printf("Error - OCI_SUCCESS_WITH_INFO\n"); break; case OCI_NEED_DATA: (void)printf("Error - OCI_NEED_DATA\n"); break; case OCI_NO_DATA: (void)printf("Error - OCI_NODATA\n"); break; case OCI_ERROR: (void)OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR); (void)printf("Error - %.*s\n", 512, errbuf); break; case OCI_INVALID_HANDLE: (void)printf("Error - OCI_INVALID_HANDLE\n"); break; case OCI_STILL_EXECUTING: (void)printf("Error - OCI_STILL_EXECUTE\n"); break; case OCI_CONTINUE: (void)printf("Error - OCI_CONTINUE\n"); break; default: break; } } /* * Exit program with an exit code. */ void cleanup() { if (envhp) (void)OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV); return; } void myfflush() { eb1 buf[50]; fgets((char *)buf, 50, stdin); } /* end of file cdemo81.c */
-
(window)C语言OCI的方式连接oracle
2022-04-16 18:29:52/* 在服务上下文句柄中设置服务器属性*/ (void)OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errhp); /* 分配一个用户会话句柄 */ (void)...Oracle创建表数据
说明: 我们需要通用的实验数据,emp表 与 dept表 但是数据库中有没有。 这时,我们可以手动创建。
Oracle数据库sql语句练习【emp和dept的连表查询由浅入深】-- 创建表与数据 CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2) ); INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('2022-04-11', 'yyyy-mm-dd'), 800, NULL, 20); INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('2022-04-12', 'yyyy-mm-dd'), 1600, 300, 30); INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('2022-03-12', 'yyyy-mm-dd'), 1250, 500, 30); INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2022-03-13', 'yyyy-mm-dd'), 2975, NULL, 20); INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('2022-03-14', 'yyyy-mm-dd'), 1250, 1400, 30); INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('2022-03-15', 'yyyy-mm-dd'), 2850, NULL, 30); INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('2022-03-16', 'yyyy-mm-dd'), 2450, NULL, 10); INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('2022-03-17', 'yyyy-mm-dd'), 3000, NULL, 20); INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('2022-03-18', 'yyyy-mm-dd'), 5000, NULL, 10); INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('2022-03-19', 'yyyy-mm-dd'), 1500, 0, 30); INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('2022-03-20', 'yyyy-mm-dd'), 1100, NULL, 20); INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('2022-03-21', 'yyyy-mm-dd'), 950, NULL, 30); INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('2022-03-22', 'yyyy-mm-dd'), 3000, NULL, 20); INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('2022-03-23', 'yyyy-mm-dd'), 1300, NULL, 10); CREATE TABLE DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13) ); INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON '); select *from emp select *from dept -- 添加约束(可选) alter table emp add constraint emp_pk primary key(empno); alter table dept add constraint dept_pk primary key(deptno); alter table dept add constraint emp_fk_dept foreign key(deptno) references dept; alter table emp add constraint emp_fk_emp foreign key(mgr) references emp;
VsCode新建项目
如果不知道自己的oracle安装路径,就看附录中的查看方法
项目配置
配置包含目录(头文件)
配置属性–>VC++目录–>包含目录(头文件)–》添加路径(注意最上方的一行 配置根据实际情况选,如果不知道按照图片上的选)
E:\oracle11g\product\11.2.0\dbhome_1\OCI\include
配置库目录(库目录)
配置属性–>VC++目录–>库目录–》添加路径(注意最上方的一行 配置根据实际情况选,如果不知道按照图片上的选)
E:\oracle11g\product\11.2.0\dbhome_1\OCI\lib\MSVC
配置依赖项
配置属性–>链接器–>输入–>附加依赖项–>oci.lib(注意最上方的一行 配置根据实际情况选,如果不知道按照图片上的选)
E:\oracle11g\product\11.2.0\dbhome_1\OCI\lib\MSVC
关闭SDL检查
配置属性–>C/C+±->SDL检查–>否(注意最上方的一行 配置根据实际情况选,如果不知道按照图片上的选)
代码部分
引入头文件
右键头文件-点击添加-选择现有项。将OCI\include的oci.h引入进来。
C代码
#define _CRT_SECURE_NO_WARNINGS //这个宏定义最好要放到.c文件的第一行 #include <stdio.h> #include <stdlib.h> #include <string.h> #include <oci.h> static text *username = (text *) "scott"; static text *password = (text *) "oracle"; /* Define SQL statements to be used in program. */ static text *insert = (text *)"INSERT INTO emp(empno, ename, job, sal, deptno)\ VALUES (:empno, :ename, :job, :sal, :deptno)"; static text *seldept = (text *)"SELECT dname FROM dept WHERE deptno = :1"; static text *maxemp = (text *)"SELECT NVL(MAX(empno), 0) FROM emp"; static text *selemp = (text *)"SELECT ename, job FROM emp"; static OCIEnv *envhp; static OCIError *errhp; static void checkerr(/*_ OCIError *errhp, sword status _*/); static void cleanup(/*_ void _*/); static void myfflush(/*_ void _*/); int main(/*_ int argc, char *argv[] _*/); static sword status; int main() { sword empno, sal, deptno; sword len, len2, rv, dsize, dsize2; sb4 enamelen = 10; sb4 joblen = 9; sb4 deptlen = 14; sb2 sal_ind, job_ind; sb2 db_type, db2_type; sb1 name_buf[20], name2_buf[20]; text *cp, *ename, *job, *dept; sb2 ind[2]; /* indicator */ ub2 alen[2]; /* actual length */ ub2 rlen[2]; /* return length */ OCIDescribe *dschndl1 = (OCIDescribe *)0, *dschndl2 = (OCIDescribe *)0, *dschndl3 = (OCIDescribe *)0; OCISession *authp = (OCISession *)0; /* 用户会话句柄 */ OCIServer *srvhp; /* 服务器句柄 */ OCISvcCtx *svchp; /* 服务句柄 */ OCIStmt *inserthp, *stmthp, *stmthp1; OCIDefine *defnp = (OCIDefine *)0; OCIBind *bnd1p = (OCIBind *)0; /* the first bind handle */ OCIBind *bnd2p = (OCIBind *)0; /* the second bind handle */ OCIBind *bnd3p = (OCIBind *)0; /* the third bind handle */ OCIBind *bnd4p = (OCIBind *)0; /* the fourth bind handle */ OCIBind *bnd5p = (OCIBind *)0; /* the fifth bind handle */ OCIBind *bnd6p = (OCIBind *)0; /* the sixth bind handle */ sword errcode = 0; /* 将模式初始化为线程和对象环境 */ errcode = OCIEnvCreate((OCIEnv **)&envhp, (ub4)OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t)) 0, (void(*)(dvoid *, dvoid *)) 0, (size_t)0, (dvoid **)0); if (errcode != 0) { (void)printf("OCIEnvCreate failed with errcode = %d.\n", errcode); exit(1); } /* 分配一个错误句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0); /* 分配一个服务器句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0); /* 分配一个服务句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, (size_t)0, (dvoid **)0); /* 创建服务器上下文 指定要使用的数据库服务器。该参数指向一个字符串,该字符串指定一个连接字符串或一个服务点。 如果连接字符串是NULL,则此调用附加到默认主机。 字符串本身可能处于UTF-16编码模式,也可能不处于编码模式,这取决于mode应用程序环境句柄中的 或设置的长度在dblink中指定dblink_len。 调用者可以在dblink返回时释放指针*/ (void)OCIServerAttach(srvhp, errhp, (text *)"", strlen(""), 0); /* 在服务上下文句柄中设置服务器属性*/ (void)OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errhp); /* 分配一个用户会话句柄 */ (void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&authp, (ub4)OCI_HTYPE_SESSION, (size_t)0, (dvoid **)0); /* 在用户会话句柄中设置用户名属性 */ (void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION, (dvoid *)username, (ub4)strlen((char *)username), (ub4)OCI_ATTR_USERNAME, errhp); /* 在用户会话句柄中设置密码属性 */ (void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION, (dvoid *)password, (ub4)strlen((char *)password), (ub4)OCI_ATTR_PASSWORD, errhp); checkerr(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT)); /* 在服务上下文句柄中设置用户会话属性*/ (void)OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)authp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhp); checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0)); checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp1, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0)); /* Retrieve the current maximum employee number. */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, maxemp, (ub4)strlen((char *)maxemp), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* bind the input variable */ checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 1, (dvoid *)&empno, (sword) sizeof(sword), SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* execute and fetch */ /*将应用程序请求与服务器相关联。*/ if (status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT)) { if (status == OCI_NO_DATA) empno = 10; else { checkerr(errhp, status); cleanup(); return OCI_ERROR; } } /* * When we bind the insert statement we also need to allocate the storage * of the employee name and the job description. * Since the lifetime of these buffers are the same as the statement, we * will allocate it at the time when the statement handle is allocated; this * will get freed when the statement disappears and there is less * fragmentation. * * sizes required are enamelen+2 and joblen+2 to allow for \n and \0 * */ checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&inserthp, OCI_HTYPE_STMT, (size_t)enamelen + 2 + joblen + 2, (dvoid **)&ename)); job = (text *)(ename + enamelen + 2); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, insert, (ub4)strlen((char *)insert), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp1, errhp, seldept, (ub4)strlen((char *)seldept), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Bind the placeholders in the INSERT statement. */ if ((status = OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":ENAME", -1, (dvoid *)ename, enamelen + 1, SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) || (status = OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":JOB", -1, (dvoid *)job, joblen + 1, SQLT_STR, (dvoid *)&job_ind, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) || (status = OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":SAL", -1, (dvoid *)&sal, (sword) sizeof(sal), SQLT_INT, (dvoid *)&sal_ind, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) || (status = OCIBindByName(stmthp, &bnd4p, errhp, (text *) ":DEPTNO", -1, (dvoid *)&deptno, (sword) sizeof(deptno), SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) || (status = OCIBindByName(stmthp, &bnd5p, errhp, (text *) ":EMPNO", -1, (dvoid *)&empno, (sword) sizeof(empno), SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT))) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } /* Bind the placeholder in the "seldept" statement. */ if (status = OCIBindByPos(stmthp1, &bnd6p, errhp, 1, (dvoid *)&deptno, (sword) sizeof(deptno), SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } /* Allocate the dept buffer now that you have length. */ /* the deptlen should eventually get from dschndl3. */ deptlen = 14; dept = (text *)malloc((size_t)deptlen + 1); /* Define the output variable for the select-list. */ if (status = OCIDefineByPos(stmthp1, &defnp, errhp, 1, (dvoid *)dept, deptlen + 1, SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } for (;;) { /* Prompt for employee name. Break on no name. */ printf("\nEnter employee name (or CR to EXIT): "); fgets((char *)ename, (int)enamelen + 1, stdin); cp = (text *)strchr((char *)ename, '\n'); if (cp == ename) { printf("Exiting... "); cleanup(); return OCI_SUCCESS; } if (cp) *cp = '\0'; else { printf("Employee name may be truncated.\n"); myfflush(); } /* Prompt for the employee's job and salary. */ printf("Enter employee job: "); job_ind = 0; fgets((char *)job, (int)joblen + 1, stdin); cp = (text *)strchr((char *)job, '\n'); if (cp == job) { job_ind = -1; /* make it NULL in table */ printf("Job is NULL.\n");/* using indicator variable */ } else if (cp == 0) { printf("Job description may be truncated.\n"); myfflush(); } else *cp = '\0'; printf("Enter employee salary: "); scanf("%d", &sal); myfflush(); sal_ind = (sal <= 0) ? -2 : 0; /* set indicator variable */ /* * Prompt for the employee's department number, and verify * that the entered department number is valid * by executing and fetching. */ do { printf("Enter employee dept: "); scanf("%d", &deptno); myfflush(); if ((status = OCIStmtExecute(svchp, stmthp1, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT)) && (status != OCI_NO_DATA)) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } if (status == OCI_NO_DATA) printf("The dept you entered doesn't exist.\n"); } while (status == OCI_NO_DATA); /* * Increment empno by 10, and execute the INSERT * statement. If the return code is 1 (duplicate * value in index), then generate the next * employee number. */ empno += 10; if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT)) && status != 1) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } while (status == 1) { empno += 10; if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT)) && status != 1) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } } /* end for (;;) */ /* Commit the change. */ if (status = OCITransCommit(svchp, errhp, 0)) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } printf("\n\n%s added to the %s department as employee number %d\n", ename, dept, empno); } } void checkerr(errhp, status) OCIError *errhp; sword status; { text errbuf[512]; sb4 errcode = 0; switch (status) { case OCI_SUCCESS: break; case OCI_SUCCESS_WITH_INFO: (void)printf("Error - OCI_SUCCESS_WITH_INFO\n"); break; case OCI_NEED_DATA: (void)printf("Error - OCI_NEED_DATA\n"); break; case OCI_NO_DATA: (void)printf("Error - OCI_NODATA\n"); break; case OCI_ERROR: (void)OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR); (void)printf("Error - %.*s\n", 512, errbuf); break; case OCI_INVALID_HANDLE: (void)printf("Error - OCI_INVALID_HANDLE\n"); break; case OCI_STILL_EXECUTING: (void)printf("Error - OCI_STILL_EXECUTE\n"); break; case OCI_CONTINUE: (void)printf("Error - OCI_CONTINUE\n"); break; default: break; } } /* * Exit program with an exit code. */ void cleanup() { if (envhp) (void)OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV); return; } void myfflush() { eb1 buf[50]; fgets((char *)buf, 50, stdin); } /* end of file cdemo81.c */
Q&A
windows服务器下oracle数据库查看客户端安装位置
win+R键调出运行界面,输入 services.msc
bin 前面的路径就是 oracle 的根目录。
库计算机类型“x64”与目标计算机类型“x86”冲突
E:\oracle11g\product\11.2.0\dbhome_1\OCI\lib\MSVC\oci.lib : warning LNK4272: 库计算机类型“x64”与目标计算机类型“x86”冲突
两种解决方式
- 调整编译程序的类型
- 重新引入依赖项引入ociw32.lib的文件
配置属性–>链接器–>输入–>附加依赖项–>ociw32.lib(注意最上方的一行 配置根据实际情况选,如果不知道按照图片上的选)
附录
oracle的OCI目录下没有samples包
如果装的oracle不是完整版,oci目录下就没有samples包,这时就需要单独安装一下。我这里的版本是oracle11g,配套的版本在官网链接如下,里面win64_11gR2_examples.zip
https://www.oracle.com/cn/database/technologies/microsoft-windows.html
下载下来以后,双击安装就会在oracle的OCI目录下生成samples目录,如果安装失败了,就直接去资料包中网盘中去下载samples包吧。
samples文件夹中 每个c文件的Demo含义,参考官方文档https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/ociabdem.htmoci的手册下载
https://docs.oracle.com/cd/E11882_01/nav/portal_5.htm
参考
11g oci的手册
https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci02bas.htm
11g oci的simple的demo列表
https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/ociabdem.htm
21 oci的手册
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/oci-programming-basics.html
21 oci的simple的demo列表
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/oci-demo-programs.html资料包
链接:https://pan.baidu.com/s/1ICPj3eHMDCxpi5zdWcsdag
提取码:jgeg
–来自百度网盘超级会员V4的分享 -
C语言OCI操作oracle类
2010-03-25 09:14:55用oracle oci接口函数封装了自己的操作oracle的类,使用简单方便。 -
oci 编程教程oci函数的详细介绍
2012-06-09 03:27:55oci函数的详细介绍 和应用实例 OCI 连接过程比较复杂,除了分配设置各个基本句柄外,还要明确彼此之间的联系,大致流程如下: 创建环境句柄: OCIEnvCreate(&envhp;, …); 创建一个指定环境的错误句柄: ... -
OCI数据类型与C语言数据类型问题
2021-05-21 05:16:27通过OCI函数执行数据库sql,提取值到内存中进行处理,执行sql如下select out_bytes/1024 from bf_discharge_info_t;...OCI数据类型与C语言数据类型对照表:表字段类型 OCI类型 C类型备注Number(N)... -
SQLite教程(十四):C语言编程实例代码(2)
2021-01-21 15:07:33纵观众多流行的数据库接口,如OCI(Oracle API)、MySQL API和PostgreSQL API等,OCI提供的编程接口最为方便,实现方式也最为高效。SQLite作为一种简单灵活的嵌入式数据库也同样提供了该功能,但是实现方式并不像其他... -
C程序设计----UNIX系统接口
2015-09-06 21:41:351.fsize程序是ls命令的一个特殊形式,它打印命令行参数表中指定的所有文件的长度。 void fsize(char *name); //打印文件name的长度2.每个目录中都包含自身“.” 和父目录“..”的项目 -
C语言DCI(OCI)方式连接DM数据库
2021-12-26 21:32:21C语言DCI(OCI)方式连接DM数据库 一、背景 近期用户使用DCI的方式连接达梦数据库出现中文乱码的问题,所以决定写一个测试Demo。 因为使用DCI的方式连接,适配中心... -
学习OCI编程
2014-10-14 18:12:00最近公司做的一个项目,要处理海量数据,数据是存放在Oracle数据库里,刚开始用的是ADO访问,速度极慢,后来改用Proc,效果还是不如人意,最后才用的OCI。因为之前对OCI不了解过,经2个星期的努力,终于完成了对... -
linux下用C编写的OCI连接Oracle数据库程序代码
2021-05-02 08:24:29/* Initialize OCI evironment*/ rc = OCIEnvCreate((OCIEnv **) &p_env,OCI_DEFAULT,(dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0,... -
oracle oci api
2021-05-07 06:57:00oci=C:\php-sdk\oracle\instantclient10\sdk,shared" "--with-oci8=C:\php-sdk\oracle\instantclient10\sdk,shared" "--with-oci8-11g=C:\php-sdk\oracle\instantclient11\sdk,shared" "--enable-object-out-dir=../... -
OCI--学习OCI编程
2014-02-14 14:56:08学习OCI编程 2011-07-26 16:18:39| 分类: 数据库 | 标签:oci |举报|字号 订阅 最近公司做的一个项目,要处理海量数据,数据是存放在Oracle数据库里,刚开始用的是ADO访问,速度极慢,后来改用... -
linux c oci 配置环境 编程 详解
2021-05-16 03:45:10为了直接操作oracle数据的内核,是存取的更快,我选择了OCI——oracle提供的c语言接口,来作为开发的工具。首先我先查了很多关于OCI提供的函数资料,这些资料如果你有需要联系我,我会发到你的邮箱里:yzzhang.gw.... -
C函数篇(OCI函数)----oracle编程
2021-01-20 14:26:361、Oracle常用的OCI函数----https://blog.csdn.net/weixin_34080571/article/details/85483158?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-3&spm=1001.2101.3001.4242 2、O -
OCI编程
2013-11-28 10:08:46最近公司做的一个项目,要处理海量数据,数据是存放在Oracle数据库里,刚开始用的是ADO访问,速度极慢,后来改用Proc,效果还是不如人意,最后才用的OCI。因为之前对OCI不了解过,经2个星期的努力,终于完成了对... -
oci编程,避免报错误ORA-01405: 提取的列值为 NULL
2021-05-03 05:57:32指示器变量:由于在Oracle中,列值可以为NULL,但在C语言中没有NULL值,为了能使OCI程序表达NULL列值,OCI函数允许程序为所执行语句中的结合变量同时关联一个指示符变量或指示符变量数组,以说明所结合的占位符是否... -
基于Oracle OCI的数据访问C语言接口ORADBI
2015-06-30 14:36:06基于Oracle OCI的数据访问C语言接口ORADBI cheungmine@gmail.com ORADBI是我在Oracle OCI(Oracle 调用接口)基础上开发的,支持Oracle8i、9i、10g等数据库。根据Oracle公司的介绍:OCI是如此可靠,以至于... -
用C语言实现的扑克牌洗牌程序
2021-05-20 02:55:20PHP操作Oracle数据库 原文出处 (这是来自“百度文库”中的文章写得很不错) PHP操作Oracle数据库(OCI数据抽象层)OCI(Oracle 8 Call-Interface)是PHP中内置的数据库抽象层函数.下面针对 ... 生成mif文件的几种方法... -
C语言连接Oracle数据库
2020-03-31 09:42:20Oracle数据库功能强大,性能卓越,无与伦比,并提供了数据访问接口OCI,OCI非常强大,强大到了普通C/C++程序员难以驾驭。 freecplus框架把OCI(Oracle Call Interface)封装成了connection和sqlstatement类,采用... -
极简OCI连接TimesTen程序
2016-07-17 11:32:47相较于Pro*C,OCI编程稍显复杂。但OCI提供更细粒度的控制,提供更丰富的功能和更好的性能。因此,对于熟悉C语言的编程人员,为性能和可控制性,以及易于调试,都应该首选OCI而非Pro*C。和Pro*C一样, OCI也支持用TNS... -
数据库基础、使用C语言构建一个数据库、SQL语言、MySQL
2021-11-20 23:53:48数据库发展和展望二、使用C语言实现一个简单的数据库三、使用C语言连接数据库四、SQL语言和MySQL教程1.SQL是什么1) DDL - Data Definition Language,数据定义语言2) DML - Data Manipulation Language,数据处理... -
[Unix下基于OCI的Oracle数据库的访问]访问数据库
2021-05-06 02:38:08【摘 要】文章对比了Unix下几种访问Oracle数据库的访你问方式的同时,深入介绍了通过OCI接口对数据库的操作方法,并给出了具体实例。另外,例子中通过对OCI函数的封装极大地方便了对Oracle数据库的操作。【关键词】... -
OCI 简介
2011-10-09 19:43:36OCI(Oracle Call Interface)是ORACLE公司开发的一个应用程序开发工具,是一个通过访问Oracle数据库的服务器,控制各类SQL语句的执行,进而创建应用程序的的应用程序接口(API)。它支持SQL所有的数据定义,数据... -
关于OCI编程基础的学习(一)
2012-11-27 20:05:55最近看老师讲的OCI的编程视频,代码太多并且很多函数不了解,于是从网上找了一些资料供大家参考: ...一:系统环境:要想使用OCI编程需要安装Oracle的客户端,而这个普通的客户端比较大,在Oracle10g版本后推出了大小只 -
OCI简介
2019-06-27 17:14:38oci - 简介 OCI(Oracle Call Interface)是ORACLE公司开发的一个应用程序开发工具,是一个通过访问Oracle数据库的服务器,控制各类SQL语句的执行,进而创建应用程序的的应用程序接口(API)。它支持SQL所有的...