使用ocilib库访问oracle数据库

来源：互联网发布：知乎提问怎么取消匿名编辑：程序博客网时间：2024/06/03 22:46

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

#include "ocilibDemo.h"void err_handler(OCI_Error *err){    printf(                "code  : ORA-%05i\n"                "msg   : %s\n"                "sql   : %s\n",                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 : %i\n", OCI_GetServerMajorVersion(cn));         printf("Server minor    version : %i\n", OCI_GetServerMinorVersion(cn));         printf("Server revision version : %i\n", OCI_GetServerRevisionVersion(cn));         printf("Connection      version : %i\n", 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_ENV_DEFAULT))        return;    cn = OCI_ConnectionCreate(tnsName, user, password, 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, NULL, OCI_ENV_DEFAULT))        return;    cn = OCI_ConnectionCreate(tnsName, user, password, 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, NULL, OCI_ENV_DEFAULT))        return;    cn = OCI_ConnectionCreate(tnsName, user, password, 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, NULL, OCI_ENV_DEFAULT))        return;    cn = OCI_ConnectionCreate(tnsName, user, password, 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 : %d\n", OCI_GetBatchErrorCount(st));                   err = OCI_GetBatchError(st);            while (err) {                printf("Error at row %d : %s\n", OCI_ErrorGetRow(err), OCI_ErrorGetString(err));                       err = OCI_GetBatchError(st);            }        }        OCI_Commit(cn);//      printf("row processed : %d\n", 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路径。

1 0