探索Oracle自动类型转换（上）

IT那活儿发布于2023-01-11 13:19 / 2625人阅读

探索Oracle自动类型转换（上）

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

Oracle数据处理

Oracle中对不同类型数据的处理有显式类型转换(Explicit)和自动类型转换(或叫隐式类型转换Implicit)两种方式，这和其他语言类似，对显式类型转换，是可控的，但是对自动类型转换，不建议使用，因为很难控制，有不少缺点，比如可能会对性能产生不好的影响。

虽然Oracle不建议使用自动类型转换，但是在Oracle开发中，会经常遇到自动类型转换，这时如果你不了解自动类型转换的规则，那么查找和解决问题就会变得很困难，所以，Oracle开发和维护人员很有必要了解自动类型转换的相关规则，从而对自动类型转换了然于胸。

本文首先会介绍自动类型转换的缺点，然后阐述Oracle自动类型转换的规则，并结合实例分析自动类型转换可能造成的问题。

为什么不建议使用自动类型转换

自动类型转换的确可以让我们少写一些代码，比如可以少写个TO_CHAR函数，SQL看似简单了，但是它却隐藏着危险：

1. 使用显式类型转换会让SQL可读性更强,但是自动类型转换却没有这个优点。

如：

SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL;

看到上面的SQL，也许你会想，我没有看错吧，你写的语句是错的，TO_DATE函数中第1个参数是字符类型才对。你提的这个问题很好，我想你是时候需要了解Oracle自动类型转换规则了。

我可以很明确地告诉你，这个语句有时正确，但是有时却是错误的，正确与否依赖于具体的上下文，比如这里SYSDATE是DATE类型，那么Oracle需要将DATE类型转为字符类型，这是自动转换的，也就是说，Oracle要自动调用TO_CHAR(date,fmt,nlsparam)函数，这个fmt要依赖于上下文的NLS_DATE_FORMAT，nlsparam要依赖于NLS_DATE_LANGUAGE的设置，下面看测试结果：

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMMDD;

会话已更改。

--其实在SQL*PLUS中DATE类型输出就是按照NLS_DATE_FORMAT和NLS_DATE_LANGUAGE参数自动转为字符类型的，这里就是先将SYSDATE转为YYYYMMDD格式，然后再转为DATE类型，最后因为在SQL*PLUS中输出的是字符串，所以又转为了YYYYMMDD格式的字符串。

DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMMDD) FROM DUAL;

TO_DATE(
--------
20210611

--下面的出错了，因为自动转换后SYSDATE变为字符串，格式是YYYYMMDD。

DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL;
SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL
*
第 1 行出现错误:
ORA-01830: 日期格式图片在转换整个输入字符串之前结束

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMM;

会话已更改。

DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL;

TO_DAT
------
202106

--同样地，这个也出错，因为这里的SYSDATE转为YYYYMM格式字符串。

DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMMDD) FROM DUAL;
SELECT TO_DATE(SYSDATE,YYYYMMDD) FROM DUAL
*
第 1 行出现错误:
ORA-01840: 输入值对于日期格式不够长


DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMONDD;

会话已更改。

DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMONDD) FROM DUAL;

TO_DATE(SYSDAT
--------------
20216月 11

DINGJUN123>ALTER SESSION SET NLS_DATE_LANGUAGE = AMERICAN;

会话已更改。

--看NLS_DATE_LANGUAGE设置对结果的影响。

DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMONDD) FROM DUAL;

TO_DATE(SYSD
------------
2021JUN11

自动类型转换的确难以理解，如果对自动类型转换的规则不理解，那么会感觉匪夷所思。

2. 自动类型转换往往对性能产生不好的影响，特别是左值的类型被自动转为了右值的类型(当然如果你写value=column那就左右值互换了，这里说的左值是常规写法：column=value)。这种方式很可能使本来应该使用索引的而没有用上索引，也有可能会导致结果出错。

如：

DINGJUN123>DROP TABLE t;

表已删除。

DINGJUN123>CREATE TABLE t(name VARCHAR2(10));

表已创建。

DINGJUN123>INSERT INTO t VALUES(1);

已创建 1 行。

DINGJUN123>INSERT INTO t VALUES(abc);

已创建 1 行。

DINGJUN123>COMMIT;

提交完成。

DINGJUN123>CREATE INDEX idx_t ON t (name);

索引已创建。

案例1:自动类型转换导致出错

--出错因为NAME转为数值类型失败，abc是无法转为NUMBER类型的。

DINGJUN123>SELECT * FROM t
2 WHERE name = 1;
ERROR:
ORA-01722: 无效数字
未选定行

--正确写法：

DINGJUN123>SELECT * FROM t
2 WHERE name = 1;

NAME
------
1

案例2:自动类型转换导致本该用索引而没有用

--NAME = 1，因为NAME是字符类型，字符与数值比较，则字符自动转为数值类型，见执行计划加粗部分，走全表扫描。

--查看执行计划没有真正执行，因此不报错。

DINGJUN123>EXPLAIN PLAN FOR
2        SELECT * FROM t
3        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):
---------------------------------------------------

  1 - filter(TO_NUMBER("NAME")=1)

Note
-----
- rule based optimizer used (consider using cbo)

案例3:不使用自动类型转换，符合要求

--没有自动类型转换，走索引了，这里的测试是在RBO优化器下，我没有收集统计信息，这里只是做一个演示。

DINGJUN123>EXPLAIN PLAN FOR
2        SELECT * FROM t
3        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):
---------------------------------------------------

 1 - access("NAME"=1)

Note
-----
- rule based optimizer used (consider using cbo)

案例1，如果这个语句很庞大，找这个错误还真不容易，如果使用显式类型转换，找这个错误就容易多了，一般查找类型转换问题，首先看表的字段类型，然后结合执行计划的FILTER部分查看是否发生自动类型转换。

案例2的自动类型转换使表T建立的索引失效(如果直接运行还会出错，但是使用EXPLAIN PLAN查看计划还是可以的)，无法用上索引，导致性能低下，当然，这个测试例子就无所谓性能不性能了。

案例3不使用类型转换，左值和右值都是字符类型，则该走索引就走索引，符合预期结果。

案例1和2中问题是一个低级错误，如果有良好的编码习惯，这种错误自然就可避免。特别是在写存储过程中，一个程序可能会很大，开发人员经常不注意字段类型，导致SQL测试明明性能很好，但是到PL/SQL中运行，效率却很低，这种问题，首先应该定位测试时的SQL与存储过程中的SQL计划是否一致，找出差异，就可以很容易解决这样的问题

3. 自动类型转换依赖于发生转换时的上下文，比如例1中的DATE类型自动转为字符类型，一旦上下文改变，很可能原先的程序就不能运行，所以存在自动类型转换的程序的可移植性无法保证。

4. 自动类型转换的算法或规则，以后Oracle可能会改变，这是很危险的，意味着旧的代码很可能在新的Oracle版本中运行出现问题(性能、错误等)，显式类型转换则不存在这个问题。

5. 自动类型转换是要消耗时间的，当然同等的显式类型转换时间也差不多，最好的方法就是避免类似的转换，对于显式类型转换，最好不要对左值(第2点已经说了左值是相对的)进行类型转换，到时候有索引也用不上索引，可能要建函数索引，索引储存和管理开销增大。

如：

DINGJUN123>DROP TABLE t;

表已删除。

DINGJUN123>CREATE TABLE t
2 AS
3 SELECT SYSDATE+LEVEL done_date
4 FROM DUAL
5 CONNECT BY LEVEL < 10;

表已创建。

DINGJUN123>CREATE INDEX idx_t ON t (done_date);

索引已创建。

现在有这样的需求：需要查找指定日期的行。我经常看到有人这么写：

DINGJUN123>SET AUTOTRACE ON EXPLAIN
DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT=YYYY-MM-DD;

会话已更改。
DINGJUN123>SELECT * FROM t
2        WHERE TO_CHAR(done_date,YYYYMMDD) = 20210612;

DONE_DATE
----------
2021-06-12


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_CHAR(INTERNAL_FUNCTION("DONE_DATE"),YYYYMMDD)=202106
12)

Note
-----
- rule based optimizer used (consider using cbo)

这种写法对左值进行了显式类型转换，导致索引失效，是不很好的写法，但是在实际开发中真是屡见不鲜啊，特别是对日期类型的处理(看优化器中还冒出了个INTERNAL_FUNCTION，对于这个不必深究，这个内部函数在日期类型的自动类型转换中经常出现，比如DATE类型自动转为TIMESTAMP)，强烈建议改变此坏习惯。改写它：

DINGJUN123>SELECT * FROM t
2           WHERE done_date >= TO_DATE(20210612,YYYYMMDD)
3           AND done_date < TO_DATE(20210613,YYYYMMDD);

DONE_DATE
--------------
2021-06-12


执行计划
----------------------------------------------------------
Plan hash value: 2296882198

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 |  INDEX RANGE SCAN| IDX_T |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("DONE_DATE">=TO_DATE(2021-06-12 00:00:00, yyyy-mm-dd
hh24:mi:ss) AND "DONE_DATE"2021-06-13 00:00:00, yyyy-mm-dd
hh24:mi:ss))

Note
-----
- rule based optimizer used (consider using cbo)

OK，索引生效，符合预期，其实很少遇到不能对右值进行改写或不能改写SQL条件而必须要对左值进行转换的情况，如果真遇到这种情况，可以考虑函数索引。

自动类型转换规则

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>DROP TABLE t;

表已删除。

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

表已创建。

DINGJUN123>INSERT INTO t VALUES(SYSDATE);

已创建 1 行。

DINGJUN123>SELECT x FROM t;

X
---------------------
2021-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>DROP TABLE t;

表已删除。

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>DROP TABLE t;

表已删除。

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>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 999999999
DINGJUN123>SELECT * FROM t;

X
----------
1234567
123456792

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

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

DINGJUN123>DROP TABLE t;

表已删除。

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>DROP TABLE t;

表已删除。

DINGJUN123>CREATE TABLE t(x BINARY_DOUBLE);

表已创建。

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

已创建 1 行。

DINGJUN123>DROP TABLE t1;

表已删除。

DINGJUN123>CREATE TABLE t1(x BINARY_FLOAT);

表已创建。

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>DROP TABLE t;

表已删除。

DINGJUN123>CREATE TABLE t
2 AS SELECT TO_DATE(2021-06-12,YYYY-MM-DD) x
3 FROM DUAL;

表已创建。

DINGJUN123>SELECT * FROM t WHERE x = 2021-06-12;
SELECT * FROM t WHERE x = 2021-06-12
*
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配
DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT=YYYY-MM-DD;

会话已更改。

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

X
----------
2021-06-12

从结果看出，2021-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
----------------
2021-06-12

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

13. 字符类型之间的类型转换，CHAR,VACHAR2,NCHAR,NVARCHAR2，NVACHAR2需要国家字符集(9i后有UTF8和AL16UTF16)的支持，而且是按字符存储的，CHAR,VARCHAR2受数据库默认字符集的支持。

数据库字符集支持的CHAR,VARCHAR2默认转换到NCHAR,NVARCHAR2，当然VARCHAR2与CHAR是CHAR转VARCHAR2，如下：

字符类型内部转换表

从上表看出，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: 字符串连接的结果过长

本文说了很多自动类型转换的规则和问题，的确，自动类型转换是容易被Oracle技术人员忽略的重要知识点，在实际应用中，经常会遇到各种各样的自动类型转换问题，通过上面相关内容的描述，我想，你应该对自动类型转换的规则很熟悉了，当然，最好就是杜绝自动类型转换，这样才能避免类似问题的发生。

下文我们再来说一下自动类型转换常见错误。

本文作者：丁俊（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129289.html

一次慢查询暴露的隐蔽的问题

摘要：最近解决了一个生产慢查询的问题，排查问题之后发现一些比较隐匿且容易忽略的问题。所以实际在数据库查询如下可能这里发生一次隐式转换。这次查询走的是索引。 showImg(https://segmentfault.com/img/bVbmJNK?w=6000&h=4000); Photo by Iga Palacz on Unsplash 最近解决了一个生产 SQL 慢查询的问题，排查问题之...

missonce 2019-08-16 14:11 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师

我要关注我要私信

TA的文章

消息中间件故障分析一例

阅读 1249·2023-01-11 13:20
RAC双节点crash回复一例

阅读 1557·2023-01-11 13:20
ORA-600处理一例

阅读 1011·2023-01-11 13:20
双节点RAC实例2 HANG 故障分析一例

阅读 1680·2023-01-11 13:20
RAC集群节点1重启分析一例

阅读 3971·2023-01-11 13:20
CRS启动报错CRS-1656处理分享

阅读 2519·2023-01-11 13:20
oracle 12CR2打补丁报错处理一例

阅读 1310·2023-01-11 13:20
分布式缓存组件故障分析及监控优化

阅读 3485·2023-01-11 13:20

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

探索Oracle自动类型转换（上）

Oracle数据处理

为什么不建议使用自动类型转换

自动类型转换规则

本文作者：丁俊（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

相关文章

一次慢查询暴露的隐蔽的问题

发表评论

0条评论

IT那活儿

男|高级讲师

TA的文章

消息中间件故障分析一例

RAC双节点crash回复一例

ORA-600处理一例

双节点RAC实例2 HANG 故障分析一例

RAC集群节点1重启分析一例

CRS启动报错CRS-1656处理分享

oracle 12CR2打补丁报错处理一例

分布式缓存组件故障分析及监控优化

最新活动

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

探索Oracle自动类型转换（上）

Oracle数据处理

为什么不建议使用自动类型转换

自动类型转换规则

本文作者：丁 俊（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

相关文章

发表评论

0条评论

男|高级讲师

TA的文章

最新活动

本文作者：丁俊（上海新炬王翦团队）