C语言实现Oracle数据库操作(c 实现oracle操作)

Oracle是一种十分流行的关系型数据库管理系统,被广泛应用于各种企业和组织中。C语言是一种高效、稳定和可移植性强的编程语言,能够很好地与Oracle数据库进行交互。本文将介绍如何使用C语言实现对Oracle数据库的常见操作。

一、Oracle数据库连接

要操作Oracle数据库,首先需要建立连接。Oracle提供了OCI(Oracle Call Interface)库来实现连接。OCI是Oracle公司提供的C语言编程接口,允许开发人员以C语言的方式操作Oracle数据库。具体实现步骤如下:

1.连接数据库

OCI连接函数为OCIInitialize、OCIEnvCreate、OCIHandleAlloc和OCILogon等。在连接Oracle数据库之前,需要先定义以下一些参数:

– 服务器名、端口号、用户名、密码以及数据库名等连接参数;

– 用于存放错误信息的缓冲区;

– 用于存放OCI环境句柄和会话句柄的指针变量。

代码示例:

“`c

OCIEnv* envhp = NULL; // OCI环境句柄

OCIServer* srverhp = NULL; // OCI服务器句柄

OCIError* errhp = NULL; // OCI错误句柄

OCISvcCtx* svchp = NULL; // OC服务上下文句柄

OCISession* sesshp = NULL; // OCI会话句柄

OCISessionPool* sesspoolhp = NULL; // OCI会话池句柄

char* dbuser = “username”;

char* dbpass = “password”;

char* dbname = “orcl”; // 或者“//localhost:1521/orcl”

char* dbhost = “localhost”;

unsigned int dbport = 1521;

sword ret = 0;

ret = OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL);

if (ret != OCI_SUCCESS)

{

printf(“OCIInitialize fled\n”);

exit(-1);

}

ret = OCIEnvCreate((OCIEnv**)&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);

if (ret != OCI_SUCCESS)

{

printf(“OCIEnvCreate fled\n”);

exit(-1);

}

ret = OCIHandleAlloc((dvoid*)envhp, (dvoid**)&errhp, OCI_HTYPE_ERROR, 0, NULL);

if (ret != OCI_SUCCESS)

{

printf(“OCIHandleAlloc fled\n”);

exit(-1);

}

ret = OCIHandleAlloc((dvoid*)envhp, (dvoid**)&srvhp, OCI_HTYPE_SERVER, 0, NULL);

if (ret != OCI_SUCCESS)

{

printf(“OCIHandleAlloc fled\n”);

exit(-1);

}

ret = OCIServerAttach(srvhp, errhp, (OraText*)dbname, strlen((char*)dbname), OCI_DEFAULT);

if (ret != OCI_SUCCESS)

{

printf(“OCIServerAttach fled\n”);

exit(-1);

}

ret = OCIHandleAlloc((dvoid*)envhp, (dvoid**)&svchp, OCI_HTYPE_SVCCTX, 0, NULL);

if (ret != OCI_SUCCESS)

{

printf(“OCIHandleAlloc fled\n”);

exit(-1);

}

ret = OCIHandleAlloc((dvoid*)envhp, (dvoid**)&sesshp, OCI_HTYPE_SESSION, 0, NULL);

if (ret != OCI_SUCCESS)

{

printf(“OCIHandleAlloc fled\n”);

exit(-1);

}

ret = OCISessionBegin(svchp, errhp, sesshp, OCI_CRED_RDBMS, OCI_DEFAULT);

if (ret != OCI_SUCCESS)

{

printf(“OCISessionBegin fled\n”);

}

const oratext *userid = (oratext *)dbuser;

const oratext *password = (oratext *)dbpass;

const oratext *dbname = (oratext *)dbname;

ub4 userid_len = strlen(dbuser);

ub4 password_len = strlen(dbpass);

ub4 dbname_len = strlen(dbname);

ret = OCILogon(envhp, errhp, &svchp, userid, userid_len, password, password_len, dbname, dbname_len);

if (ret != OCI_SUCCESS)

{

printf(“OCILogon fled\n”);

exit(-1);

}


2.断开连接

在使用完Oracle数据库之后,应该断开与数据库的连接,释放资源。可以使用OCILogoff和OCIHandleFree等函数来实现。

代码示例:

```c
ret = OCILogoff(svchp, errhp);
if (ret != OCI_SUCCESS)
{
printf("OCILogoff fled\n");
exit(-1);
}

ret = OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
if (ret != OCI_SUCCESS)
{
printf("OCIServerDetach fled\n");
exit(-1);
}
OCIHandleFree((dvoid*)envhp, (dvoid*)sesshp, OCI_HTYPE_SESSION);
OCIHandleFree((dvoid*)envhp, (dvoid*)svchp, OCI_HTYPE_SVCCTX);
OCIHandleFree((dvoid*)envhp, (dvoid*)srvhp, OCI_HTYPE_SERVER);
OCIHandleFree((dvoid*)envhp, (dvoid*)errhp, OCI_HTYPE_ERROR);
OCIHandleFree((dvoid*)envhp, (dvoid*)envhp, OCI_HTYPE_ENV);

二、Oracle数据库查询

使用C语言操作Oracle数据库查询的一种方法是使用OCI提供的OCIStmt、OCIDefine和OCIBind等函数。OCIStmt用于执行SQL语句,OCIDefine和OCIBind用于绑定占位符和结果集。具体步骤如下:

1.绑定占位符

在SQL语句中,可能会包含占位符以便于传递参数。OCIBind函数用于将占位符与变量绑定,并将其传递给Oracle数据库。首先需要定义以下一些参数:

– 定义存储绑定结果的变量类型、数据类型、数据长度等;

– 定义变量存储地址以及缓冲区大小等。

代码示例:

“`c

char name[20], passwd[20];

int id = 0;

OCIStmt* stmthp = NULL;

OCIDefine* defnp = NULL;

OCIBind* bndp = NULL;

ub2 id_len = sizeof(int);

ub2 name_len = sizeof(name);

ub2 passwd_len = sizeof(passwd);

char* sql = “SELECT * FROM users WHERE id=:1 AND name=:2 AND passwd=:3”;

memset(&id, 0, id_len);

memset(&name, 0, name_len);

memset(&passwd, 0, passwd_len);

ret = OCIStmtPrepare(stmthp, errhp, (text *)sql, (ub4)strlen(sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

if (ret != OCI_SUCCESS)

{

printf(“OCIStmtPrepare fled\n”);

exit(-1);

}

ret = OCIBindByName(stmthp, &bndp, errhp, (text*) “:1”, (sb4)strlen(“:1”), (dvoid *)&id,(sb4)id_len, SQLT_INT, NULL, NULL, NULL, 0, NULL, (ub2) OCI_DEFAULT);

if (ret != OCI_SUCCESS)

{

printf(“OCIBindByName fled\n”);

exit(-1);

}

ret = OCIBindByName(stmthp, &bndp, errhp, (text*) “:2”, (sb4)strlen(“:2”), (dvoid *)&name,(sb4)name_len, SQLT_STR, NULL, NULL, NULL, 0, NULL, (ub2) OCI_DEFAULT);

if (ret != OCI_SUCCESS)

{

printf(“OCIBindByName fled\n”);

exit(-1);

}

ret = OCIBindByName(stmthp, &bndp, errhp, (text*) “:3”, (sb4)strlen(“:3”), (dvoid *)&passwd,(sb4)passwd_len, SQLT_STR, NULL, NULL, NULL, 0, NULL, (ub2) OCI_DEFAULT);

if (ret != OCI_SUCCESS)

{

printf(“OCIBindByName fled\n”);

exit(-1);

}


2.执行SQL语句

具体操作步骤包括:

- 使用OCIStmtExecute函数执行SQL语句;
- 使用OCIDefineByPos或者OCIDefineByName将结果集绑定到相应变量;
- 使用OCIDefineGetXXX函数从结果集中获取数据。

代码示例:

```c
ret = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
if (ret != OCI_SUCCESS)
{
printf("OCIStmtExecute fled\n");
exit(-1);
}

OCIBindByName(stmthp

数据运维技术 » C语言实现Oracle数据库操作(c 实现oracle操作)