Oracle Sql函数

函数是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" -->标点和引用的字符会在日期中原样输出
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