第四章單列函數

完成本章節後您將：

• 在 SQL 子句中不同資料型態所使用的函數
• 利用數值、字串和日期的函數使用於 SELECT
• 透過轉換函數轉變資料格式

一、SQL Functions

◎可輸入一或多個值經由函數運算輸出一個結果值

• 資料可以經過計算或比對
• 只對項目中的個體作修改
• 格式化數值與日期的顯示
• 轉換欄位型態

二、兩種 SQL Functions

◎除多對一之外亦能運用一對一單列函數運算

單列函數型態：

• Character(字串)
• Number(數值)
• Date(日期)
• Conversion(轉換)
• Case(ANSI 1999)

在這裡我以範例來解說以下的函數會比較容易理解

●Character Function：

範例、利用函數 LOWER、UPPER、INITCAP 轉換大小寫與字首大寫：

SELECT UPPER(last_name)||' 的工作是： '||INITCAP(job_id)||'('||LOWER(job_id) || ')' AS "員工執掌" FROM employees;

結果：

員工執掌
ZLOTKEY 的工作是： Sa_Man(sa_man)
TUCKER 的工作是： Sa_Rep(sa_rep)
BERNSTEIN 的工作是： Sa_Rep(sa_rep)
HALL 的工作是： Sa_Rep(sa_rep)

說明：

使用UPPER將 SELECT 出來的 last_name 英文名字轉為大寫
使用INITCAP將 SELECT 出來的 job_id 轉為字首大寫
使用LOWER將 SELECT 出來的 job_id 轉為小寫

　　如果資料為中文的話當然也就不能作為以上三個函數轉換，你應該沒有看過中文有大小寫吧?!

　　除此之外當你有在 SELECT 句子中添加 WHERE 子句時在 ORACLE 資料庫是有區分大小寫的，此時你就可以利用以上函數做轉換，將帶入的變數轉為與資料符合的格式。

●利用下表函數處理字串：

CONCAT→字串合併(與 || 符號功能相同)。
SUBSTR→字串擷取，1, 5參數意義為，擷取第一個字到第五個字。
LENGTH→計算字串長度。
INSTR→計算參數 'W' 位於字串中第幾位。
LPAD→字串或數值長度不足指定長度時，左邊補足設定字元至指定   　　　
　　　　長度。
RPAD→字串或數值長度不足指定長度時，右邊補足設定字元至指定
              長度。
TRIM→去除指定字串。

範例：
SELECT employee_id AS 員工編號, CONCAT(first_name,  last_name) AS 姓名, job_id AS 工作項目, LENGTH(last_name) AS 姓名長度, INSTR(last_name, 'a') AS "包含字串 'a'?" FROM employees WHERE SUBSTR(job_id, 4) = 'REP'; 

員工編號	姓名	工作項目	姓名長度	包含字串 'a'?
150	PeterTucker	SA_REP	6	0
151	DavidBernstein	SA_REP	9	0
152	PeterHall	SA_REP	4	2
153	ChristopherOlsen	SA_REP	5	0

●數值函數：

○ROUND→保留幾位小數點並對下一位做四捨五入。

SELECT ROUND(45.926, 2) FROM DUAL;

ROUND(45.926,2)
45.93

○TRUNC→保留幾位小數點且不四捨五入。

SELECT TRUNC(45.92, 2) FROM DUAL;

TRUNC(45.92,2)
45.92

○MOD→除法求餘數。

SELECT MOD(1600, 300) FROM DUAL;

MOD(1600,300)
100

•

●日期函數

○Oracle 資料庫儲存日期格式為數值屬性：1百年(世紀)、
   年、月、日、時、分、秒。

○預設日期顯示格式為：DD-MON-RR

SELECT last_name AS 姓名, hire_date AS 到職日 FROM employees WHERE  last_name like 'G%';

SYSDATE是一個Oracle內建函數，它會回傳：

○日期(預設只有年月日)

○時間(要顯示時間必需設定 Session 日期時間格式)