ORACLE专题-自动类型转换..docVIP

ORACLE专题-自动类型转换 简介 Oracle中对不同类型数据的处理有显式类型转换和自动类型转换两种方式，显式类型转换是可控的，但自动类型转换，不建议使用，很难控制，有不少缺点。虽然Oracle不建议使用自动类型转换，但在开发中，经常遇到自动类型转换，如果不了解自动类型转换，那么查找和解决问题都会变的很困难。 自动类型转换缺点 自动类型转换会导致本该使用的索引而没有使用 SQL EXPLAIN PLAN FOR SELECT * FROM EMP WHERE ENAME = 1; ID Operation Name 0 SELECT STATEMENT 1 TABLE ACCESS FULL EMP 未使用索引原因：NAME=1，因为NAME是字符型，字符与数值比较，则字符自动转化为数值类型。 SQL EXPLAIN PLAN FOR SELECT * FROM EMP WHERE ENAME = ‘1’; ID Operation Name 0 SELECT STATEMENT 1 INDEX RANGE SCAN IDX_EMP 上面为正确用法（1加上引号），没有进行类型转换，使用索引了，该测试在RBO优化器模式下。 自动类型转换导致SQL执行时出错 SQL SELECT * FROM EMP WHERE ENAME = 1 ERROR: ORA_01722：无效数字 出错原因：ENAME（值为‘SMITH’等）转为数值类型失败，报无效数字错误。 自动类型转换本身是要消耗时间的 当然同等的显式类型转换消耗时间也出不多，最好的方式就是避免类似的转换。 自动类型转换的算法和规则，以后Oracle可能改变，很危险，意味着旧的代码很可能在新的Oracle版本中运行出现问题，显式类型转换则不存在这个问题。 自动类型转换的一般规则 自动类型转换时根据上下文及一些预定的规则，经过语法语义分析之后进行相关的类型转换。 转化为目标类型 在INSERT和UPDATE语句中，Oracle将赋值的类型转为目标列的类型。 在SELECT中，Oracle将查询到的列的数据类型自动转为目标变量的类型 当比较字符与数值时，数值会有更高的优先级，也就是将字符转为数值后进行比较 当字符串与DATE类型比较，将字符串转为DATE类型 SQL CREATE TABLE T AS SELECT TO_DATE(‘2010-06-12’,’YYYY-MM-DD’) X FROM DUAL; SQLSELECT * FROM T WHERE X=’2010-06-12’; SQL INSERT INTO T VALUES(123456789); SQL SELECT x FROM T; X ----------- 1234567 123456792 在字符类型（可转化为数值的字符）、NUMBER类型与浮点类型转换，可能丢失精度（数值型字符和NUMBER是十进制表示数字的，而浮点类型是以二进制表示的） SQL CREATE TABLE T(x BINARY_FLOAT); SQL INSERT INTO T VALUES(1234567); SQL INSERT INTO T VALUES(123456789); SQL SELECT x FROM T; X ----------- 1234567 123456792 插入时是NUMBER类型，但实际该列为BINARY_FLOAT，要转化为BINARY_FLOAT类型，就发生了精度的丢失。 当使用SQL函数与操作符的时候，如传入的类型和实际应该接受的类型不一致，那么会将传入的类型根据具体需要转为一致。 当做赋值操作（=）时，将右边被赋的值的类型自动转为左边目标类型一致的类型 自动类型转换常见错误 下面为一个在PLSQL程序中常犯的错误说明自动类型转换容易导致的问题，特别是DATE类型的自动转换问题。 CREATE TABLE T(ID NUMBER,DONE_DATE DATE); --建立存储过程的并没有报错 CREATE OR REPLACE PROCDURE sp_t(i_tablename varchar2,i_date date,o_count out number) --根据传入的表名和日期查询大于此日期的记录 AS V_sql varchar2(2000); BEGIN v_sql := ‘SE