开发环境: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/):