必須重視的Oracle自動類型轉(zhuǎn)換

29.必須重視的Oracle自動類型轉(zhuǎn)換顯示類型轉(zhuǎn)換以date類型為例子。Oracle中對不同類型的處理具有顯式類型轉(zhuǎn)換(Explicit)和自動類型轉(zhuǎn)換(隱式類型轉(zhuǎn)換Implicit)兩種方式，對于顯式類型轉(zhuǎn)換，我們是可控的，但是對于自動類型轉(zhuǎn)換，當然不建議使用，因為很難控制，有不少缺點，但是我們很難避免碰到自動類型轉(zhuǎn)換，如果不了解自動類型轉(zhuǎn)換的規(guī)則，那么往往會改變我們SQL的執(zhí)行計劃，從而可能導致效率降低或其它問題，所以，Oracle開發(fā)人員很有必要了解Oracle自動類型轉(zhuǎn)換的相關規(guī)則，從而避免自動類型轉(zhuǎn)換導致相關問題的產(chǎn)生。本章首先會對Oracle自動類型轉(zhuǎn)換的規(guī)則做闡述，然后結(jié)合相關實例分析自動類型轉(zhuǎn)換可能造成的問題。29.1數(shù)據(jù)類型優(yōu)先級Oracle使用數(shù)據(jù)類型的優(yōu)先級來決定自動類型轉(zhuǎn)換,Oracle類型如下優(yōu)先:Datetimeandinterval類型BINARY_DOUBLEBINARY_FLOATNUMBER■字符類型■所有其它內(nèi)置類型上面說的不夠具體，我們看第二節(jié)具體的類型轉(zhuǎn)換規(guī)則。29.2自動類型轉(zhuǎn)換規(guī)則一般一個表達式不能包含多種數(shù)據(jù)類型，比如一個表達式5*10然后加上'james'，但是Oracle會有自動類型轉(zhuǎn)換和顯式類型轉(zhuǎn)換兩種規(guī)則，我們看如下例子：DINGJUN123>select5*10+'james'fromdual;select5*10+'james'fromdual*第1行出現(xiàn)錯誤：ORA-01722:無效數(shù)字我們看到，報無效數(shù)字錯誤。當然，這里Oracle使用了自動類型轉(zhuǎn)換將'james'轉(zhuǎn)為數(shù)字類型，但是這個轉(zhuǎn)換是失敗的，所以報錯，所以自動類型轉(zhuǎn)換的第1個規(guī)則就是必須自動類型轉(zhuǎn)換能夠成功，否則報錯。我們看下面的就轉(zhuǎn)換成功了：DINGJUN123>select5*10+'2'fromdual;5*10+'2'52OK,看到了結(jié)果正確，這里的字符串'2'被自動轉(zhuǎn)為數(shù)值類型的2(不明白為什么會這樣轉(zhuǎn)換，請往下看)，所以結(jié)果為52.。29.2.1為什么不建議使用自動類型轉(zhuǎn)換?自動類型轉(zhuǎn)換的確可以讓我們少寫一些內(nèi)容，比如可以少寫個to_char函數(shù)之類的東西,但是它經(jīng)常是不好的：1.使用顯示類型轉(zhuǎn)換會讓我們的SQL更加容易被理解，也就是可讀性更強，但是自動類型轉(zhuǎn)換卻沒有這個優(yōu)點，如：DINGJUN123>selectto_date(sysdate,'yyyymm')fromdual;也許你會想，我沒有看錯吧,你寫的語句是錯的,to_date中間的第1個參數(shù)是字符類型哦,你提的這個問題很好，我想你應該需要了解了解Oracle中的自動類型轉(zhuǎn)換了。我可以很明確地告訴你，這個語句是可以的，但是能不能運行正確就要依賴于具體的上下文了，比如這里sysdate是date類型，那么需要將date類型轉(zhuǎn)為字符，這是自動轉(zhuǎn)換的，也就是Oracle要自動調(diào)用to_char(sysdate,fmt)，這個fmt就依賴于上下文的nls_date_format,也有可能會依賴于nls_date_language的設置，看我們的結(jié)果：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自動類型轉(zhuǎn)換的確難以理解，不知道的人以為這真是太神奇了，可能以為Oracle的函數(shù)定義搞錯了，還是了解下這方面的內(nèi)容吧，這樣才可以運籌帷幄，決勝千里。2.自動類型轉(zhuǎn)換往往對性能產(chǎn)生不好的影響，特別是左值的類型被自動轉(zhuǎn)為了右值的類型。這種方式很可能使我們本來可以使用索引的而沒有用上索引，也有可能會導致結(jié)果出錯。如：DINGJUN123>droptablet;表已刪除。DINGJUN123>createtablet(namevarchar2(10));表已創(chuàng)建。DINGJUN123>insertintotvalues('abc');已創(chuàng)建1行。DINGJUN123>insertintotvalues('1');已創(chuàng)建1行。DINGJUN123>commit;提交完成。DINGJUN123>createindexidx_tont(name);索引已創(chuàng)建。 案例1:自動類型轉(zhuǎn)換導致出錯 DINGJUN123>select*fromtwherename=1;select*fromtwherename=1*第1行出現(xiàn)錯誤：ORA-01722:無效數(shù)字DINGJUN123>select*fromtwherename='1';NAME—1 -案例2:自動類型轉(zhuǎn)換導致本該用索引而沒有用 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,如果這個語句很龐大，找這個錯誤還真不容易，如果是顯示轉(zhuǎn)換的話，找個錯誤就容易多了。案例2我使用RBO優(yōu)化器的，我沒有收集統(tǒng)計信息，而且還加了rule,這里不加rule一樣，如果列自動發(fā)生了類型轉(zhuǎn)換，很可能使索引失效，這句select*fromtwherename=1沒有寫select*fromtwhereto_number(name)=1發(fā)現(xiàn)索引失效明顯。但是如果我們感覺應該用索引而沒有用上索引，而且左邊的列和右邊的值類型不一樣，那么很可能發(fā)生了自動類型轉(zhuǎn)換，當然看執(zhí)行計劃有這樣的類型轉(zhuǎn)換信息，雖然我們沒有顯示地寫，往往看執(zhí)行計劃是我們第1步尋找問題的方法。自動類型轉(zhuǎn)換可能依賴于發(fā)生轉(zhuǎn)換時的上下文環(huán)境，比如1中的to_date(sysdate,fmt),一旦上下文環(huán)境改變，很可能我們的程序就不能運行。自動類型轉(zhuǎn)換的算法或規(guī)則，以后Oracle可能改變，這是很危險的，意味著舊的代碼很可能在新的Oracle版本中運行出現(xiàn)問題(性能、錯誤等)，顯示類型轉(zhuǎn)換總是有最高的優(yōu)先級，所以顯示類型轉(zhuǎn)換沒有這種版本更替可能帶來的問題。自動類型轉(zhuǎn)換是要消耗時間的，當然同等的顯式類型轉(zhuǎn)換時間也差不多，最好的方法就是避免類似的轉(zhuǎn)換，在顯示類型轉(zhuǎn)換上我們會看到，最好不要將左值進行類型轉(zhuǎn)

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