国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

探索Oracle自動類型轉換（上）

IT那活兒 / 2645人閱讀
探索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那活兒”公眾號

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129289.html

相關文章

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

    摘要：最近解決了一個生產慢查詢的問題，排查問題之后發現一些比較隱匿且容易忽略的問題。所以實際在數據庫查詢如下可能這里發生一次隱式轉換。這次查詢走的是索引。 showImg(https://segmentfault.com/img/bVbmJNK?w=6000&h=4000); Photo by Iga Palacz on Unsplash 最近解決了一個生產 SQL 慢查詢的問題，排查問題之...

    missonce 評論0 收藏0

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<