oracle 使用occi方式 批量插入多条数据

时间:2022-02-19 23:38:47
if (vecInfo.empty()) {
        return 1; //数据为空,不上传,不上传标志设置为1,只有0表示上传成功
    }
    std::string strUserName = userName;
    std::string strPasswd = passWd;
    std::string strDbName;
    std::string strIp = ip;
    std::string strPort = port;
    if (strPort.empty()) {
        strPort = "1521"; //如果输入的数据为空,设置默认值
    }
    if (dbName.empty()) {
        strDbName = "orcl";//如果输入的数据为空,设置默认值
    }
    else {
        strDbName = dbName;
    }
    std::string strSrvName = strIp + ":" + strPort + "/" + strDbName;//"10.194.98.34:1521/orcl";

    std::cout << "数据库连接信息为:" << strSrvName << std::endl;

    Environment *env = Environment::createEnvironment("ZHS16GBK", "UTF8");
    Connection * conn = NULL;
    Statement * stmt = NULL;
    if (NULL == env) {
    
        return -1;
    }

    try
    {
        conn = env->createConnection(strUserName, strPasswd, strSrvName);
        if (conn == NULL) {
        
            return -1;
        }

        stmt = conn->createStatement();

        std::string strSQL = "INSERT INTO VDS.LOG_OPERATION (USER_ID, OPERATOR, IP,MAC,OPERATOR_ORG_ID,OPERATOR_ORG_NAME,APP_CODE,OBJECT_ORG_IDS,OBJECT_ORG_NAMES,OPERATOR_OBJECT_TYPE,OPERATOR_OBJECT_KEYS,OPERATOR_OBJECT_VALUES,BUSINESS,ACT,OPERATE_RESULT,SEARCH_INFO,CONTENT,CREATOR,CREATE_TIME,UPDATE_TIME) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,current_timestamp,current_timestamp)";
        stmt->setSQL(strSQL);
        int count = vecInfo.size();
        stmt->setMaxIterations(count); //最多一次性批量插入的最大数据
        stmt->setMaxParamSize(1, 1000);
        stmt->setMaxParamSize(2, 1000);
        stmt->setMaxParamSize(3, 1000);
        stmt->setMaxParamSize(4, 1000);
        stmt->setMaxParamSize(5, 1000);
        stmt->setMaxParamSize(6, 1000);
        stmt->setMaxParamSize(7, 1000);
        stmt->setMaxParamSize(8, 1000);
        stmt->setMaxParamSize(9, 1000);
        stmt->setMaxParamSize(10, 1000);
        stmt->setMaxParamSize(11, 1000);
        stmt->setMaxParamSize(12, 1000);
        stmt->setMaxParamSize(13, 1000);
        stmt->setMaxParamSize(14, 1000);
        stmt->setMaxParamSize(16, 1000);
        stmt->setMaxParamSize(17, 1000);
        stmt->setMaxParamSize(18, 1000);
        //std::string strSQL = "INSERT INTO VDS.LOG_OPERATION (USER_ID, OPERATOR, IP,MAC,OPERATOR_ORG_ID,OPERATOR_ORG_NAME,APP_CODE,OBJECT_ORG_IDS,OBJECT_ORG_NAMES,OPERATOR_OBJECT_TYPE,OPERATOR_OBJECT_KEYS,OPERATOR_OBJECT_VALUES,BUSINESS,ACT,OPERATE_RESULT,SEARCH_INFO,CONTENT,CREATOR,CREATE_TIME,UPDATE_TIME) VALUES ";
        
        for (int j = 0; j< count ; j++){
            int i = 1;

            stmt->setString(i++, *(vecInfo.at(j)._user_id));
            stmt->setString(i++, *(vecInfo.at(j)._operator));
            stmt->setString(i++, *(vecInfo.at(j)._ip));
            stmt->setString(i++, *(vecInfo.at(j)._mac));
            stmt->setString(i++, *(vecInfo.at(j)._operator_org_id));
            stmt->setString(i++, *(vecInfo.at(j)._operator_org_name));
            stmt->setString(i++, *(vecInfo.at(j)._app_code));
            stmt->setString(i++, *(vecInfo.at(j)._object_org_ids));
            stmt->setString(i++, *(vecInfo.at(j)._object_org_names));
            stmt->setString(i++, *(vecInfo.at(j)._operator_object_type));
            stmt->setString(i++, *(vecInfo.at(j)._operator_object_keys));
            stmt->setString(i++, *(vecInfo.at(j)._operator_object_values));
            stmt->setString(i++, *(vecInfo.at(j)._business));
            stmt->setString(i++, *(vecInfo.at(j)._act));
            stmt->setInt(i++, vecInfo.at(j)._operate_result);
            stmt->setString(i++, *(vecInfo.at(j)._search_info));
            stmt->setString(i++, *(vecInfo.at(j)._content));
            stmt->setString(i++, *(vecInfo.at(j)._creator));


            if (j != count - 1) //count至少为1
            {
                stmt->addIteration();
            }
        }
        //
        //for (int i = 0; i < vecInfo.size(); i++) {
        //    std::string strVal;
        //    strVal += "(";

        //    strVal += "'" + *(vecInfo.at(i)._user_id)+"',";
        //    strVal += "'" + *(vecInfo.at(i)._operator) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._ip) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._mac) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._operator_org_id) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._operator_org_name) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._app_code) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._object_org_ids) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._object_org_names) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._operator_object_type) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._operator_object_keys) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._operator_object_values) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._business) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._act) + "',";
        //    std::stringstream iToStr;
        //    std::string strResult;
        //    iToStr << vecInfo.at(i)._operate_result;
        //    iToStr >> strResult;
        //    strVal += "" + strResult + ",";
        //    strVal += "'" + *(vecInfo.at(i)._search_info) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._content) + "',";
        //    strVal += "'" + *(vecInfo.at(i)._creator) + "',";
        //    strVal += "current_timestamp,current_timestamp";

        //    strVal += ")";
        //    if (i != vecInfo.size() - 1) {
        //        strVal += ", ";
        //    }
        //    //else {
        //    //    strVal += ";";
        //    //}

        //    strSQL += strVal;
        //}
        //std::cout << "sql为: " << std::endl;
        //std::cout << strSQL << std::endl;

        int iRet = stmt->executeUpdate();

    }
    catch (SQLException ex) {
        std::cout << ex.getMessage() << std::endl;
        return -1;
    }
    catch (...) {

        return -1;
    }

    //释放连接
    conn->terminateStatement(stmt);
    env->terminateConnection(conn);
    env->terminateEnvironment(env);
    return 0;

批量插入一定要注意的事项:

stmt->setMaxIterations(count); count值必须同要插入的数据条数一样。
setMaxParamSize需要对string类型的设置最大参数的大小。

遇到的错误有:

ORA-32109: invalid column or parameter position

ORA-32132: maximum iterations cannot be changed  ->setMaxIterations和setMaxParamSize引起的问题