Oracle 10g数据库基础之基本查询语句-中-函数
—资料参考：张烈 张建中《数据库管理员培训讲义》 函数：
使用函数的目的是为了操作数据
将输入的变量处理，返回一个结果。
变量可以有好多。
传入的变量可以是列的值，也可以是表达式。
函数可以嵌套。
内层函数的结果是外层函数的变量。
单行函数：每一行都有一个返回值，但可以有多个变量。
多行函数：多行有一个返回值。
单行函数的分类：
字符操作函数
数字操作函数
日期操作函数
数据类型转换函数
综合数据类型函数
字符操作函数： 大小写操作函数
Lower,upper,initcap 小写大写首字母大写
字符串操作函数 Concat,length,substr,instr,trim,replace,lpad,rpad 实验12：操作字符串的函数 字符操作函数：
大小写操作函数
Lower,upper,initcap 小写大写首字母大写
字符串操作函数
Concat,length,substr,instr,trim,replace,lpad,rpad
该实验的目的是掌握常用的字符串操作的函数.
字符串的大小写操作
SQL> Select lower(ename),upper(ename),initcap(ename) from emp; SQL>Select lower(ename) 小写字母,upper(ename) 大写字母,initcap(ename) 首字母大写 from emp;
SQL> select lower(' mf TR') 小写字母, upper('mf TR') 大写字母, initcap('mf TR') 首字母大写 from dual;
Dual是虚表，让我们用表的形式来访问函数的值。
字符串操作函数
Concat,length,substr,instr,trim,replace,lpad,rpad
其它字符串操作函数
concat将两个字符连接到一起 select ename,job,concat(ename,job) from emp;
求字符串的长度
下面三句话是求字符串的长度，字符串要单引。
select length('张三') from dual;–按照字 select lengthb('张三') from dual;–按字节
select lengthc('张三') from dual;–unicode的长度
substr截取字符串
substr(字符串,m,n),m是从第几个字符开始，如果为负的意思是从后边的第几个开始。N是数多少个，如果不说就是一直到字符串的结尾
SQL> select ename,substr(ename,1,1) "第一个字母",substr(ename,-2) "最后两个字母"from emp;
求子串在父串中的位置，0表示没有在父串中找到该子串。 SQL> select ename,instr(ename,'A') "A在第几位" from emp;
trim截断字符串和添加字符串的函数 Trim函数是截掉头或者尾连续的字符，一般我们的用途是去掉空格。 SQL> select trim(leading 'a' from 'aaabababaaa') from dual; 截掉连续的前置的a
SQL> select trim(trailing 'a' from 'aaabababaaa') from dual; 截掉连续的后置的a
SQL> select trim(both 'a' from 'aaabababaaa') from dual; 截掉连续的前置和后置的a
SQL> select trim('a' from 'aaabababaaa') from dual; 如果不说明是前置还是后置就是both全截断。
Lpad和rpad字符填充
SQL> select lpad(ename,20,'*') ename,rpad(ename,20,'。') ename from emp;
左填充和右填充，20是总共填充到多少位，*和。是要填充的字符串。
Lpad左填充，rpad右填充，一般的用途是美化输出的结果。
如果位数不足，按照截取后的结果显示，不报错。 select lpad(sal,18,'*')左填充18位星号,rpad(sal,25,'。')右填充25位句号,sal from emp;
select lpad(sal,30,' ')左填充30位空格,rpad(sal,30,' ')右填充30位空格,sal from emp;
REPLACE替换字符
SQL> SELECT REPLACE('JACK and JUE','J','xj_') FROM DUAL;
将字符串中的J全部替换位xj_
实验13：操作数字的函数
该实验的目的是掌握常用的关于数字操作的函数.
ROUND，TRUNC数字操作函数
以小数点位核心，2是小数点后两位，0可以不写，表示取整，-1表示小数点前一位
ROUND是四舍五入：
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;
TRUNC是截断，全部舍弃：
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;
ceil取整，上进位，和trunc全部去掉正好相反 SQL> select ceil(45.001) from dual;
取绝对值 SQL> select abs(-23.00) from dual;
取余数 SQL> select mod(8.88,2) from dual;
实验14：操作日期的函数
该实验的目的是掌握常用的关于日期操作的函数.
系统日期的操作
日期是很特殊的数据类型，用好了可以提高数据库的性能，而使用不当往往是错误的根源，如果你使用字符型数据来存储日期，就放弃日期特有的计算功能。
函数SYSDATE求当前数据库的时间。
SQL> select sysdate from dual;
日期的显示格式和客户端的配置相关。
查看当前的日期显示格式
SQL> select * from nls_session_parameters
where parameter='NLS_DATE_FORMAT';
col value for a20
代表的含义是凡是列的名称是value的，都按照20个宽度来显示，你想取消该列的定义
col value clear，其中col是column的缩写。你想查看帮助help column即可
alter session set NLS_DATE_FORMAT='yyyy/mm/dd:hh24:mi:ss';
重新设定为我们想要的格式。
select sysdate from dual; SYSDATE ——————- 2012/5/16007/05/01:16:32:54
查看系统时间，数据库本身没有时间，它有scn号，和我们的时间不同。
alter session set NLS_DATE_FORMAT='DD-MON-RR';
设定为默认的显示格式
select sysdate from dual;
再次查看，我们发现日期的显示随着客户端的格式变化而变化。
日期的内部存储都是以yyyymmddhh24miss存在数据库中
日期的操作函数 SQL> select round(sysdate-hiredate) days,sysdate,hiredate from emp;
两个日期相减的结果单位为天，往往是带小数点。我们通过函数可以取整。
取两个日期的月间隔
SQL> select months_between(sysdate,hiredate) 两个日期的月间隔,sysdate,hiredate from emp;
六个月过后是哪天?
SQL> select sysdate 今天, add_months(sysdate,6) 六个月后 from dual;
SQL> select next_day(hiredate,5) ,hiredate from emp;
当前的日期算起，下一个星期五是哪一天，这句话你可能运行失败，因为日期和客户端的字符集设置有关系，如果你是英文的客户端，就的用Friday来表达，日期是格式和字符集敏感的。如果你是中文的客户端，就的用‘星期五’来表达。
该日期的月底是哪一天。
日期的进位和截取 select hiredate,round(hiredate,'mm') ,round(hiredate,'month') from emp;
select hiredate,round(hiredate,'yyyy') ,round(hiredate,'year') from emp;
select hiredate,trunc(hiredate,'mm') ,trunc(hiredate,'month') from emp;
select hiredate,trunc(hiredate,'yyyy') ,trunc(hiredate,'year') from emp;
数字的进位和截取是以小数点为中心，我们取小数点前或后的值，而日期的进位和截取是以年，月，日，时，分，秒为中心。
数据类型的隐式转换
字符串可以转化为数字和日期。
数字要合法，日期要格式匹配。
select ename,empno from emp where empno='7900';
数字和日期在赋值的时候也可以转为字符串，但在表达式的时候不可以转换。
select ename,empno from emp where ename='123'; select ename,empno from emp where ename=123; 数据类型的显式转换 To_char,to_date,to_number 日期转化为字符串，请说明字符串的格式。
SQL> select ename,to_char(hiredate,'yyyy/mm/dd') from emp;
FM消除前置的零和空格。
SQL> select ename,to_char(hiredate,'fmyyyy/mm/dd') from emp;
其他格式：year,month,mon,day,dy,am,ddsp,ddspth
格式内加入字符串请双引。
SQL> select to_char(hiredate,'fmyyyy "年" mm "月" dd "日"') from emp;
当前距离零点的秒数.
select sysdate,to_char(sysdate,'sssss') ss from dual;
数据类型的显式转换
数字转为字符串
格式为9,0,$,l,.
col salary for a30
SQL> select ename,to_char(sal,'9999.000') salary from emp;
SQL> select ename,to_char(sal,'$00099999000.00' ) salary from emp;
SQL> select ename,to_char(sal,'l99,999.000') salary from emp;
SQL> select ename,TO_char(sal,'9G999D99') salary from emp;
9是代表有多少宽度，如果不足会显示成######,0代表强制显示0，但不会改变你的结果。G是千分符，D是小数点。
在数据库中16进制的表达是按照字符串来描述的，所以你想将十进制的数转换为十六进制的数使用to_char函数。
SQL> select to_char(321,'xxxxx') from dual;
其中xxxxx的位数要足够，不然报错，你就多写几个，足够大就可以。
数据类型的显式转换 To_number,to_date
如果你想将十六进制的数转换为十进制的数请使用to_number函数。
SQL> select to_number('abc32','xxxxxxxx') from dual;
日期是格式和语言敏感的，切记！
SQL> select TO_NUMBER('100.00', '9G999D99') from dual;
G为千分符，D为小数点
RR和yy日期数据类型
select to_char(sysdate,'yyyy') "当前",
to_char(to_date('98','yy'),'yyyy') "yy98", to_char(to_date('08','yy'),'yyyy') "yy08", to_char(to_date('98','rr'),'yyyy') "rr98", to_char(to_date('08','rr'),'yyyy') "rr08" from dual;
结果为
yy是两位来表示年，世纪永远和说话者的当前世纪相同。
RR比较灵活，它将世纪分为上半世纪和下半世纪。如果你处于上半世纪，描述的是0-49，那么就和当前世纪相同，描述的是50-99就是上世纪。如果你处于下半世纪，描述的是0-49，那么是下个世纪，描述的是50-99就是当前世纪。从而可以看出，RR的设计完全为了1990年到2010之间我们的思维习惯而设计的。当我们时间到2050前后，使用起来就非常的别扭。
实验15：操作数据为null的函数
该实验的目的是掌握常用的关于NULL值操作的函数.
综合数据类型函数 NVL (expr1, expr2)
如果expr1为非空，就返回expr1, 如果expr1为空返回expr2，两个表达式的数据类型一定要相同。
NVL2 (expr1, expr2, expr3)
如果expr1为非空，就返回expr2, 如果expr1为空返回expr3
NULLIF (expr1, expr2)
如果expr1和 expr2相同就返回空，否则返回expr1
COALESCE (expr1, expr2, …, exprn)
返回括号内第一个非空的值。
SQL> select ename,comm,nvl(comm,0) from emp;
有奖金就返回奖金，奖金为空就返回0。
select ename 姓名,sal 工资,comm 奖金,sal+nvl(comm,0) 总工资,nvl2(comm,'工资加奖金','纯工资') "收入类别" from emp;
有奖金就返回‘工资加奖金‘，奖金为空就返回‘纯工资’。
SQL> select ename,nullif(ename,'KING') from emp; 如果员工的名称为king就返回空，否则返回自己的名字。
SQL> select ename 姓名,COALESCE(comm,0) "奖金" from emp;
如果有奖金就返回奖金，如果没有奖金就返回0，起个别名叫做"奖金"。
实验16：分支的函数
该实验的目的是掌握分支操作的函数.
Case语句
9I以后才支持的新特性，说叫语句其实是函数。目的是为了分支。
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END 例子：
例题：判别job,不同工作的人赋予不同的工资，除了CLERK，SALESMAN，ANALYST以外，其它的人工资不变，将函数的值起一个别名为"处理后数据"。
语句： SELECT ename, job, sal, CASE job WHEN 'CLERK' THEN 1.1111*sal WHEN 'SALESMAN' THEN 2.2222*sal WHEN 'ANALYST' THEN 3.3333*sal
ELSE sal END "处理后数据"
FROM emp;
Decode函数：
和CASE语句一样都是分支语句，但Decode函数是ORACLE自己定义的，其它数据库可能不支持。
语法如下：
DECODE(col|expression, search1, result1 [, search2, result2,…,] [, default])
例题：判别job,不同工作的人赋予不同的工资，除了CLERK，SALESMAN，ANALYST以外，其它的人工资不变，将函数的值起一个别名为"处理后数据"。
语句： SELECT ename, job, sal, decode(job ,'CLERK' , 1.11*sal ,'SALESMAN' , 3.33*sal ,'ANALYST' , 8.88*sal
, sal ) "处理后数据"
FROM emp;
下面的例题是求税率：
不同工资上的税率不同。每2000一个台阶，8000以上一律40%的税。
SQL>SELECT ename, sal, DECODE (TRUNC(sal/1000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30 , 0.40 ) 税率 FROM emp;
不管CASE语句还是DECODE函数，他们都是单行函数，每一行都有一个返回值。从ORACLE角度来讲，DECODE更好，因为各个版本的数据库都支持，横向来说，CASE语句更好，因为它是国标，不同的数据库间都认可。 实验17：分组统计函数
该实验的目的是掌握常用的组函数.理解group by的操作.
需要掌握的知识点：
1。组函数
2。分组统计
3。NULL值在组函数中的作用
4。HAVING的过滤作用
5。组函数的嵌套
组函数
这种函数每次处理多行，给出一个返回值
Avg平均
Sum求和
Max最大
Min最小
Count计数
所有组函数，除了count(*)以外，都忽略null值，count是计数，查看有多少行，count(列)是查看该列有多少非空的行。
例子：
Group by 子句
Having是在结果中再次筛选。Having一定得出现在group by 子句得后面。不能独立存在。
组函数的嵌套注意要使用GROUP BY子句。
巧用DECODE函数，改变排版方式
SQL>select sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981", sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982", sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987", count(ename) "总人数" from emp;
《完》
本文转自xjzhujunjie 51CTO博客，原文链接:http://blog.51cto.com/xjzhujunjie/866373