Oracle中对不同类型的处理具有显式类型转换(Explicit)和自动类型转换(隐式类型转换Implicit)两种方式，对于显式类型转换，我们是可控的，但是对于自动类型转换，当然不建议使用,因为很难控制，有不少缺点，但是我们很难避免碰到自动类型转换,如果不了解自动类型转换的规则，那么往往会改变我们SQL的执行计划，从而可能导致效率降低或其它问题，所以，Oracle开发人员很有必要了解Oracle自动类型转换的相关规则，从而避免自动类型转换导致相关问题的产生。本章首先会对Oracle自动类型转换的规则做阐述,然后结合相关实例分析自动类型转换可能造成的问题。29.1 数据类型优先级Oracle使用数据类型的优先级来决定自动类型转换,Oracle类型如下优先：■ Datetime and interval 类型■ BINARY_DOUBLE■ BINARY_FLOAT■ NUMBER■ 字符类型■ 所有其它内置类型上面说的不够具体，我们看第二节具体的类型转换规则。29.2 自动类型转换规则一般一个表达式不能包含多种数据类型，比如一个表达式5*10然后加上'james',但是Oracle会有自动类型转换和显式类型转换两种规则，我们看如下例子：DINGJUN123>select 5*10+'james' from dual;select 5*10+'james' from dual*第 1 行出现错误:ORA-01722: 无效数字我们看到,报无效数字错误。当然，这里Oracle使用了自动类型转换将'james'转为数字类型，但是这个转换是失败的，所以报错，所以自动类型转换的第1个规则就是必须自动类型转换能够成功，否则报错。我们看下面的就转换成功了：DINGJUN123>select 5*10+'2' from dual;5*10+'2'----------52OK，看到了结果正确，这里的字符串'2'被自动转为数值类型的2(不明白为什么会这样转换，请往下看)，所以结果为52.。29.2.1为什么不建议使用自动类型转换?自动类型转换的确可以让我们少写一些内容,比如可以少写个to_char函数之类的东西,但是它经常是不好的：1. 使用显示类型转换会让我们的SQL更加容易被理解,也就是可读性更强,但是自动类型转换却没有这个优点,如：DINGJUN123>select to_date(sysdate,'yyyymm') from dual;也许你会想,我没有看错吧,你写的语句是错的,to_date中间的第1个参数是字符类型哦,你提的这个问题很好,我想你应该需要了解了解Oracle中的自动类型转换了。我可以很明确地告诉你，这个语句是可以的，但是能不能运行正确就要依赖于具体的上下文了,比如这里sysdate是date类型，那么需要将date类型转为字符，这是自动转换的，也就是Oracle要自动调用to_char(sysdate,fmt)，这个fmt就依赖于上下文的nls_date_format,也有可能会依赖于nls_date_language的设置，看我们的结果：DINGJUN123>alter session set nls_date_format='yyyymm';会话已更改。DINGJUN123>select to_date(sysdate,'yyyymm') from dual;TO_DAT------201005DINGJUN123>alter session set nls_date_format='yyyymondd';会话已更改。DINGJUN123>select to_date(sysdate,'yyyymondd') from dual;TO_DATE(SYSDAT--------------20105月 16DINGJUN123>alter session set nls_date_language='American';会话已更改。DINGJUN123>select to_date(sysdate,'yyyymondd') from dual;TO_DATE(SYSD------------2010may16自动类型转换的确难以理解,不知道的人以为这真是太神奇了,可能以为Oracle的函数定义搞错了,还是了解下这方面的内容吧，这样才可以运筹帷幄，决胜千里。2. 自动类型转换往往对性能产生不好的影响，特别是左值的类型被自动转为了右值的类型。这种方式很可能使我们本来可以使用索引的而没有用上索引,也有可能会导致结果出错。如：DINGJUN123>drop table t;表已删除。DINGJUN123>create table t(name varchar2(10));表已创建。DINGJUN123>insert into t values('abc');已创建 1 行。DINGJUN123>insert into t values('1');已创建 1 行。DINGJUN123>commit;提交完成。DINGJUN123>create index idx_t on t (name);索引已创建。-------------------------------------案例1:自动类型转换导致出错------------------------------------DINGJUN123>select * from t where name = 1;select * from t where name = 1*第 1 行出现错误:ORA-01722: 无效数字DINGJUN123>select * from t where name = '1';NAME--------------------1--------------------------------------案例2:自动类型转换导致本该用索引而没有用----------DINGJUN123>explain plan for select * from t where name = 1;已解释。DINGJUN123>select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------Plan hash value: 1601196873----------------------------------| Id | Operation | Name |----------------------------------| 0 | SELECT STATEMENT | ||* 1 | TABLE ACCESS FULL| T |----------------------------------Predicate Information (identified by operation id):---------------------------------------------------PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------1 - filter(TO_NUMBER("NAME")=1)Note------ rule based optimizer used (consider using cbo)DINGJUN123>explain plan for select * from t where name = '1';已解释。DINGJUN123>select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2296882198----------------------------------| Id | Operation | Name |----------------------------------| 0 | SELECT STATEMENT | ||* 1 | INDEX RANGE SCAN| IDX_T |----------------------------------Predicate Information (identified by operation id):---------------------------------------------------PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------1 - access("NAME"='1')Note------ rule based optimizer used (consider using cbo)我们看案例1，如果这个语句很庞大，找这个错误还真不容易，如果是显示转换的话，找个错误就容易多了。案例2我使用RBO优化器的，我没有收集统计信息，而且还加了rule,这里不加rule一样，如果列自动发生了类型转换，很可能使索引失效,这句select * from t where name = 1没有写select * from t where to_number(name) =1发现索引失效明显。但是如果我们感觉应该用索引而没有用上索引，而且左边的列和右边的值类型不一样，那么很可能发生了自动类型转换，当然看执行计划有这样的类型转换信息，虽然我们没有显示地写，往往看执行计划是我们第1步寻找问题的方法。3. 自动类型转换可能依赖于发生转换时的上下文环境，比如1中的to_date(sysdate,fmt),一旦上下文环境改变，很可能我们的程序就不能运行。4. 自动类型转换的算法或规则，以后Oracle可能改变，这是很危险的，意味着旧的代码很可能在新的Oracle版本中运行出现问题(性能、错误等)，显示类型转换总是有最高的优先级，所以显示类型转换没有这种版本更替可能带来的问题。5. 自动类型转换是要消耗时间的,当然同等的显式类型转换时间也差不多,最好的方法就是避免类似的转换,在显示类型转换上我们会看到，最好不要将左值进行类型转换，到时候有索引也用不上索引，还要建函数索引，索引储存和管理开销增大。29.2.2 自动类型转换规则Oracle自动类型转换是根据上下文环境以及一些预定的规则,经过语法语义的分析之后进行相关的自动类型转换,自动类型转换首要条件就是这个转换有意义，要正确，否则转换不成功，要报错,我们前面已经举了这样的例子。看下图，Oracle自动类型转换的矩阵图,图上没有具体地转换方向，但是我们最起码看图了解到一点，自动类型转换不是什么类型都可以相互转换的，有的不可相互自动转换。(-的说明不转换，X的说明可以转换)Oracle自动类型转换有如下规则(转换方向):1. 在insert和update语句中，Oracle将赋值的类型转为目标列的类型。这很容易理解，当然最终存到我们目标列的类型是要符合定义的，如：DINGJUN123>drop table t;表已删除。DINGJUN123>create table t(x varchar2(100));表已创建。DINGJUN123>insert into t values(sysdate);已创建 1 行。DINGJUN123>select x from t;X--------------------2010may16看到了吧，其实sysdate在插入的时候就已经根据nls_date_format和nls_date_language参数转为字符类型varchar2(100)了。2. 在SELECT中,Oracle会自动将查询到的列的值转为目标变量的类型。如：DINGJUN123>declare2 var char(10);3 begin4 select 1 into var from dual;5 dbms_output.put_line('var is '||var||',the length is '||length(var));6 end;7 /var is 1 ,the length is 10看,数值1被转为char(10)了。3. 对数值类型的操作，Oracle经常将数值类型的值调整为最大的精度(precision)和刻度(scale),这种情况下经常看到的结果和表中存储的结果不一样。4. 当比较字符与数值的时候，数值会有更高的优先级，也就是将字符转为数值进行比较。DINGJUN123>explain plan for select * from t where x = 1;DINGJUN123>select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUTPlan hash value: 1601196873----------------------------------| Id | Operation | Name |----------------------------------| 0 | SELECT STATEMENT | ||* 1 | TABLE ACCESS FULL| T |----------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter(TO_NUMBER("X")=1)Note------ rule based optimizer used (consider using cbo)看上面的t表的x列是varchar2类型，select * from t where x = 1将列x自动通过to_number转为数值类型了。5. 在字符类型、NUMBER数值类型与浮点类型的数值之间相互转换，可能会丢失精度，因为NUMBER是以10进制(0-9)精度表示数字的，而浮点类型数值是以二进制(0和1)表示的精度。DINGJUN123>drop table t;表已删除。DINGJUN123>create table t(x binary_float);表已创建。DINGJUN123>insert into t values(1234567);已创建 1 行。DINGJUN123>insert into t values(123456789);已创建 1 行。DINGJUN123>column x format 9999999999999DINGJUN123>select * from t;X------------------------------------------------------1234567123456792我们插入的时候是NUMBER类型，但是实际表是BINARY_FLOAT,那么肯定要转为BINARY_FLOAT类型，看123456789插入的时候就发生了精度的丢失。6. 将CLOB转为字符类型或将BLOB转为RAW类型的时候，如果被转换的类型长度比目标类型长，那么会出错，其实，其他的类型转换在自动类型，显示类型转换中如果被转换的类型的长度比目标类型长，那么都是会报错的(但是在某些函数中自动截断，不报错，见第14)。DINGJUN123>drop table t;表已删除。DINGJUN123>create table t(x varchar2(10));表已创建。DINGJUN123>insert into t values(to_clob('12212121212121'));insert into t values(to_clob('12212121212121'))*第 1 行出现错误:ORA-12899: 列 "DINGJUN123"."T"."X" 的值太大 (实际值: 14, 最大值: 10)我们这里只是做个例子，没有必要用to_clob函数，看到了这个clob最大长度应该是10，但是实际是14，所以自动类型转换失败。7. BINARY_FLOAT自动转为BINARY_DOUBLE是准确的，当然这毋庸置疑。反之，BINARY_DOUBLE自动转为BINARY_FLOAT可能就是不准确的了，如BINARY_DOUBLE转为BINARY_FLOAT需要更多的精度位的支持。8. 当字符串与DATE类型比较，DATE类型具有较高优先级，将字符串转为DATE类型，这种自动转换需要上下文的支持，见前面DATE转为字符串的例子。DINGJUN123>drop table t;表已删除。DINGJUN123>create table t(x date);表已创建。DINGJUN123>insert into t values(to_date('2010-01-01','yyyy-mm-dd'));已创建 1 行。DINGJUN123>select * from t where x ='2010-01-01';select * from t where x ='2010-01-01'*第 1 行出现错误:ORA-01861: 文字与格式字符串不匹配DINGJUN123>alter session set nls_date_format='yyyy-mm-dd';会话已更改。DINGJUN123>select * from t where x ='2010-01-01';X----------2010-01-01看，的确可以自动类型转换。'2010-01-01'根据nls_date_format和nls_date_language转为了DATE类型。9. 当使用SQL函数或操作符的时候，如果传入的类型和实际应该接受的类型不一致，那么将传入的类型根据上下文环境转为一致。DINGJUN123>select replace(12345,4) from dual;REPLACE(--------1235DINGJUN123>select '10'+0 from dual;'10'+0--------------------------------------------------------------------------10DINGJUN123>select '10'|| 0 from dual;'10'||------100看上面的例子,replace接受的参数是两个字符类型，但是我们的是两个数值类型，会自动转为字符类型，返回值也是字符类型。'10'+0会自动将'10'转为10，最终结果是数值类型，而'10'||0会将0转为'0'(CHAR)所以结果是字符'100'。10. 当做赋值操作(=)的时候,Oracle会将右边被赋的值的类型自动转为和左边目标类型一致的类型。其实前面我们说的select语句的值赋给目标变量也类似。注意我们这里说的赋值操作可不是where xx = yy中=(这里的是比较操作)，而是赋值给变量或列，比如insert,update,PL/SQL中的赋值操作。11. 在做连接操作的时候，Oracle会将非字符类型转为CHAR或NCHAR。第9点已经举了例子说明。12. 在字符和非字符之间的算术和比较操作中，ORACLE会根据日期，ROWID，数值类型优先级最大来进行转换。算术操作一般都要转为NUMBER,比如where rowid='…'要将字符串转为ROWID，where date ='….'会将字符串根据nls的设置转为日期类型。DINGJUN123>select rowid from t;ROWID------------------AAAOi7AAEAAAPpWAAADINGJUN123>select * from t where rowid = 'AAAOi7AAEAAAPpWAAA';X----------2010-01-01DINGJUN123>select * from t where x = '2010-01-01'2 ;X----------2010-01-01DINGJUN123>select to_char(x,'yyyymmdd')+1 from t;TO_CHAR(X,'YYYYMMDD')+1--------------------------------------------------20100102表t中的x是DATE类型，看字符与rowid比较会将字符转为rowid类型。字符与数字运算转为数值类型，日期与字符比较会将字符转为日期根据nls的设置。我们再看一个例子说明这种自动类型转换的特点：DINGJUN123>drop table t;表已删除。DINGJUN123>create table t2 as3 with tmp as4 (select '15' id from dual5 union all6 select '2' from dual7 union all8 select '38' from dual9 union all10 select '4' from dual)11 select * from tmp;表已创建。--------------------选择的结果按字符类型排序的，不符合要求-------------DINGJUN123>select * from t order by id;j----152384------------自动转换数值类型排序，当然最好用to_number(id)----------------------DINGJUN123>select * from t order by id+0;j----24153813. 字符类型之间的类型转换，CHAR,VACHAR2,NCHAR,NVARCHAR2，我们知道，NVACHAR2需要国家字符集(9i后有UTF8和AL16UTF16)的支持，而且是按字符存储的，CHAR,VARCHAR2受数据库默认字符集的支持。那么数据库字符集支持的CHAR,VARCHAR2默认转换到NCHAR,NVARCHAR2,当然VARCHAR2与CHAR是CHAR转VARCHAR2，如下：到CHAR 到VARCHAR2 到NCHAR 到NVARCHAR2CHAR -- VARCHAR2 NCHAR NVARCHAR2VARCHAR2 VARCHAR2 -- NVARCHAR2 NVARCHAR2NCHAR NCHAR NCHAR -- NVARCHAR2NVARCHAR2 NVARCHAR2 NVARCHAR2 NVARCHAR2 --我们看到，NVARCHAR2最大，所有的遇到它都要自动转为NVARCHAR2类型。CHAR遇到VARCHAR2要转为VARCHAR2。14. 很多SQL函数可以接受CLOB类型，对参数要求是VARCHAR2或CHAR的如果传入CLOB类型也是可以的，但是有最大长度限制为4000字节，也就是说如果CLOB超过4000字节只取前4000字节。29.3显式类型转换显式数据类型转换就不详细说了，主要就是to_char,to_date,to_number,to_clob这些函数，对于LONG和LONG RAW类型我们几乎不用，这东西太麻烦，限制太多,要了解参考Oracle SQL Rerfernce。对于显式类型转换我们要注意一点，在写SQL的时候尽量不要对左值进行显式类型转换，否则对能用索引的用不上索引，到时候要建立函数索引的。比如：----好的写法-----select * from t where date >to_date('201001','yyyymm');---不好的写法----select * from t where to_number(to_char(date,'yyyymm' ))>201001;
2642
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
