oracle oci,oracle oci 举例

看过了dcba的ociUldr的程序，非常有感触,七八年没编程了，忽然间有了写写程序的冲动，顺便也学学oci的编程。学习编程最好的方法是看demo。如下分享给大家一个demo,供入门用。

1 demo要实现的功能

以hr/hr用库连入orcl库，执行select first_name from employees where employee_id=102语句，并返回结果。其中oci编程的基本部分都有涉及。

2 demo脚本

#include #include #include #include

static OCIEnv           *p_env;

static OCIError         *p_err;

static OCISvcCtx        *p_svc;

static OCIStmt          *p_sql;

static OCIDefine        *p_dfn    = (OCIDefine *) 0;

static OCIBind          *p_bnd    = (OCIBind *) 0;

int main()

{

int             p_bvi;

char            *p_sli;

int             rc;

char            errbuf[100];

int             errcode;

char            mysql[100];

p_sli=(char *)malloc(20);

memset(p_sli,0,20);

/* 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,

(size_t) 0, (dvoid **) 0);

/* Initialize handles */

rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,

(size_t) 0, (dvoid **) 0);

rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,

(size_t) 0, (dvoid **) 0);

/* Connect to database server */

rc = OCILogon(p_env, p_err, &p_svc, "hr", 2, "hr", 2, "orcl", 4);

//rc = OCILogon(p_env, p_err, &p_svc, "", 10, NULL, -1, NULL, -1);

if (rc != 0) {

OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);

printf("Error - %.*s\n", 512, errbuf);

exit(8);

}

else

{

printf("Connect to orcl successful!\n");

}

/* Allocate and prepare SQL statement */

rc = OCIHandleAlloc((dvoid *) p_env, (dvoid **) &p_sql,

OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);

/* set my sql statement */

strcpy(mysql,"select first_name from employees where employee_id=:x");

rc = OCIStmtPrepare(p_sql, p_err, mysql,

(ub4) strlen(mysql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

/* Bind the values for the bind variables */

p_bvi = 102;  /* Use employee_id=102 */

rc = OCIBindByName(p_sql, &p_bnd, p_err, (text *) ":x",

-1, (dvoid *) &p_bvi, sizeof(int), SQLT_INT, (dvoid *) 0,

(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);

/* Define the select list items */

rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 1, (dvoid *) p_sli,

(sword) 20, SQLT_STR, (dvoid *) 0, (ub2 *)0,

(ub2 *)0, OCI_DEFAULT);

/* Execute the SQL statment */

rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,

(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);

while (rc != OCI_NO_DATA) {             /* Fetch the remaining data */

printf("%s\n",p_sli);

rc = OCIStmtFetch(p_sql, p_err, 1, 0, 0);

}

rc = OCILogoff(p_svc, p_err);                           /* Disconnect */

rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT);    /* Free handles */

rc = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);

rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);

return;

}

3 编译命令

gcc -D_LARGEFILE64_SOURCE -D_FILE_OFFSET_BITS=64 -I${ORACLE_HOME}/rdbms/public -I${ORACLE_HOME}/rdbms/demo -L${ORACLE_HOME}/lib -lclntsh -o hh1 hh1.c

4 编译并执行

hpn2$./hh1

Connect to orcl successful!

Lex