探索Oracle自動(dòng)類型轉(zhuǎn)換（上）

IT那活兒發(fā)布于2023-01-11 13:19 / 2843人閱讀

點(diǎn)擊上方“IT那活兒”公眾號(hào)，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了?。。?/strong>

Oracle數(shù)據(jù)處理

Oracle中對(duì)不同類型數(shù)據(jù)的處理有顯式類型轉(zhuǎn)換(Explicit)和自動(dòng)類型轉(zhuǎn)換(或叫隱式類型轉(zhuǎn)換Implicit)兩種方式，這和其他語(yǔ)言類似，對(duì)顯式類型轉(zhuǎn)換，是可控的，但是對(duì)自動(dòng)類型轉(zhuǎn)換，不建議使用，因?yàn)楹茈y控制，有不少缺點(diǎn)，比如可能會(huì)對(duì)性能產(chǎn)生不好的影響。

雖然Oracle不建議使用自動(dòng)類型轉(zhuǎn)換，但是在Oracle開發(fā)中，會(huì)經(jīng)常遇到自動(dòng)類型轉(zhuǎn)換，這時(shí)如果你不了解自動(dòng)類型轉(zhuǎn)換的規(guī)則，那么查找和解決問(wèn)題就會(huì)變得很困難，所以，Oracle開發(fā)和維護(hù)人員很有必要了解自動(dòng)類型轉(zhuǎn)換的相關(guān)規(guī)則，從而對(duì)自動(dòng)類型轉(zhuǎn)換了然于胸。

本文首先會(huì)介紹自動(dòng)類型轉(zhuǎn)換的缺點(diǎn)，然后闡述Oracle自動(dòng)類型轉(zhuǎn)換的規(guī)則，并結(jié)合實(shí)例分析自動(dòng)類型轉(zhuǎn)換可能造成的問(wèn)題。

為什么不建議使用自動(dòng)類型轉(zhuǎn)換

自動(dòng)類型轉(zhuǎn)換的確可以讓我們少寫一些代碼，比如可以少寫個(gè)TO_CHAR函數(shù)，SQL看似簡(jiǎn)單了，但是它卻隱藏著危險(xiǎn)：

1. 使用顯式類型轉(zhuǎn)換會(huì)讓SQL可讀性更強(qiáng),但是自動(dòng)類型轉(zhuǎn)換卻沒有這個(gè)優(yōu)點(diǎn)。

如：

SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL;

看到上面的SQL，也許你會(huì)想，我沒有看錯(cuò)吧，你寫的語(yǔ)句是錯(cuò)的，TO_DATE函數(shù)中第1個(gè)參數(shù)是字符類型才對(duì)。你提的這個(gè)問(wèn)題很好，我想你是時(shí)候需要了解Oracle自動(dòng)類型轉(zhuǎn)換規(guī)則了。

我可以很明確地告訴你，這個(gè)語(yǔ)句有時(shí)正確，但是有時(shí)卻是錯(cuò)誤的，正確與否依賴于具體的上下文，比如這里SYSDATE是DATE類型，那么Oracle需要將DATE類型轉(zhuǎn)為字符類型，這是自動(dòng)轉(zhuǎn)換的，也就是說(shuō)，Oracle要自動(dòng)調(diào)用TO_CHAR(date,fmt,nlsparam)函數(shù)，這個(gè)fmt要依賴于上下文的NLS_DATE_FORMAT，nlsparam要依賴于NLS_DATE_LANGUAGE的設(shè)置，下面看測(cè)試結(jié)果：

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMMDD;

會(huì)話已更改。

--其實(shí)在SQL*PLUS中DATE類型輸出就是按照NLS_DATE_FORMAT和NLS_DATE_LANGUAGE參數(shù)自動(dòng)轉(zhuǎn)為字符類型的，這里就是先將SYSDATE轉(zhuǎn)為YYYYMMDD格式，然后再轉(zhuǎn)為DATE類型，最后因?yàn)樵赟QL*PLUS中輸出的是字符串，所以又轉(zhuǎn)為了YYYYMMDD格式的字符串。

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

TO_DATE(
--------
20210611

--下面的出錯(cuò)了，因?yàn)樽詣?dòng)轉(zhuǎn)換后SYSDATE變?yōu)樽址?，格式是YYYYMMDD。

DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL;
SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-01830: 日期格式圖片在轉(zhuǎn)換整個(gè)輸入字符串之前結(jié)束

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMM;

會(huì)話已更改。

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

TO_DAT
------
202106

--同樣地，這個(gè)也出錯(cuò)，因?yàn)檫@里的SYSDATE轉(zhuǎn)為YYYYMM格式字符串。

DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMMDD) FROM DUAL;
SELECT TO_DATE(SYSDATE,YYYYMMDD) FROM DUAL
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-01840: 輸入值對(duì)于日期格式不夠長(zhǎng)


DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMONDD;

會(huì)話已更改。

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

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

DINGJUN123>ALTER SESSION SET NLS_DATE_LANGUAGE = AMERICAN;

會(huì)話已更改。

--看NLS_DATE_LANGUAGE設(shè)置對(duì)結(jié)果的影響。

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

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

自動(dòng)類型轉(zhuǎn)換的確難以理解，如果對(duì)自動(dòng)類型轉(zhuǎn)換的規(guī)則不理解，那么會(huì)感覺匪夷所思。

2. 自動(dòng)類型轉(zhuǎn)換往往對(duì)性能產(chǎn)生不好的影響，特別是左值的類型被自動(dòng)轉(zhuǎn)為了右值的類型(當(dāng)然如果你寫value=column那就左右值互換了，這里說(shuō)的左值是常規(guī)寫法：column=value)。這種方式很可能使本來(lái)應(yīng)該使用索引的而沒有用上索引，也有可能會(huì)導(dǎo)致結(jié)果出錯(cuò)。

如：

DINGJUN123>DROP TABLE t;

表已刪除。

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

表已創(chuàng)建。

DINGJUN123>INSERT INTO t VALUES(1);

已創(chuàng)建 1 行。

DINGJUN123>INSERT INTO t VALUES(abc);

已創(chuàng)建 1 行。

DINGJUN123>COMMIT;

提交完成。

DINGJUN123>CREATE INDEX idx_t ON t (name);

索引已創(chuàng)建。

案例1:自動(dòng)類型轉(zhuǎn)換導(dǎo)致出錯(cuò)

--出錯(cuò)因?yàn)镹AME轉(zhuǎn)為數(shù)值類型失敗，abc是無(wú)法轉(zhuǎn)為NUMBER類型的。

DINGJUN123>SELECT * FROM t
2 WHERE name = 1;
ERROR:
ORA-01722: 無(wú)效數(shù)字
未選定行

--正確寫法：

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

NAME
------
1

案例2:自動(dòng)類型轉(zhuǎn)換導(dǎo)致本該用索引而沒有用

--NAME = 1，因?yàn)镹AME是字符類型，字符與數(shù)值比較，則字符自動(dòng)轉(zhuǎn)為數(shù)值類型，見執(zhí)行計(jì)劃加粗部分，走全表掃描。

--查看執(zhí)行計(jì)劃沒有真正執(zhí)行，因此不報(bào)錯(cuò)。

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:不使用自動(dòng)類型轉(zhuǎn)換，符合要求

--沒有自動(dòng)類型轉(zhuǎn)換，走索引了，這里的測(cè)試是在RBO優(yōu)化器下，我沒有收集統(tǒng)計(jì)信息，這里只是做一個(gè)演示。

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，如果這個(gè)語(yǔ)句很龐大，找這個(gè)錯(cuò)誤還真不容易，如果使用顯式類型轉(zhuǎn)換，找這個(gè)錯(cuò)誤就容易多了，一般查找類型轉(zhuǎn)換問(wèn)題，首先看表的字段類型，然后結(jié)合執(zhí)行計(jì)劃的FILTER部分查看是否發(fā)生自動(dòng)類型轉(zhuǎn)換。

案例2的自動(dòng)類型轉(zhuǎn)換使表T建立的索引失效(如果直接運(yùn)行還會(huì)出錯(cuò)，但是使用EXPLAIN PLAN查看計(jì)劃還是可以的)，無(wú)法用上索引，導(dǎo)致性能低下，當(dāng)然，這個(gè)測(cè)試?yán)泳蜔o(wú)所謂性能不性能了。

案例3不使用類型轉(zhuǎn)換，左值和右值都是字符類型，則該走索引就走索引，符合預(yù)期結(jié)果。

案例1和2中問(wèn)題是一個(gè)低級(jí)錯(cuò)誤，如果有良好的編碼習(xí)慣，這種錯(cuò)誤自然就可避免。特別是在寫存儲(chǔ)過(guò)程中，一個(gè)程序可能會(huì)很大，開發(fā)人員經(jīng)常不注意字段類型，導(dǎo)致SQL測(cè)試明明性能很好，但是到PL/SQL中運(yùn)行，效率卻很低，這種問(wèn)題，首先應(yīng)該定位測(cè)試時(shí)的SQL與存儲(chǔ)過(guò)程中的SQL計(jì)劃是否一致，找出差異，就可以很容易解決這樣的問(wèn)題

3. 自動(dòng)類型轉(zhuǎn)換依賴于發(fā)生轉(zhuǎn)換時(shí)的上下文，比如例1中的DATE類型自動(dòng)轉(zhuǎn)為字符類型，一旦上下文改變，很可能原先的程序就不能運(yùn)行，所以存在自動(dòng)類型轉(zhuǎn)換的程序的可移植性無(wú)法保證。

4. 自動(dòng)類型轉(zhuǎn)換的算法或規(guī)則，以后Oracle可能會(huì)改變，這是很危險(xiǎn)的，意味著舊的代碼很可能在新的Oracle版本中運(yùn)行出現(xiàn)問(wèn)題(性能、錯(cuò)誤等)，顯式類型轉(zhuǎn)換則不存在這個(gè)問(wèn)題。

5. 自動(dòng)類型轉(zhuǎn)換是要消耗時(shí)間的，當(dāng)然同等的顯式類型轉(zhuǎn)換時(shí)間也差不多，最好的方法就是避免類似的轉(zhuǎn)換，對(duì)于顯式類型轉(zhuǎn)換，最好不要對(duì)左值(第2點(diǎn)已經(jīng)說(shuō)了左值是相對(duì)的)進(jìn)行類型轉(zhuǎn)換，到時(shí)候有索引也用不上索引，可能要建函數(shù)索引，索引儲(chǔ)存和管理開銷增大。

如：

DINGJUN123>DROP TABLE t;

表已刪除。

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

表已創(chuàng)建。

DINGJUN123>CREATE INDEX idx_t ON t (done_date);

索引已創(chuàng)建。

現(xiàn)在有這樣的需求：需要查找指定日期的行。我經(jīng)常看到有人這么寫：

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

會(huì)話已更改。
DINGJUN123>SELECT * FROM t
2        WHERE TO_CHAR(done_date,YYYYMMDD) = 20210612;

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


執(zhí)行計(jì)劃
----------------------------------------------------------
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)

這種寫法對(duì)左值進(jìn)行了顯式類型轉(zhuǎn)換，導(dǎo)致索引失效，是不很好的寫法，但是在實(shí)際開發(fā)中真是屢見不鮮啊，特別是對(duì)日期類型的處理(看優(yōu)化器中還冒出了個(gè)INTERNAL_FUNCTION，對(duì)于這個(gè)不必深究，這個(gè)內(nèi)部函數(shù)在日期類型的自動(dòng)類型轉(zhuǎn)換中經(jīng)常出現(xiàn)，比如DATE類型自動(dòng)轉(zhuǎn)為TIMESTAMP)，強(qiáng)烈建議改變此壞習(xí)慣。改寫它：

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


執(zhí)行計(jì)劃
----------------------------------------------------------
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，索引生效，符合預(yù)期，其實(shí)很少遇到不能對(duì)右值進(jìn)行改寫或不能改寫SQL條件而必須要對(duì)左值進(jìn)行轉(zhuǎn)換的情況，如果真遇到這種情況，可以考慮函數(shù)索引。

自動(dòng)類型轉(zhuǎn)換規(guī)則

Oracle自動(dòng)類型轉(zhuǎn)換是根據(jù)上下文以及一些預(yù)定的規(guī)則，經(jīng)過(guò)語(yǔ)法語(yǔ)義的分析之后進(jìn)行相關(guān)的類型轉(zhuǎn)換。自動(dòng)類型轉(zhuǎn)換是由Oracle自己控制的，自動(dòng)類型轉(zhuǎn)換首要條件就是這個(gè)轉(zhuǎn)換有意義，要正確，否則轉(zhuǎn)換不成功則報(bào)錯(cuò)。如：

--下面的轉(zhuǎn)換是不成功的，因?yàn)?號(hào)的意義在Oracle中是數(shù)學(xué)運(yùn)算，所以將ab轉(zhuǎn)為數(shù)字不成功：

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

第 1 行出現(xiàn)錯(cuò)誤:

ORA-01722: 無(wú)效數(shù)字

--下面轉(zhuǎn)換成功了,11可以自動(dòng)轉(zhuǎn)為數(shù)字：

DINGJUN123>SELECT 5*10+11 FROM DUAL;

5*10+11
------------
61

看下圖，Oracle自動(dòng)類型轉(zhuǎn)換的矩陣圖，圖上沒有標(biāo)明轉(zhuǎn)換方向，但是看圖至少了解到自動(dòng)類型轉(zhuǎn)換不是什么類型之間都可以相互轉(zhuǎn)換的，有的類型之間不可相互自動(dòng)轉(zhuǎn)換 (-的說(shuō)明不可轉(zhuǎn)換，X的說(shuō)明可以轉(zhuǎn)換)。

自動(dòng)類型轉(zhuǎn)換矩陣圖

Oracle自動(dòng)類型轉(zhuǎn)換有如下規(guī)則(注意轉(zhuǎn)換方向):

1. 在INSERT和UPDATE語(yǔ)句中，Oracle將賦值的類型轉(zhuǎn)為目標(biāo)列的類型。這很容易理解，不轉(zhuǎn)為目標(biāo)列類型就不符合列的定義了。

如：

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));

表已創(chuàng)建。

DINGJUN123>INSERT INTO t VALUES(SYSDATE);

已創(chuàng)建 1 行。

DINGJUN123>SELECT x FROM t;

X
---------------------
2021-06-11

看到了吧，其實(shí)SYSDATE在插入的時(shí)候就已經(jīng)根據(jù)參數(shù)NLS_DATE_FORMAT和NLS_DATE_LANGUAGE轉(zhuǎn)為字符類型了。

2. 在SELECT中，Oracle會(huì)將查詢到的列的數(shù)據(jù)類型自動(dòng)轉(zhuǎn)為目標(biāo)變量的類型。

如：

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 過(guò)程已成功完成。

從上面的結(jié)果看出，數(shù)字1被轉(zhuǎn)為CHAR(10)了。

3. 對(duì)數(shù)值類型的操作，Oracle經(jīng)常調(diào)整其精度(precision)和刻度(scale)，從而允許最大容量，這種情況下經(jīng)?？吹降慕Y(jié)果類型和表中存儲(chǔ)的類型不一樣(指精度和刻度不一樣)。

如：

DINGJUN123>DROP TABLE t;

表已刪除。

DINGJUN123>CREATE TABLE t AS
2 SELECT CAST(3 AS NUMBER(2)) AS id FROM DUAL;

表已創(chuàng)建。

DINGJUN123>SELECT id/8 FROM t;

ID/8
-----------------
 .375

上面的結(jié)果為0.375，與表中ID存儲(chǔ)的NUMBER(2)類型不同。

4. 當(dāng)比較字符與數(shù)值的時(shí)候，數(shù)值會(huì)有更高的優(yōu)先級(jí)，也就是將字符轉(zhuǎn)為數(shù)值進(jìn)行比較。

如：

DINGJUN123>DROP TABLE t;

表已刪除。

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

表已創(chuàng)建。

DINGJUN123>SELECT * FROM t WHERE x = 1;

未選定行

DINGJUN123>SET AUTOTRACE ON EXPLAIN
DINGJUN123>SELECT * FROM t WHERE x = 1;

未選定行


執(zhí)行計(jì)劃
----------------------------------------------------------
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語(yǔ)句，執(zhí)行計(jì)劃中列X自動(dòng)通過(guò)TO_NUMBER函數(shù)轉(zhuǎn)為數(shù)值類型了。

5. 在字符類型(可轉(zhuǎn)為數(shù)值的字符)、NUMBER類型與浮點(diǎn)類型轉(zhuǎn)換，可能會(huì)丟失精度，因?yàn)閿?shù)值型字符和NUMBER是以10進(jìn)制表示數(shù)字的，而浮點(diǎn)類型是以二進(jìn)制表示。

如：

DINGJUN123>DROP TABLE t;

表已刪除。

DINGJUN123>CREATE TABLE t(x BINARY_FLOAT);

表已創(chuàng)建。

DINGJUN123>INSERT INTO t VALUES(1234567);

已創(chuàng)建 1 行。

DINGJUN123>INSERT INTO t VALUES(123456789);

已創(chuàng)建 1 行。

DINGJUN123>COLUMN x FORMAT 999999999
DINGJUN123>SELECT * FROM t;

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

插入的時(shí)候是NUMBER類型，但是實(shí)際表是BINARY_FLOAT，那么肯定要轉(zhuǎn)為BINARY_FLOAT類型，看123456789插入的時(shí)候就發(fā)生了精度的丟失。

6. 將CLOB轉(zhuǎn)為字符類型（如VARCHAR2）或?qū)LOB轉(zhuǎn)為RAW類型的時(shí)候，如果被轉(zhuǎn)換的類型長(zhǎng)度比目標(biāo)類型長(zhǎng)，那么會(huì)出錯(cuò)。

DINGJUN123>DROP TABLE t;

表已刪除。

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

表已創(chuàng)建。

DINGJUN123>INSERT INTO t VALUES(TO_CLOB(121212121212));
INSERT INTO t VALUES(TO_CLOB(121212121212))
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-12899: 列 "DINGJUN123"."T"."X" 的值太大 (實(shí)際值: 12, 最大值: 10)

目標(biāo)列最大長(zhǎng)度為10字節(jié)，而插入了12字節(jié)，所以報(bào)錯(cuò)。

7. BINARY_FLOAT自動(dòng)轉(zhuǎn)為BINARY_DOUBLE是精確的，反之，BINARY_DOUBLE自動(dòng)轉(zhuǎn)為BINARY_FLOAT可能就不準(zhǔn)確了。注意數(shù)值類型之間的優(yōu)先級(jí)順序：BINARY_DOUBLE > BINARY_FLOAT > NUMBER，因?yàn)楸热缒繕?biāo)列是BINARY_FLOAT，賦值的是NUMBER，則會(huì)轉(zhuǎn)為BINARY_FLOAT類型。

案例1:BINARY_FLOAT轉(zhuǎn)BINARY_DOUBLE精確

DINGJUN123>DROP TABLE t;

表已刪除。

DINGJUN123>CREATE TABLE t(x BINARY_DOUBLE);

表已創(chuàng)建。

DINGJUN123>DROP TABLE t1;

表已刪除。

DINGJUN123>CREATE TABLE t1(x BINARY_FLOAT);

表已創(chuàng)建。

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

已創(chuàng)建 1 行。

DINGJUN123>INSERT INTO t
2 SELECT x FROM t1;

已創(chuàng)建 1 行。

DINGJUN123>SELECT x FROM t;

X
----------
3.42E+037

已選擇 1 行。

DINGJUN123>SELECT x FROM t1;

X
----------
3.42E+037

已選擇 1 行。

案例2:BINARY_DOUBLE轉(zhuǎn)為BINARY_FLOAT不精確

DINGJUN123>DROP TABLE t;

表已刪除。

DINGJUN123>CREATE TABLE t(x BINARY_DOUBLE);

表已創(chuàng)建。

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

已創(chuàng)建 1 行。

DINGJUN123>DROP TABLE t1;

表已刪除。

DINGJUN123>CREATE TABLE t1(x BINARY_FLOAT);

表已創(chuàng)建。

DINGJUN123>INSERT INTO t1
2 SELECT x FROM t;

已創(chuàng)建 1 行。

DINGJUN123>SELECT * FROM t;

X
----------
1.798E+039

已選擇 1 行。

DINGJUN123>SELECT x FROM t1;

X
----------
   Inf

已選擇 1 行。

從案例1看出，BINARY_FLOAT轉(zhuǎn)BINARY_DOUBLE沒有問(wèn)題，但是BINARY_DOUBLE值轉(zhuǎn)為BINARY_FLOAT需要更多精度支持的時(shí)候，則不準(zhǔn)確，如案例2，BINARY_DOUBLE：1.79769313486E+39轉(zhuǎn)為BINARY_FLOAT變?yōu)镮NFINITY(無(wú)窮大)。

8. 當(dāng)字符串與DATE類型比較，DATE類型具有較高優(yōu)先級(jí)，將字符串轉(zhuǎn)為DATE類型，這種自動(dòng)轉(zhuǎn)換需要上下文的支持，和第1點(diǎn)類似。

在下一篇文章我會(huì)用PL/SQL常見的拼湊字符串說(shuō)明DATE類型自動(dòng)轉(zhuǎn)換的常見錯(cuò)誤。

DINGJUN123>DROP TABLE t;

表已刪除。

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

表已創(chuàng)建。

DINGJUN123>SELECT * FROM t WHERE x = 2021-06-12;
SELECT * FROM t WHERE x = 2021-06-12
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-01861: 文字與格式字符串不匹配
DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT=YYYY-MM-DD;

會(huì)話已更改。

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

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

從結(jié)果看出，2021-01-01根據(jù)NLS_DATE_FORMAT轉(zhuǎn)為了DATE類型。

9. 當(dāng)使用SQL函數(shù)或操作符的時(shí)候，如果傳入的類型和實(shí)際應(yīng)該接受的類型不一致，那么會(huì)將傳入的類型根據(jù)具體需要轉(zhuǎn)為一致。

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接受的參數(shù)是兩個(gè)字符類型，但是我傳的是兩個(gè)數(shù)值類型，會(huì)自動(dòng)轉(zhuǎn)為字符類型，返回值也是字符類型(SQL*PLUS里的字符左對(duì)齊，數(shù)值右對(duì)齊)。10+0中的10根據(jù)操作符環(huán)境自動(dòng)轉(zhuǎn)為10，最終結(jié)果是數(shù)值類型，而10||0會(huì)將0轉(zhuǎn)為0，所以結(jié)果是字符100。

10. 當(dāng)做賦值操作(=)的時(shí)候,Oracle會(huì)將右邊被賦的值的類型自動(dòng)轉(zhuǎn)為和左邊目標(biāo)類型一致的類型。

其實(shí)前面說(shuō)的SELECT語(yǔ)句的值賦給目標(biāo)變量也類似。注意我這里說(shuō)的賦值操作可不是WHERE column = yy中=（WHERE條件的中的=是比較操作，按比較操作規(guī)則），而是說(shuō)賦值給變量或列，比如UPDATE,PL/SQL中的賦值操作。

11. 在做連接操作的時(shí)候，Oracle會(huì)將非字符類型轉(zhuǎn)為字符類型。

實(shí)際上這也是根據(jù)具體上下文和運(yùn)算環(huán)境決定的自動(dòng)轉(zhuǎn)換，第9點(diǎn)已經(jīng)舉了例子說(shuō)明。

12. 在字符和非字符之間的算術(shù)和比較操作中，ORACLE會(huì)將字符轉(zhuǎn)為日期，ROWID，數(shù)值類型。

算術(shù)操作一般都要轉(zhuǎn)為數(shù)值類型，和ROWID比較如WHERE ROWID=…，要將字符轉(zhuǎn)為ROWID，和日期比較如WHERE date_column =….，會(huì)將字符串根據(jù)nls參數(shù)的設(shè)置轉(zhuǎn)為日期類型。

--使用8里面的表：

DINGJUN123>SELECT ROWID FROM t;

ROWID
------------------
AAAPCiAAEAAAVfUAAA

DINGJUN123>SELECT * FROM t
2 WHERE ROWID = AAAPCiAAEAAAVfUAAA;

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

上面的右邊的字符串被轉(zhuǎn)為ROWID類型了。

13. 字符類型之間的類型轉(zhuǎn)換，CHAR,VACHAR2,NCHAR,NVARCHAR2，NVACHAR2需要國(guó)家字符集(9i后有UTF8和AL16UTF16)的支持，而且是按字符存儲(chǔ)的，CHAR,VARCHAR2受數(shù)據(jù)庫(kù)默認(rèn)字符集的支持。

數(shù)據(jù)庫(kù)字符集支持的CHAR,VARCHAR2默認(rèn)轉(zhuǎn)換到NCHAR,NVARCHAR2，當(dāng)然VARCHAR2與CHAR是CHAR轉(zhuǎn)VARCHAR2，如下：

字符類型內(nèi)部轉(zhuǎn)換表

從上表看出，NVARCHAR2優(yōu)先級(jí)最高，所有字符類型遇到它都要自動(dòng)轉(zhuǎn)為NVARCHAR2類型。CHAR遇到VARCHAR2要轉(zhuǎn)為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 過(guò)程已成功完成。

如果a = b是VARCHAR2轉(zhuǎn)為CHAR類型，那么采用填補(bǔ)空格的比較，則肯定相等，但是現(xiàn)在的結(jié)果是不等的，那是因?yàn)镃HAR被轉(zhuǎn)為了VARCHAR2，從而采用非填補(bǔ)空格的比較方式。

14. 很多SQL字符函數(shù)可以接受CLOB類型(比如SUBSTR,INSTR等都能接受CLOB類型)。

對(duì)不接受CLOB類型的會(huì)自動(dòng)轉(zhuǎn)為字符類型，對(duì)參數(shù)要求是VARCHAR2或CHAR的，但是不允許CLOB類型的，如果傳入CLOB類型也是可以的，但是有最大長(zhǎng)度限制，只能最大4000字節(jié)，否則報(bào)錯(cuò)。另外有些函數(shù)比如LPAD，RPAD等如果上下文是CHAR或VARCHAR2，也最多只能取4000字節(jié)。

如：

--返回4000，LPAD如果第1個(gè)參數(shù)是字符類型，最大只能是4000個(gè)字節(jié)
DINGJUN123>SELECT LENGTH(TO_CLOB(LPAD(a,6000,a)))
2  FROM DUAL;

LENGTH(TO_CLOB(LPAD(A,6000,A)))
-----------------------------------
4000

--返回6000，LPAD如果第1個(gè)參數(shù)是CLOB,那么最大可以達(dá)到CLOB最大長(zhǎng)度
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

--報(bào)錯(cuò)，INITCAP不接受CLOB列，自動(dòng)類型轉(zhuǎn)換只允許最多4000個(gè)字節(jié)
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 行出現(xiàn)錯(cuò)誤:
ORA-22835: 緩沖區(qū)對(duì)于 CLOB 到 CHAR 轉(zhuǎn)換或 BLOB 到 RAW 轉(zhuǎn)換而言太小 (實(shí)際: 6000, 最大: 4000)

--正確，取最大4000字節(jié)
DINGJUN123>SELECT LENGTH(INITCAP(TO_CLOB(LPAD(TO_CLOB(a),4000,a))))
2  FROM DUAL;

LEN
----------
4000

15. 上面很多規(guī)則說(shuō)的都是SQL中的規(guī)則，那么在PL/SQL中也會(huì)存在類似的規(guī)則，只需要注意一下SQL和PL/SQL的區(qū)別即可。

比如SQL中的VARCHAR2最大4000字節(jié)，在PL/SQL中最大為32767字節(jié)，以第14點(diǎn)為例子，在SQL和PL/SQL中就有區(qū)別，對(duì)于PL/SQL的自動(dòng)類型轉(zhuǎn)換規(guī)則一般都可以根據(jù)類型的區(qū)別推算出，所以只舉一個(gè)例子說(shuō)明，讀者有興趣可以詳細(xì)研究一下。

DINGJUN123>SET SERVEROUTPUT ON
--在PL/SQL中LPAD(a,6000,a)是6000字節(jié)，但是在SQL中只能取到4000字節(jié)
--在PLSQL中LPAD(a,6000,a) || a是正確的，但是在SQL中就超出了4000字節(jié)的范圍，運(yùn)算出錯(cuò)
--在PL/SQL中，超出定義的最大字節(jié)數(shù)32767也出錯(cuò)
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 行出現(xiàn)錯(cuò)誤:
ORA-06502: PL/SQL: 數(shù)字或值錯(cuò)誤 : 字符串緩沖區(qū)太小
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 行出現(xiàn)錯(cuò)誤:
ORA-01489: 字符串連接的結(jié)果過(guò)長(zhǎng)

本文說(shuō)了很多自動(dòng)類型轉(zhuǎn)換的規(guī)則和問(wèn)題，的確，自動(dòng)類型轉(zhuǎn)換是容易被Oracle技術(shù)人員忽略的重要知識(shí)點(diǎn)，在實(shí)際應(yīng)用中，經(jīng)常會(huì)遇到各種各樣的自動(dòng)類型轉(zhuǎn)換問(wèn)題，通過(guò)上面相關(guān)內(nèi)容的描述，我想，你應(yīng)該對(duì)自動(dòng)類型轉(zhuǎn)換的規(guī)則很熟悉了，當(dāng)然，最好就是杜絕自動(dòng)類型轉(zhuǎn)換，這樣才能避免類似問(wèn)題的發(fā)生。

下文我們?cè)賮?lái)說(shuō)一下自動(dòng)類型轉(zhuǎn)換常見錯(cuò)誤。

本文作者：丁俊（上海新炬王翦團(tuán)隊(duì)）

本文來(lái)源：“IT那活兒”公眾號(hào)

GPU云服務(wù)器云服務(wù)器 oracle中類型轉(zhuǎn)換 js 數(shù)據(jù)類型的自動(dòng)轉(zhuǎn)換類型轉(zhuǎn)換強(qiáng)制轉(zhuǎn)換類型

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://www.ezyhdfw.cn/yun/129289.html

上一篇：日常巡檢自動(dòng)化

下一篇：探索Oracle自動(dòng)類型轉(zhuǎn)換（下）

相關(guān)文章

一次慢查詢暴露的隱蔽的問(wèn)題

摘要：最近解決了一個(gè)生產(chǎn)慢查詢的問(wèn)題，排查問(wèn)題之后發(fā)現(xiàn)一些比較隱匿且容易忽略的問(wèn)題。所以實(shí)際在數(shù)據(jù)庫(kù)查詢?nèi)缦驴赡苓@里發(fā)生一次隱式轉(zhuǎn)換。這次查詢走的是索引。 showImg(https://segmentfault.com/img/bVbmJNK?w=6000&h=4000); Photo by Iga Palacz on Unsplash 最近解決了一個(gè)生產(chǎn) SQL 慢查詢的問(wèn)題，排查問(wèn)題之...

missonce 2019-08-16 14:11 評(píng)論0 收藏0

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

IT那活兒

男|高級(jí)講師

我要關(guān)注我要私信

TA的文章
閱讀更多

消息中間件故障分析一例

閱讀 1491·2023-01-11 13:20

RAC雙節(jié)點(diǎn)crash回復(fù)一例

閱讀 1850·2023-01-11 13:20

ORA-600處理一例

閱讀 1289·2023-01-11 13:20

雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

閱讀 2041·2023-01-11 13:20

RAC集群節(jié)點(diǎn)1重啟分析一例

閱讀 4241·2023-01-11 13:20

CRS啟動(dòng)報(bào)錯(cuò)CRS-1656處理分享

閱讀 2948·2023-01-11 13:20

oracle 12CR2打補(bǔ)丁報(bào)錯(cuò)處理一例

閱讀 1580·2023-01-11 13:20

分布式緩存組件故障分析及監(jiān)控優(yōu)化

閱讀 3852·2023-01-11 13:20

最新活動(dòng)

亚洲中字慕日产2020,大陆极品少妇内射AAAAAA,无码av大香线蕉伊人久久,久久精品国产亚洲av麻豆网站

資訊專欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！

探索Oracle自動(dòng)類型轉(zhuǎn)換（上）

Oracle數(shù)據(jù)處理

為什么不建議使用自動(dòng)類型轉(zhuǎn)換

自動(dòng)類型轉(zhuǎn)換規(guī)則

本文作者：丁俊（上海新炬王翦團(tuán)隊(duì)）

本文來(lái)源：“IT那活兒”公眾號(hào)

相關(guān)文章

一次慢查詢暴露的隱蔽的問(wèn)題

發(fā)表評(píng)論

0條評(píng)論

IT那活兒

男|高級(jí)講師

TA的文章

消息中間件故障分析一例

RAC雙節(jié)點(diǎn)crash回復(fù)一例

ORA-600處理一例

雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

RAC集群節(jié)點(diǎn)1重啟分析一例

CRS啟動(dòng)報(bào)錯(cuò)CRS-1656處理分享

oracle 12CR2打補(bǔ)丁報(bào)錯(cuò)處理一例

分布式緩存組件故障分析及監(jiān)控優(yōu)化

最新活動(dòng)

資訊專欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！

探索Oracle自動(dòng)類型轉(zhuǎn)換（上）

Oracle數(shù)據(jù)處理

為什么不建議使用自動(dòng)類型轉(zhuǎn)換

自動(dòng)類型轉(zhuǎn)換規(guī)則

本文作者：丁 俊（上海新炬王翦團(tuán)隊(duì)）

本文來(lái)源：“IT那活兒”公眾號(hào)

相關(guān)文章

發(fā)表評(píng)論

0條評(píng)論

男|高級(jí)講師

TA的文章

最新活動(dòng)

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！

本文作者：丁俊（上海新炬王翦團(tuán)隊(duì)）