第三章單行函數

雖然各個資料庫有各個資料庫自己的開發架構，但是對於資料庫而言，對開發者就兩塊重要的內容：SQL語句+單行函數

所有的單行函數，嚴格來講，程序都可以處理。

所謂的單行函數指的就是完成某一具體功能的操作函數，例如：轉大寫、日期格式轉換

基本格式：「返回值函數名稱（參數）」

按類型一共分為以下5種函數

第一節字元串函數

主要是處理字元串數據的（字元串數據有可能是從列上找到的，也有可能是直接設置的字元串常量）

包含的函數有如下幾種

函數名

描述

字元串UPPER（列|字元串）

將傳入的字元串變為大寫字母形式

字元串LOWER（列|字元串）

將傳入的字元串變為小寫字母形式

字元串INITCAP（列|字元串）

開頭首字母大寫，其他全部變為小寫字母

數字LENGTH(列|字元串)

取得指定字元串長度

字元串SUBSTR（列字元串，開始索引[長度]）

進行字元串的截取，如果沒有設置長度，默認為開始索引，一直截取到結尾

字元串REPLACE（列|字元串，舊內容，新內容）

將指定字元串的數據以新數據替換舊數據

（啟動監聽服務）

在Oracle中，所有的函數如果想要進行驗證，也必須編寫SQL語句，為了方便用戶進行驗證或是一些不需要查詢表（查詢對象），專門提供了一個dual的虛擬表

實例1.驗證基本操作：轉大寫

SELECT UPPER(『hello』)

FROM dual；

實例2.將所以僱員的姓名轉小寫

SELECT LOWER(ename)

FROM emp；

在oracle中，本身數據是區分大小寫，但在實際開發中會發現，許多代碼本身是不區分大小寫的，所以用戶在進行信息查詢是，往往不會考慮大小寫

實例3.由用戶輸入要查詢的僱員姓名，而後顯示僱員的完整信息

在oracle中，如果要實現數據的輸入操作，可以使用替代變數來完成，格式「&標記」。

SELECT *

FROM emp

WHERE ename=UPPER（『&inputname』）

實例4.將所有僱員姓名按照首字母大寫，其他字母小寫的方式保存

SELECT ename，INITCAP（ename）

FROM emp；

實例5：取得字元串長度

SELECT LENGTH（『helloworld！！』）FROM dual；

實例6.查詢僱員姓名長度為5的全部僱員信息

SELECT*

FROM emp

WHERE LENGTH（ename）=5；

字元串截取：

從指定位置截取到結尾，字元串SUBSTR（列|字元串，開始索引）；

截取部分內容，，字元串SUBSTR（列|字元串，開始索引，長度）；

實例7.驗證函數

SELECT SUBSTR(『helloworld』,6)FROM dual；

SELECT SUBSTR(『helloworld』,0)FROM dual；

SELECT SUBSTR(『helloworld』,0，5)FROM dual；

在程序中，所有的字元串的首字母的索引號都是0，但是在Oracle中所有的字元串的首字母的索引號都是1；如果設置的是0，也可以按照1來進行處理。

實例8.要求截取所有僱員姓名的前三個字母

SELECT SUBSTR(ename,1，3)

FROM emp；

實例9.要求截取所有僱員姓名的後三個字母

SELECT SUBSTR(ename,LENGTH（ename）-2)

FROM emp；

在Oracle中，有一點注意：在設置SUBSTR()函數時考慮到了由後截取的情況，可以直接用負數表示後面第幾位字元。此類設置方法只有在oracle資料庫才有，在程序中不能這樣設置。

SELECT SUBSTR(ename,-3)

FROM emp；

第二節數值函數

數值函數主要是對數字的處理

函數名稱

數字ROUND(列|數字[，小數位])

實現數據的四捨五入，可以設置保留小數位

數字TRUNC(列|數字[，小數位])

實現數據的截取，即：只截取，不算近似值

數字MOD(數字，列|數字)

求余

實例1.使用ROUND（）函數

SELECT ROUND(789.12345)FROM dual；

SELECT ROUND(789.12345，2)FROM dual；

SELECT ROUND(789.12345，-2)FROM dual；

如果沒有設置小數點位置，那麼會直接不保留小數位進行進位；

如果設置為負數，那麼表示進行整數位的倒數第幾位開始，進行四捨五入。

實例2.驗證TRUNC()

TRUNC()函數和ROUND（）函數使用形式上差別不大，唯一的區別在於TRUNC（）只截取，不進位；

SELECT TRUNC(789.12345)

TRUNC(789.12345，2)

TRUNC(789.12345，-2)

FROM dual；

如果沒有設置小數點位置，那麼會直接不保留；

實例.求余函數

SELECT MOD(10,3)FROM dual；

第三節日期函數

如果想處理任何日期，都必須具備一個前提：知道當前的日期；

如果想要取得當前日期，則在Oracle中，專門提供了一個偽列「SYSDATE」(SYSTIMESTAMP)；

偽列：非真實列，但存在；

實例1.驗證偽列

SELECT SYSDATE FROM dual；

SELECT SYSTIMESTAMP FROM dual；

SYSDATE偽列里包含了日期時間的內容，只不過現在只顯示了日期數據；除了要知道當前日期，還需要知道三個日期的操作公式：

日期+數字=日期（表示若干天之後的日期）

日期-數字=日期（表示若干天之前的日期）

日期-日期=數字（兩個日期間隔天數）

實例2.實現日期的基本操作

SELECT SYSDATE-7,SYSDATE+240 FROM dual；

對於日期而言，每個月的天數是不同的，所以直接進行天數加法實現月數計算是不準確的。

實例3.要求查詢出每個僱員的編號、姓名、職位、已經被僱傭的天數

SELECT empno，ename，job，SYSDATE-hiredate

FROM emp；

如何用天數轉換成年或月呢？在Oracle中，提供了四個日期處理函數

函數名稱

日期ADD_MONTHS(列|日期，月數)

在指定日期之上增加若干個月後的日期

數字MONTHS_BETWEEN(列|日期，列|日期)

返回兩個日期之間的月數

日期LAST_DAY(列|日期)

取得指定日期當月的最後一天

日期NEXT_DAY(列|日期，星期X)

返回指定日期，對應的下一個指定的星期幾的具體日期

實例4.在當前日期下增加指定月份

SELECT ADD_MONTHS(SYSDATE,4) FROM dual；

在進行月數增加時，是不會限制數據的大小的。

實例5.計算所以僱員到今天位置僱傭的月數

SELECT empno,ename,hiredate,TRUNC(MONTH_BETWEEN(hiredate，SYSDATE))

FROM emp;

實例6.計算當前所在月的最後一天日期

SELECT LAST_DAY(SYSDATE)FROM dual；

實例7.要求查詢出所有僱傭所在月倒數第三天僱傭的僱員信息

SELECT *

FROM emp

WHERE hiredate=（LAST_DAY(hire date)-2）；

一般而言，日期函數操作比較麻煩，但是對於計算日期是最準確的

實例8.驗證NEXT_DAY()

SELECT NEXT_DAY(SYSDATE,』星期二』)FROM dual;

實例9.要求以年、月、日的方式計算出每個僱員到現在為止僱傭年限（以僱員7698，BLACK為例）

第一步：求出每一位僱員到現在為止僱傭的年份。在Oracle中，有兩種方式計算年份：

方式一：（日期-日期）/365

方式二：MONTHS_BETWEEN(SYSDATE,hiredate)/12

第二種方式更好，因為第一種方式沒有排除閏年

第二步，求出僱傭月數

第三步，求出僱傭天數：日期1-日期2=天數；日期1：SYSDATE;日期2：ADD_MONTHS(hire date，MONTH_BETWEEN(SYSDATE,hiredate))

SELECT empno，ename，hiredate，TRUNC( MONTH_BETWEEN(SYSDATE,hiredate)/12) year

TRUNC(MOD( MONTH_BETWEEN(SYSDATE,hiredate),12) )months

TRUNC(SYSDATE-ADD_MONTHS(hire date，MONTH_BETWEEN(SYSDATE,hiredate)) )days

FROM emp；

第四節轉換函數

目前為止，已經接觸了數字型、字元串型、日期型三類數據，而這三類數據之間是可以實現轉換操作的，需要使用如下轉換函數完成

函數名稱

字元串TO_CHAR(列|日期|數字，轉換格式)

將日期、數字轉換成指定格式

日期 TO_DATE(列|字元串，轉換格式)

按照指定格式編寫字元串後，轉換為日期格式

數字 TO_NUMBER(列|字元串)

將字元串轉換為數字

1. 轉字元串函數：TO_CHAR

如果想要將一個日期或數字變成字元串，那麼首先必須清楚轉換格式的標記：

日期：年（yyyy）、月（mm）、日（dd）

時間：時（hh、hh24）、分（mi）、秒（ss）

數字：任意數字（9），本地貨幣符號（L）；

實例1：將日期顯示格式化

SELECT TO_CHAR(SYSDATE,』yyyy-mm-dd』)FROM dual；

在之前一直強調SYSDATE包含日期和時間兩類內容

實例2.格式化日期和時間

SELECT TO_CHAR(SYSDATE,』yyyy-mm-dd hh24：mi：ss』)FROM dual；

注意：使用TO_CHAR函數可以實現日期數據的拆分

實例3.實現日期數據的拆分

SELECT TO_CHAR(SYSDATE,』yyyy』), TO_CHAR(SYSDATE,』mm』), TO_CHAR(SYSDATE,』dd』)

FROM dual；

實例4.找出2月份僱傭的僱員信息

SELECT *

FROM emp

WHERE TO_CHAR(SYSDATE,』mm』)=02；

除了可以轉換日期，還可以轉換數字

SELECT TO_CHAR(1987654320,』999，L999，999，999，999』)FROM dual；

2. 轉日期函數

在之前發現如果要想編寫一個日期型數據，那麼需要按照指定的字元串格式編寫，這種操作屬於Oracle的自動轉換，我們還可以手工轉換

實例5.將字元串變為日期

SELECT TO_DATE(『1988-10-29』,』yyyy-mm-dd』)FROM dual；

3. 轉數字函數

SELECT TO_NUMBER(『1』)+TO_NUMBER(『2』)FROM dual;

因為Oracle自帶很多轉換功能，所以，我們掌握第一個轉字元串函數就可以了。

第五節通用函數

一般指的是Oracle的特色函數，主要有兩個通用函數

函數名稱

數字NVL（列|NULL，默認值）

如果傳入內容是NULL，則使用默認值處理，反之，則使用原始數值處理

數據類型DECODE（列|字元串|數值，比較內容1，顯示內容1，比較內容2，顯示內容2， ……，默認顯示內容）

設置的內容會與每一個比較內容進行比較，如果內容相同，則會使用顯示內容進行輸出，如果都不相同，則使用默認信息輸出

1. 處理NULL函數

實例1.要求查詢出每個僱員的編號、姓名、基本工資、傭金、年薪；

SELECT empno，ename，sal，comm，（sal+comm）*12

FROM emp;

發現運行結果顯示，沒有傭金的僱員的年薪也為NULL；因為有NULL的運算，結果都為空，此時，為了保證計算成功，需要把NULL替換成0，此操作就是NVL（）函數的作用範疇。

SELECT empno，ename，sal，comm，（sal+NVL（comm，0））*12

FROM emp;

2. DECODE函數

類似於程序的if……else，但此處不能判斷關係，只能判斷內容是否相同。

實例2.將所有的職位信息替換為中文顯示

SELECT empno，ename，job，DECODE（job，『clerk』，『辦事員』，『salesman』，『銷售員』，『manager』，『經理』，『總裁』）

FROM emp;

如果沒有設置默認值，則默認值為NULL