Oracle中的类型转换
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'
----------
52
OK,看到了结果正确,这里的字符串'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
------
201005
DINGJUN123>alter session set nls_date_format='yyyymondd';
会话已更改。
DINGJUN123>select to_date(sysdate,'yyyymondd') from dual;
TO_DATE(SYSDAT
--------------
20105月 16
DINGJUN123>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>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)了。
3. 对数值类型的操作,Oracle经常将数值类型的值调整为最大的精度(precision)和刻度(scale),这种情况下经常看到的结果和表中存储的结果不一样。
4. 当比较字符与数值的时候,数值会有更高的优先级,也就是将字符转为数值进行比较。
DINGJUN123>explain plan for select * from t where x = 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):
---------------------------------------------------
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 9999999999999
DINGJUN123>select * from t;
X
------------------------------------------------------
1234567
123456792
我们插入的时候是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(
--------
1235
DINGJUN123>select '10'+0 from dual;
'10'+0
--------------------------------------------------------------------------
10
DINGJUN123>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
------------------
AAAOi7AAEAAAPpWAAA
DINGJUN123>select * from t where rowid = 'AAAOi7AAEAAAPpWAAA';
X
----------
2010-01-01
DINGJUN123>select * from t where x = '2010-01-01'
2 ;
X
----------
2010-01-01
DINGJUN123>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 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;
j
----
2
4
15
38
13. 字符类型之间的类型转换,CHAR,VACHAR2,NCHAR,NVARCHAR2,我们知道,NVACHAR2需要国家字符集(9i后有UTF8和AL16UTF16)的支持,而且是按字符存储的,CHAR,VARCHAR2受数据库默认字符集的支持。那么数据库字符集支持的CHAR,VARCHAR2默认转换到NCHAR,NVARCHAR2,当然VARCHAR2与CHAR是CHAR转VARCHAR2,如下:
到CHAR 到VARCHAR2 到NCHAR 到NVARCHAR2
CHAR -- VARCHAR2 NCHAR NVARCHAR2
VARCHAR2 VARCHAR2 -- NVARCHAR2 NVARCHAR2
NCHAR NCHAR NCHAR -- NVARCHAR2
NVARCHAR2 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;
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'
----------
52
OK,看到了结果正确,这里的字符串'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
------
201005
DINGJUN123>alter session set nls_date_format='yyyymondd';
会话已更改。
DINGJUN123>select to_date(sysdate,'yyyymondd') from dual;
TO_DATE(SYSDAT
--------------
20105月 16
DINGJUN123>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>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)了。
3. 对数值类型的操作,Oracle经常将数值类型的值调整为最大的精度(precision)和刻度(scale),这种情况下经常看到的结果和表中存储的结果不一样。
4. 当比较字符与数值的时候,数值会有更高的优先级,也就是将字符转为数值进行比较。
DINGJUN123>explain plan for select * from t where x = 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):
---------------------------------------------------
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 9999999999999
DINGJUN123>select * from t;
X
------------------------------------------------------
1234567
123456792
我们插入的时候是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(
--------
1235
DINGJUN123>select '10'+0 from dual;
'10'+0
--------------------------------------------------------------------------
10
DINGJUN123>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
------------------
AAAOi7AAEAAAPpWAAA
DINGJUN123>select * from t where rowid = 'AAAOi7AAEAAAPpWAAA';
X
----------
2010-01-01
DINGJUN123>select * from t where x = '2010-01-01'
2 ;
X
----------
2010-01-01
DINGJUN123>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 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;
j
----
2
4
15
38
13. 字符类型之间的类型转换,CHAR,VACHAR2,NCHAR,NVARCHAR2,我们知道,NVACHAR2需要国家字符集(9i后有UTF8和AL16UTF16)的支持,而且是按字符存储的,CHAR,VARCHAR2受数据库默认字符集的支持。那么数据库字符集支持的CHAR,VARCHAR2默认转换到NCHAR,NVARCHAR2,当然VARCHAR2与CHAR是CHAR转VARCHAR2,如下:
到CHAR 到VARCHAR2 到NCHAR 到NVARCHAR2
CHAR -- VARCHAR2 NCHAR NVARCHAR2
VARCHAR2 VARCHAR2 -- NVARCHAR2 NVARCHAR2
NCHAR NCHAR NCHAR -- NVARCHAR2
NVARCHAR2 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;