8.3 使用TO_CHAR、TO_NUMBER和TO_DATE轉(zhuǎn)換函數(shù)

這一考試目標(biāo)包括使用示例對(duì)TO_NUMBER、TO_DATE和TO_CHAR函數(shù)作系統(tǒng)描述。對(duì)TO_CHAR的討論可分為從兩種類型的數(shù)據(jù)到字符的轉(zhuǎn)換：DATE和NUMBER。控制轉(zhuǎn)換到字符值的不同格式掩碼的可用性保證了這種劃分。還有其他許多轉(zhuǎn)換函數(shù)，但這些轉(zhuǎn)換函數(shù)是最常用的。

使用轉(zhuǎn)換函數(shù)

在很多情況下都需要使用轉(zhuǎn)換函數(shù)。這些情況包括格式化報(bào)告中的DATE字段，以確保從字符字段提取的數(shù)字在應(yīng)用于算術(shù)表達(dá)式之前被正確地轉(zhuǎn)換為數(shù)字。

表8-3說明了單行顯式數(shù)據(jù)類型轉(zhuǎn)換函數(shù)的語法。

可選國(guó)家語言支持參數(shù)(nls_parameters)對(duì)于指定返回日期和數(shù)字元素的語言和格式非常有用。通常沒有指定這些參數(shù)，因此會(huì)使用日期和數(shù)字元素的默認(rèn)值(例如日或者月的名稱和縮寫詞)。如圖8-2所示，有一種公共可用的視圖NLS_SESSION_PARAMETERS，它包含當(dāng)前會(huì)話的NLS參數(shù)。NLS_CURRENCY的默認(rèn)值是美元符號(hào)($)，但它在用戶會(huì)話級(jí)別可以改變。例如，要將該貨幣符號(hào)改變?yōu)槿齻€(gè)字符長(zhǎng)的字符串GBP，那么可以執(zhí)行下面的命令：ALTER SESSION set NLS_CURRENCY='GBP';

1．使用TO_CHAR函數(shù)將數(shù)字轉(zhuǎn)換為字符

TO_CHAR函數(shù)返回VARCHAR2數(shù)據(jù)類型的值。當(dāng)將它應(yīng)用于NUMBER數(shù)據(jù)類型的值時(shí)，可以使用幾種格式選項(xiàng)。其語法如下所示：

        TO_CHAR(num, [format], [nls_parameter])

num參數(shù)是強(qiáng)制性的，它必須是一個(gè)數(shù)字值。可選的format參數(shù)用來指定數(shù)字格式信息——例如寬度、貨幣符號(hào)、小數(shù)點(diǎn)的位置和組(或者千位)分隔符，必須將它們包含在單引號(hào)內(nèi)。除此之外，對(duì)于要轉(zhuǎn)換為字符的數(shù)字而言，還有其他一些表示數(shù)字格式信息的選項(xiàng)，其中一些格式如表8-4所示。例如下面兩個(gè)查詢：

        查詢1: select to_char(00001)||' is a special number' from dual;
        查詢2: select to_char(00001, '0999999')||' is a special number' from dual;

查詢1計(jì)算數(shù)字00001，刪除其前面的零，將數(shù)字1轉(zhuǎn)換為字符’1'，返回字符串’1 is a special number'。查詢2將數(shù)字格式掩碼’0999999’應(yīng)用于數(shù)字00001，將數(shù)字00001轉(zhuǎn)換為字符串'0000001'。在連接到字符字面值之后，返回的字符串是’0000001 is a special number'。對(duì)于TO_CHAR函數(shù)而言，格式掩碼中的零和6個(gè)9說明必須顯示前面的零，而且顯示的寬度必須設(shè)置為7個(gè)字符。因此，TO_CHAR函數(shù)返回的字符串包含7個(gè)字符。

2．使用TO_CHAR函數(shù)將日期轉(zhuǎn)換為字符

使用TO_CHAR函數(shù)，可以利用各種格式模型將DATE項(xiàng)轉(zhuǎn)換為幾乎所有日期的字符表示形式。其語法如下所示：

        TO_CHAR(date1, [format], [nls_parameter])

只有date1參數(shù)是強(qiáng)制的，date1必須是可以被隱式轉(zhuǎn)換為日期的值。可選的format參數(shù)區(qū)分大小寫，必須將它包含在單引號(hào)內(nèi)。格式掩碼指定提取哪些日期元素，是用長(zhǎng)的名稱還是用縮寫名稱來描述這個(gè)元素。還會(huì)自動(dòng)給日和月的名稱填充空格。可以使用格式掩碼的修飾符來刪除這些空格，這個(gè)修飾符稱為填充模式(fm)運(yùn)算符。在格式模型之前添加字母fm，就會(huì)命令Oracle 從日和月的名稱中刪除所有空格。對(duì)于被轉(zhuǎn)換為字符的日期而言還有許多格式選項(xiàng)，其中一些選項(xiàng)如表8-5所示。考察下面三個(gè)查詢：

        查詢1: select to_char(sysdate)||' is today''s date' from dual;
        查詢2: select to_char(sysdate, 'Month')||'is a special time' from dual;
        查詢3: select to_char(sysdate, 'fmMonth')||'is a special time' from dual;

如果當(dāng)前系統(tǒng)日期是03/JAN/09，默認(rèn)顯示格式是DD/MON/RR，那么查詢1返回字符串’03/ JAN/09 is today's date'。查詢2中有兩個(gè)值得注意的部分。第一，只提取當(dāng)前系統(tǒng)日期的月部分并轉(zhuǎn)換為字符類型。第二，因?yàn)楦袷窖诖a區(qū)分大小寫，而’Month’是首字母大寫，所以返回的字符串是’January is a special time'。不需要在字面值’is a special time’前面添加空格，因?yàn)門O_CHAR函數(shù)會(huì)自動(dòng)給月名添加空格。如果查詢2中的格式掩碼是’MONTH'，那么返回的字符串應(yīng)該是’JANUARY is a special time'。將fm修飾符應(yīng)用于查詢3，返回的字符串是'Januaryis a special time'。注意，January和字面值’is a special time’之間沒有空格。在表8-5中，假設(shè)格式元素作用于日期02-JUN-1975，現(xiàn)在的年份是2009。

表8-6列出了關(guān)于周、季度、世紀(jì)和其他不常用格式掩碼的日期格式。該表使用日期24-SEP-1000BC，以及格式元素列的格式掩碼來計(jì)算TO_CHAR函數(shù)，得到的結(jié)果在表中結(jié)果列顯示。

使用表8-7中的格式模型提取日期時(shí)間數(shù)據(jù)類型的時(shí)間部分。該表使用包括其時(shí)間部分的日期27-JUN-2010 21:35:13，以及格式元素列中的格式掩碼計(jì)算TO_CHAR函數(shù)，得到結(jié)果列。

表8-8列出了其他一些能夠在日期時(shí)間格式模型中使用的元素。標(biāo)點(diǎn)符號(hào)用來分隔格式元素。有三種類型的后綴可以用來格式化日期時(shí)間元素的組件。而且，如果將字符字面值包含在雙引號(hào)內(nèi)，那么就能夠在返回值中包含它們。使用日期12/SEP/08 14:31，以及說明和格式掩碼列中的格式掩碼來計(jì)算TO_CHAR函數(shù)，就可以得到表8-8中的結(jié)果。

JOB_HISTORY表記錄公司員工從事的工作。圖8-3中的查詢依據(jù)END_DATE、EMPLOYEE_ ID和JOB_ID字段檢索關(guān)于每名員工辭職日期的說明語句。需要將必要的字符表達(dá)式連接到格式模型為’fmDay "the "ddth "of" Month YYYY’的TO_CHAR函數(shù)。fm修飾符用來刪除跟在較短的日和較短的月名稱后面的空格。雙引號(hào)中包含的兩個(gè)字符字面值是單詞"the"和"of"。將’th’格式模型應(yīng)用于’dd’日期元素來創(chuàng)建序數(shù)日，如第17和第31。'Month’ 格式模型以首字母大寫的形式顯示END_DATE列中月元素的全名。最后，YYYY格式掩碼檢索4位數(shù)的年部分。

盡管在默認(rèn)情況下不會(huì)顯示世紀(jì)組成部分，但當(dāng)插入或者更新日期值時(shí)，它還是會(huì)保存在數(shù)據(jù)庫中，并且可用于檢索。DD-MON-RR格式掩碼是顯示和輸入的默認(rèn)格式。當(dāng)插入或者更新日期值時(shí)，可以從SYSDATE函數(shù)中獲得世紀(jì)部分(如果沒有提供它)。RR日期格式掩碼不同于YY格式掩碼，因?yàn)樗梢砸罁?jù)當(dāng)前和指定的年份確定不同的世紀(jì)。考慮下面的原則，可以更好地理解分配給日期(該日期的年組成部分使用RR格式)的世紀(jì)組成部分：

● 如果當(dāng)前年和指定年的兩個(gè)數(shù)字在0～49之間，則返回當(dāng)前世紀(jì)。假設(shè)當(dāng)前日期是02-JUN- 2007，那么以DD-MON-RR為格式的日期24-JUL-04返回的世紀(jì)是20。

● 如果當(dāng)前年的兩個(gè)數(shù)字介于0～49之間，指定年介于50～99之間，那么返回上一個(gè)世紀(jì)。假設(shè)當(dāng)前日期是02-JUN-2007，那么為24-JUL-94返回的世紀(jì)是19。

● 如果當(dāng)前年和指定年的兩個(gè)數(shù)字介于50～99之間，在默認(rèn)情況下返回當(dāng)前世紀(jì)。假設(shè)當(dāng)前日期是02-JUN-1975，那么為24-JUL-94返回的世紀(jì)是19。

● 如果當(dāng)前年的兩個(gè)數(shù)字介于50～99之間，指定年介于0～49之間，則返回下個(gè)世紀(jì)。假設(shè)當(dāng)前日期是02-JUN-1975，那么為24-JUL-07返回的世紀(jì)是20。

3．使用TO_DATE函數(shù)將字符轉(zhuǎn)換為日期

TO_DATE函數(shù)返回DATE類型的值。轉(zhuǎn)換為日期的字符串可能包含所有或者部分組成DATE的日期時(shí)間元素。當(dāng)只轉(zhuǎn)換包含日期時(shí)間元素子集的字符串時(shí)，Oracle 提供默認(rèn)值來構(gòu)造完整的日期。字符串的組成部分通過格式模型或掩碼與不同的日期時(shí)間元素相關(guān)聯(lián)。其語法如下所示：

        TO_DATE(string1, [format], [nls_parameter])

只有string1參數(shù)是強(qiáng)制性的，如果沒有提供格式掩碼，string1必須會(huì)隱式轉(zhuǎn)換為日期。幾乎總是使用可選的 format 參數(shù)，在單引號(hào)內(nèi)指定它。格式掩碼與表8-5、表8-6和表8-7中列出的格式掩碼相同。TO_DATE函數(shù)有fx修飾符，它類似于TO_CHAR函數(shù)使用的fm。fx修飾符指定string1和格式掩碼完全匹配。指定fx修飾符時(shí)，不完全匹配格式掩碼的字符項(xiàng)會(huì)產(chǎn)生錯(cuò)誤。例如下面5個(gè)查詢：

        查詢1: select to_date('25-DEC-2010') from dual;
        查詢2: select to_date('25-DEC') from dual;
        查詢3: select to_date('25-DEC', 'DD-MON') from dual;
        查詢4: select to_date('25-DEC-2010 18:03:45', 'DD-MON-YYYY
                HH24:MI:SS') from dual;
        查詢5: select to_date('25-DEC-10', 'fxDD-MON-YYYY') from dual;

查詢1計(jì)算字符串25-DEC-2010，它有足夠的信息將其隱式轉(zhuǎn)換為DATE項(xiàng)，使用的默認(rèn)掩碼是DD-MON-YYYY。連字號(hào)分隔符會(huì)被另一個(gè)標(biāo)點(diǎn)字符取代。因?yàn)闆]有提供時(shí)間組成部分，因此將轉(zhuǎn)換日期的時(shí)間設(shè)置為午夜或者00:00:00。查詢2無法將字符串隱式轉(zhuǎn)換為日期，因?yàn)闆]有足夠的信息，所以會(huì)返回“ORA-01840:input value is not long enough for date format”錯(cuò)誤。在查詢3中給字符串25-DEC提供格式掩碼DD-MON, Oracle能夠讓數(shù)字25匹配DD，讓縮寫的月名DEC匹配MON部分。沒有年和時(shí)間部分，因此使用SYSDATE函數(shù)返回的當(dāng)前年，并將時(shí)間設(shè)置為午夜。如果當(dāng)前年是2009，查詢3就會(huì)返回日期25/DEC/09 00:00:00。查詢4執(zhí)行包含所有日期時(shí)間元素的字符串的完整轉(zhuǎn)換，Oracle沒有提供默認(rèn)值。查詢5在其格式掩碼中使用 fx 修飾符。因?yàn)樽址哪瓴糠质?0，而對(duì)應(yīng)的格式掩碼是YYYY，因此會(huì)返回錯(cuò)誤“ORA-01862: the numeric value does not match the length of the format item”。

4．使用TO_NUMBER函數(shù)將字符轉(zhuǎn)換為數(shù)字

TO_NUMBER函數(shù)返回NUMBER類型的值。轉(zhuǎn)換為數(shù)字的字符串必須有合適的格式，以便用相應(yīng)的格式掩碼轉(zhuǎn)換或者刪除所有非數(shù)字組成部分。其語法如下所示：

        TO_NUMBER(string1, [format], [nls_parameter])

只有string1參數(shù)是強(qiáng)制性的，如果沒有提供格式掩碼，string1就必須是可以隱式轉(zhuǎn)換為數(shù)字的值。用單引號(hào)指定可選的format參數(shù)。格式掩碼與表8-4列出的格式掩碼相同。例如下面兩個(gè)查詢：

        查詢1: select to_number('$1,000.55') from dual;
        查詢2: select to_number('$1,000.55', '$999,999.99') from dual;

查詢1不能執(zhí)行到數(shù)字的隱式轉(zhuǎn)換，因?yàn)橛忻涝?hào)、逗號(hào)和句號(hào)，因此返回錯(cuò)誤“ORA-1722: invalid number”。查詢2讓字符串中的美元符號(hào)、逗號(hào)和句號(hào)匹配格式掩碼，盡管數(shù)字寬度大于字符串寬度，但還是返回?cái)?shù)字1000.55。