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

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

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