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

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

點擊上方“IT那活兒”公眾號，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！??！

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

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

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

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

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

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

1. 使用顯式類型轉(zhuǎn)換會讓SQL可讀性更強,但是自動類型轉(zhuǎn)換卻沒有這個優(yōu)點。

如：

SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL;

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

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

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMMDD;

會話已更改。

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

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

TO_DATE(
--------
20210611

--下面的出錯了，因為自動轉(zhuǎn)換后SYSDATE變?yōu)樽址袷绞荵YYYMMDD。

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

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMM;

會話已更改。

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

TO_DAT
------
202106

--同樣地，這個也出錯，因為這里的SYSDATE轉(zhuǎn)為YYYYMM格式字符串。

DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMMDD) FROM DUAL;
SELECT TO_DATE(SYSDATE,YYYYMMDD) FROM DUAL
*
第 1 行出現(xiàn)錯誤:
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設(shè)置對結(jié)果的影響。

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

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

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

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

如：

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:自動類型轉(zhuǎn)換導(dǎo)致出錯

--出錯因為NAME轉(zhuǎn)為數(shù)值類型失敗，abc是無法轉(zhuǎn)為NUMBER類型的。

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

--正確寫法：

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

NAME
------
1

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

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

--查看執(zhí)行計劃沒有真正執(zhí)行，因此不報錯。

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

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

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

案例2的自動類型轉(zhuǎn)換使表T建立的索引失效(如果直接運行還會出錯，但是使用EXPLAIN PLAN查看計劃還是可以的)，無法用上索引，導(dǎo)致性能低下，當然，這個測試例子就無所謂性能不性能了。

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

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

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

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

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

如：

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;

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

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


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

這種寫法對左值進行了顯式類型轉(zhuǎn)換，導(dǎo)致索引失效，是不很好的寫法，但是在實際開發(fā)中真是屢見不鮮啊，特別是對日期類型的處理(看優(yōu)化器中還冒出了個INTERNAL_FUNCTION，對于這個不必深究，這個內(nèi)部函數(shù)在日期類型的自動類型轉(zhuǎn)換中經(jīng)常出現(xiàn)，比如DATE類型自動轉(zhuǎn)為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


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

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

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

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

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

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

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

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

DINGJUN123>SELECT 5*10+11 FROM DUAL;

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

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

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

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

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

如：

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

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

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

如：

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 過程已成功完成。

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

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

如：

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存儲的NUMBER(2)類型不同。

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

如：

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

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

如：

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

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

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

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)錯誤:
ORA-12899: 列 "DINGJUN123"."T"."X" 的值太大 (實際值: 12, 最大值: 10)

目標列最大長度為10字節(jié)，而插入了12字節(jié)，所以報錯。

7. BINARY_FLOAT自動轉(zhuǎn)為BINARY_DOUBLE是精確的，反之，BINARY_DOUBLE自動轉(zhuǎn)為BINARY_FLOAT可能就不準確了。注意數(shù)值類型之間的優(yōu)先級順序：BINARY_DOUBLE > BINARY_FLOAT > NUMBER，因為比如目標列是BINARY_FLOAT，賦值的是NUMBER，則會轉(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沒有問題，但是BINARY_DOUBLE值轉(zhuǎn)為BINARY_FLOAT需要更多精度支持的時候，則不準確，如案例2，BINARY_DOUBLE：1.79769313486E+39轉(zhuǎn)為BINARY_FLOAT變?yōu)镮NFINITY(無窮大)。

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

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

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)錯誤:
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

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

9. 當使用SQL函數(shù)或操作符的時候，如果傳入的類型和實際應(yīng)該接受的類型不一致，那么會將傳入的類型根據(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ù)是兩個字符類型，但是我傳的是兩個數(shù)值類型，會自動轉(zhuǎn)為字符類型，返回值也是字符類型(SQL*PLUS里的字符左對齊，數(shù)值右對齊)。10+0中的10根據(jù)操作符環(huán)境自動轉(zhuǎn)為10，最終結(jié)果是數(shù)值類型，而10||0會將0轉(zhuǎn)為0，所以結(jié)果是字符100。

10. 當做賦值操作(=)的時候,Oracle會將右邊被賦的值的類型自動轉(zhuǎn)為和左邊目標類型一致的類型。

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

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

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

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

算術(shù)操作一般都要轉(zhuǎn)為數(shù)值類型，和ROWID比較如WHERE ROWID=…，要將字符轉(zhuǎn)為ROWID，和日期比較如WHERE date_column =….，會將字符串根據(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需要國家字符集(9i后有UTF8和AL16UTF16)的支持，而且是按字符存儲的，CHAR,VARCHAR2受數(shù)據(jù)庫默認字符集的支持。

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

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

從上表看出，NVARCHAR2優(yōu)先級最高，所有字符類型遇到它都要自動轉(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 過程已成功完成。

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

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

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

如：

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

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

--返回6000，LPAD如果第1個參數(shù)是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列，自動類型轉(zhuǎn)換只允許最多4000個字節(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)錯誤:
ORA-22835: 緩沖區(qū)對于 CLOB 到 CHAR 轉(zhuǎn)換或 BLOB 到 RAW 轉(zhuǎn)換而言太小 (實際: 6000, 最大: 4000)

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

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

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

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

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

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

下文我們再來說一下自動類型轉(zhuǎn)換常見錯誤。

本文作者：丁 ?。ㄉ虾Ｐ戮嫱豸鍒F隊）

本文來源：“IT那活兒”公眾號

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

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

轉(zhuǎn)載請注明本文地址：http://systransis.cn/yun/129289.html

發(fā)表評論

登陸后可評論

0條評論

IT那活兒

男|高級講師

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

TA的文章

消息中間件故障分析一例

閱讀 1358·2023-01-11 13:20
RAC雙節(jié)點crash回復(fù)一例

閱讀 1707·2023-01-11 13:20
ORA-600處理一例

閱讀 1215·2023-01-11 13:20
雙節(jié)點RAC實例2 HANG 故障分析一例

閱讀 1907·2023-01-11 13:20
RAC集群節(jié)點1重啟分析一例

閱讀 4165·2023-01-11 13:20
CRS啟動報錯CRS-1656處理分享

閱讀 2759·2023-01-11 13:20
oracle 12CR2打補丁報錯處理一例

閱讀 1402·2023-01-11 13:20
分布式緩存組件故障分析及監(jiān)控優(yōu)化

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

成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

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

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

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

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

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

本文作者：丁 ?。ㄉ虾Ｐ戮嫱豸鍒F隊）

本文來源：“IT那活兒”公眾號

相關(guān)文章

一次慢查詢暴露的隱蔽的問題

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

消息中間件故障分析一例

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

ORA-600處理一例

雙節(jié)點RAC實例2 HANG 故障分析一例

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

CRS啟動報錯CRS-1656處理分享

oracle 12CR2打補丁報錯處理一例

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

最新活動

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！

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

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

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

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

本文作者：丁 ?。ㄉ虾Ｐ戮嫱豸鍒F隊）

本文來源：“IT那活兒”公眾號

相關(guān)文章

發(fā)表評論

0條評論

男|高級講師

TA的文章

最新活動

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

本文作者：丁 ?。ㄉ虾Ｐ戮嫱豸鍒F隊）