oracle与infomix异同点

　　之前是做oracle数据库应用开发的，现在工作用的是informix，特别不习惯。用了一段时间后才慢慢适应，最近做系统升级，把informix换成oracle数据库。顺便整理了一下informix与oracle区别，希望对各位有用。

差异点	oracle	infomix
update多列	update set c1 = 'c1', c2 = 'c2'	update set (c1, c2) = ('c1', 'c2')
命令行操作工具	sqlplus	dbaccess
插入字段内容超长	插入失败，报错	超长部分可能被截断
对正在修改的表进行查询	无影响	如果不走索引，会引起“Could not position within a table ”问题 需要在查询前设置 set lock mode to wait n;（n是等待秒数）
查看数据库用户下所有表名	user_tables	sysmaster.systables
导出库表数据	自己写程序或者脚本	load
导入库表数据	sqlldr	unload
对象名长度限制	32个字符	没查过，但远超过32
空字符串	没有空字符串，只有NULL，''与NULL等价	''与NULL是两个不同的东西，所以字符设置not null也可以插入''
rowid	全局不同	每个表都是从1开始
rownum	有	无，但支持select [skip] first n
日期date类型	需要比较显示地使用to_date和to_char函数互转。如果没写系统会自动进行隐式转换。	直接使用类似日期的字符串
保留字	resource、union、level、mode、 start 这些都是oracle保留字，不能用于库表或字段名	这些不是保留字（应该还有其它的保留字区别，这里只整理我们项目碰到的）
yestorday current、year、today、day、month、weekday	不支持，需要使用sysdate-1，sysdate，add_year(sysdate, 1)等写法	支持
varchar	最长4000，叫varchar2	最长256
char	自动补全空格	自动补全空格，但查询时会自动删除这些空格，所以对于用户来说感觉跟不补空格一样
字符串截取	只支持substr	支持substr或者字段名[first, n]的写法，如res[1, 2]
调用存储过程	直接调用存储过程名称	call 或者是execute procedure call 调用如果有错误只是会提示笼统错误。而execute则是提示具体错误
number类型	有	decimal
dual	有	没有，但是可以自己创建一张
保存查询结果	支持create table as select insert into select	只支持insert into select
统计更新	dbms_stat .gather_table_stats	update statistcs
大事务（如一个语句更新几万条数据）	对数据库性能影响比较小	对数据库性能影响比较大
连接字符串	一样，可以使用||	一样，可以使用||
库表连接	一样，支持=写法	一样，支持=写法
truncate	支持	支持
merge	支持	支持

另外，在项目数据迁移过程中，我总结了unload导出数据特点：

 

1 char类型导出时，会自动去除尾部空格

2 如果varchar类型字段的值是null，则导出结果是空字符串，但如果值是''，导出结果是斜杠空格("\ ")

3 char类型字段如果值是''，导出结果是空格(" ")

4 unload导出时可以指定字段分隔符delimiter，如果字段值本身包含delimiter，则会导出成\delimiter

5 如果字段值含有'\'，则会导出成双斜杠（"\\"）

 

我写了一个将informix用unload导出来的文本装载回oracle的程序，大家有需要可以看看（操作oracle是用的otl，所以理论上也支持导入数据到informix，不过没测试过）

load_from_file.cc

 

//文件装载程序
#ifndef WIN32
//程序里面使用了get_rpc，用以统计成功入库的记录
//根据otl文档描述，在入库出现异常时，get_rpc在odbc和oci模式下返回结果有较大差异
//odbc模式下入库异常时get_rpc返回0，oci下则可以正确返回
#define OTL_ORA11G_R2
//#define OTL_ODBC_UNIX
//#define OTL_INFORMIX_CLI
#else
//#define OTL_ODBC  
#define OTL_ORA11G_R2
#ifndef snprintf
#define snprintf _snprintf
#endif
#endif

#define OTL_STREAM_READ_ITERATOR_ON //在没有返回数据的情况下抛出异常
#define OTL_PARANOID_EOF
#define OTL_STL


#include <string>
#include <string.h>
#include <assert.h>
#include <stdio.h>
#include <stdlib.h>
#include <iostream>
#include "otlv4.h"

using namespace std;
#define MAX_COLUMN_LEN 2048
#define MAX_LINE_LEN 8092

//是否检查列里面有空行记录 如果不定义这个宏则不主动检查
#define CHECK_NULL_COLUMN 0

extern "C" {
    //获取数据库登录信息
    int GetODBCInfo(char *dbname,char *dbusername,char *dbpasswd);
}

//函数出口比较多是，释放资源偷懒类
template<typename T, typename FUN>
class CSimpleRelease
{
public:
    CSimpleRelease(T *pResourse, FUN releaseFunc):m_pResourse(pResourse), m_releaseFunc(releaseFunc)
    {

    }

    ~CSimpleRelease()
    {
        m_releaseFunc(m_pResourse);
    }

public:
    T *m_pResourse;
    FUN m_releaseFunc;
};

class ColumnInfo
{
public:
    ColumnInfo()
    {
        columnTypes = NULL;
        columnCnt = 0;
    }

    ~ColumnInfo()
    {
        if (columnTypes != NULL)
        {
            delete []columnTypes;
            columnTypes = NULL;
        }
    }

    int columnCnt;
    int *columnTypes; //0字符串类型，1时间类型

#ifdef CHECK_NULL_COLUMN
    vector<string> vecName;
    vector<int> vecNullable;
#endif

private:
    ColumnInfo(const ColumnInfo& other)
    {
        //不让用户拷贝赋值
    }


    ColumnInfo& operator =(const ColumnInfo& other)
    {
        //不让用户拷贝赋值
    }
};

//使用select * from table_name返回的列信息拼接成insert sql
bool getTabInfo(const char *tableName, otl_connect& db, string& insertSql, ColumnInfo &columnInfo)
{
    insertSql = string("insert into ") + tableName;
    string valuesSql = " values(";

    char column_type_format_str[64];

    snprintf(column_type_format_str, sizeof(column_type_format_str), ":c%%d<char[%d]>, ", MAX_COLUMN_LEN);
    char column_type_format_date[] = ":c%d<timestamp>, ";

    bool bRet = true;
    otl_stream cur;
    int nColumnCnt = 0;
    otl_column_desc *column_des = NULL;
    try
    {
        string selectSql = string("select * from ") + tableName;
        cur.open(1, selectSql.c_str(), db);

        column_des = cur.describe_select(nColumnCnt);

    }
    catch(otl_exception& p)
    {
        cerr << "database err:" << endl;
        cerr << "stm_text = " << p.stm_text << endl;
        cerr << "msg = " << p.msg << endl;
        cerr << "var_info = " << p.var_info << endl;
        bRet = false;
    }

    if (!bRet || nColumnCnt <= 0 || column_des == NULL)
    {
        cerr << "errmsg=获取库表列信息出错" << endl;
        return false;
    }

    columnInfo.columnCnt = nColumnCnt;
    columnInfo.columnTypes = new int[nColumnCnt];
    assert(columnInfo.columnTypes != NULL);

    //对于大多数数据库来说，数据类型和字符串类型在输入时没区别
    //举例insert into demo(num_int, num_double, str) values('1', '1.1', '1')
    for (int i = 0; i < nColumnCnt; i++)
    {
#ifdef CHECK_NULL_COLUMN
        columnInfo.vecName.push_back(column_des[i].name);
        columnInfo.vecNullable.push_back(column_des[i].nullok);
#endif
        const char *pColumnTypeFormat = NULL;
        //这里只需要区分是否时间字段即可
        switch (column_des[i].otl_var_dbtype) {
        case otl_var_timestamp:
        case otl_var_db2time:
        case otl_var_db2date:
            columnInfo.columnTypes[i] = 1;
            pColumnTypeFormat = column_type_format_date;
            break;

        default:
            columnInfo.columnTypes[i] = 0;
            pColumnTypeFormat = column_type_format_str;
            break;
        }

        char buffer[256];
        snprintf(buffer, sizeof(buffer), pColumnTypeFormat, i);
        valuesSql += buffer;
    }
    column_des = NULL;
    cur.close();

    //去掉末尾的", "
    valuesSql.resize(valuesSql.length() - 2);

    insertSql += valuesSql + ")";
    return true;
}

bool connectDataBase(otl_connect &db)
{
    char  P1[128] = {0}, P2[128] = {0}, P3[128] = {0};
    int iRet = GetODBCInfo(P1,P2,P3);
    if(iRet != 0) 
    {
        cout << "GetODBCInfo err" << endl;        
    }

    /*
    cout << "P1 = " << P1 << endl;
    cout << "P2 = " << P2 << endl;
    cout << "P3 = " << P3 << endl;
    */

    bool bRet = true;
    try
    {
        otl_connect::otl_initialize(1); // initialize OCI environment 多线程设置1
        char connect_str[128];
        snprintf(connect_str, sizeof(connect_str), "%s/%s@%s", P2, P3, P1); 
        db.rlogon(connect_str, 0);
    }
    catch(otl_exception& p)
    {
        cerr << "database err:" << endl;
        cerr << "stm_text = " << p.stm_text << endl;
        cerr << "msg = " << p.msg << endl;
        cerr << "var_info = " << p.var_info << endl;
        bRet = false;
    }

    return bRet;
}

//在字符串中查找一个字节，忽略中文（有些特殊中文编码正好有竖线）
char *strchrEngOnly(char *str, char ch)
{
    while (*str)
    {
        //中文字符占两个字节，第一个字节第一位是1
        if (0x80 & *str)
        {
            str++;
            //一般不会有这样的情况 防异常
            if (*str == '\0')
            {
                break;
            }
        }
        else
        {
            if (*str == ch)
            {
                return str;
            }
        }

        str++;
    }

    return NULL;
}


//出错还原文件行
void concatOneFileLine(char *lineBuf, vector<char *>& vecColumn)
{
    for (size_t i = 1; i < vecColumn.size(); i++)
    {
        char *p = vecColumn[i] - 1;
        *p = '|';
    }
}


//用竖线把字符串拆分成多个字段
void splitOneFileLine(char *lineBuf, vector<char *>& vecColumn)
{
    char *pStart = lineBuf;
    char *pEnd = NULL;
    while ((pEnd = strchrEngOnly(pStart, '|')) != NULL)
    {
        *pEnd = '\0';
        vecColumn.push_back(pStart);
        pStart = pEnd + 1;
    }

    //去掉行尾回车换行符
    pEnd = strchr(pStart, '\n');
    assert(pEnd != NULL);
    *pEnd = '\0';
    // 如果行末是换行符则不认为是新字段
    if (pStart != pEnd) vecColumn.push_back(pStart);
}

bool commit(otl_stream& cur, otl_connect& db, int &effectRows)
{
    try
    {
        effectRows = 0;
        cur.flush();
        effectRows = cur.get_rpc();
        db.commit();
    }
    catch(otl_exception& p)
    {
        cerr << "database commit err:" << endl;
        //oci的get_rpc才准
        effectRows = cur.get_rpc();
        cerr << "stm_text = " << p.stm_text << endl;
        cerr << "msg = " << p.msg << endl;
        cerr << "var_info = " << p.var_info << endl;
        return false;
    }

    return true;
}


//informix unload文件特点：1 char类型末尾空格自动删除 2 字符串里面的'\'用双斜杠"\\"代替 
// 3 char类型的空字符串用" "表示 4 vchar类型的空字符串用"\ "表示
// 5 字符串中的分隔符（默认是竖线'|'）用"\分隔符"表示，如"\|" 
//为此做以下处理 1 "\\"替换成"\";  2 "\|" 替换成 "0x01"; 3 "\ "删除 4 "| |" 替换成 "||"
void informix_unload_dual_pre(char *pBuffer)
{
    char *pOutput = pBuffer;
    char *pCur = pBuffer;

    //第一域是空串特殊处理
    if (*pCur == ' ' && *(pCur + 1) == '|')
    {
        pCur++;
    }

    while (*pCur)
    {
        if (*pCur & 0x80)
        {
            //中文字符直接复制
            *pOutput++ = *pCur++;
            *pOutput++ = *pCur++;
        }
        else
        {
            switch(*pCur)
            {
            case '\\':
                switch (*(++pCur))
                {
                case '|':
                    *pOutput++ = (char)0x01;
                    pCur++;
                    break;
                case '\\':
                    *pOutput++ = *pCur++;
                    break;
                case ' ': //"\ "忽略
                    pCur++;
                    break;
                default:
                    *pOutput++ = *pCur++;
                    pCur++;
                    break;
                }
                break;
            case ' ':
                if (*(pCur - 1) == '|' && *(pCur + 1) == '|')
                {
                    pCur++;
                }
                else
                {
                    *pOutput++ = *pCur++;
                }
                break;
            default:
                *pOutput++ = *pCur++;
            }
        }
    }

    *pOutput = '\0';
}

void informix_unload_dual_after(char *pBuffer)
{
    while (*pBuffer)
    {
        if (*pBuffer & 0x80)
        {
            pBuffer++;
        }
        else
        {
            if (*pBuffer == 0x01)
            {
                *pBuffer = '|';
            }
        }

        pBuffer++;
    }
}

//将类时间格式字符串转换为otl_datetime类型
//支持 ^([0-9][: /-\t]*){4,14}$
//如果转换成功，dtOutput保存字符串对应的时间，如果失败dt的值不变
bool getOtlDateTime(const char *inbuf, otl_datetime& dtOutput, bool& bNullFlag)
{
    char buffer[16] = {0};
    bool bErrFlag = false;
    int nInputPos = 0;
    int nOutputPos = 0;
    otl_datetime dt;

    //最多14个数字，有15个就是错的 所以这里到了第15个数字就退出循环
    while ((nOutputPos <= 15) || (inbuf[nInputPos] != '\0'))
    {
        if ((inbuf[nInputPos] >= '0' && inbuf[nInputPos] <= '9'))
        {
            buffer[nOutputPos++] = inbuf[nInputPos++];
        }
        else if ( inbuf[nInputPos] == ' ' || inbuf[nInputPos] == '\t' 
            || inbuf[nInputPos] == ':' || inbuf[nInputPos] == '-' || inbuf[nInputPos] == '/')
        {
            nInputPos++;
        }
        else
        {
            bErrFlag = false;
            break;
        }
    }

    if (bErrFlag)
    {
        return false;
    }

    size_t len = strlen(buffer);

    switch (len)
    {
    case 0:
        bNullFlag = true;
    case 4:
        sscanf(buffer, "%4d", &dt.year);
        break;
    case 6:
        sscanf(buffer, "%4d%2d", &dt.year, &dt.month);
        break;
    case 8:
        sscanf(buffer, "%4d%2d%2d", &dt.year, &dt.month, &dt.day);
        break;
    case 10:
        sscanf(buffer, "%4d%2d%2d%2d", &dt.year, &dt.month, &dt.day, &dt.hour);
        break;
    case 12:
        sscanf(buffer, "%4d%2d%2d%2d%2d", &dt.year, &dt.month, &dt.day,
            &dt.hour, &dt.minute);
        break;
    case 14:
        sscanf(buffer, "%4d%2d%2d%2d%2d%2d", 
            &dt.year, &dt.month, &dt.day,
            &dt.hour, &dt.minute, &dt.second);
        break;
    default:
        return false;
        break;
    }
    
    dtOutput = dt;
    bNullFlag = false;
    return true;
}

typedef struct {
    int nFileLine; //已读文件行数
    int nLoadIntoDb; //成功入库行数（只有用oci底层此数据才准）否则只是参考值
    int nDualCnt; //成功处理文件行数
    int nErrcode;
}LoadInfo;

//返回值nErrcode等于0代表成功无异常，大于0，代表部分数据导入失败，小于0，代表导入完全失败（例如文件不可读）
LoadInfo loadFromFile(const char *loadSql, const char *strFileName, int nCurBuffLen, 
    const ColumnInfo &columnInfo, bool bInformixUnload/*待处理的文本是否informix unload导出格式*/,
    int nErrDualMode, otl_connect &db)
{
    LoadInfo retInfo;
    memset(&retInfo, 0, sizeof(retInfo));


    bool bRet = true;
    FILE *fp = fopen(strFileName, "r");
    if (fp == NULL)
    {
        cerr << "errmsg=open file " << strFileName << " error!";
        retInfo.nErrcode = -1;
        return retInfo;
    }
    //函数return的地方太多了，用资源托管类比较省代码
    CSimpleRelease<FILE, int(*)(FILE *)> resOfFp(fp, fclose);

    int& nFileLine = retInfo.nFileLine;  //已读取的文本行数
    int& nDualCnt = retInfo.nDualCnt; //已读取并且成功绑定到游标的行数
    int& nLoadIntoDb = retInfo.nLoadIntoDb; //已经成功提交到数据库的行数

    int effectRows = 0;
    otl_stream cur;
    otl_null otlNullValue;
    try
    {

        db.auto_commit_off();
        cur.open(nCurBuffLen, loadSql, db);
        cur.set_commit(0);

        char lineBuf[MAX_LINE_LEN];
        vector<char *> vecColumn;

        otl_datetime colDate;
        bool bColDateNullFlag = false;
        while (fgets(lineBuf, MAX_LINE_LEN, fp) != NULL)
        {
            nFileLine++;
            if ((nFileLine & 8191) == 8191) cout << "FileLine = " <<  nFileLine << endl;

            //informix unload 特殊处理
            if (bInformixUnload)
            {
                size_t len = 0;
                do 
                {
                    len += strlen(lineBuf + len);

                    if ((len < 2) || (lineBuf[len - 2] != '\\'))
                    {
                        break;
                    }

                    nFileLine++;
                    if ((nFileLine & 8191) == 8191) cout << "FileLine = " <<  nFileLine << endl;
                } while (fgets(lineBuf + len, MAX_LINE_LEN - len, fp));
            }

            if (bInformixUnload)
            {
                informix_unload_dual_pre(lineBuf);
            }

            //用竖线分割行记录
            vecColumn.clear();
            vecColumn.reserve(columnInfo.columnCnt);
            splitOneFileLine(lineBuf, vecColumn);
            if ((int) vecColumn.size() != columnInfo.columnCnt)
            {
                cerr << "errmsg=" << nFileLine << "行记录字段数与库表字段数不符" << endl;
                concatOneFileLine(lineBuf, vecColumn);
                vecColumn.clear();
                cerr << "lineBuf = " << lineBuf << endl;
                if ( (nErrDualMode == 0) && ((nFileLine - nDualCnt) <= nCurBuffLen) )
                {
                    cerr << "跳过该行" << endl;
                    continue;
                }

                cerr << "错误太多，停止导入" << endl;
                retInfo.nErrcode = 5;
                commit(cur, db, effectRows);
                retInfo.nLoadIntoDb += effectRows;
                return retInfo;
            }

            if (bInformixUnload)
            {
                for (size_t i = 0; i < vecColumn.size(); i++)
                {
                    informix_unload_dual_after(vecColumn[i]);
                }
            }

            for (int i = 0; i < columnInfo.columnCnt; i++)
            {

#ifdef CHECK_NULL_COLUMN
                //如果某列的值为空，但是该字段数据库不允许为空，则打印警告
                if (columnInfo.vecNullable[i] == 0 && vecColumn[i][0] == '\0')
                {
                    char msgBuf[1024];
                    snprintf(msgBuf, sizeof(msgBuf), "warning: %s line %d file %d is null can not insert into %s",
                        strFileName, nFileLine, i + 1, columnInfo.vecName[i].c_str());
                    cerr << "errmsg=" << msgBuf << endl;
                }
#endif
                if (columnInfo.columnTypes[i] == 0)
                {
                    cur << vecColumn[i];
                }
                else
                {
                    if (getOtlDateTime(vecColumn[i], colDate, bColDateNullFlag))
                    {
                        if (!bColDateNullFlag)
                        {
                            cur << colDate;
                        }
                        else
                        {
                            cur << otlNullValue;
                        }
                    }
                    else
                    {
                        cerr << "errmsg=" << nFileLine << "行" << i << "列时间格式非法" << endl;
                        retInfo.nErrcode = 10;
                        commit(cur, db, effectRows);
                        retInfo.nLoadIntoDb += effectRows;
                        return retInfo;
                    }
                }
            }

            //每次清空缓存后获取影响记录数
            if (cur.get_dirty_buf_len() == 0)
            {
                nLoadIntoDb += cur.get_rpc();
            }
            nDualCnt++;
        }

        //处理完文件提交数据库
        cout << "文件处理完毕，准备提交事务" << endl;
        if ( commit(cur, db, effectRows) )
        {
            cout << "事务提交成功" << endl;
            retInfo.nLoadIntoDb += effectRows;
        }
        else
        {
            cerr << "提交数据到数据库出错" << endl;
            retInfo.nLoadIntoDb += effectRows;
            retInfo.nErrcode = 15;
            return retInfo;
        }
        cur.close();
    }
    catch(otl_exception& p)
    {
        cerr << "database err:" << endl;
        if (cur.get_connected() && commit(cur, db, effectRows) )
        {
            //提交数据库出错处理
            retInfo.nLoadIntoDb += effectRows;
        }

        //oci这些函数才有用，具体见otl文档
        //cerr << "cur.get_number_of_errors_in_batch() = " <<  cur.get_number_of_errors_in_batch() << endl;
        //cerr << "cur.get_error() = " << cur.get_error() << endl;
        cerr << "stm_text = " << p.stm_text << endl;
        cerr << "msg = " << p.msg << endl;
        cerr << "var_info = " << p.var_info << endl;
        retInfo.nErrcode = 20;
        return retInfo;
    }

    cout << nDualCnt << " lines load." << endl;
    return retInfo;
}

int main(int argc, char *argv[])
{
    // 参数1设置文本列数与库表不匹配时跳过该记录还是退出
    // 参数5设置输入的格式是否informix unload导出文件
    if (argc < 5) 
    {
        cerr << "errmsg=error param!" << endl;
        cerr << "usage: load_from_file continue|break buffer_cnt table_name file_name [1]" << endl;
        exit(1);
    }

    int nRet = 0;

    int nErrDualMode = 0;
    if (strcmp(argv[1], "continue") == 0)
    {
        nErrDualMode = 0;
    }
    else if (strcmp(argv[1], "break") == 0)
    {
        nErrDualMode = 1;
    }
    else
    {
        cerr << "errmsg=error param!" << endl;
        exit(1);
    }

    int nCurBuffLen = atoi(argv[2]);
    const char *strTableName = argv[3];
    const char *strFileName = argv[4];
    bool bInformixUnload = false;
    if (argc > 5)
    {
        if (atoi(argv[5]) == 1)
        {
            bInformixUnload = true;
        }
    }

    otl_connect db;
    cout << "开始连接数据库" << endl;
    if (!connectDataBase(db))
    {
        cerr << "数据库连接失败" << endl;
        nRet = 1;
    }

    ColumnInfo columnInfo;
    string insertSql;
    cout << "开始获取表结构:" << strTableName << endl;
    if (nRet == 0)
    {
        if (!getTabInfo(strTableName, db, insertSql, columnInfo))
        {
            cout << "表结构信息获取失败" << endl;
            nRet = 2;
        }
    }

    if (nRet == 0)
    {
        cout << "开始导入库表" << endl;
        LoadInfo retInfo;
        try
        {
            retInfo = loadFromFile(insertSql.c_str(), strFileName, nCurBuffLen, 
                columnInfo, bInformixUnload, nErrDualMode, db);
        }
        catch(...)
        {
            cerr << "errmsg=导入发生意外" << endl;
        }

        nRet = retInfo.nErrcode;
        if (nRet > 0)
        {
            cout << "导入部分失败" << endl;
            char errBuf[1024];
            snprintf(errBuf, sizeof(errBuf), "已读记录数%d，已处理记录数%d，确定入库记录数%d",
                retInfo.nFileLine, retInfo.nDualCnt, retInfo.nLoadIntoDb);
            cout << errBuf << endl;
        }
        else if (nRet == 0)
        {
            cout << "导入成功" << endl;
        }
        else
        {
            cout << "导入失败" << endl;
            nRet = 3;
        }
    }

    try
    {
        db.logoff();
    }
    catch(...)
    {
        cerr << "与数据库断开连接时发生意外" << endl;
    }

    if (nRet != 0)
    {
        cout << "发生异常，请检查日志" << endl;
    }
    return nRet;
}