ORACLE中的單行函式

OCP-007-Chapter3 單行函式中有提到oracle中的單行函式
目的
完成這一章，可以作下列事情：
描述SQL中可用的不同函式型別
在select語句中使用字元、數字和日期函式
描述轉換函式的應用

函式是SQL非常有用的特性，可以實現下列功能：
執行資料的計算
編輯單獨的資料項
操作多行的輸出（Manipulate output for groups of rows）
格式化日期和數字的顯示
轉換資料型別

SQL函式有時候需要引數，但是總會返回一個值；過程主要是完成一個事件
單行函式：運算元據項；
                接受引數並返回一個值；
                作用於每一個返回的行；
                每行返回一個結果；
                可能會改變資料型別；
                可以巢狀；
               可以使用使用者定義的常量、變數值、列名或者表示式作為引數

單行函式分為字元函式、數字函式、日期函式、轉換函式和一般函式，可以在select, where, order by子句中使用

一般函式：
  NVL，NVL2，NULLIF，COALSECE，CASE，DECODE

字元函式
   接受字元資料，返回字元或者數字。
   字元函式分為大小寫轉換函式（Lower, Upper, Initcap首字目大寫)和字串操作函式（concat, substr, length, instr, lpad/rpad, trim, replace）。

SUBSTR(column|expression,m[,n])->返回從m開始的n個字元；若m為負值，則從末尾開始數m
INSTR(column|expression,'string', [,m], [n] )->從m位置開始尋找第n個string字元（串）（預設m,n為1）
LPAD/RPAD(column|expression, n,'string')->擴充套件到n個字元寬，不足部分使用string字元新增
TRIM(leading|trailing|both, trim_character FROM trim_source)->刪除前導字串，如果後面的  trim_character和trim_source都是literal字串，一定要使用單引號包圍起來
REPLACE(text,search_string,replacement_string)->替換

CONCAT('Hello', 'World') -> HelloWorld
SUBSTR('HelloWorld',1,5) -> Hello
LENGTH('HelloWorld') -> 10
INSTR('HelloWorld', 'W') ->6
LPAD(salary,10,'*') -> *****24000
RPAD(salary, 10, '*') -> 24000*****
TRIM('H' FROM 'HelloWorld') -> elloWorld

數字函式：
     Round圓整，Trunc截斷，Mod取模 ->其中Round, Trunc也可以用於日期函式
ROUND(column|expression, n)->四捨五入到n為小數，如果n為負值，則從小數點向左邊數
round(345.23,-3)=0; round(345.23,-2)=300; round(345.23,-1)=350; round(345.23)=345
TRUNC(column|expression,n)->截斷到n為小數，如果n為負值，則從小數點向左邊數
trunc(345.23,-3)＝0; trunc(345.23,-2)=300; trunc(345.23,-1)=340; trunc(345.23)=345
測試函式或者計算結果時可以使用dual這個虛假的表

SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM DUAL;
Oracle資料庫內部以下列方式儲存日期：
世紀，年，月，日，小時，分，秒，預設的日期格式為DD-MON-RR
當插入日期函式時，世紀值自動從sysdate函式（返回當前資料庫伺服器的日期和時間）取得
允許的日期和數字的操作：
date + number, date - number, date - date, date + number/24

日期函式：
MONTHS_BETWEEN(date1, date2)->兩個日期間的月份，注意月份為中文的情況
ADD_MONTHS(date, n)->新增月份
NEXT_DAY(date, 'char')->找到下一個星期幾（可以為數字或者字串）
LAST_DAY(date)->發現包含date所在月份的最後一天
ROUND(date[,'fmt'])->圓整到格式中所指定的單位，省略的話圓整到最近的天
TRUNC(date[, 'fmt'])->截斷到格式中所指定的單位，省略的話截斷到最近的天

假定SYSDATE = '25-JUL-95':
• ROUND(SYSDATE,'MONTH') -> 01-AUG-95
• ROUND(SYSDATE ,'YEAR') -> 01-JAN-96
• TRUNC(SYSDATE ,'MONTH') -> 01-JUL-95
• TRUNC(SYSDATE ,'YEAR') -> 01-JAN-95

轉換函式
隱式轉換和顯式轉換（data type to data type）
儘管隱式轉換可以使用，推薦使用顯式轉換以使得SQL語句更可靠
隱式轉換：VARCHAR OR VARCHAR2 -> NUMBER; VARCHAR OR VARCHAR2 -> DATE
NUMBER -> VARCHAR2; DATE -> VARCHAR2

顯式轉換：
TO_CHAR(number|date,[ fmt],[nlsparams])
TO_NUMBER(char,[fmt],[nlsparams])
TO_DATE(char,[fmt],[nlsparams])
The nlsparams parameter specifies the following characters, which are returned by number format elements:
· Decimal character
· Group separator
· Local currency symbol
· International currency symbol

TO_CHAR(date, 'format_model')->格式模式是大小寫敏感的，必須使用單引號包圍，一定要使用逗號把格式和日期值間隔開來，使用fm可以把前面的空格或者零壓縮掉
日期格式模式：
CC （世紀），YYYY,YYY,YY,Y(四個數字表示的年份；分別表示使用4位、後3位、後兩位和後一位來表示年份），YEAR（拼寫的年份），Q（季節），MM（兩個數字表示的月份），MONTH（月份全名，九個字元長），MON（月份的三字縮寫），RM（羅馬數字表示的月份），WW（一年的第幾個星期），W（月份的第幾個星期），DY（星期的三字縮寫），DAY（星期全稱，九個字元長），DDD（一年的第幾天），DD（一個月的第幾天），D（一星期的第幾天）
BC，B.C.，AM，A.M.，HH，HH12，HH24，MI，SS，SSSSS（午夜過後的秒數）
HH24:MI:SS AM -> 15:45:32 PM; DD "of" MONTH -> 12 of OCTOBER;
/.,"of the" --&gt標點和引用的字元會在日期中原樣輸出
DDspth -> fourteenth； DDth -> 4th； DDsp -> four

SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY') HIREDATE
FROM employees;

TO_CHAR(number, 'format_model')
9:代表數字 0:強制顯式0 $:顯示$符號 L:顯示本地貨幣符號 .:列印小數點 ,:顯示千位的分隔符
當值大於給定格式的位數時，Orale資料庫會把整個數字顯式為＃
TO_DATE(char[, 'format_model']) ->有個fx限制符，使用後必須精確的匹配，包括前後的空格個數

RR日期格式：
________|_____給定年份_________________
當前年份|____0～49___|____50～99_______
0～49___|___Current__|____Before_______
50～99__|____Next____|____Current______

單行函式可以巢狀任意層，從最內層開始計算
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS
(hire_date, 6), 'FRIDAY'),
'fmDay, Month DDth, YYYY')
"Next 6 Month Review"
FROM employees
ORDER BY hire_date;

一般函式
可以使用任何資料型別，並且可以使用NULL
NVL (expr1, expr2)->expr1為NULL，返回expr2；不為NULL，原樣返回。注意兩者的型別要一致
NVL2 (expr1, expr2, expr3) ->expr1不為NULL，返回expr2；為NULL，返回expr3。expr2和expr3型別不同的話，expr3會轉換為expr2的型別
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1
COALESCE (expr1, expr2, ..., exprn) ->返回第一個不為NULL的表示式，各個表示式型別一致

條件表示式
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END "alias_name" ->此處可以指定一個別名
如果沒有任何一個條件匹配，而且沒有ELSE子句，那麼返回NULL（不能在上面返回的表示式中使用literal NULL）。上面所有的表示式都必須為同一種資料型別

DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
"alias_name" ->此處可以指定一個別名

練習(答案在卷2的288頁)
1. select sysdate "Date" from dual;
2. select employee_id, last_name, salary, round(salary+salary*0.15) "New Salary" from employees;
4. select employee_id, last_name, salary, round(salary+salary*0.15) "New Salary", round(salary+salary*0.15)-salary "Increase" from employees;
5. select initcap(last_name) "Name", length(last_name) "Length" from employees where substr(initcap(last_name),1,1) in('J','A','M') order by last_name; 注意後面字母的單引號！！

SELECT INITCAP(last_name) "Name",
LENGTH(last_name) "Length"
FROM employees
WHERE last_name LIKE 'J%'
OR last_name LIKE 'M%'
OR last_name LIKE 'A%'
ORDER BY last_name;
6. select last_name,round(months_between(sysdate,hire_date)) "MONTHS_WORKED" from employees order by "MONTHS_WORKED"; 注意使用前面的日期減去後面的日期

7. select last_name||' earns '||to_char(salary,'$999,999.99')||' monthly but wants '||to_char(salary*3,'$999,999.99')||'.' "Dream Salaries" from employees; 單引號！！

8. select last_name,LPAD(salary,15,'$') salary from employees;

9. select last_name, hire_date, to_char(next_day(add_months(hire_date,6),'monday'),'fmDay, "The" Ddspth "of" fmMonth, YYYY') review from employees; 如果要在next_day中使用數字，按英國的習慣星期天為1

10. select last_name, hire_date, to_char(hire_date,'DAY') day from employees order by to_char(hire_date-1, 'D'); 注意後面排序的使用

11. select last_name,nvl(to_char(commission_pct),'NO COMMISSION') COMM from employees 注意這裡格式一定要一致

12. select last_name||' '||lpad(' ',salary/1000+1,'*') "EMPLOYEES_AND_THEIR_SALARIES" from employees order by salary desc 此題看了答案才知道，慚愧

13. select job_id,decode (job_id, 'AD_PRES', 'A', 'ST_MAN', 'B', 'IT_PROG', 'C', 'AS_REP', 'D', 'ST_CLERK', 'E','O') G from employees 注意decode要使用小括號，表示式之間使用逗號間隔

14. select job_id,case job_id when 'AD_PRES' then 'A' when 'ST_MAN' then 'B' when 'IT_PROG' then 'C' when 'AS_REP' then 'D' when 'ST_CLERK'then 'E' else 'O' end G from employees