8.1 描述和使用SQL中的字符、數(shù)字和日期函數(shù)

SQL函數(shù)可以廣義區(qū)分為計(jì)算并返回?cái)?shù)據(jù)集中各行值的函數(shù)和為所有行返回一個(gè)集合值的函數(shù)。本節(jié)將先討論字符大小寫轉(zhuǎn)換函數(shù)，然后分別討論字符操作函數(shù)、數(shù)字函數(shù)和日期函數(shù)。

8.1.1 定義函數(shù)

函數(shù)是接收輸入?yún)?shù)(輸入?yún)?shù)是可選的)，執(zhí)行運(yùn)算并返回單個(gè)值的程序。每次執(zhí)行時(shí)，函數(shù)只返回一個(gè)值。

三個(gè)重要組成部分構(gòu)成了函數(shù)定義的基礎(chǔ)。第一個(gè)是輸入?yún)?shù)列表。它指定零個(gè)或者多個(gè)參數(shù)，這些參數(shù)可以作為輸入傳遞給函數(shù)處理。這些實(shí)參或者形參是可選的，具有不同的數(shù)據(jù)類型。第二個(gè)是返回值的數(shù)據(jù)類型。每次執(zhí)行時(shí)，函數(shù)只返回一個(gè)預(yù)定數(shù)據(jù)類型的值。第三個(gè)封裝函數(shù)執(zhí)行處理的細(xì)節(jié)，包含可選操作輸入?yún)?shù)、執(zhí)行計(jì)算和運(yùn)算并生成返回值的程序代碼。

函數(shù)通常被稱為黑盒(black box)，它獲取輸入，執(zhí)行計(jì)算并返回值。不要求關(guān)注實(shí)現(xiàn)函數(shù)的細(xì)節(jié)，而是應(yīng)該關(guān)注內(nèi)置函數(shù)提供的功能。

函數(shù)可以嵌套在其他函數(shù)內(nèi)，例如F1(x, y, F2(a, b), z)，其中F2有兩個(gè)輸入?yún)?shù)：a和b，它形成提交到F1的4個(gè)參數(shù)中的第三個(gè)參數(shù)。函數(shù)可以作用于任何數(shù)據(jù)類型，最常用的是字符、日期和數(shù)字?jǐn)?shù)據(jù)。函數(shù)的操作數(shù)可以是列或者表達(dá)式。

例如，考慮用來(lái)計(jì)算某個(gè)人年齡的函數(shù)。AGE函數(shù)使用一個(gè)日期輸入?yún)?shù)，這個(gè)參數(shù)是某人的生日。AGE函數(shù)返回的結(jié)果是表示此人年齡的數(shù)字。通過黑盒計(jì)算得到當(dāng)前日期和生日輸入?yún)?shù)之間年份的差值。

8.1.2 函數(shù)類型

可以將函數(shù)廣義地分為兩大類，即單行函數(shù)和多行函數(shù)。這兩種函數(shù)的差別對(duì)于理解使用函數(shù)的較長(zhǎng)上下文至關(guān)重要。

1．單行函數(shù)

有幾種類型的單行函數(shù)，包括字符、數(shù)字、日期、轉(zhuǎn)換函數(shù)和通用函數(shù)。這些函數(shù)每次只作用于數(shù)據(jù)集的一行。如果查詢選擇10行，函數(shù)就會(huì)執(zhí)行10次，每行一次，并將來(lái)自一行的值作為函數(shù)的輸入。

下面這個(gè)查詢選擇REGIONS表的兩列以及一個(gè)對(duì)REGION_NAME列使用LENGTH函數(shù)的表達(dá)式：

        select region_id, region_name, length(region_name) from regions;

REGIONS表中的每行都要計(jì)算REGION_NAME列的長(zhǎng)度，因此函數(shù)被執(zhí)行了4次，每行返回一個(gè)結(jié)果。

單行函數(shù)操作行中的數(shù)據(jù)項(xiàng)，提取并且格式化它們以方便顯示。單行函數(shù)的輸入值可以是用戶指定的常量或者字面值、列數(shù)據(jù)、變量或者由其他嵌套的單行函數(shù)提供的表達(dá)式。嵌套單行函數(shù)是一種常用方法。函數(shù)可以依據(jù)輸入?yún)?shù)返回不同數(shù)據(jù)類型的值。前面的查詢顯示了LENGTH函數(shù)如何接受一個(gè)字符輸入?yún)?shù)，返回?cái)?shù)字輸出。

除了包含在SQL查詢的SELECT列表中之外，還可以在WHERE和ORDER BY子句中使用單行函數(shù)。

2．多行函數(shù)

顧名思義，這類函數(shù)每次作用于多行。通常使用多行函數(shù)來(lái)計(jì)算數(shù)字列值的和或者平均值，或者計(jì)算集合中記錄的總數(shù)。這些函數(shù)有時(shí)稱為聚合函數(shù)或者分組函數(shù)，第9章將討論它們。

8.1.3 使用大小寫轉(zhuǎn)換函數(shù)

包括應(yīng)用程序接口和批處理程序在內(nèi)的眾多數(shù)據(jù)源都將字符數(shù)據(jù)保存在表中。通常假設(shè)以大小寫一致的方式輸入字符數(shù)據(jù)，但這種假設(shè)并不可靠。字符大小寫轉(zhuǎn)換函數(shù)有兩個(gè)重要作用。首先，可以使用它們來(lái)修改字符數(shù)據(jù)項(xiàng)的外觀來(lái)進(jìn)行顯示；其次，可以使用它們來(lái)顯示一致的字符數(shù)據(jù)項(xiàng)以便于比較運(yùn)算。對(duì)于搜索字符串而言，使用一致的大小寫格式比測(cè)試匹配字符串的大小寫字符更簡(jiǎn)單。記住，這些函數(shù)沒有更改保存在表中的數(shù)據(jù)。它們?nèi)匀皇侵蛔xSQL查詢的一部分。

這些函數(shù)所需的字符串參數(shù)可能是任何字符串字面值、字符列值、字符表達(dá)式或者數(shù)字或日期值(會(huì)被隱式轉(zhuǎn)換為字符串)。

1．LOWER函數(shù)

LOWER函數(shù)用小寫形式替換字符串中的大寫字符。其語(yǔ)法是LOWER(s)。下列查詢演示了該函數(shù)的用法：

        select lower(100+100), lower('SQL'), lower(sysdate) from dual;

假設(shè)系統(tǒng)的當(dāng)前日期是17-DEC-2014。返回字符串“200”、“sql”和“17-dec-2014”。注意根據(jù)會(huì)話參數(shù)，日期結(jié)果可能不同。在執(zhí)行LOWER函數(shù)之前先計(jì)算數(shù)字和日期表達(dá)式，并隱式轉(zhuǎn)換為字符數(shù)據(jù)。

下列條件中使用LOWER函數(shù)來(lái)定位LAST_NAME字段中字母“u”和“r”彼此相鄰的記錄。

        select first_name, last_name, lower(last_name) from employees
        where lower(last_name) like '%ur%';

不使用大小寫轉(zhuǎn)換函數(shù)，再寫一個(gè)查詢，讓它返回相同的結(jié)果。代碼可能如下所示：

        select first_name, last_name from employees
        where last_name like '%ur%' or last_name like '%UR%'
        or last_name like '%uR%' or last_name like '%Ur%'

這個(gè)查詢可以運(yùn)行，但比較麻煩，隨著搜索字符串長(zhǎng)度的增加，所需OR子句的數(shù)量會(huì)顯著增加。

2．UPPER函數(shù)

UPPER函數(shù)與LOWER函數(shù)邏輯相反，它用大寫形式替換字符串中的小寫字符。其語(yǔ)法是UPPER (s)。下面的查詢說(shuō)明了這個(gè)函數(shù)的用法：

        select * from countries where upper(country_name) like '%U%S%A%';

這個(gè)查詢從COUNTRIES表中提取COUNTRY_NAME值依次包含字母“U”、“S”和“A”的行。

3．INITCAP函數(shù)

INITCAP函數(shù)將字符串轉(zhuǎn)換為首字母大寫的形式。它通常用于數(shù)據(jù)表示。字符串中每個(gè)單詞的第一個(gè)字母都被轉(zhuǎn)換為大寫形式，而每個(gè)單詞余下的字母被轉(zhuǎn)換為小寫字母形式。單詞通常指相鄰字符組成的字符串，單詞之間用空格或者下劃線分開，但有些字符(如百分比符號(hào)(%)、感嘆號(hào)(! )或者美元符號(hào)($))也都是有效的單詞分隔符。標(biāo)點(diǎn)符號(hào)和特殊字符也是有效的單詞分隔符。

INITCAP函數(shù)只能使用一個(gè)參數(shù)。其語(yǔ)法是INITCAP(s)。下面的查詢說(shuō)明了這個(gè)函數(shù)的用法：

        select initcap('init cap or init_cap or init%cap') from dual

該查詢返回Init Cap Or Init_Cap Or Init%Cap。

練習(xí)8-1 使用大小寫轉(zhuǎn)換函數(shù)

構(gòu)造一個(gè)查詢，從EMPLOYEES表中檢索所有FIRST_NAME和LAST_NAME值的列表，其中FIRST_NAME包含字符串“l(fā)i”。

(1) 啟動(dòng)SQL Developer或SQL* Plus，連接到HR模式。

(2) WHERE子句必須將FIRST_NAME列值與包含字符串“l(fā)i”的所有可能大小寫組合的字符模式進(jìn)行比較。因此，如果某行的FIRST_NAME包含字符串“LI”、“Li”、“l(fā)I”或者“l(fā)i”，就必須檢索該行。

(3) LIKE運(yùn)算符用于字符匹配，使用由OR關(guān)鍵字分開的四個(gè)WHERE子句可以提取這4種組合。然而，大小寫轉(zhuǎn)換函數(shù)可以簡(jiǎn)化這些條件。如果在FIRST_NAME列上使用LOWER函數(shù)，那么使用一個(gè)WHERE子句條件可以完成比較，也可以使用UPPER或者INITCAP函數(shù)。

(4) 執(zhí)行下列語(yǔ)句返回包含字符“l(fā)i”的員工名字：

        select first_name, last_name from employees where lower(first_name) like '%li%';

8.1.4 使用字符操作函數(shù)

Oracle某些最強(qiáng)大的功能是字符操作函數(shù)。它們?cè)跀?shù)據(jù)操作方面的有用性幾乎無(wú)可匹敵，許多經(jīng)驗(yàn)豐富的技術(shù)專家都使用這些函數(shù)來(lái)處理數(shù)據(jù)項(xiàng)。嵌套這些函數(shù)也很常見。通常使用串聯(lián)運(yùn)算符(||)而不是CONCAT函數(shù)。LENGTH、INSTR、SUBSTR和REPLACE函數(shù)經(jīng)常一起使用，RPAD、LPAD和TRIM也是一樣。

1．CONCAT函數(shù)

CONCAT函數(shù)連接兩個(gè)字符字面值、列或者表達(dá)式，從而生成一個(gè)更大的字符表達(dá)式。CONCAT函數(shù)有兩個(gè)參數(shù)。其語(yǔ)法是CONCAT(s1, s2)，其中s1和s2表示字符串字面值、字符列值或者產(chǎn)生字符值的表達(dá)式。下面的查詢說(shuō)明了這個(gè)函數(shù)的用法：

        select concat('Today is:', SYSDATE) from dual;

CONCAT函數(shù)的第二個(gè)參數(shù)是SYSDATE，它返回當(dāng)前系統(tǒng)日期。這個(gè)值被隱式轉(zhuǎn)換為字符串，第一個(gè)參數(shù)中的字面值連接這個(gè)字符串。如果系統(tǒng)日期是17-DEC-2014，查詢就返回字符串“Today is 17-DEC-2014”。

請(qǐng)思考如何使用CONCAT函數(shù)連接三個(gè)項(xiàng)，但只返回一個(gè)字符串。因?yàn)镃ONCAT只使用兩個(gè)參數(shù)，因此只能使用它連接兩項(xiàng)。解決方案是在一個(gè)CONCAT函數(shù)內(nèi)嵌套另一個(gè)CONCAT函數(shù)，如下所示：

        select concat('Outer1 ', concat('Inner1', ' Inner2')) from dual;

第一個(gè)CONCAT函數(shù)有兩個(gè)參數(shù)：第一個(gè)是字面值“Outer1”，第二個(gè)是嵌套的CONCAT函數(shù)。第二個(gè)CONCAT函數(shù)有兩個(gè)參數(shù)：第一個(gè)是字面值“Inner1”，第二個(gè)是字面值“Inner2”。這個(gè)查詢會(huì)生成下面的字符串：Outer1 Inner1 Inner2。后面的章節(jié)將詳細(xì)討論嵌套函數(shù)。第7章討論的連接運(yùn)算符||也可以用于連接這些項(xiàng)。

2．LENGTH函數(shù)

LENGTH函數(shù)返回組成字符串的字符數(shù)。空格、制表符和特殊字符都被LENGTH函數(shù)計(jì)算在內(nèi)。LENGTH函數(shù)只有一個(gè)參數(shù)。其語(yǔ)法是LENGTH(s)。考慮下列查詢：

        select * from countries where length(country_name) > 10;

LENGTH函數(shù)用于從COUNTRIES表中提取長(zhǎng)度大于10個(gè)字符的COUNTRY_NAME值。

3．LPAD和RPAD函數(shù)

LPAD和RPAD函數(shù)也被稱為左填充和右填充函數(shù)，它們分別返回在給定字符串左邊或右邊填充指定數(shù)量的字符后形成的字符串。用于填充的字符串包括字符字面值、列值、表達(dá)式、空格(默認(rèn))、制表符和特殊字符。

LPAD和RPAD函數(shù)有三個(gè)參數(shù)。其語(yǔ)法是LPAD(s, n, p)和RPAD(s, n, p)，其中s表示源字符串，n表示返回字符串的最終長(zhǎng)度，p 指定用于填充的字符串。如果使用LPAD函數(shù)，填充字符串p就會(huì)被添加到源字符串s的左邊，直到其長(zhǎng)度為n為止。如果使用RPAD函數(shù)，填充字符串p就被添加到源字符串s的右邊，直到其長(zhǎng)度為n為止。注意，如果參數(shù)n小于或者等于源字符串s的長(zhǎng)度，就不會(huì)添加任何字符，此時(shí)只返回源字符串s的前n個(gè)字符。考慮圖8-1中所示的查詢。

第一個(gè)查詢沒有填充數(shù)據(jù)，結(jié)果的可讀性不如第二個(gè)查詢的輸出。RPAD用于在必要時(shí)給first_name和last_name的串聯(lián)值添加空格，直至每個(gè)名字達(dá)18個(gè)字符長(zhǎng)，而LPAD在薪水值的開始部分添加空格，直至每個(gè)薪水值達(dá)6個(gè)字符長(zhǎng)。

4．TRIM函數(shù)

TRIM函數(shù)從字符值的開頭或者結(jié)尾刪除一些字符，從而生成一個(gè)更簡(jiǎn)短的字符項(xiàng)。TRIM函數(shù)使用的參數(shù)由一個(gè)強(qiáng)制組成部分和一個(gè)可選組成部分構(gòu)成。其語(yǔ)法是TRIM([trailing|leading|both] trimstring from s)。被修整的字符串(s)是強(qiáng)制的。下面幾點(diǎn)說(shuō)明了使用該函數(shù)的規(guī)則：

● TRIM(s)刪除輸入字符串兩邊的空格。

● TRIM(trailing trimstring from s)從字符串s的結(jié)尾刪除所有trimstring(如果存在的話)。

● TRIM(leading trimstring from s)從字符串s的開頭刪除所有trimstring(如果存在的話)。

● TRIM(both trimstring from s)從字符串s的開頭和結(jié)尾刪除所有trimstring(如果存在的話)。

        select trim(both '*' from '****Hidden****'),
        trim(leading '*' from '****Hidden****'),
        trim(trailing '*' from '****Hidden****') from dual;

前面的查詢返回字符串“Hidden”、“Hidden****”和“****Hidden”。注意，雖然只指定一個(gè)修整字符，但如果它們繼續(xù)存在，就會(huì)進(jìn)行多次刪除。

5．INSTR函數(shù)（In-string）

INSTR函數(shù)確定搜索字符串在給定字符串內(nèi)的位置。它返回?cái)?shù)字位置，在這個(gè)位置上，搜索字符串開始第 n 次出現(xiàn)(相對(duì)于指定的起始位置而言)。如果搜索字符串不存在，那么INSTR函數(shù)就返回零。

INSTR函數(shù)使用兩個(gè)可選參數(shù)和兩個(gè)強(qiáng)制參數(shù)。其語(yǔ)法是INSTR(source string, search string, [search start position], [nth occurrence])。search start position的默認(rèn)值是1或者source string的開頭。nth occurrence的默認(rèn)值是1或者第1次出現(xiàn)。考慮下面的查詢：

        查詢1: select instr('1#3#5#7#9#', '#') from dual;
        查詢2: select instr('1#3#5#7#9#', '#' ,5) from dual;
        查詢3: select instr('1#3#5#7#9#', '#', 3, 4) from dual;

查詢1從源字符串的開始處搜索哈希符第1次出現(xiàn)的位置，返回位置2。查詢2從源字符串的位置5處開始搜索哈希符，發(fā)現(xiàn)哈希符后續(xù)出現(xiàn)在位置6上。查詢3從源字符串的位置3處開始搜索哈希符，發(fā)現(xiàn)位置10是哈希符第4次出現(xiàn)的位置。

6．SUBSTR函數(shù)（Substring）

SUBSTR函數(shù)從給定源字符串中給定的位置開始，提取指定長(zhǎng)度的子字符串。如果起始位置大于源字符串的長(zhǎng)度，就會(huì)返回 null。如果從給定起始位置提取的字符數(shù)大于源字符串的長(zhǎng)度，返回的部分就是從起始位置到字符串結(jié)尾的子字符串。

SUBSTR函數(shù)有三個(gè)參數(shù)，前兩個(gè)是強(qiáng)制的。其語(yǔ)法是SUBSTR(source string, start position, [number of characters to extract])。要提取的默認(rèn)字符數(shù)為從start position到source string結(jié)尾的字符數(shù)。考慮下面的查詢：

        查詢1: select substr('1#3#5#7#9#', 5) from dual;
        查詢2: select substr('1#3#5#7#9#', 5, 3) from dual;
        查詢3: select substr('1#3#5#7#9#', -3, 2) from dual;

查詢1從位置5開始提取子字符串。因?yàn)闆]有指定第三個(gè)參數(shù)，所以默認(rèn)的提取長(zhǎng)度為從起始位置到源字符串結(jié)尾的字符數(shù)，也就是6。查詢1返回的子字符串是“5#7#9#”。查詢2返回位置5～7處的字符，即子字符串“5#7”。查詢3將數(shù)字-3作為它的起始位置。負(fù)數(shù)起始位置參數(shù)說(shuō)明Oracle從字符串的結(jié)尾向前搜索3個(gè)字符。因此，起始位置是從字符串結(jié)尾向前3個(gè)字符，即位置8。第三個(gè)參數(shù)是2，因此返回子字符串“#9”。

7．REPLACE函數(shù)

REPLACE函數(shù)用替換項(xiàng)取代源字符串中出現(xiàn)的所有搜索項(xiàng)。如果替換項(xiàng)的長(zhǎng)度與搜索項(xiàng)的長(zhǎng)度不同，那么返回字符串的長(zhǎng)度與源字符串的長(zhǎng)度也不同。如果沒有找到搜索字符串，就會(huì)原封不動(dòng)地返回源字符串。REPLACE函數(shù)有三個(gè)參數(shù)，前兩個(gè)是強(qiáng)制的。其語(yǔ)法是REPLACE(source string, search item, [replacement term])。如果省略replacement term參數(shù)，就會(huì)從source string中刪除所有出現(xiàn)的search item。也就是說(shuō)用空字符串取代search item。考慮下面的查詢：

        查詢1: select replace('1#3#5#7#9#', '#', '->') from dual;
        查詢2: select replace('1#3#5#7#9#', '#') from dual;

查詢1中將哈希符指定為搜索字符，替換字符串指定為“->”。哈希符在源字符串中出現(xiàn)了5次，因此最后得到的字符串是：1->3->5->7->9->。查詢2沒有指定替換字符串。因此默認(rèn)行為是用空字符串取代搜索字符串，實(shí)際上就是從源字符串中完全刪除搜索字符，結(jié)果返回字符串“13579”。

8.1.5 使用數(shù)字函數(shù)

Oracle提供了一定數(shù)量的內(nèi)置數(shù)字函數(shù)，來(lái)與流行的電子數(shù)據(jù)表軟件包的數(shù)學(xué)工具包抗衡。數(shù)字函數(shù)與其他函數(shù)的顯著不同是它們只接受和返回?cái)?shù)字?jǐn)?shù)據(jù)。Oracle提供的數(shù)字函數(shù)能夠解決三角運(yùn)算、求冪和對(duì)數(shù)等問題。本章重點(diǎn)關(guān)注三個(gè)單行數(shù)字函數(shù)：ROUND、TRUNC和MOD。

1．?dāng)?shù)字ROUND函數(shù)

ROUND函數(shù)依據(jù)指定的小數(shù)精度對(duì)數(shù)值進(jìn)行舍入運(yùn)算。返回依據(jù)有效數(shù)字以指定的小數(shù)精度進(jìn)行上舍入或者下舍入的值。如果指定的小數(shù)精度為 n，則要舍入的有效數(shù)字在小數(shù)點(diǎn)右邊(n+1)個(gè)位置。如果n為負(fù)數(shù)，那么要舍入的有效數(shù)字在小數(shù)點(diǎn)左邊n個(gè)位置。如果有效數(shù)字的數(shù)值大于或者等于5，就進(jìn)行“上舍入”，其他情況進(jìn)行“下舍入”。

ROUND函數(shù)有兩個(gè)參數(shù)。其語(yǔ)法是ROUND(source number, decimal precision)。source number參數(shù)表示任何數(shù)字值。decimal precision參數(shù)指定舍入的精度，它是可選的。如果沒有指定decimal precision參數(shù)，則舍入的默認(rèn)精度是零，也就是說(shuō)將源數(shù)字舍入為最接近的整數(shù)。

看一下表8-1中列出的數(shù)字1601.916的小數(shù)精度。負(fù)的小數(shù)精度值位于小數(shù)點(diǎn)左邊，而正值在小數(shù)點(diǎn)右邊。

如果decimal precision參數(shù)是1，那么會(huì)將源數(shù)字舍入為最接近的十分位數(shù)。如果它是2，那么會(huì)將源數(shù)字舍入為最近的百分位數(shù)，以此類推。下面的查詢說(shuō)明了這個(gè)函數(shù)的用法：

        查詢1: select round(1601.916, 1) from dual;
        查詢2: select round(1601.916, 2) from dual;
        查詢3: select round(1601.916, -3) from dual;
        查詢4: select round(1601.916) from dual;

查詢1的decimal precision參數(shù)(n)為1，也就是說(shuō)將源數(shù)字舍入為最近的十分位數(shù)。因?yàn)榘俜治?n + 1)數(shù)字是1(小于5)，所以不進(jìn)行舍入，返回的數(shù)字是1601.9。查詢2中小數(shù)精度參數(shù)是2，因此將源數(shù)字舍入為最近的百分位數(shù)。因?yàn)榍Х治皇?(大于5)，所以進(jìn)行舍入，返回的數(shù)字是1601.92。查詢3中的小數(shù)精度參數(shù)是-3。因?yàn)樗秦?fù)數(shù)，所以舍入的有效數(shù)字在小數(shù)點(diǎn)左邊第3個(gè)位置，即百位，也就是6。因?yàn)榘傥皇?，所以進(jìn)行舍入，返回的數(shù)字是2000。查詢4未指定小數(shù)精度參數(shù)。這說(shuō)明應(yīng)該舍入到最接近的整數(shù)。因?yàn)槭治皇?，因此向上舍入，返回1602。

2．?dāng)?shù)字TRUNC函數(shù)（Truncate）

TRUNC函數(shù)依據(jù)指定的小數(shù)精度對(duì)數(shù)值執(zhí)行截取運(yùn)算。數(shù)字截取不同于舍入，因?yàn)槿绻?shù)精度是正數(shù)的話，最后的值依據(jù)指定的小數(shù)精度刪除數(shù)字，并不進(jìn)行向上或者向下舍入。然而，如果指定的小數(shù)精度(n)為負(fù)數(shù)，輸入值從小數(shù)點(diǎn)左邊第n個(gè)數(shù)位開始向后歸0。

TRUNC函數(shù)有兩個(gè)參數(shù)。其語(yǔ)法是TRUNC(source number, decimal precision)。source number 表示任何數(shù)字值。decimal precision 指定截取的精度，它是可選的。如果沒有指定decimal precision參數(shù)，那么默認(rèn)精度為零，也就是說(shuō)將source number截取到最接近的整數(shù)。

如果decimal precision參數(shù)為1，那么將source number截取到十分位。如果它為2，那么將source number截取到百分位，以此類推。下面的查詢說(shuō)明了這個(gè)函數(shù)的用法：

        查詢1: select trunc(1601.916, 1) from dual;
        查詢2: select trunc(1601.916, 2) from dual;
        查詢3: select trunc(1601.916, -3) from dual;
        查詢4: select trunc(1601.916) from dual;

查詢1的decimal precision 參數(shù)為1，也就意味著將source number截取到十分位，返回?cái)?shù)字1601.9。查詢2中的decimal precision參數(shù)是2，因此在百分位截取source number，返回的數(shù)字是1601.91。注意，如果執(zhí)行舍入運(yùn)算，結(jié)果就不是這樣，因?yàn)槲恢?n+1)上的數(shù)字為6(大于5)。查詢3指定負(fù)數(shù)(-3)作為decimal precision。小數(shù)點(diǎn)左邊的第三個(gè)位置說(shuō)明截取在百位發(fā)生，如前面的表8-1所示。因此，從其百位數(shù)字(6)開始向后將source number歸零，返回的數(shù)字是1000。最后，查詢4沒有decimal precision參數(shù)，也就意味著截取在整數(shù)精度完成。返回的數(shù)字是1601。

3．MOD函數(shù)（Modulus）

MOD函數(shù)返回除法運(yùn)算的余數(shù)。提供兩個(gè)數(shù)——被除數(shù)和除數(shù)，執(zhí)行除法運(yùn)算。如果除數(shù)是被除數(shù)的因數(shù)，MOD就返回零，因?yàn)闆]有余數(shù)。如果除數(shù)等于零，則返回no division by zero錯(cuò)誤，MOD函數(shù)也返回零。如果除數(shù)大于被除數(shù)，那么MOD函數(shù)返回被除數(shù)作為結(jié)果。這是因?yàn)樗鼘⒊龜?shù)除以零次，余數(shù)等于被除數(shù)。

MOD函數(shù)有兩個(gè)參數(shù)。其語(yǔ)法是MOD(dividend, divisor)。dividend和divisor參數(shù)都可以表示數(shù)字字面值、列或者表達(dá)式，它們可以是負(fù)數(shù)也可以是正數(shù)。下面的查詢說(shuō)明了這個(gè)函數(shù)的用法：

        查詢1: select mod(6, 2) from dual;
        查詢2: select mod(5, 3) from dual;
        查詢3: select mod(7, 35) from dual;
        查詢4: select mod(5.2, 3) from dual;

查詢1用6除以2，余數(shù)為0。查詢2用5除以3，商為1，余數(shù)為2。查詢3用7除以35。因?yàn)槌龜?shù)大于被除數(shù)，所以返回?cái)?shù)字7作為模數(shù)。查詢4用小數(shù)作為被除數(shù)。5.2除以3商為1，余數(shù)為2.2。

8.1.6 使用日期

Date函數(shù)是解決與日期相關(guān)問題的簡(jiǎn)單函數(shù)，它不需要記錄閏年或者特殊月份的天數(shù)。在詳細(xì)討論SYSDATE函數(shù)之前，我們先討論Oracle如何保存日期以及默認(rèn)的日期格式掩碼。隨后討論日期運(yùn)算和日期操作函數(shù)：ADD_MONTHS、MONTHS_BETWEEN、LAST_DAY、NEXT_DAY、ROUND和TRUNC。

1．?dāng)?shù)據(jù)庫(kù)中的日期存儲(chǔ)

數(shù)據(jù)庫(kù)在內(nèi)部以數(shù)字格式存儲(chǔ)日期，這種格式支持存儲(chǔ)世紀(jì)、年、月和日，以及時(shí)間信息，例如時(shí)、分和秒。從表中訪問日期信息時(shí)，結(jié)果的默認(rèn)格式由表示日的兩個(gè)數(shù)字、表示月的三個(gè)字母縮寫和表示年的兩個(gè)數(shù)字組成。為了避免自動(dòng)轉(zhuǎn)換類型的異常，下述查詢中的字面量日期已使用本章后面介紹的TO_DATE轉(zhuǎn)換函數(shù)轉(zhuǎn)換為日期類型。根據(jù)NLS會(huì)話參數(shù)的不同，結(jié)果可能不同。

2．SYSDATE函數(shù)

SYSDATE函數(shù)沒有參數(shù)，它返回?cái)?shù)據(jù)庫(kù)服務(wù)器當(dāng)前的系統(tǒng)日期和時(shí)間。如果數(shù)據(jù)庫(kù)服務(wù)器安裝時(shí)，使用AMERICAN作為默認(rèn)語(yǔ)言，SYSDATE函數(shù)就返回當(dāng)前系統(tǒng)日期的DD-MON-RR組成部分。如果數(shù)據(jù)庫(kù)服務(wù)器所處的時(shí)區(qū)與查詢數(shù)據(jù)庫(kù)的客戶機(jī)所在時(shí)區(qū)不同，那么SYSDATE返回的日期和時(shí)間就會(huì)與客戶機(jī)上的本地操作系統(tǒng)時(shí)鐘不同。檢索數(shù)據(jù)庫(kù)服務(wù)器日期的查詢?nèi)缦滤荆?/p>

        select sysdate from dual;

3．日期運(yùn)算

下面的等式說(shuō)明了關(guān)于日期運(yùn)算的重要原則：

Date1 - Date2 = Num1.

可以從另一個(gè)日期中減去日期。這兩個(gè)日期項(xiàng)之間的差值表示它們之間的天數(shù)。可以將所有數(shù)字(包括小數(shù))添加到日期項(xiàng)或者從日期項(xiàng)中減去。在該上下文中，數(shù)字表示天數(shù)。數(shù)字和日期項(xiàng)之間的和或者差值總是返回日期項(xiàng)。這條原則說(shuō)明不允許相加、相乘或者相除兩個(gè)日期項(xiàng)。

4．MONTHS_BETWEEN函數(shù)

MONTHS_BETWEEN函數(shù)返回表示兩個(gè)強(qiáng)制的日期參數(shù)之間月數(shù)的數(shù)值。其語(yǔ)法是MONTHS_ BETWEEN(date1, date2)。該函數(shù)計(jì)算date1和date2之間月份的差值(每月31天)。如果date1在date2之前，就返回負(fù)數(shù)。這兩個(gè)日期參數(shù)之間的差值可能由整數(shù)和小數(shù)部分組成。整數(shù)表示這兩個(gè)日期之間的月數(shù)。小數(shù)部分表示計(jì)算年和月之間整數(shù)差值之后剩余的天數(shù)和時(shí)間，以31天的月份為基礎(chǔ)。如果要比較的日期的日組成部分相同或者是各自月份的最后一天，那么就返回沒有小數(shù)部分的整數(shù)。

下面的查詢說(shuō)明了MONTHS_BETWEEN函數(shù)的用法：

        select months_between(sysdate, sysdate-31) from dual;

假設(shè)當(dāng)前日期是16-APR-2009。這個(gè)查詢返回1，因?yàn)?6-APR-2009和16-MAY-2009之間是1個(gè)月。

5．ADD_MONTHS函數(shù)

ADD_MONTHS函數(shù)返回日期項(xiàng)，這個(gè)日期項(xiàng)通過將指定月數(shù)添加到給定日期值計(jì)算得出。ADD_MONTHS函數(shù)有兩個(gè)強(qiáng)制參數(shù)。其語(yǔ)法是ADD_MONTHS (start date, number of months)。在將指定的月數(shù)添加到start date之后，函數(shù)才計(jì)算目標(biāo)日期。月數(shù)可能是負(fù)數(shù)，這樣返回的目標(biāo)日期就早于起始日期。number of months可以是小數(shù)，但會(huì)忽略小數(shù)部分，而使用整數(shù)部分。下面三個(gè)查詢說(shuō)明了ADD_MONTHS函數(shù)的用法。

        Query 1: select add_months(to_date('07-APR-2009', 'DD-MON-YYYY'), 1) from dual;
        Query 2: select add_months(to_date('31-DEC-2008', 'DD-MON-YYYY'), 2.5) from dual;
        Query 3: select add_months(to_date('07-APR-2009', 'DD-MON-YYYY'), -12) from dual;

第一個(gè)查詢返回07-MAY-2009，其中日組成部分保持不變而月份遞增1。第二個(gè)查詢有兩個(gè)有趣的方面。用來(lái)指定要添加的月數(shù)的參數(shù)包含小數(shù)部分，但小數(shù)部分會(huì)被忽略。給日期31-DEC-2008添加2個(gè)月，應(yīng)該返回日期31- FEB-2009，但這個(gè)日期不存在，因此返回該月的最后一天28-FEB-2009。第三個(gè)查詢中添加的月數(shù)是-12，所以返回07-APR-2008，這個(gè)日期比起始日期早12個(gè)月。

6．NEXT_DAY函數(shù)

NEXT_DAY函數(shù)返回的日期是星期內(nèi)指定的日子下一次出現(xiàn)時(shí)的日期。NEXT_DAY函數(shù)有兩個(gè)強(qiáng)制參數(shù)。其語(yǔ)法是NEXT_DAY (start date, day of the week)。函數(shù)計(jì)算在start date之后 day of the week 參數(shù)下一次出現(xiàn)的日期。day of the week 參數(shù)必須是由NLS_DATE_LANGUAGE會(huì)話參數(shù)確定的有效字符值。對(duì)于NLS_DATE_LANGUAGE=AMERICAN，默認(rèn)值至少是日子名稱的前三個(gè)字符，表示星期幾的字符值可以指定為任何大小寫形式。簡(jiǎn)短名稱可以大于三個(gè)字符，例如，星期日可以表示為sun、sund、sunda或者Sunday。考慮下列查詢：

        查詢1: select next_day(to_date('01-JAN-2009', 'DD-MON-YYYY'), 'tue')from dual;
        查詢2: select next_day(to_date('01-JAN-2009', 'DD-MON-YYYY'), 'WEDNE') from dual;

01-JAN-2009是星期四。因此下一次出現(xiàn)星期二的時(shí)間是五天之后(即06-JAN-2009)。第二個(gè)查詢指定字符字面值“WEDNE”，它表示星期三。01-JAN-2009之后的下一個(gè)星期三是07-JAN-2009。

7．LAST_DAY函數(shù)

LAST_DAY函數(shù)返回指定日子所屬的月的最后一天的日期。LAST_DAY函數(shù)有一個(gè)強(qiáng)制參數(shù)。其語(yǔ)法是LAST_ DAY(start date)。該函數(shù)提取start date參數(shù)所屬的月，并計(jì)算該月最后一天的日期。下面的查詢返回日期31-JAN-2009。

        select last_day(to_date('01-JAN-2009', 'DD-MON-YYYY')) from dual;

8．日期ROUND函數(shù)

日期ROUND函數(shù)依據(jù)指定的日期精度格式對(duì)值進(jìn)行舍入運(yùn)算。返回的值要么向上舍入，要么向下舍入為最接近的日期精度格式。日期ROUND函數(shù)使用一個(gè)強(qiáng)制參數(shù)和一個(gè)可選參數(shù)。其語(yǔ)法是ROUND(source date, [date precision format])。source date參數(shù)表示任意日期項(xiàng)。date precision format參數(shù)指定舍入的精度，它是可選的。如果沒有指定它，默認(rèn)的舍入精度是日。date precision formats包括世紀(jì)(CC)、年(YYYY)、季度(Q)、月(MM)、星期(W)、日(DD)、時(shí)(HH)和分(MI)。

向上舍入到世紀(jì)相當(dāng)于給當(dāng)前世紀(jì)加1個(gè)世紀(jì)。如果日部分大于16，就會(huì)向上舍入到下一個(gè)月，否則就會(huì)向下舍入到當(dāng)月的開頭。如果月在1～6之間，那么舍入到年就會(huì)返回當(dāng)年開頭的日期，否則返回下一年開頭的日期。考慮下列查詢及其結(jié)果：

        SQL> select round(sysdate) day, round(sysdate, 'w') week,
          2        round(sysdate, 'month') month, round(sysdate, 'year') year
          3  from dual;

       DAY        WEEK       MONTH      YEAR
        ---------  ---------  ---------  ---------
        17-APR-09  15-APR-09  01-MAY-09  01-JAN-09

假定這個(gè)查詢?cè)?7-APR-2009的00:05運(yùn)行。第一項(xiàng)將日期舍入到最接近的日子。因?yàn)闀r(shí)間是00:05，它在12:00之后，因此不將日期上舍入。第二項(xiàng)將日期舍入到與當(dāng)月的第一天是同一周天的日期。由于01-APR-2009是星期三，返回的日期是該日期所處周的星期三。記住，默認(rèn)情況下，一周的第一天是星期日。因此從12-APR-2009開始的一周的第一個(gè)星期三是15-APR-2009。第三項(xiàng)將日期舍入為下個(gè)月的開頭，因?yàn)槿詹糠质?7，它返回01-MAY-2009。第四項(xiàng)向上舍入為當(dāng)年開頭的日期，因?yàn)樵虏糠质?，它返回01-JAN-2009。

9．日期TRUNC函數(shù)

日期TRUNC函數(shù)依據(jù)指定的日期精度格式對(duì)值進(jìn)行截取運(yùn)算。

日期TRUNC函數(shù)使用一個(gè)強(qiáng)制參數(shù)和一個(gè)可選參數(shù)。其語(yǔ)法是TRUNC(source date, [date precision format])。source date參數(shù)表示任意日期項(xiàng)。date precision format參數(shù)指定截取的精度，它是可選的。如果沒有指定它，默認(rèn)的截取精度是日。這就意味著將source date的所有時(shí)間部分都設(shè)置為午夜，即00:00:00 (00時(shí)、00分和00秒)。在月級(jí)別上的截取將source date 的日期設(shè)置為該月的第一天。年級(jí)別上的截取返回當(dāng)年開頭的日期。下列查詢顯示了SELECT列表中的四項(xiàng)，每一項(xiàng)都將日期字面值截取到不同的精度：

        SQL> select trunc(sysdate) day, trunc(sysdate, 'w') week,
          2        trunc(sysdate, 'month') month, trunc(sysdate, 'year') year
          3  from dual;

       DAY        WEEK       MONTH      YEAR
        ---------  ---------  ---------  ---------
        17-APR-09  15-APR-09  01-APR-09  01-JAN-09

假定這個(gè)查詢?cè)?7-APR-2009的12:05AM運(yùn)行。第一項(xiàng)將時(shí)間部分00:05設(shè)置為00:00，返回當(dāng)天。第二項(xiàng)將日期截取為與當(dāng)月的第一天是同一周天星期三的日期，它返回15-APR-2009。第三項(xiàng)將日期截取為當(dāng)月的開頭，返回01-APR-2009。第四項(xiàng)將日期截取為當(dāng)年的開頭，它返回01-JAN-2009。

練習(xí)8-2 使用字符操作函數(shù)

連接到OE模式，構(gòu)造一個(gè)查詢，從CUSTOMERS.EMAIL列中提取唯一的電子郵件主機(jī)名。

(1) 啟動(dòng)SQL Developer并連接到OE模式。

(2) CUSTOMERS.CUST_EMAIL項(xiàng)的格式通常如下所示：firstname.lastname@hostname. EXAMPLE.COM。主機(jī)名位于@符號(hào)與EXAMPLE.com字符之間。SUBSTR函數(shù)可用于提取該值。不過，開始位置和長(zhǎng)度仍是未知的。INSTR函數(shù)可用于定位@符號(hào)和字符EXAMPLE.com第一次出現(xiàn)的位置。

(3) 可能的解決方案如下所示：

        select distinct substr(cust_email, instr(cust_email, '@')+1,
        instr(cust_email, '.EXAMPLE.COM')-instr(cust_email, '@')-1)
        hostname from customers;

8.2 描述SQL中可用的各種類型的轉(zhuǎn)換函數(shù)

SQL轉(zhuǎn)換函數(shù)是單行函數(shù)，它們可用來(lái)改變列值、表達(dá)式或者字面值數(shù)據(jù)類型的本質(zhì)。最廣泛使用的轉(zhuǎn)換函數(shù)是TO_CHAR、TO_NUMBER和TO_DATE這三個(gè)函數(shù)。TO_CHAR函數(shù)將數(shù)字和日期信息轉(zhuǎn)換為字符，而TO_NUMBER和TO_DATE函數(shù)將字符數(shù)據(jù)分別轉(zhuǎn)換為數(shù)字和日期。

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

Oracle允許將列定義為ANSI、DB2和SQL/DS數(shù)據(jù)類型。在內(nèi)部這些類型被轉(zhuǎn)換為Oracle數(shù)據(jù)類型。每一列都有一個(gè)相關(guān)數(shù)據(jù)類型，這種數(shù)據(jù)類型約束列能夠存儲(chǔ)的數(shù)據(jù)的性質(zhì)。NUMBER列不能存儲(chǔ)字符信息。DATE列不能存儲(chǔ)隨機(jī)字符或者隨機(jī)數(shù)。然而，在VARCHAR2字段中可以存儲(chǔ)數(shù)字和日期信息的字符形式。

如果接受字符輸入?yún)?shù)的函數(shù)使用數(shù)字，Oracle就會(huì)自動(dòng)將它轉(zhuǎn)換為字符形式。如果接受數(shù)字或者日期參數(shù)的函數(shù)使用字符值，就有一些特殊條件，在這些條件下才會(huì)發(fā)生自動(dòng)數(shù)據(jù)類型轉(zhuǎn)換。雖然可以使用隱式數(shù)據(jù)類型轉(zhuǎn)換，但更可靠的方法是使用單行轉(zhuǎn)換函數(shù)將這些值從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型。

1．隱式數(shù)據(jù)類型轉(zhuǎn)換

如果可能，可以將數(shù)據(jù)類型與函數(shù)所需參數(shù)的數(shù)據(jù)類型不相符的值隱式轉(zhuǎn)換為所需的格式。VARCHAR2和CHAR數(shù)據(jù)類型統(tǒng)稱為字符類型。字符字段非常靈活，幾乎允許存儲(chǔ)所有類型的信息。因此，可以方便地將DATE和NUMBER值轉(zhuǎn)換為它們的字符形式。這些轉(zhuǎn)換稱為數(shù)字到字符(number to character)和日期到字符(date to character)轉(zhuǎn)換。例如下面的查詢：

        查詢1: select length(1234567890) from dual;
        查詢2: select length(SYSDATE) from dual;

這兩個(gè)查詢都使用LENGTH函數(shù)，該函數(shù)使用字符串參數(shù)。在LENGTH函數(shù)計(jì)算之前，查詢1中的數(shù)1234567890被隱式轉(zhuǎn)換為字符串’1234567890'。這個(gè)查詢返回?cái)?shù)字10。查詢2先計(jì)算SYSDATE函數(shù)，假設(shè)它為07-APR-38。這個(gè)日期被隱式轉(zhuǎn)換為字符串’07-APR-38', LENGTH函數(shù)返回?cái)?shù)字9。

將字符數(shù)據(jù)隱式轉(zhuǎn)換為數(shù)字?jǐn)?shù)據(jù)類型的情況并不常見，因?yàn)槌霈F(xiàn)這種情況的唯一條件是該字符數(shù)據(jù)表示有效數(shù)字。字符串’11’會(huì)被隱式轉(zhuǎn)換為數(shù)字，但’11.123.456’則不會(huì)，如下面的查詢所示：

        查詢3: select mod('11', 2) from dual;
        查詢4: select mod('11.123', 2) from dual;
        查詢5: select mod('11.123.456', 2) from dual;
        查詢6: select mod('$11', 2) from dual;

在MOD函數(shù)計(jì)算并返回結(jié)果1和1.123之前，查詢3和查詢4分別將字符串’11’和’11.123'隱式轉(zhuǎn)換為數(shù)字11和11.123。當(dāng)Oracle試圖實(shí)現(xiàn)隱式的字符到數(shù)字(character to number)轉(zhuǎn)換時(shí)，查詢5返回錯(cuò)誤“ORA-1722:invalid number”。這種轉(zhuǎn)換會(huì)失敗是因?yàn)樽址?1.123.456'不是有效數(shù)字。查詢6也會(huì)因?yàn)闊o(wú)效數(shù)字錯(cuò)誤而失敗，因?yàn)槊涝?hào)不能被隱式轉(zhuǎn)換為數(shù)字。

當(dāng)字符串符合下面的日期格式時(shí)，可以實(shí)現(xiàn)隱式字符到日期(character to date)的轉(zhuǎn)換：[D|DD]separator1[MON|MONTH]separator2[R|RR|YY|YYYY]。D和DD分別表示月份中1位和2位的日子。MON是月的三字符縮寫詞，而MONTH是月的全名。R和RR分別表示1位和2位數(shù)字的年。YY和YYYY分別表示2位和4位數(shù)字的年。separator1和separator2元素可以是大多數(shù)標(biāo)點(diǎn)符號(hào)、空格和制表符。表8-2說(shuō)明了字符到日期的隱式轉(zhuǎn)換，它列出了一些函數(shù)調(diào)用以及SQL Developer返回的結(jié)果。這里的結(jié)果假定系統(tǒng)使用American會(huì)話默認(rèn)值。

2．顯式數(shù)據(jù)類型轉(zhuǎn)換

許多函數(shù)可以將值從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型，這些函數(shù)稱為顯式數(shù)據(jù)類型轉(zhuǎn)換函數(shù)。這些函數(shù)返回的值保證是所需的類型，它們?yōu)檗D(zhuǎn)換數(shù)據(jù)項(xiàng)提供了一種安全可靠的方法。

使用TO_CHAR函數(shù)，可以將NUMBER和DATE項(xiàng)顯式轉(zhuǎn)換為字符項(xiàng)。使用TO_NUMBER函數(shù)，可以將字符串顯式轉(zhuǎn)換為NUMBER項(xiàng)。TO_DATE函數(shù)用來(lái)將字符串顯式轉(zhuǎn)換為DATE項(xiàng)。Oracle的格式掩碼能夠廣泛控制字符到數(shù)字和字符到日期的轉(zhuǎn)換。