C程序输出Oracle数据表的操作实例（c 输出oracle表）

C程序输出Oracle数据表的操作实例

随着大数据时代的到来，数据管理已经成为企业不可避免的问题。Oracle作为世界领先的数据库管理系统，应用于许多规模大，业务复杂的企业。本文介绍如何使用C程序获取Oracle数据表的数据并输出。

1.连接Oracle数据库

连接Oracle数据库需要使用Oracle提供的OCI（Oracle Call Interface）编程接口。OCI是Oracle在C语言基础上的开发接口，通过OCI可以和Oracle的数据库服务器建立连接，执行SQL语句等操作。

下面是一个连接Oracle数据库的示例代码：

“`c

#include

#include

#include

int mn()

{

OCIEnv *envhp;

OCIError *errhp;

OCISvcCtx *svchp;

OCIServer *srvhp;

sword status;

status = OCIEnvCreate(&envhp,OCI_THREADED|OCI_OBJECT,NULL,NULL,NULL,NULL,0,NULL);

if(status != OCI_SUCCESS)

{

printf(“Error creating OCI environment”);

exit(1);

}

status = OCIHandleAlloc(envhp,(void **) &errhp,OCI_HTYPE_ERROR,0,NULL);

if(status != OCI_SUCCESS)

{

printf(“Error allocating OCI error handle”);

exit(1);

}

status = OCIHandleAlloc(envhp,(void **) &srvhp,OCI_HTYPE_SERVER,0,NULL);

if(status != OCI_SUCCESS)

{

printf(“Error allocating OCI server handle”);

exit(1);

}

status = OCIHandleAlloc(envhp,(void **) &svchp,OCI_HTYPE_SVCCTX,0,NULL);

if(status != OCI_SUCCESS)

{

printf(“Error allocating OCI service context handle”);

exit(1);

}

status = OCIServerAttach(srvhp,errhp,(text *)”ORCL”,strlen(“ORCL”),OCI_DEFAULT);

if(status != OCI_SUCCESS)

{

printf(“Error attaching to OCI server”);

exit(1);

}

status = OCIAttrSet(svchp,OCI_HTYPE_SVCCTX,srvhp,0,OCI_ATTR_SERVER, errhp);

if(status != OCI_SUCCESS)

{

printf(“Error setting OCI service context attribute”);

exit(1);

}

status = OCIAttrSet(svchp,OCI_HTYPE_SVCCTX,envhp,0,OCI_ATTR_ENV, errhp);

if(status != OCI_SUCCESS)

{

printf(“Error setting OCI service context attribute”);

exit(1);

}

printf(“Connected to Oracle database!”);

return 0;

}

2.执行SQL语句

连接Oracle数据库之后，我们可以执行SQL语句来获取数据表的数据。下面是一个获取数据表中所有数据的示例代码：

```c
#include 
#include 
#include 
 
int mn()
{
    OCIEnv *envhp;
    OCIError *errhp;
    OCISvcCtx *svchp;
    OCIServer *srvhp;
    sword status;
 
    status = OCIEnvCreate(&envhp,OCI_THREADED|OCI_OBJECT,NULL,NULL,NULL,NULL,0,NULL);
    if(status != OCI_SUCCESS)
    {
        printf("Error creating OCI environment");
        exit(1);
    }
 
    status = OCIHandleAlloc(envhp,(void **) &errhp,OCI_HTYPE_ERROR,0,NULL);

    if(status != OCI_SUCCESS)
    {
        printf("Error allocating OCI error handle");
        exit(1);
    }
 
    status = OCIHandleAlloc(envhp,(void **) &srvhp,OCI_HTYPE_SERVER,0,NULL);

    if(status != OCI_SUCCESS)
    {
        printf("Error allocating OCI server handle");
        exit(1);
    }
 
    status = OCIHandleAlloc(envhp,(void **) &svchp,OCI_HTYPE_SVCCTX,0,NULL);

    if(status != OCI_SUCCESS)
    {
        printf("Error allocating OCI service context handle");
        exit(1);
    }

    status = OCIServerAttach(srvhp,errhp,(text *)"ORCL",strlen("ORCL"),OCI_DEFAULT);

    if(status != OCI_SUCCESS)
    {
        printf("Error attaching to OCI server");
        exit(1);
    }
 
    status = OCIAttrSet(svchp,OCI_HTYPE_SVCCTX,srvhp,0,OCI_ATTR_SERVER, errhp);

    if(status != OCI_SUCCESS)
    {
        printf("Error setting OCI service context attribute");
        exit(1);
    }
 
    status = OCIAttrSet(svchp,OCI_HTYPE_SVCCTX,envhp,0,OCI_ATTR_ENV, errhp);

    if(status != OCI_SUCCESS)
    {
        printf("Error setting OCI service context attribute");
        exit(1);
    }

    OCISession *authp;
    status = OCIHandleAlloc(envhp,(void **)&authp,OCI_HTYPE_SESSION,0,NULL);
    if(status != OCI_SUCCESS)
    {
        printf("Error allocating OCI authentication handle");
        exit(1);
    }

    status = OCIAttrSet(authp,OCI_HTYPE_SESSION,"username",strlen("username"),OCI_ATTR_USERNAME,errhp);

    if(status != OCI_SUCCESS)
    {
        printf("Error setting OCI session attribute");
        exit(1);
    }

    status = OCIAttrSet(authp,OCI_HTYPE_SESSION,"password",strlen("password"),OCI_ATTR_PASSWORD,errhp);

    if(status != OCI_SUCCESS)
    {
        printf("Error setting OCI session attribute");
        exit(1);
    }

    status = OCISessionBegin(svchp,errhp,authp,OCI_CRED_RDBMS,OCI_DEFAULT);

    if(status != OCI_SUCCESS)
    {
        printf("Error beginning OCI session");
        exit(1);
    }

    OCIStmt *stmthp;
    status = OCIHandleAlloc(envhp,(void **)&stmthp,OCI_HTYPE_STMT,0,NULL);
    if(status != OCI_SUCCESS)
    {
        printf("Error allocating OCI statement handle");
        exit(1);
    }

    status = OCIStmtPrepare(stmthp,errhp,(text *)"SELECT * FROM table1",strlen("SELECT * FROM table1"),OCI_NTV_SYNTAX,OCI_DEFAULT);

    if(status != OCI_SUCCESS)
    {
        printf("Error preparing OCI statement");
        exit(1);
    }

    status = OCIStmtExecute(svchp,stmthp,errhp,1,0,NULL,NULL,OCI_DEFAULT);

    if(status != OCI_SUCCESS)
    {
        printf("Error executing OCI statement");
        exit(1);
    }

    OCIParam *colhp;
    ub2 col_type;
    ub2 col_width;
    ub4 size;
    status = OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT);

    while(status != OCI_NO_DATA)
    {
        int i = 1;
        while (OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, (void **) &colhp, i) == OCI_SUCCESS) {
            status = OCIAttrGet(colhp, OCI_DTYPE_PARAM, (void **)&col_type, NULL, OCI_ATTR_DATA_TYPE, errhp);
            status = OCIAttrGet(colhp, OCI_DTYPE_PARAM, (void **)&col_width, NULL, OCI_ATTR_DATA_SIZE, errhp);
            char *col_val = (char*)malloc(col_width);
            memset(col_val,0,col_width);
            status = OCIDefineByPos(stmthp, &colhp, errhp, i, col_val, col_width, col_type, NULL, NULL, NULL, OCI_DEFAULT);          
            ++i;
        }

        printf("%s %s %s\n",col_val,col_val,col_val);
        status = OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT);
    }

    status = OCISessionEnd(svchp,errhp,authp,OCI_DEFAULT);

    if(status != OCI_SUCCESS)
    {
        printf("Error ending OCI session");
        exit(1);
    }

    status = OCIServerDetach(srvhp,errhp,OCI_DEFAULT);

    if(status != OCI_SUCCESS)
    {
        printf("Error detaching OCI server");
        exit(1);
    }

    printf("Got table1 data from Oracle database!");

    return 0;
}

3.总结

本文介绍了如何使用C程序连接Oracle数据库，并获取数据表中的数据。这些操作是企业数据管理的基础，掌握了这些技能可以让企业更好地管理自己的数据。