使用ocilib库访问oracle数据库

• 下面是一个简单的建立数据库连接的代码：

#include "ocilibDemo.h"

void err_handler(OCI_Error *err)
{
    printf(
                "code : ORA-%05in"
                "msg : %sn"
                "sql : %sn",OCI_ErrorGetOCICode(err),OCI_ErrorGetString(err),OCI_GetSql(OCI_ErrorGetStatement(err))
           );
}

void conn()
{
    OCI_Connection *cn; 

    if (!OCI_Initialize(err_handler,NULL,OCI_ENV_DEFAULT))
        return;

    cn = OCI_ConnectionCreate(tnsName,user,password,OCI_SESSION_DEFAULT);

    if (cn != NULL) {
        printf(OCI_GetVersionServer(cn)); 

        printf("Server major version : %in",OCI_GetServerMajorVersion(cn)); 
        printf("Server minor version : %in",OCI_GetServerMinorVersion(cn)); 
        printf("Server revision version : %in",OCI_GetServerRevisionVersion(cn)); 

        printf("Connection version : %in",OCI_GetVersionConnection(cn)); 

        /* ... application code here ... */ 

        OCI_ConnectionFree(cn); 
    }

    OCI_Cleanup(); 

    return; 
}

其中err_handler是作为异常处理函数，会在数据库error发生时被调用。
注意ocilib代码在编译时要设置宏/DOCI_API=__stdcall和/DOCI_CHARSET_XXX，XXX是具体字符集类型有三种：
1. OCI_CHARSET_ANSI : all strings are in ANSI
2. OCI_CHARSET_UNICODE : all strings are Unicode (versions of Oracle> = 9i)
3. OCI_CHARSET_MIXED : SQL statements + metadata are in ANSI,user data and resultset data in Unicode
客户端和服务器端最好都统一设置ANSI或者UNICODE，避免麻烦。

• 数据库操作代码

void  createTable(){
    OCI_Statement  *st = NULL;
    OCI_Connection *cn;

    if (!OCI_Initialize(err_handler,NULL,OCI_SESSION_DEFAULT);

    if (cn!=NULL) {
        st = OCI_StatementCreate(cn);
        char sql[]=MT("create table test_table ")
                   MT("( ")
                   MT("val_int number,")
                   MT("val_flt float,")
                   MT("val_str varchar2(30),")
                   MT("val_date date")
                   MT(")");
        cout<<"Create table:n"<<sql<<endl;
        OCI_ExecuteStmt(st,sql);
        OCI_ConnectionFree(cn); 
    }

    OCI_Cleanup();
}

void dropTable(){
    OCI_Statement  *st = NULL;
    OCI_Connection *cn;

    if (!OCI_Initialize(err_handler,OCI_SESSION_DEFAULT);

    if (cn!=NULL) {
        st = OCI_StatementCreate(cn);
        char sql[]=MT("drop table test_table");
        cout<<"Drop table:n"<<sql<<endl;
        OCI_ExecuteStmt(st,sql);
        OCI_ConnectionFree(cn); 
    }

    OCI_Cleanup();
}

void insertBind(){
    OCI_Date *date;

    int i;
    double flt;

    OCI_Statement  *st = NULL;
    OCI_Connection *cn;

    if (!OCI_Initialize(err_handler,OCI_SESSION_DEFAULT);
    if (cn!=NULL) {
        st = OCI_StatementCreate(cn);
        char sql[]=MT("insert into test_table ")
                   MT("( ")
                   MT(" val_int,val_flt,val_str,val_date")
                   MT( ") " )
                   MT( "values ")
                   MT( "( ")
                   MT( " :val_int,:val_flt,:val_str,:val_date")
                   MT(") ");
        cout<<"Intsert table:n"<<sql<<endl;
        OCI_Prepare(st,sql);
        i   = 1;
        flt = 3.14;
        string s="sfsdfsdfsfsdfsdfsd";
        date = OCI_DateCreate(cn);
        OCI_DateSysDate(date);

        OCI_BindInt(st,MT(":val_int"),&i);
        OCI_BindDouble(st,MT(":val_flt"),&flt);
        OCI_BindString(st,MT(":val_str"),const_cast<char *>(s.c_str()),30);
        OCI_BindDate(st,MT(":val_date"),date);
        OCI_Execute(st);
        OCI_DateFree(date);
        OCI_Commit(cn);

        OCI_ConnectionFree(cn); 
    }

    OCI_Cleanup();
}

void insertArray(){
    OCI_Connection *cn;
    OCI_Statement  *st;
    OCI_Error      *err;
    int count=20000000;

    int batchSize=5000;
    int tab_int[batchSize];
    double tab_flt[batchSize];
    char tab_str[batchSize][31];
    OCI_Date* tab_date[batchSize];

    int i;

    if (!OCI_Initialize(err_handler,OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);

    OCI_Prepare(st,"insert into test_table values(:i,:f,:s,:t)");
    OCI_BindArraySetSize(st,batchSize);
    OCI_BindArrayOfInts(st,":i",tab_int,0);
    OCI_BindArrayOfDoubles(st,":f",tab_flt,0);
    OCI_BindArrayOfStrings(st,":s",(char*) tab_str,30,0);
    OCI_BindArrayOfDates(st,":t",tab_date,0);


    for (i=0;i<batchSize;i++) {
        tab_int[i] = i+1;
        tab_flt[i]=3.14;
        sprintf(tab_str[i],"Name %d",i+1);

        tab_date[i] = OCI_DateCreate(cn);
        OCI_DateSysDate(tab_date[i]);
    }

    int round=count/batchSize;

    clock_t start=clock();
    cout<<start<<endl;
    for (int j=0;j<round;j++) {
        if (!OCI_Execute(st)) {
            printf("Number of DML array errors : %dn",OCI_GetBatchErrorCount(st));       

            err = OCI_GetBatchError(st);

            while (err) {
                printf("Error at row %d : %sn",OCI_ErrorGetRow(err),OCI_ErrorGetString(err));       

                err = OCI_GetBatchError(st);
            }
        }
        OCI_Commit(cn);
// printf("row processed : %dn",OCI_GetAffectedRows(st));
    }

    clock_t stop=clock();
    cout<<stop<<endl;
    int costTime=stop-start;
    double numberPerSec=(count/costTime)*1000;
    cout<<"Insert records "<<count<<" cost time "<<costTime<<" ms"<<endl;
    cout<<"Insert records "<<numberPerSec<<"records/s "<<endl;
    for (i=0;i<batchSize;i++) {
        OCI_DateFree(tab_date[i]);
    }
    OCI_Commit(cn);
    OCI_ConnectionFree(cn);
    OCI_Cleanup();

    return;
}

ocilib的代码可以跑在装有Oracle client或instant client的任何机器上，通过tns访问数据库，如果是instant client注意设置tns和home路径。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!