ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle中的单行函数

oracle中的单行函数

原创 Linux操作系统 作者:tengrid 时间:2009-05-18 19:12:43 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94384/viewspace-600292/,如需转载,请注明出处,否则将追究法律责任。

上一篇: nvl, nvl2, nullif
请登录后发表评论 登录
全部评论

注册时间:2009-05-18

  • 博文量
    136
  • 访问量
    381159