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/）：