OCI读取单条记录(C)

开发环境：fedora12 + oracle-client11 + eclipse

目标数据库：oracle10g

一、准备

1. 建表

Create Table testTable_OCI
(
       SID                int primary key,
       Seq                varchar2(10),
       UName              varchar2(20),
       Introduce          VARCHAR2(2000),
       Intime             DATE default sysdate
)

-- Create sequence 
create sequence TESTTABLE_OCISEQ
minvalue 1
maxvalue 9999999999999999999999
start with 1
increment by 1
cache 20
cycle;

2. 建立存储过程（插入记录）

Create or Replace Procedure proc_OCI_Insert
is
       curID int;
begin
       select TESTTABLE_OCISEQ.Nextval into curID from dual;
       insert into testtable_oci(SID,Seq,UName,Introduce) values(curID, 'Seq'||curID, 'Name'||curID, 'Introduce_'||curID);
end;

二、代码

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

text *username = (text*)"scott";
text *password = (text*)"scott";
text *servicename = (text*)"orcl";

OCIEnv 		*envhp;						// 环境句柄
OCIServer 		*srvhp;						// 服务器句柄
OCISvcCtx		*svchp;						// 服务上下文句柄
OCIError		*errhp;						// 错误句柄
OCISession		*usrhp;						// 用户会话句柄
OCIStmt		*stmthp;						// 语句句柄
OCIBind		*bndhp;						// 绑定句柄


int init_env_handle()
{
	// 创建OCI环境： 任何OCI函数都是在这个环境上下文运行的
	if(OCIEnvCreate((OCIEnv**)&envhp, (ub4)OCI_OBJECT, (void*)0, (void*(*)(void*,size_t))0,
			(void*(*)(void*, void*, size_t))0, (void(*)(void*, void*))0, (size_t)0, (void**)0))
	{
		printf("FAILED: OCIEnvCreate()\n");
		return 0;
	}
	sword status = 0;
	if(status = OCIHandleAlloc((void*)envhp, (void**)&errhp, OCI_HTYPE_ERROR, (size_t)0, 0))
	{
		printf("Failed: OCIHandleAlloc() on errhp\n");
		return 0;
	}
	if(status = OCIHandleAlloc((void*)envhp, (void**)&srvhp, OCI_HTYPE_SERVER, (size_t)0, 0))
	{
		printf("Failed: OCIHandleAlloc() on srvhp\n");
		return 0;
	}
	if(status = OCIHandleAlloc((void*)envhp, (void**)&svchp, OCI_HTYPE_SVCCTX, (size_t)0, 0))
	{
		printf("Failed: OCIHandleAlloc() on svchp\n");
		return 0;
	}
	if(status = OCIHandleAlloc((void*)envhp, (void**)&usrhp, OCI_HTYPE_SESSION, (size_t)0, 0))
	{
		printf("Failed: OCIHandleAlloc() on usrhp\n");
		return 0;
	}
	if(status = OCIHandleAlloc((void*)envhp, (void**)&stmthp, OCI_HTYPE_STMT, (size_t)0, 0))
	{
		printf("Failed: OCIHandleAlloc() on stmthp\n");
		return 0;
	}
	return 1;
}

int connect_server()
{
	sword status = 0;
	// 连接数据库
	if(status = OCIServerAttach((OCIServer*)srvhp, (OCIError*)errhp, servicename, strlen(servicename), (ub4)OCI_DEFAULT))
	{
		printf("Failed: OCIServerAttach() on srvhp\n");
		return 0;
	}

	// 设置会话属性
	if(status = OCIAttrSet((void*)svchp, OCI_HTYPE_SVCCTX, (void*)srvhp, 0, OCI_ATTR_SERVER, errhp))
	{
		printf("Failed: OCIAttrSet() on svchp\n");
		return 0;
	}
	if(status = OCIAttrSet((void*)usrhp, OCI_HTYPE_SESSION, (void*)username, strlen(username), OCI_ATTR_USERNAME, errhp))
	{
		printf("Failed: OCIAttrSet() on usrhp for user\n");
		return 0;
	}
	if(status = OCIAttrSet((void*)usrhp, OCI_HTYPE_SESSION, (void*)password, strlen(password), OCI_ATTR_PASSWORD, errhp))
	{
		printf("Failed: OCIAttrSet() on usrhp for password\n");
		return 0;
	}

	// 开始会话
	if(status = OCISessionBegin((OCISvcCtx*)svchp, errhp, usrhp, OCI_CRED_RDBMS, OCI_DEFAULT))
	{
		printf("Failed: OCISessionBegin()\n");
		return 0;
	}
	printf("Session begin succssfully!\n");

	// 设置会话属性
	if(status = OCIAttrSet((void*)svchp, OCI_HTYPE_SVCCTX, (void*)usrhp, 0, OCI_ATTR_SESSION, errhp))
	{
		printf("Failed: OCIAttrSet() on svchp\n");
		return 0;
	}
	return 1;
}

void cleanup()
{
	if(stmthp)
		OCIHandleFree(stmthp, OCI_HTYPE_STMT);
	if(usrhp)
		OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
	if(svchp)
		OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
	if(srvhp)
		OCIHandleFree(srvhp, OCI_HTYPE_SERVER);
	if(errhp)
		OCIHandleFree(errhp, OCI_HTYPE_ERROR);
	if(envhp)
		OCIHandleFree(envhp, OCI_HTYPE_ENV);
}

int disconnect_server()
{
	printf("Logged off and detached from server.\n");
	OCISessionEnd((OCISvcCtx*)svchp, errhp, usrhp, OCI_DEFAULT);
	OCIServerDetach((OCIServer*)srvhp, errhp, OCI_DEFAULT);

	return 1;
}

// 调用package取出结果集并返回
int getResult(OCIStmt *stmthp)
{
	int ret = 0;
	int s[5] = {0};
	int vSID = 1;
	char ch[3][1024] = {0};
	text sql[1024] = {0};
	ub4 ub4RecordNo = 1;
	sb4 sb4ErrorCode;
	char sErrorMsg[1024] = {0};
	OCIDefine *hDefine[2] = {NULL};
	sb2 sb2Ind[2] = {0};					// 所选择的列可能存在NULL，所以要用指示器变量

	sprintf(sql, "%s", "select UName,Introduce from TestTable_OCI where SID=:vSID");
	OCIStmtPrepare(stmthp, errhp, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

	s[1] = OCIDefineByPos(stmthp, &hDefine[0], errhp, 1, ch[0], sizeof(ch[0]), SQLT_STR, &sb2Ind[0], NULL, NULL, OCI_DEFAULT);
	s[2] = OCIDefineByPos(stmthp, &hDefine[1], errhp, 2, ch[1], sizeof(ch[1]), SQLT_STR, &sb2Ind[1], NULL, NULL, OCI_DEFAULT);

	for(vSID = 0; vSID < 12; vSID++)			// 循环绑定第一个输入变量以执行不同的sql语句
	{
		s[0] = OCIBindByPos(stmthp, &bndhp, errhp, 1, (void*)&vSID, sizeof(vSID), SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT);
		s[3] = OCIStmtExecute(svchp, stmthp, errhp, 1, 0, NULL, NULL, OCI_DEFAULT);
		if(s[3] == OCI_SUCCESS)
		{
			printf("%d: ch[0] = (%d)%s\n", vSID, strlen(ch[0]), ch[0]);
			printf("ch[1] = (%d)%s\n\n", strlen(ch[1]), ch[1]);
		}
		else if(s[3] == OCI_NO_DATA)
		{
			printf("%d: There is NO_DATA\n\n", vSID);
		}
		else
		{
			if(OCIErrorGet(errhp, 1, NULL, &sb4ErrorCode, (OraText*)sErrorMsg, sizeof(sErrorMsg), OCI_HTYPE_ERROR) == OCI_SUCCESS)
				printf("%d: OCIStmtExecute: Error Message = %s\n", vSID, sErrorMsg);
			return -1;
		}
	}
	return 0;
}

int main(void)
{
	if(init_env_handle())
	{
		if(connect_server())
		{
			printf("connect server successful.\n");

			getResult(stmthp);				// 提取结果集

			disconnect_server();
		}
		cleanup();
	}
	return EXIT_SUCCESS;
}

不要忘了添加环境变量（我的oracle客户端安装在/opt/oracle/）：