oracle隐式类型转换

我们的系统的的选择框的选项是存储在一个数据库表中的，其中大致包含了选择框的id以及选择选项的数据，大致如下表。其中fieldid的类型为int，optionVal为int，optionName为varchar。

fieldid	optionVal	optionName
1	0	北京
1	1	南京
1	2	天津
1	3	杭州
2	0	拉面
2	1	米饭
今天在做需求的时候，没有注意相应字段的类型，直接通过java代码就拼接了sql，大致如下。

StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("UPDATE tablename SET")
    .append(optionFieldid)
    .append(" = '")
    .append(optionValue);
    .append("'");

optionValue字符串为''时他存储在数据库里面的值就变成了0，原以为应该把这个字段变成''，这时我才注意到这个字段的类型为int，发生了隐式类型转换。这里总结一下相关数据库的隐式类型转换，同时在接下来的开发过程中需要多加注意数据库的类型，避免产生意想不到的结果。

oracle

oracle遵循如下的数据类型转换规则：

对于insert和update操作，oracle将值转换为受影响的的列的类型。
对于SELECT FROM操作，oracle会将列的值的类型转换为目标变量的类型。
有如下表

create table testType(id int,name varchar(200),content blob,datetime date);

执行如下的sql

explain plan for select * from testType where name = to_date('2017-11-12','yyyy-mm-dd') and id = 12;
select * from table(DBMS_XPLAN.DISPLAY)

当操作数字类型的值的时候，oracle通常会调整精度，以适应最大的容量。在者中情况下，数字类型的操作的结果可能与上表有差异。
当字符串类型和数值类型做比较时，oracle会将字符串类型转换为数值类型。

select sysdate from dual where 1.2 = '1.2'; //正确
select sysdate from dual where 1.2 = '1.2a';//ORA-01722: 无效数字

使用字符串或者NUMBER类型和浮点数类型进行比较是不精确的。因为字符串和NUMBER类型使用的是decimal精度，而浮点数使用的是binary精度。
当把CLOB类型的数据转换为向VARCHAR2这样的类型，或者将BLOB转换为RAW数据，如果被转换的数据比目标类型大，那么将会报错。
在将timestamp类型转换为date类型的时候，小数秒部分会被阶段。
从BINARY_FLOAT转换为BINARY_DOUBLE是精确的。
从BINARY_DOUBLE转换到BINARY_FLOAT类型时，如果BINARY_ DOUBLE使用了超过BINARY_FLOAT支持的精度，那么转换是不精确的。
当字符串值和date值比较时，会将字符串值转换为date类型。对于这种情况我们不应该依赖隐式转换，应该使用TO_DATE进行显示转换，隐式转换遵循的转换格式如下：

select * from nls_session_parameters  WHERE parameter='NLS_DATE_FORMAT'; //DD-MON-RR

所以在中文环境下使用如下，使用其他的格式则会报错ORA-01861: 文字与格式字符串不匹配。

select 1 + 1 from dual where to_date('2018-1-15 12:23:23','yyyy-mm-dd hh24:mi:ss') > '15-1月 -18';

当你使用sql或者函数，传的参数不是参数接受的类型，那么会把传的这个参数转换为函数接受的类型。
当字符串和一个非字符串类型进行比较或者算数运算时，会将字符串转换为列一个数据的类型。当算数运算的两边是CHAR/VARCHAR2 和 NCHAR/NVARCHAR2，oracle将他们转换为NUMBER。

select '123' + '123.2' from dual; //246.2