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


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