探索Oracle自动类型转换_PART2

自动类型转换规则

    Oracle自动类型转换是根据上下文以及一些预定的规则，经过语法语义的分析之后进行相关的类型转换。自动类型转换是由Oracle自己控制的，自动类型转换首要条件就是这个转换有意义，要正确，否则转换不成功则报错。如：

--下面的转换是不成功的，因为+号的意义在Oracle中是数学运算，所以将'ab'转为数字不成功

DINGJUN123>SELECT 5*10+'ab' FROM DUAL;

SELECT 5*10+'ab' FROM DUAL

            *

第 1 行出现错误:

ORA-01722: 无效数字

--下面转换成功了,'11'可以自动转为数字

DINGJUN123>SELECT 5*10+'11' FROM DUAL;

 5*10+'11'

------------

        61

       

看下图，Oracle自动类型转换的矩阵图，图上没有标明转换方向，但是看图至少了解到自动类型转换不是什么类型之间都可以相互转换的，有的类型之间不可相互自动转换 (-的说明不可转换，X的说明可以转换)。

 自动类型转换矩阵图

Oracle自动类型转换有如下规则(注意转换方向):

1.  在INSERT和UPDATE语句中，Oracle将赋值的类型转为目标列的类型。这很容易理解，不转为目标列类型就不符合列的定义了。如：

DINGJUN123>SELECT parameter,value

  2   FROM NLS_SESSION_PARAMETERS

  3   WHERE parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE') ;

PARAMETER                                VALUE

---------------------------------------- ---------------------------------

NLS_DATE_FORMAT                          YYYY-MM-DD

NLS_DATE_LANGUAGE                        SIMPLIFIED CHINESE

表已删除。

DINGJUN123>CREATE TABLE t

  2        (x VARCHAR2(100));

表已创建。

DINGJUN123>INSERT INTO t VALUES(SYSDATE);

已创建 1 行。

DINGJUN123>SELECT x FROM t;

X

---------------------

2010-06-11

看到了吧，其实SYSDATE在插入的时候就已经根据参数NLS_DATE_FORMAT和NLS_DATE_LANGUAGE转为字符类型了。

2.  在SELECT中，Oracle会将查询到的列的数据类型自动转为目标变量的类型。如：

DINGJUN123>SET SERVEROUTPUT ON

DINGJUN123>DECLARE

  2      var CHAR(10);

  3  BEGIN

  4      SELECT 1

  5       INTO var

  6       FROM DUAL;

  7      DBMS_OUTPUT.PUT_LINE('var is ' || var || ',the length is ' || LENGTH(var));

  8  END;

  9    /

var is 1         ,the length is 10

PL/SQL 过程已成功完成。

    从上面的结果看出，数字1被转为CHAR(10)了。

3.  对数值类型的操作，Oracle经常调整其精度(precision)和刻度(scale)，从而允许最大容量，这种情况下经常看到的结果类型和表中存储的类型不一样(指精度和刻度不一样)。如：

表已删除。

DINGJUN123>CREATE TABLE t AS

  2  SELECT CAST(3 AS NUMBER(2)) AS id FROM DUAL;

表已创建。

DINGJUN123>SELECT id/8 FROM t;

      ID/8

-----------------

      .375

   

    上面的结果为0.375，与表中ID存储的NUMBER(2)类型不同。

4.  当比较字符与数值的时候，数值会有更高的优先级，也就是将字符转为数值进行比较。如：

表已删除。

DINGJUN123>CREATE TABLE t(x VARCHAR2(100));

表已创建。

DINGJUN123>SELECT * FROM t WHERE x = 1;

未选定行

DINGJUN123>SET AUTOTRACE ON EXPLAIN

DINGJUN123>SELECT * FROM t WHERE x = 1;

未选定行

执行计划

----------------------------------------------------------

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进制表示数字的，而浮点类型是以二进制表示。如：

表已删除。

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 999999999

DINGJUN123>SELECT * FROM t;

         X

----------

   1234567

 123456792

插入的时候是NUMBER类型，但是实际表是BINARY_FLOAT，那么肯定要转为BINARY_FLOAT类型，看123456789插入的时候就发生了精度的丢失。

6.  将CLOB转为字符类型（如VARCHAR2）或将BLOB转为RAW类型的时候，如果被转换的类型长度比目标类型长，那么会出错。

表已删除。

DINGJUN123>CREATE TABLE t

  2  ( x VARCHAR2(10));

表已创建。

DINGJUN123>INSERT INTO t VALUES(TO_CLOB('121212121212'));

INSERT INTO t VALUES(TO_CLOB('121212121212'))

                     *

第 1 行出现错误:

ORA-12899: 列 "DINGJUN123"."T"."X" 的值太大 (实际值: 12, 最大值: 10)

   

目标列最大长度为10字节，而插入了12字节，所以报错。

7.  BINARY_FLOAT自动转为BINARY_DOUBLE是精确的，反之，BINARY_DOUBLE自动转为BINARY_FLOAT可能就不准确了。注意数值类型之间的优先级顺序：BINARY_DOUBLE > BINARY_FLOAT > NUMBER，因为比如目标列是BINARY_FLOAT，赋值的是NUMBER，则会转为BINARY_FLOAT类型。

--案例1:BINARY_FLOAT转BINARY_DOUBLE精确--------------------------------------

DINGJUN123>DROP TABLE t;

表已删除。

DINGJUN123>CREATE TABLE t(x BINARY_DOUBLE);

表已创建。

DINGJUN123>DROP TABLE t1;

表已删除。

 

DINGJUN123>CREATE TABLE t1(x BINARY_FLOAT);

表已创建。

DINGJUN123>INSERT INTO t1 VALUES(3.42E+37F);

已创建 1 行。

DINGJUN123>INSERT INTO t

  2  SELECT x FROM t1;

已创建 1 行。

DINGJUN123>SELECT x  FROM t;

         X

----------

 3.42E+037

已选择 1 行。

DINGJUN123>SELECT x FROM t1;

         X

----------

 3.42E+037

已选择 1 行。

--案例2:BINARY_DOUBLE转为BINARY_FLOAT不精确-------------------------

DINGJUN123>TRUNCATE TABLE t;

表被截断。

DINGJUN123>TRUNCATE TABLE t1;

表被截断。

DINGJUN123>INSERT INTO t VALUES(1.79769313486E+39);

已创建 1 行。

DINGJUN123>INSERT INTO t1

  2  SELECT x FROM t;

已创建 1 行。

DINGJUN123>SELECT * FROM t;

         X

----------

1.798E+039

已选择 1 行。

DINGJUN123>SELECT x FROM t1;

         X

----------

       Inf

已选择 1 行。

     从案例1看出，BINARY_FLOAT转BINARY_DOUBLE没有问题，但是BINARY_DOUBLE值转为BINARY_FLOAT需要更多精度支持的时候，则不准确，如案例2，BINARY_DOUBLE：1.79769313486E+39转为BINARY_FLOAT变为INFINITY(无穷大)。

8.  当字符串与DATE类型比较，DATE类型具有较高优先级，将字符串转为DATE类型，这种自动转换需要上下文的支持，和第1点类似。在下一部分我会用PL/SQL常见的拼凑字符串说明DATE类型自动转换的常见错误。

表已删除。

DINGJUN123>CREATE TABLE t

  2       AS SELECT TO_DATE('2010-06-12','YYYY-MM-DD') x

  3  FROM DUAL;

表已创建。

DINGJUN123>SELECT * FROM t WHERE x = '2010-06-12';

SELECT * FROM t WHERE x = '2010-06-12'

                          *

第 1 行出现错误:

ORA-01861: 文字与格式字符串不匹配

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

会话已更改。

DINGJUN123>SELECT * FROM t WHERE x = '2010-06-12';

X

----------

2010-06-12

从结果看出，'2010-01-01'根据NLS_DATE_FORMAT转为了DATE类型。

9.  当使用SQL函数或操作符的时候，如果传入的类型和实际应该接受的类型不一致，那么会将传入的类型根据具体需要转为一致。

DINGJUN123>SELECT REPLACE(12345,4) x FROM DUAL;

X

--------

1235

DINGJUN123>SELECT '10' + 0 x FROM DUAL;

         X

----------------

        10

DINGJUN123>SELECT '10' || 0 x FROM DUAL;

X

------

100

    看上面的例子,REPLACE接受的参数是两个字符类型，但是我传的是两个数值类型，会自动转为字符类型，返回值也是字符类型(SQL*PLUS里的字符左对齐，数值右对齐)。'10'+0中的'10'根据操作符环境自动转为10，最终结果是数值类型，而'10'||0会将0转为'0'，所以结果是字符'100'。

10.当做赋值操作(=)的时候,Oracle会将右边被赋的值的类型自动转为和左边目标类型一致的类型。其实前面说的SELECT语句的值赋给目标变量也类似。注意我这里说的赋值操作可不是WHERE column = yy中=（WHERE条件的中的=是比较操作，按比较操作规则），而是说赋值给变量或列，比如UPDATE,PL/SQL中的赋值操作。

11.在做连接操作的时候，Oracle会将非字符类型转为字符类型。实际上这也是根据具体上下文和运算环境决定的自动转换，第9点已经举了例子说明。

12.在字符和非字符之间的算术和比较操作中，ORACLE会将字符转为日期，ROWID，数值类型。算术操作一般都要转为数值类型，和ROWID比较如WHERE ROWID='…'，要将字符转为ROWID，和日期比较如WHERE date_column ='….'，会将字符串根据nls参数的设置转为日期类型。

--使用8里面的表

DINGJUN123>SELECT ROWID FROM t;

ROWID

------------------

AAAPCiAAEAAAVfUAAA

DINGJUN123>SELECT * FROM t

  2  WHERE ROWID = 'AAAPCiAAEAAAVfUAAA';

X

----------------

2010-06-12

    上面的右边的字符串被转为ROWID类型了。

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。如：

DINGJUN123>SET SERVEROUTPUT ON

DINGJUN123>DECLARE

 2   a CHAR(4):='ab  ';

  3   b VARCHAR2(4):='ab';

  4   BEGIN

  5    IF a = b THEN

  6        DBMS_OUTPUT.PUT_LINE('a = b');

  7    ELSE

  8         DBMS_OUTPUT.PUT_LINE('a <> b');

  9   END IF;

 10   END;

 11  /

a <> b

PL/SQL 过程已成功完成。

    如果a = b是VARCHAR2转为CHAR类型，那么采用填补空格的比较，则肯定相等，但是现在的结果是不等的，那是因为CHAR被转为了VARCHAR2，从而采用非填补空格的比较方式。

14.很多SQL字符函数可以接受CLOB类型(比如SUBSTR,INSTR等都能接受CLOB类型)。对不接受CLOB类型的会自动转为字符类型。对参数要求是VARCHAR2或CHAR的，但是不允许CLOB类型的，如果传入CLOB类型也是可以的，但是有最大长度限制，只能最大4000字节，否则报错。另外有些函数比如LPAD，RPAD等如果上下文是CHAR或VARCHAR2，也最多只能取4000字节。如：

--返回4000，LPAD如果第1个参数是字符类型，最大只能是4000个字节

DINGJUN123>SELECT LENGTH(TO_CLOB(LPAD('a',6000,'a')))

  2  FROM DUAL;

LENGTH(TO_CLOB(LPAD('A',6000,'A')))

-----------------------------------

                               4000

--返回6000，LPAD如果第1个参数是CLOB,那么最大可以达到CLOB最大长度

DINGJUN123>SELECT LENGTH(TO_CLOB(LPAD(TO_CLOB('a'),6000,'a')))  len

  2  FROM DUAL;

       LEN

----------

      6000

--返回6000,SUBSTR也可以接受CLOB列，则返回CLOB

DINGJUN123>SELECT LENGTH(SUBSTR(TO_CLOB(LPAD(TO_CLOB('a'),6000,'a')),1,6000)) len

  2  FROM DUAL;

       LEN

----------

      6000

--报错，INITCAP不接受CLOB列，自动类型转换只允许最多4000个字节

DINGJUN123>SELECT LENGTH(INITCAP(TO_CLOB(LPAD(TO_CLOB('a'),6000,'a')))) len

  2  FROM DUAL;

SELECT LENGTH(INITCAP(TO_CLOB(LPAD(TO_CLOB('a'),6000,'a')))) len

                      *

第 1 行出现错误:

ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 6000, 最大: 4000)

--正确，取最大4000字节

DINGJUN123>SELECT LENGTH(INITCAP(TO_CLOB(LPAD(TO_CLOB('a'),4000,'a'))))

  2  FROM DUAL;

     LEN

----------

      4000

15.上面很多规则说的都是SQL中的规则，那么在PL/SQL中也会存在类似的规则，只需要注意一下SQL和PL/SQL的区别即可，比如SQL中的VARCHAR2最大4000字节，在PL/SQL中最大为32767字节，以第14点为例子，在SQL和PL/SQL中就有区别，对于PL/SQL的自动类型转换规则一般都可以根据类型的区别推算出，所以只举一个例子说明，读者有兴趣可以详细研究一下。

DINGJUN123>SET SERVEROUTPUT ON

--在PL/SQL中LPAD('a',6000,'a')是6000字节，但是在SQL中只能取到4000字节

--在PLSQL中LPAD('a',6000,'a') || 'a'是正确的，但是在SQL中就超出了4000字节的范围，运算出错

--在PL/SQL中，超出定义的最大字节数32767也出错

DINGJUN123>DECLARE

  2   v_str VARCHAR2(32767);

  3  BEGIN

  4   v_str := LPAD('a',6000,'a');

  5   DBMS_OUTPUT.PUT_LINE(LENGTH(v_str));

  6   v_str := v_str ||'a';

  7  DBMS_OUTPUT.PUT_LINE(LENGTH(v_str));

  8   v_str := LPAD('a',32768,'a');

  9  END;

 10  /

6000

6001

DECLARE

*

第 1 行出现错误:

ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小

ORA-06512: 在 line 8

DINGJUN123>SELECT LENGTH(LPAD('a',6000,'a')) FROM DUAL;

LENGTH(LPAD('A',6000,'A'))

--------------------------

                      4000

已选择 1 行。

DINGJUN123>SELECT LENGTH(LPAD('a',6000,'a') || 'a') FROM DUAL;

SELECT LENGTH(LPAD('a',6000,'a') || 'a') FROM DUAL

                                              *

第 1 行出现错误:

ORA-01489: 字符串连接的结果过长