# Qt下调用接口（OCI）访问oracle数据库

Qt下调用接口（OCI）访问oracle数据库

简介

Oracle调用接口是一个让我们通过调用来访问Oracle数据库和控制SQL语句执行各个阶段的应用程序编程接口（API）。

qt下oci添加库和头文件的方法

需要把Oracle安装包下的OCI目录里面的include和lib的目录添加进来，如果你的电脑没有安装Oracle，那就去已经安装Oracle的电脑上对应的目录拷贝一份即可，然后在项目的.pro文件中加入如下所示代码，路径根据自己电脑所在路径即可；

QMAKE_LFLAGS +=D:\app\Administrator\product\11.2.0\dbhome_1\BIN\oci.dll
INCLUDEPATH += D:\app\Administrator\product\11.2.0\dbhome_1\OCI\include

OCI基本编程

oci使用以下基本编程顺序

1、初始化句柄

 	// 环境句柄指针
    OCIEnv *envhpp=NULL;
    // 服务器句柄
    OCIServer *servhpp=NULL;
    // 错误句柄
    OCIError *errhpp=NULL;
    // 会话句柄
    OCISession *usrhpp=NULL;
    // 服务上下文句柄
    OCISvcCtx *svchpp=NULL;
    // 表达式句柄
    OCIStmt *stmthpp=NULL;

2、初始化OCI编程环境

每一个OCI函数调用都是在OCIEnvCreate()函数所创建的环境中执行的。这个函数必须在其他OCI函数执行前被调用。

	// 创建 OCI 环境 , 并设置环境句柄。
    sword swResult = OCIEnvCreate(&envhpp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
    //设置环境句柄失败
    if (swResult != OCI_SUCCESS)
    {
        cout << "Oracle environment initialization error!" << endl;
        return false;
    }
    cout << "Oracle environment initialization success!" << endl;

   

3、连接oracle服务器

oracle数据库为我们提供了用于分配和释放句柄及描述符的oci函数，在向oci函数传递句柄前我们必须使用OCIHandleAlloc（）函数分配句柄。

 	// 创建错误句柄
    OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);

    // 创建服务句柄
    OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&servhpp, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0);

    // 连接服务器，如果失败则获取错误码
    if (OCIServerAttach(servhpp, errhpp, (text *)server.c_str(), strlen(server.c_str()), 0) != OCI_SUCCESS)
    {
    	//设置缓冲区
        char errbuf[512] = "";
        sb4 errcode;

        // 获取错误指针和 OCI 错误代码
        OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);

        cout << "Oracle server attach error:" << errbuf << endl;

        //释放句柄
        OCIHandleFree((dvoid *)envhpp,OCI_HTYPE_ENV);
        OCIHandleFree((dvoid *)servhpp,OCI_HTYPE_SERVER);
        OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR);
        return false;
    }
    cout << "Oracle server attach success!"<< endl;
    return true; 

4、连接数据库

bool myoci::mySessionBegin(string user,string pwd)
{
    // 创建错误句柄
    OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
    // 创建服务上下文句柄
    OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&svchpp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **)0);
    // 创建用户连接句柄
    OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&usrhpp, (ub4)OCI_HTYPE_SESSION, (size_t) 0, (dvoid **)0);
    // 设置用户名、密码
    OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)user.c_str(), (ub4)strlen(user.c_str()), (ub4)OCI_ATTR_USERNAME, errhpp);
    OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)pwd.c_str(), (ub4)strlen(pwd.c_str()), (ub4)OCI_ATTR_PASSWORD, errhpp);

    // 创建会话连接
    if(OCISessionBegin(svchpp, errhpp, usrhpp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
    {
        char errbuf[512]={'\0'};
        sb4 errcode;

        // 获取错误指针和 OCI 错误代码
        OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
        cout << "User session error:" << errbuf << endl;
        OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR);
        OCIHandleFree((dvoid *)usrhpp,OCI_HTYPE_SESSION);
        OCIHandleFree((dvoid *)svchpp,OCI_HTYPE_SVCCTX);
        return false;
    }
    cout << "user session success!" << endl;
    return true;
}

5、执行sql语句

bool myoci::myStmtExecute(string sql2)
{
    //创建一个表达式句柄
    if (OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&stmthpp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0) != OCI_SUCCESS)
    {
        cout << "Create STMT error !" << endl;
        return false;
    }
    cout << "Create stmt success !" << endl;
    //创建错误句柄
    OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);

    // 准备Sql语句
    if (OCIStmtPrepare(stmthpp, errhpp, (text *)sql2.c_str(), (ub4)sql2.length(), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
    {
        cout << "Create prepare error!" << sql2 << endl;
        return false;
    }
    cout << "Create prepare success!" << endl;


    // 执行SQL 语句
    if(OCIStmtExecute(svchpp, stmthpp, errhpp, (ub4)1, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT) != OCI_SUCCESS)
    {
        cout << "Create execute error!" << endl;
        return false;
    }
    cout << "Create execute success!" << endl;
    return true;
}

6、释放句柄

// 断开用户会话
    OCILogoff(svchpp, errhpp);

    // 断开服务器连接
    OCIServerDetach(servhpp, errhpp, OCI_DEFAULT);

    // 释放资源
    OCIHandleFree((dvoid *) stmthpp, OCI_HTYPE_STMT);
    OCIHandleFree((dvoid *) svchpp, OCI_HTYPE_SVCCTX);
    OCIHandleFree((dvoid *) servhpp, OCI_HTYPE_SERVER);
    OCIHandleFree((dvoid *) errhpp, OCI_HTYPE_ERROR);
    qDebug()<<"释放完成"<<endl;