29.必須重視的Oracle自動類型轉換顯示類型轉換以date類型為例子。Oracle中對不同類型的處理具有顯式類型轉換(Explicit)和自動類型轉換(隱式類型轉換Implicit)兩種方式，對于顯式類型轉換，我們是可控的，但是對于自動類型轉換，當然不建議使用，因為很難控制，有不少缺點，但是我們很難避免碰到自動類型轉換，如果不了解自動類型轉換的規則，那么往往會改變我們SQL的執行計劃，從而可能導致效率降低或其它問題，所以，Oracle開發人員很有必要了解Oracle自動類型轉換的相關規則，從而避免自動類型轉換導致相關問題的產生。本章首先會對Oracle自動類型轉換的規則做闡述，然后結合相關實例分析自動類型轉換可能造成的問題。29.1數據類型優先級Oracle使用數據類型的優先級來決定自動類型轉換,Oracle類型如下優先:Datetimeandinterval類型BINARY_DOUBLEBINARY_FLOATNUMBER■字符類型■所有其它內置類型上面說的不夠具體，我們看第二節具體的類型轉換規則。29.2自動類型轉換規則一般一個表達式不能包含多種數據類型，比如一個表達式5*10然后加上'james'，但是Oracle會有自動類型轉換和顯式類型轉換兩種規則，我們看如下例子：DINGJUN123>select5*10+'james'fromdual;select5*10+'james'fromdual*第1行出現錯誤：ORA-01722:無效數字我們看到，報無效數字錯誤。當然，這里Oracle使用了自動類型轉換將'james'轉為數字類型，但是這個轉換是失敗的，所以報錯，所以自動類型轉換的第1個規則就是必須自動類型轉換能夠成功，否則報錯。我們看下面的就轉換成功了：DINGJUN123>select5*10+'2'fromdual;5*10+'2'52OK,看到了結果正確，這里的字符串'2'被自動轉為數值類型的2(不明白為什么會這樣轉換，請往下看)，所以結果為52.。29.2.1為什么不建議使用自動類型轉換?自動類型轉換的確可以讓我們少寫一些內容，比如可以少寫個to_char函數之類的東西,但是它經常是不好的：1.使用顯示類型轉換會讓我們的SQL更加容易被理解，也就是可讀性更強，但是自動類型轉換卻沒有這個優點，如：DINGJUN123>selectto_date(sysdate,'yyyymm')fromdual;也許你會想，我沒有看錯吧,你寫的語句是錯的,to_date中間的第1個參數是字符類型哦,你提的這個問題很好，我想你應該需要了解了解Oracle中的自動類型轉換了。我可以很明確地告訴你，這個語句是可以的，但是能不能運行正確就要依賴于具體的上下文了，比如這里sysdate是date類型，那么需要將date類型轉為字符，這是自動轉換的，也就是Oracle要自動調用to_char(sysdate,fmt)，這個fmt就依賴于上下文的nls_date_format,也有可能會依賴于nls_date_language的設置，看我們的結果：DINGJUN123>altersessionsetnls_date_format='yyyymm';會話已更改。DINGJUN123>selectto_date(sysdate,'yyyymm')fromdual;TO_DAT201005DINGJUN123>altersessionsetnls_date_format='yyyymondd';會話已更改。DINGJUN123>selectto_date(sysdate,'yyyymondd')fromdual;TO_DATE(SYSDAT20105月16DINGJUN123>altersessionsetnls_date_language='American';會話已更改。DINGJUN123>selectto_date(sysdate,'yyyymondd')fromdual;TO_DATE(SYSD—2010may16自動類型轉換的確難以理解，不知道的人以為這真是太神奇了，可能以為Oracle的函數定義搞錯了，還是了解下這方面的內容吧，這樣才可以運籌帷幄，決勝千里。2.自動類型轉換往往對性能產生不好的影響，特別是左值的類型被自動轉為了右值的類型。這種方式很可能使我們本來可以使用索引的而沒有用上索引，也有可能會導致結果出錯。如：DINGJUN123>droptablet;表已刪除。DINGJUN123>createtablet(namevarchar2(10));表已創建。DINGJUN123>insertintotvalues('abc');已創建1行。DINGJUN123>insertintotvalues('1');已創建1行。DINGJUN123>commit;提交完成。DINGJUN123>createindexidx_tont(name);索引已創建。 案例1:自動類型轉換導致出錯 DINGJUN123>select*fromtwherename=1;select*fromtwherename=1*第1行出現錯誤：ORA-01722:無效數字DINGJUN123>select*fromtwherename='1';NAME—1 -案例2:自動類型轉換導致本該用索引而沒有用 DINGJUN123>explainplanforselect*fromtwherename=1;已解釋。DINGJUN123>select*fromtable(dbms_xplan.display);PLAN_TABLE_OUTPUT—Planhashvalue:1601196873—|Id|Operation |Name|—|0|SELECTSTATEMENT| ||*1|TABLEACCESSFULL|T |—PredicateInformation(identifiedbyoperationid):—PLAN_TABLE_OUTPUT-filter(TO_NUMBER("NAME")=1)Note—-rulebasedoptimizerused(considerusingcbo)DINGJUN123>explainplanforselect*fromtwherename='1';

已解釋。已解釋。DINGJUN123>select*fromtable(dbms_xplan.display);DINGJUN123>select*PLAN_TABLE_OUTPUTPlanhashvalue:2296882198—|Id|Operation |Name|—| 0|SELECTSTATEMENT| ||*1|INDEXRANGESCAN|IDX_T|—PredicateInformation(identifiedbyoperationid):—PLAN_TABLE_OUTPUT—1-access("NAME"='1')Note—-rulebasedoptimizerused(considerusingcbo)我們看案例1,如果這個語句很龐大，找這個錯誤還真不容易，如果是顯示轉換的話，找個錯誤就容易多了。案例2我使用RBO優化器的，我沒有收集統計信息，而且還加了rule,這里不加rule一樣，如果列自動發生了類型轉換，很可能使索引失效，這句select*fromtwherename=1沒有寫select*fromtwhereto_number(name)=1發現索引失效明顯。但是如果我們感覺應該用索引而沒有用上索引，而且左邊的列和右邊的值類型不一樣，那么很可能發生了自動類型轉換，當然看執行計劃有這樣的類型轉換信息，雖然我們沒有顯示地寫，往往看執行計劃是我們第1步尋找問題的方法。自動類型轉換可能依賴于發生轉換時的上下文環境，比如1中的to_date(sysdate,fmt),一旦上下文環境改變，很可能我們的程序就不能運行。自動類型轉換的算法或規則，以后Oracle可能改變，這是很危險的，意味著舊的代碼很可能在新的Oracle版本中運行出現問題(性能、錯誤等)，顯示類型轉換總是有最高的優先級，所以顯示類型轉換沒有這種版本更替可能帶來的問題。自動類型轉換是要消耗時間的，當然同等的顯式類型轉換時間也差不多，最好的方法就是避免類似的轉換，在顯示類型轉換上我們會看到，最好不要將左值進行類型轉

換，到時候有索引也用不上索引，還要建函數索引，索引儲存和管理開銷增大。29.2.2自動類型轉換規則Oracle自動類型轉換是根據上下文環境以及一些預定的規則，經過語法語義的分析之后進行相關的自動類型轉換，自動類型轉換首要條件就是這個轉換有意義，要正確，否則轉換不成功，要報錯,我們前面已經舉了這樣的例子?？聪聢D，Oracle自動類型轉換的矩陣圖，圖上沒有具體地轉換方向，但是我們最起碼看圖了解到一點，自動類型轉換不是什么類型都可以相互轉換的，有的不可相互自動轉換。（-的說明不轉換，X的說明可以轉換）自動類型轉換矩陣圖BO-J0MBO-J0Mmo-Jm8010CHMOIXMW0NO—I-LV2LLAHVNEBLlJmsnNTVAtiUJJ_N_Lu豆dSHVHOUVANHVHONIXVHOCHARXXXXXXXXXXXXXVARCHAR2XXXXXXXXXXXXXNCHARXXXXXXXXXXXXXNVARCHAR2XXXXXXXXXXXXXDATEXXXXDATETIME/INTERVMXXXXXNUMBERXXXXXXBINARY,FLOATXXXXXXBINARY,DOUBLEXXXXXXLONGXXXXXXXXRAWXXXXXXROWiDXXXCLOBXXXXXXBLOBXNCLOBXXXXXXOracle自動類型轉換有如下規則（轉換方向）：1.在insert和update語句中，Oracle將賦值的類型轉為目標列的類型。這很容易理解，當然最終存到我們目標列的類型是要符合定義的，如:DINGJUN123>droptablet;表已刪除。DINGJUN123>createtablet(xvarchar2(100));表已創建。DINGJUN123>insertintotvalues(sysdate);已創建1行。DINGJUN123>selectxfromt;X2010may16看到了吧，其實sysdate在插入的時候就已經根據nls_date_format和nls_date_language參數轉為字符類型varchar2(100)了。在SELECT中,Oracle會自動將查詢到的列的值轉為目標變量的類型。如：DINGJUN123>declarevarchar(10);beginselect1intovarfromdual;dbms_output.put_line('varis'llvarll',thelengthis'lllength(var));end;/varis1 ,thelengthis10看，數值1被轉為char(10)了。對數值類型的操作，Oracle經常將數值類型的值調整為最大的精度(precision)和刻度(scale)，這種情況下經常看到的結果和表中存儲的結果不一樣。當比較字符與數值的時候，數值會有更高的優先級，也就是將字符轉為數值進行比較。DINGJUN123>explainplanforselect*fromtwherex=1;DINGJUN123>select*fromtable(dbms_xplan.display);PLAN_TABLE_OUTPUTPlanhashvalue:1601196873IdlOperation lNamell0lSELECTSTATEMENTl ll*1lTABLEACCESSFULLlTlPredicateInformation(identifiedbyoperationid):—-filter(TO_NUMBER("X")=1)Note—-rulebasedoptimizerused(considerusingcbo)看上面的t表的x列是varchar2類型，select*fromtwherex=1將列x自動通過to_number轉為數值類型了。在字符類型、NUMBER數值類型與浮點類型的數值之間相互轉換，可能會丟失精度，因為NUMBER是以10進制(0-9)精度表示數字的，而浮點類型數值是以二進制(0和1)表示的精度。DINGJUN123>droptablet;表已刪除。DINGJUN123>createtablet(xbinary_float);表已創建。DINGJUN123>insertintotvalues(1234567);已創建1行。DINGJUN123>insertintotvalues(123456789);已創建1行。DINGJUN123>columnxformat9999999999999DINGJUN123>select*fromt;X—1234567123456792我們插入的時候是NUMBER類型，但是實際表是BINARY_FLOAT那么肯定要轉為BINARY_FLOAT類型，看123456789插入的時候就發生了精度的丟失。將CLOB轉為字符類型或將BLOB轉為RAW類型的時候，如果被轉換的類型長度比目標類型長，那么會出錯，其實，其他的類型轉換在自動類型，顯示類型轉換中如果被轉換的類型的長度比目標類型長，那么都是會報錯的(但是在某些函數中自動截斷，不報錯，見第14)。DINGJUN123>droptablet;表已刪除。DINGJUN123>createtablet(xvarchar2(10));表已創建。DINGJUN123>insertintotvalues(to_clob('12212121212121'));insertintotvalues(to_clob('12212121212121'))*第1行出現錯誤：ORA-12899:歹U”DINGJUN123”.”T”.”X”的值太大(實際值：14,最大值：10)我們這里只是做個例子，沒有必要用to_clob函數，看到了這個clob最大長度應該是10,但是實際是14,所以自動類型轉換失敗。7.BINARY_FLOAT自動轉為BINARY_DOUBLE是準確的，當然這毋庸置疑。反之，BINARY_DOUBLE自動轉為BINARY_FLOAT可能就是不準確的了，如BINARY_DOUBLE轉為BINARY_FLOAT需要更多的精度位的支持。8.當字符串與DATE類型比較，DATE類型具有較高優先級，將字符串轉為DATE類型，這種自動轉換需要上下文的支持，見前面DATE轉為字符串的例子。DINGJUN123>droptablet;表已刪除。DINGJUN123>createtablet(xdate);表已創建。DINGJUN123>insertintotvalues(to_date('2010-01-01','yyyy-mm-dd'));已創建1行。DINGJUN123>select*fromtwherex='2010-01-01';select*fromtwherex='2010-01-01'*第1行出現錯誤：ORA-01861:文字與格式字符串不匹配DINGJUN123>altersessionsetnls_date_format='yyyy-mm-dd';會話已更改。DINGJUN123>select*fromtwherex='2010-01-01';X—2010-01-01看，的確可以自動類型轉換。'2010-01-01'根據nls_date_format和nls_date_language轉為了DATE類型。9.當使用SQL函數或操作符的時候，如果傳入的類型和實際應該接受的類型不一致，那么將傳入的類型根據上下文環境轉為一致。DINGJUN123>selectreplace(12345,4)fromdual;REPLACE(1235DINGJUN123>select'10'+'0'fromdual;'10'+'0'DINGJUN123>select'10'll0fromdual;'10'll10010看上面的例子,replace接受的參數是兩個字符類型，但是我們的是兩個數值類型，會自動轉為字符類型，返回值也是字符類型。’10'+'0'會根據操作符環境自動轉為10+0，最終結果是數值類型，而'10'110會將0轉為'0'(CHAR)所以結果是字符'100'。經?？吹接腥藛柶鹞业娜掌谠趺锤袷交粚Π?，如下：DINGJUN123>setserveroutputonDINGJUN123>begindbms_output.put_line(to_date('20100511','yyyymmdd'));end;/11-5月-10PL/SQL過程已成功完成。你真的格式化了嗎?還是和前面說的to_date(sysdate,fmt)類似,dbms_output.put_line過程只接受字符類型的參數，你傳入了日期，當然要自動轉換成字符了，同前面說的一樣依賴于nls環境的設置,不想依賴于于環境那么再次to_char一下就可以了。當做賦值操作仁)的時候,Oracle會將右邊被賦的值的類型自動轉為和左邊目標類型一致的類型。其實前面我們說的select語句的值賦給目標變量也類似。注意我們這里說的賦值操作可不是wherexx=yy中=(這里的是比較操作)，而是賦值給變量或歹L比如update,PL/SQL中的賦值操作。在做連接操作的時候，Oracle會將非字符類型轉為CHAR或NCHAR0第9點已經舉了例子說明。在字符和非字符之間的算術和比較操作中，ORACLE會根據日期，ROWID，數值類型優先級最大來進行轉換。算術操作一般都要轉為 NUMBER，比如whererowid='...'要將字符串轉為ROWID，wheredate='....'會將字符串根據nls的設置轉為日期類型。DINGJUN123>selectrowidfromt;ROWIDAAAOi7AAEAAAPpWAAADINGJUN123>select*fromtwhererowid='AAAOi7AAEAAAPpWAAA';X2010-01-01DINGJUN123>select*fromtwherex='2010-01-012010-01-01DINGJUN123>selectto_char(x,'yyyymmdd')+1fromt;TO_CHAR(X,'YYYYMMDD')+1—20100102表t中的x是DATE類型，看字符與rowid比較會將字符轉為rowid類型。字符與數字運算轉為數值類型，日期與字符比較會將字符轉為日期根據nls的設置。我們再看一個例子說明這種自動類型轉換的特點：DINGJUN123>droptablet;表已刪除。DINGJUN123>createtabletaswithtmpas(select'15'idfromdualunionallselect'2'fromdualunionallselect'38'fromdualunionallselect'4'fromdual)select*fromtmp;表已創建。 選擇的結果按字符類型排序的，不符合要求 DINGJUN123>select*fromtorderbyid;j—152384 自動轉換數值類型排序，當然最好用to_number(id) DINGJUN123>select*fromtorderbyid+0;j---253813.字符類型之間的類型轉換，CHAR,VACHAR2,NCHAR,NVARCHAR2，我們知道，NVACHAR2需要國家字符集(9i后有UTF8和AL16UTF16)的支持，而且是按字符存儲的，CHAR,VARCHAR2受數據庫默認字符集的支持。那么數據庫字符集支持的CHAR,VARCHAR2默認轉換到NCHAR,NVARCHAR2,當然VARCHAR2與CHAR是CHAR轉VARCHAR2，如下：到UCHAR到VARCHAR2到UNCHAR到NVARCHAR2CHAR--VARCHAR2NCHARNVARCHAR2VARCHAR2VARCHAR2--NVARCHAR2NVARCHAR2NCHARNCHARNCHAR--NVARCHAR2NVARCHAR2NVARCHAR2NVARCHAR2NVARCHAR2--我們看到，NVARCHAR2最大，所有的遇到它都要自動轉為NVARCHAR2類型。CHAR遇到VARCHAR2要轉為VARCHAR2。14,很多SQL函數可以接受CLOB類型，對參數要求是VARCHAR2或CHAR的如果傳入CLOB類型也是可以