共计 4943 个字符，预计需要花费 13 分钟才能阅读完成。
学习 Oracle 也有一段时间了，发现 oracle 中的函数好多，对于做后台的程序猿来说，大把大把的时间还要学习很多其他的新东西，再把这些函数也都记住是不太现实的，所以总结了一下 Oracle 中的一些常用函数及示例，一是为了和大家分享，二是可以在以后工作中忘记了随时查阅。废话不多说，下面直接上函数。
一. 单行函数
只处理单个行，并且为每行返回一个结果。
1. 字符函数
（1）concat(str1,str2) 字符串拼接函数
| select concat('Hello','World') from dual; | |
| -- 等价于 | |
| select 'Hello'||'World' from dual; |
（2）initcap(str) 将每个单词首字母大写，其他字母小写
| select initcap('hello world!') from dual; -- 返回结果为 'Hello World!' | |
| select initcap('HELLO WORLD!') from dual; -- 返回结果为 'Hello World!' |
（3）instr(x,find_string[,start][,occurrence]) 返回指定字符串在某字符串中的位置，可以指定搜索的开始位置和返回第几次搜索出来的结果
| ---------- 搜索时下标从 1 开始计算 | |
| select instr('Hello World!','o') from dual;-- 从 1 位置开始搜索，返回第一次出现的 o 的位置，结果为 5 | |
| select instr('Hello World!','o',6) from dual;-- 从 6 位置开始搜索，返回第一次出现的 o 的位置，结果为 8 | |
| select instr('Hello World!','o',1,2) from dual;-- 从 1 位置开始搜索，返回第二次出现 o 的位置，结果为 8 |
（4）length(str) 返回表达式中的字符数
| select length('Hello World!') from dual;-- 返回结果为 12 | |
| select length('张三') from dual;-- 返回结果为 2 |
（5）lengthb(str) 返回表达式中的字节数
| select lengthb('Hello World!') from dual;-- 返回结果为 12 | |
| select lengthb('张三') from dual;-- 返回结果为 6 |
（6）lower(str) 将字符串转换为小写
select lower('Hello World!') from dual;
（7）upper(str) 将字符串转换为大写
select upper('Hello World!') from dual;
（8）lpad(str,width[,pad_string]) 当字符串长度不够时，左填充补齐，可以指定补齐时用什么字符补齐, 若不指定，则以空格补齐
| select lpad('Hello World!',20) from dual;-- 返回结果为 'Hello World!' | |
| select lpad('Hello World!',20,'*') from dual;-- 返回结果为 '********Hello World!' |
（9）rpad(str,width[,pad_string]) 当字符串长度不够时，右填充补齐，原理同左填充
| select rpad('Hello World!',20) from dual;-- 返回结果为 'Hello World!' | |
| select rpad('Hello World!',20,'*+') from dual;-- 返回结果为 'Hello World!*+*+*+*+' |
（10）ltrim(x[,trim_string]) 从字符串左侧去除指定的所有字符串，若没有指定去除的字符串，则默认去除左侧空白符
| select ltrim('Hello World!') from dual;-- 返回结果为 'Hello World!' | |
| select ltrim('***+*Hello World!***+*','*+') from dual;-- 返回结果为 'Hello World!***+*' |
（11）rtrim(x[,trim_string]) 从字符串右侧去除指定的所有字符串，原理同 ltrim()
| select rtrim('Hello World!') from dual;-- 返回结果为 'Hello World!' | |
| select rtrim('***+*Hello World!***+*','*+') from dual;-- 返回结果为 '***+*Hello World!' |
（12）trim(trim_string from x) 从字符串两侧去除指定的所有字符串
select trim('*+' from '***+*Hello World!***+*') from dual;
注意，ltrim() 和 rtrim() 的截取集可以使多个字符，但 trim 的截取集只能有一个字符
select trim('*+' from '***+*Hello World!***+*') from dual;-- 错误，截取集只能有一个字符
（13）nvl(x,value) 将一个 NULL 转换为另外一个值，如果 x 为 NULL, 则返回 value, 否则返回 x 值本身
| insert into student values(7,'猪猪',default,NULL); | |
| select nvl(address,'北京市') from student; |
（14）nvl2(x,value1,value2), 如果 x 不为 NULL，返回 value1，否则，返回 value2
select nvl2(address,'有地址','无地址') from student;
（15）replace(x,search_string,replace_string), 从字符串 x 中搜索 search_string 字符串，并使用 replace_string 字符串替换。并不会修改数据库中原始值
select replace('Hello World!','o','HA') from dual;
（16）substr(x,start[,length]) 返回字符串中的指定的字符，这些字符从字符串的第 start 个位置开始，长度为 length 个字符；如果 start 是负数，则从 x 字符串的末尾开始算起；如果 length 省略，则将返回一直到字符串末尾的所有字符
| select substr('Hello World',3) from dual; -- 返回结果为 'llo World' | |
| select substr('Hello World',-3) from dual;-- 返回结果为 'rld' | |
| select substr('Hello World',3,2) from dual;-- 返回结果为 'll' | |
| select substr('Hello World',-7,4) from dual;-- 返回结果为 'o Wo' |
2. 数值函数
（1）abs(value) 返回 value 的绝对值
select abs(-10) from dual;-- 返回结果为 10
（2）ceil(value) 返回大于等于 value 的最小整数
select ceil(2.3) from dual; -- 返回结果为 3
（3）floor(value) 返回小于等于 value 的最大整数
select floor(2.3) from dual; -- 返回结果为 2
（4）trunc(value,n) 对 value 进行截断，如果 n >0，保留 n 位小数；n<0，则保留 - n 位整数位；n=0，则去掉小数部分
| select trunc(555.666) from dual; -- 返回结果为 555，不加 n 时默认去掉小数部分 | |
| select trunc(555.666,2) from dual;-- 返回结果为 555.66 | |
| select trunc(555.666,-2) from dual;-- 返回结果为 500 |
（5）round(value,n) 对 value 进行四舍五入，保存小数点右侧的 n 位。如果 n 省略的话，相当于 n = 0 的情况
| select round(555.666) from dual;-- 返回结果为 556，不加 n 时默认去掉小数部分 | |
| select round(555.666,2) from dual;-- 返回结果为 555.67 | |
| select round(555.666,-2) from dual;-- 返回结果为 600 |
注意:1.trunc 和 round 用法类似，只不过 trunc 是硬生生截取，并不进行四舍五入，而 round 进行截取时四舍五入
2. 都还可以对日期的截取，可以参考写的日期函数笔记
| select round(sysdate,'year') from dual; | |
| select trunc(sysdate,'year') from dual; |
3. 转换函数
将值从一种类型转换成另外一种类型，或者从一种格式转换为另外一种格式
（1）to_char(x[,format]): 将 x 转化为字符串。format 为转换的格式，可以为数字格式或日期格式
| select to_char('12345.67') from dual; -- 返回结果为 12345.67 | |
| select to_char('12345.67','99,999.99') from dual; -- 返回结果为 12,345.67 |
（2）to_number(x [, format]): 将 x 转换为数字。可以指定 format 格式
| select to_number('970.13') + 25.5 from dual; | |
| select to_number('-$12,345.67', '$99,999.99') from dual; |
（3）cast(x as type): 将 x 转换为指定的兼容的数据库类型
select cast(12345.67 as varchar2(10)),cast('05- 7 月 -07' as date), cast(12345.678 as number(10,2)) from dual;
（4）to_date(x [,format]): 将 x 字符串转换为日期
select to_date('2012-3-15','YYYY-MM-DD') from dual
二. 聚集函数
1. 常用函数
（1）avg(x)：返回 x 的平均值
select avg(grade) from sc;
（2）count(x)：返回统计的行数
select count(name) from sc;
（3）max(x)：返回 x 的最大值
select max(grade) from sc;
（4）min(x)：返回 x 的最小值
select min(grade) from sc;
（5）sum(x)：返回 x 的总计值
select sum(grade) from sc;
2. 对分组行使用聚集函数
对分组后的行使用聚集函数，聚集函数会统计每组中的值，对于每组分别统计后返回一个值。
示例
| -- 按照职位分组，求出每个职位的最高和最低工资 | |
| select job ,max(sal),min(sal) from emp | |
| group by job | |
| order by job; |
注意：1. 分组时 select 子句后边的列名必须与 group by 子句后的列名一致，除非是聚合函数
select deptno,avg(sal) from EMP;-- 错误，因为 deptno 不是聚集函数，也不是 group by 后面跟的列名
2. 不能使用聚集函数作为 WHERE 子句的筛选条件
select deptno from emp where avg(sal)>1000;-- 错误
3. 分组后，需要使用条件进行筛选，则使用 having 过滤分组后的行，不能使用 where，where 只能放在 group by 前面。
| select deptno, avg(sal) from emp where deptno<>10 | |
| group by deptno | |
| having avg(sal) > 900; |
更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-01/139336.htm
