oracle隐式转换的规则

oracle对不同数据类型具有显示转换和隐式转换2种。

Oracle官方建议使用显示转换，原因包括以下几点：

1. 使用显示转换更方便理解sql语句
2. 隐式转换可能会降低sql语句的性能（比如隐式转换不会使用普通索引）
3. 在不同的环境下，隐式转换可能不会一直有效。比如datetime to varchar2的转换，依赖于参数NLS_DATE_FORMAT，这个参数在不同会话或者客户端都可能会不同。
4. 计算类型（Algorithms）的隐式转换在不同的oracle版本上会有所不同。同一条隐式转换的sql在不同数据库版本上可能会有不同的结果。

 

11.2.0.4隐式转换表格：

隐式转换的规则：

1. insert和update语句，oracle只转换被影响的字段（insert为所有字段，update为指定字段）
2. select from语句，oracle为了目标对象可用而转换字段类型。
3. 当要进行数值类型的转换时，oracle调整精准度到最大可用。
4. 当对比字符型和数值型时，oracle把字符型转换为数据类型
5. 当对比字符型和date型时，oracle把字符型转换为date型（不知道timestamp会怎样）
6. 当使用等号时，右边的类型转换为左边的类型
7. 当转换字符型或number型和浮点型时可能会不精准，因为字符和number使用的是小数decimal准度，而浮点型使用的是binary准度。
8. 当clob转换为varchar2，blob，raw时，如果数据过大，转换会失败。char只有4000 bytes(char(2000))，clob转换为char只转换到4000bytes。
9. 当timestamp类型转换为date时（按照第三条，隐式转换不应该把timestamp转换为date，除非insert这样的），timestamp后几位会被truncated忽略，至于忽略几位，取决于数据库版本。
10. 啊
11. binary_float to binary_double的转换很精确，binary_double to binary_float不精确，因为binary_double精度更高。（高精度到低精度会有不精准的情况，这也是为什么有第三条规则）
12. concatenation连接操作，oracle会隐式转换非字符型到字符型
13. 当有计算操作时，oracle转换字符型到数值，date，rowid等类型
14. When converting RAW or LONG RAW data to and from character data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of RAW data.
15. 自定义类型不能隐式转换，可用cast multiset显示转换。
16. Char、varchar2、nchar、nvarchar2类型可相互转换

 

看完规则有以下几个疑问：

1. 当使用等号时，如char=date，是按等号右边转左边的规则还是char转date的规则？
2. 当使用等号时，如char=number，是按等号右边转左边的规则还是char转number的规则？
3. 当使用等号时，如date=timestamp，是按等号右边转左边的规则还是精准度规则？
4. 如果有计算操作，oracle会把char转换为数值，如果char精准度更高会怎样？
5. Char、varchar2、nchar、nvarchar2之间的转换在执行计划中能看到什么？

测试与验证

char=date

SQL> create table lzlimpl(char1 char(100),date1 date,number1 number,timestamp1 timestamp);


Table created.


SQL> insert into lzlimpl values('2018-12-10 14:17:00',sysdate,1,sysdate);


1 row created.

--这里已经出现了timestamp隐式转换date

很明显filter那里显示了隐式转换的方式

使用的不是to_date，而是internal_function

 

Char=number

SQL> select * from lzlimpl where char1=number1;
select * from lzlimpl where char1=number1
                            *
ERROR at line 1:
ORA-01722: invalid number
--char1中的数据不能直接转换为number，会报ORA-01722
SQL> delete from lzlimpl where char1='2018-12-10 14:17:00';

1 row deleted.
SQL> insert into lzlimpl values('1',sysdate,1,sysdate);

1 row created.
SQL> commit
  2  /

Commit complete.

很明显是char转换为number

date=timestamp

SQL> select * from lzlimpl where date1=timestamp1;

这里也出现了internal_function，date转换为了timestamp，oracle往更高的精度转换。

从这个internal_function看，这个函数不仅可以把char转换成了date，还可以把date转换成timestamp，应该还有更多功能

 

 

精准度

SQL> truncate table lzlimpl;


Table truncated.


SQL> insert into lzlimpl values('1.234',sysdate,1,sysdate);


1 row created.

SQL> select char1*number1 from lzlimpl;


CHAR1*NUMBER1

-------------

        1.234

第二条规则

肯定是做了隐式转换，但是这样看不出来。换一种方式

SQL> select * from lzlimpl where char1*number1=1.2345678;



no rows selected

 

为了计算，只能char转换为number，不同精准度的数据也可以计算

 

char、varchar2、nchar、nvarchar2

因为char补全了空格，所以no rows 

char和varchar2

char和varchar可以直接对比没有隐式转换

char需要转换为nchar

char类型的转换总结：

1. 同一字符集存储格式间不需要转换
2. 不通字符集存储格式间需要隐式转换，从数据库字符集到国家字符集