About me:Oracle ACE pro,optimistic,passionate and harmonious.
Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
-------------------------------------案例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);
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步寻找问题的方法。
X -------------------- 2010may16 看到了吧,其实sysdate在插入的时候就已经根据nls_date_format和nls_date_language参数转为字符类型varchar2(100)了。
2. 在SELECT中,Oracle会自动将查询到的列的值转为目标变量的类型。如: DINGJUN123>declare 2 var char(10); 3 begin 4 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)了。
10. 当做赋值操作(=)的时候,Oracle会将右边被赋的值的类型自动转为和左边目标类型一致的类型。其实前面我们说的select语句的值赋给目标变量也类似。注意我们这里说的赋值操作可不是where xx = yy中=(这里的是比较操作),而是赋值给变量或列,比如insert,update,PL/SQL中的赋值操作。
DINGJUN123>create table t 2 as 3 with tmp as 4 (select '15' id from dual 5 union all 6 select '2' from dual 7 union all 8 select '38' from dual 9 union all 10 select '4' from dual) 11 select * from tmp;
表已创建。
--------------------选择的结果按字符类型排序的,不符合要求------------- DINGJUN123>select * from t order by id;
j ---- 15 2 38 4
------------自动转换数值类型排序,当然最好用to_number(id)---------------------- DINGJUN123>select * from t order by id+0;
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;