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等函数来实现。
代码示例:
```cret = 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函数从结果集中获取数据。
代码示例:
```cret = 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