必须重视的Oracle自动类型转换

29.必须重视的Oracle自动类型转换显示类型转换以date类型为例子。Oracle中对不同类型的处理具有显式类型转换(Explicit)和自动类型转换(隐式类型转换Implicit)两种方式，对于显式类型转换，我们是可控的，但是对于自动类型转换，当然不建议使用，因为很难控制，有不少缺点，但是我们很难避免碰到自动类型转换，如果不了解自动类型转换的规则，那么往往会改变我们SQL的执行计划，从而可能导致效率降低或其它问题，所以，Oracle开发人员很有必要了解Oracle自动类型转换的相关规则，从而避免自动类型转换导致相关问题的产生。本章首先会对Oracle自动类型转换的规则做阐述，然后结合相关实例分析自动类型转换可能造成的问题。29.1数据类型优先级Oracle使用数据类型的优先级来决定自动类型转换,Oracle类型如下优先:Datetimeandinterval类型BINARY_DOUBLEBINARY_FLOATNUMBER■字符类型■所有其它内置类型上面说的不够具体，我们看第二节具体的类型转换规则。29.2自动类型转换规则一般一个表达式不能包含多种数据类型，比如一个表达式5*10然后加上'james'，但是Oracle会有自动类型转换和显式类型转换两种规则，我们看如下例子：DINGJUN123>select5*10+'james'fromdual;select5*10+'james'fromdual*第1行出现错误：ORA-01722:无效数字我们看到，报无效数字错误。当然，这里Oracle使用了自动类型转换将'james'转为数字类型，但是这个转换是失败的，所以报错，所以自动类型转换的第1个规则就是必须自动类型转换能够成功，否则报错。我们看下面的就转换成功了：DINGJUN123>select5*10+'2'fromdual;5*10+'2'52OK,看到了结果正确，这里的字符串'2'被自动转为数值类型的2(不明白为什么会这样转换，请往下看)，所以结果为52.。29.2.1为什么不建议使用自动类型转换?自动类型转换的确可以让我们少写一些内容，比如可以少写个to_char函数之类的东西,但是它经常是不好的：1.使用显示类型转换会让我们的SQL更加容易被理解，也就是可读性更强，但是自动类型转换却没有这个优点，如：DINGJUN123>selectto_date(sysdate,'yyyymm')fromdual;也许你会想，我没有看错吧,你写的语句是错的,to_date中间的第1个参数是字符类型哦,你提的这个问题很好，我想你应该需要了解了解Oracle中的自动类型转换了。我可以很明确地告诉你，这个语句是可以的，但是能不能运行正确就要依赖于具体的上下文了，比如这里sysdate是date类型，那么需要将date类型转为字符，这是自动转换的，也就是Oracle要自动调用to_char(sysdate,fmt)，这个fmt就依赖于上下文的nls_date_format,也有可能会依赖于nls_date_language的设置，看我们的结果：DINGJUN123>altersessionsetnls_date_format='yyyymm';会话已更改。DINGJUN123>selectto_date(sysdate,'yyyymm')fromdual;TO_DAT201005DINGJUN123>altersessionsetnls_date_format='yyyymondd';会话已更改。DINGJUN123>selectto_date(sysdate,'yyyymondd')fromdual;TO_DATE(SYSDAT20105月16DINGJUN123>altersessionsetnls_date_language='American';会话已更改。DINGJUN123>selectto_date(sysdate,'yyyymondd')fromdual;TO_DATE(SYSD—2010may16自动类型转换的确难以理解，不知道的人以为这真是太神奇了，可能以为Oracle的函数定义搞错了，还是了解下这方面的内容吧，这样才可以运筹帷幄，决胜千里。2.自动类型转换往往对性能产生不好的影响，特别是左值的类型被自动转为了右值的类型。这种方式很可能使我们本来可以使用索引的而没有用上索引，也有可能会导致结果出错。如：DINGJUN123>droptablet;表已删除。DINGJUN123>createtablet(namevarchar2(10));表已创建。DINGJUN123>insertintotvalues('abc');已创建1行。DINGJUN123>insertintotvalues('1');已创建1行。DINGJUN123>commit;提交完成。DINGJUN123>createindexidx_tont(name);索引已创建。 案例1:自动类型转换导致出错 DINGJUN123>select*fromtwherename=1;select*fromtwherename=1*第1行出现错误：ORA-01722:无效数字DINGJUN123>select*fromtwherename='1';NAME—1 -案例2:自动类型转换导致本该用索引而没有用 DINGJUN123>explainplanforselect*fromtwherename=1;已解释。DINGJUN123>select*fromtable(dbms_xplan.display);PLAN_TABLE_OUTPUT—Planhashvalue:1601196873—|Id|Operation |Name|—|0|SELECTSTATEMENT| ||*1|TABLEACCESSFULL|T |—PredicateInformation(identifiedbyoperationid):—PLAN_TABLE_OUTPUT-filter(TO_NUMBER("NAME")=1)Note—-rulebasedoptimizerused(considerusingcbo)DINGJUN123>explainplanforselect*fromtwherename='1';

已解释。已解释。DINGJUN123>select*fromtable(dbms_xplan.display);DINGJUN123>select*PLAN_TABLE_OUTPUTPlanhashvalue:2296882198—|Id|Operation |Name|—| 0|SELECTSTATEMENT| ||*1|INDEXRANGESCAN|IDX_T|—PredicateInformation(identifiedbyoperationid):—PLAN_TABLE_OUTPUT—1-access("NAME"='1')Note—-rulebasedoptimizerused(considerusingcbo)我们看案例1,如果这个语句很庞大，找这个错误还真不容易，如果是显示转换的话，找个错误就容易多了。案例2我使用RBO优化器的，我没有收集统计信息，而且还加了rule,这里不加rule一样，如果列自动发生了类型转换，很可能使索引失效，这句select*fromtwherename=1没有写select*fromtwhereto_number(name)=1发现索引失效明显。但是如果我们感觉应该用索引而没有用上索引，而且左边的列和右边的值类型不一样，那么很可能发生了自动类型转换，当然看执行计划有这样的类型转换信息，虽然我们没有显示地写，往往看执行计划是我们第1步寻找问题的方法。自动类型转换可能依赖于发生转换时的上下文环境，比如1中的to_date(sysdate,fmt),一旦上下文环境改变，很可能我们的程序就不能运行。自动类型转换的算法或规则，以后Oracle可能改变，这是很危险的，意味着旧的代码很可能在新的Oracle版本中运行出现问题(性能、错误等)，显示类型转换总是有最高的优先级，所以显示类型转换没有这种版本更替可能带来的问题。自动类型转换是要消耗时间的，当然同等的显式类型转换时间也差不多，最好的方法就是避免类似的转换，在显示类型转换上我们会看到，最好不要将左值进行类型转

换，到时候有索引也用不上索引，还要建函数索引，索引储存和管理开销增大。29.2.2自动类型转换规则Oracle自动类型转换是根据上下文环境以及一些预定的规则，经过语法语义的分析之后进行相关的自动类型转换，自动类型转换首要条件就是这个转换有意义，要正确，否则转换不成功，要报错,我们前面已经举了这样的例子。看下图，Oracle自动类型转换的矩阵图，图上没有具体地转换方向，但是我们最起码看图了解到一点，自动类型转换不是什么类型都可以相互转换的，有的不可相互自动转换。（-的说明不转换，X的说明可以转换）自动类型转换矩阵图BO-J0MBO-J0Mmo-Jm8010CHMOIXMW0NO—I-LV2LLAHVNEBLlJmsnNTVAtiUJJ_N_Lu豆dSHVHOUVANHVHONIXVHOCHARXXXXXXXXXXXXXVARCHAR2XXXXXXXXXXXXXNCHARXXXXXXXXXXXXXNVARCHAR2XXXXXXXXXXXXXDATEXXXXDATETIME/INTERVMXXXXXNUMBERXXXXXXBINARY,FLOATXXXXXXBINARY,DOUBLEXXXXXXLONGXXXXXXXXRAWXXXXXXROWiDXXXCLOBXXXXXXBLOBXNCLOBXXXXXXOracle自动类型转换有如下规则（转换方向）：1.在insert和update语句中，Oracle将赋值的类型转为目标列的类型。这很容易理解，当然最终存到我们目标列的类型是要符合定义的，如:DINGJUN123>droptablet;表已删除。DINGJUN123>createtablet(xvarchar2(100));表已创建。DINGJUN123>insertintotvalues(sysdate);已创建1行。DINGJUN123>selectxfromt;X2010may16看到了吧，其实sysdate在插入的时候就已经根据nls_date_format和nls_date_language参数转为字符类型varchar2(100)了。在SELECT中,Oracle会自动将查询到的列的值转为目标变量的类型。如：DINGJUN123>declarevarchar(10);beginselect1intovarfromdual;dbms_output.put_line('varis'llvarll',thelengthis'lllength(var));end;/varis1 ,thelengthis10看，数值1被转为char(10)了。对数值类型的操作，Oracle经常将数值类型的值调整为最大的精度(precision)和刻度(scale)，这种情况下经常看到的结果和表中存储的结果不一样。当比较字符与数值的时候，数值会有更高的优先级，也就是将字符转为数值进行比较。DINGJUN123>explainplanforselect*fromtwherex=1;DINGJUN123>select*fromtable(dbms_xplan.display);PLAN_TABLE_OUTPUTPlanhashvalue:1601196873IdlOperation lNamell0lSELECTSTATEMENTl ll*1lTABLEACCESSFULLlTlPredicateInformation(identifiedbyoperationid):—-filter(TO_NUMBER("X")=1)Note—-rulebasedoptimizerused(considerusingcbo)看上面的t表的x列是varchar2类型，select*fromtwherex=1将列x自动通过to_number转为数值类型了。在字符类型、NUMBER数值类型与浮点类型的数值之间相互转换，可能会丢失精度，因为NUMBER是以10进制(0-9)精度表示数字的，而浮点类型数值是以二进制(0和1)表示的精度。DINGJUN123>droptablet;表已删除。DINGJUN123>createtablet(xbinary_float);表已创建。DINGJUN123>insertintotvalues(1234567);已创建1行。DINGJUN123>insertintotvalues(123456789);已创建1行。DINGJUN123>columnxformat9999999999999DINGJUN123>select*fromt;X—1234567123456792我们插入的时候是NUMBER类型，但是实际表是BINARY_FLOAT那么肯定要转为BINARY_FLOAT类型，看123456789插入的时候就发生了精度的丢失。将CLOB转为字符类型或将BLOB转为RAW类型的时候，如果被转换的类型长度比目标类型长，那么会出错，其实，其他的类型转换在自动类型，显示类型转换中如果被转换的类型的长度比目标类型长，那么都是会报错的(但是在某些函数中自动截断，不报错，见第14)。DINGJUN123>droptablet;表已删除。DINGJUN123>createtablet(xvarchar2(10));表已创建。DINGJUN123>insertintotvalues(to_clob('12212121212121'));insertintotvalues(to_clob('12212121212121'))*第1行出现错误：ORA-12899:歹U”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>droptablet;表已删除。DINGJUN123>createtablet(xdate);表已创建。DINGJUN123>insertintotvalues(to_date('2010-01-01','yyyy-mm-dd'));已创建1行。DINGJUN123>select*fromtwherex='2010-01-01';select*fromtwherex='2010-01-01'*第1行出现错误：ORA-01861:文字与格式字符串不匹配DINGJUN123>altersessionsetnls_date_format='yyyy-mm-dd';会话已更改。DINGJUN123>select*fromtwherex='2010-01-01';X—2010-01-01看，的确可以自动类型转换。'2010-01-01'根据nls_date_format和nls_date_language转为了DATE类型。9.当使用SQL函数或操作符的时候，如果传入的类型和实际应该接受的类型不一致，那么将传入的类型根据上下文环境转为一致。DINGJUN123>selectreplace(12345,4)fromdual;REPLACE(1235DINGJUN123>select'10'+'0'fromdual;'10'+'0'DINGJUN123>select'10'll0fromdual;'10'll10010看上面的例子,replace接受的参数是两个字符类型，但是我们的是两个数值类型，会自动转为字符类型，返回值也是字符类型。’10'+'0'会根据操作符环境自动转为10+0，最终结果是数值类型，而'10'110会将0转为'0'(CHAR)所以结果是字符'100'。经常看到有人问起我的日期怎么格式化不对啊，如下：DINGJUN123>setserveroutputonDINGJUN123>begindbms_output.put_line(to_date('20100511','yyyymmdd'));end;/11-5月-10PL/SQL过程已成功完成。你真的格式化了吗?还是和前面说的to_date(sysdate,fmt)类似,dbms_output.put_line过程只接受字符类型的参数，你传入了日期，当然要自动转换成字符了，同前面说的一样依赖于nls环境的设置,不想依赖于于环境那么再次to_char一下就可以了。当做赋值操作仁)的时候,Oracle会将右边被赋的值的类型自动转为和左边目标类型一致的类型。其实前面我们说的select语句的值赋给目标变量也类似。注意我们这里说的赋值操作可不是wherexx=yy中=(这里的是比较操作)，而是赋值给变量或歹L比如update,PL/SQL中的赋值操作。在做连接操作的时候，Oracle会将非字符类型转为CHAR或NCHAR0第9点已经举了例子说明。在字符和非字符之间的算术和比较操作中，ORACLE会根据日期，ROWID，数值类型优先级最大来进行转换。算术操作一般都要转为 NUMBER，比如whererowid='...'要将字符串转为ROWID，wheredate='....'会将字符串根据nls的设置转为日期类型。DINGJUN123>selectrowidfromt;ROWIDAAAOi7AAEAAAPpWAAADINGJUN123>select*fromtwhererowid='AAAOi7AAEAAAPpWAAA';X2010-01-01DINGJUN123>select*fromtwherex='2010-01-012010-01-01DINGJUN123>selectto_char(x,'yyyymmdd')+1fromt;TO_CHAR(X,'YYYYMMDD')+1—20100102表t中的x是DATE类型，看字符与rowid比较会将字符转为rowid类型。字符与数字运算转为数值类型，日期与字符比较会将字符转为日期根据nls的设置。我们再看一个例子说明这种自动类型转换的特点：DINGJUN123>droptablet;表已删除。DINGJUN123>createtabletaswithtmpas(select'15'idfromdualunionallselect'2'fromdualunionallselect'38'fromdualunionallselect'4'fromdual)select*fromtmp;表已创建。 选择的结果按字符类型排序的，不符合要求 DINGJUN123>select*fromtorderbyid;j—152384 自动转换数值类型排序，当然最好用to_number(id) DINGJUN123>select*fromtorderbyid+0;j---253813.字符类型之间的类型转换，CHAR,VACHAR2,NCHAR,NVARCHAR2，我们知道，NVACHAR2需要国家字符集(9i后有UTF8和AL16UTF16)的支持，而且是按字符存储的，CHAR,VARCHAR2受数据库默认字符集的支持。那么数据库字符集支持的CHAR,VARCHAR2默认转换到NCHAR,NVARCHAR2,当然VARCHAR2与CHAR是CHAR转VARCHAR2，如下：到UCHAR到VARCHAR2到UNCHAR到NVARCHAR2CHAR--VARCHAR2NCHARNVARCHAR2VARCHAR2VARCHAR2--NVARCHAR2NVARCHAR2NCHARNCHARNCHAR--NVARCHAR2NVARCHAR2NVARCHAR2NVARCHAR2NVARCHAR2--我们看到，NVARCHAR2最大，所有的遇到它都要自动转为NVARCHAR2类型。CHAR遇到VARCHAR2要转为VARCHAR2。14,很多SQL函数可以接受CLOB类型，对参数要求是VARCHAR2或CHAR的如果传入CLOB类型也是可以