首页 > 代码库 > oracle函数
oracle函数
oracle中函数分为3大类,分别为:单行函数,分组函数,分析函数。
1.单行函数
单行函数同时只能对一行进行操作,且对输入的每一行返回一行输出结果。
单行函数还分为:字符函数,数子函数,日期函数,转换函数,其他函数。
1.1.字符函数
字符函数是oracle中广泛使用的函数,对字符数据类型进行操作,操作结果可能是字符数据类型,也可能是数字类型。
1.1.1
INITCAP(char)
说明: 首字母大写
实例
输入:select INITCAP(‘hello‘) from dual
输出: Hello
1.1.2
LOWER(char)
说明: 转换为小写
实例
输入:select LOWER(‘ABC‘) from dual
输出: abc
1.1.3
UPPER(char)
说明: 转换为大写
实例
输入:select UPPER(‘abc‘) from dual
输出: ABC
1.1.4
LTRIM(char,set)
说明: 左裁剪
实例
输入:select LTRIM(‘zxcvbnmloiju‘,‘zxc‘) from dual
输出: vbnmloiju
1.1.5
RTRIM(char,set)
说明: 右裁剪
实例
输入:select RTRIM(‘zxcvbnmloiju‘,‘iju‘) from dual
输出: zxcvbnmlo
1.1.6
TRANSLATE(char,from,to)
说明: 按字符翻译
实例
输入:select TRANSLATE(‘jack‘,‘abcd‘,‘1234‘) from dual
输出: j13k
1.1.7
TRANSLATE(char,from,to)
说明: 按字符翻译
实例
输入:select TRANSLATE(‘jack‘,‘abcd‘,‘1234‘) from dual
输出: j13k
1.1.8
REPLACE(char,search_str,replace_str)
说明: 字符串替换
实例
输入:select REPLACE(‘jack and jue‘,‘j‘,‘bl‘) from dual
输出: black and blue
1.1.9
INSTR(char,substr[,pos])
说明: 查找字符串位置
实例
输入:select INSTR(‘asdfgghjjkkklsfsf‘,‘g‘) from dual
输出: 5
1.1.10
SUBSTR(char,pos,len)
说明:截取字符串
实例
输入:select SUBSTR(‘safgdgd‘,3,2) from dual
输出: fg
1.1.11
CONCAT(char1,char2)
说明:连接字符串
实例
输入:select CONCAT(‘asd‘,‘zxc‘) from dual
输出: asdzxc
1.2 数字函数
数字函数接受数字输入并返回数字作为输出结果。
1.2.1
ABS(n)
说明:取绝对值
实例
输入:select ABS(-10) from dual
输出: 10
1.2.2
CEIL(n)
说明:向上取整
实例
输入:select CEIL(15.1) from dual
输出: 16
1.2.3
SIGN(n)
说明:取符号
实例
输入:select SIGN(-15) from dual
输出: -1
1.2.4
FLOOR(n)
说明:向下取整
实例
输入:select FLOOR(100.52) from dual
输出: 100
1.2.5
ROUND(m,n)
说明:向下取整
实例
输入:select ROUND(100.52735,2) from dual
输出: 100.53
1.3日期函数
日期函数对日期值进行运算,根据函数的用途产生日期数据类型或数值类型的结果。
1.3.1
SYSDATE
说明:返回当前日期
实例
输入:select SYSDATE from dual
输出: 2017/1/7 16:32:32
1.3.2
MONTHS_BETWEEN
说明:返回两个日期间的月份
实例
输入:select MONTHS_BETWEEN(‘04-11月-05‘,‘11-1月-01‘) from dual
输出: 57.7741935483871
1.3.3
ADD_MONTHS
说明:返回把月份数加到日期上的新日期
实例
输入:select ADD_MONTHS(‘06-2月-03‘,1) from dual
输出: 2003/3/6
1.3.4
ROUND
说明:返回把月份数加到日期上的新日期
实例
输入:select ROUND(to_date(‘13-2月-03‘),‘YEAR‘) from dual
输出: 2003/1/1
1.4 转换函数
将值从一种数据类型转换为另一种数据类型
1.4.1
TO_CHAR
说明:返回把月份数加到日期上的新日期
实例
输入:select TO_CHAR(1234.5,‘$9999.9‘) from dual
输出: $1234.5
1.4.2
TO_DATE
说明:转换成日期类型
实例
输入:select TO_DATE(‘1980-01-01‘,‘yyyy-mm-dd‘) from dual
输出: 1980/1/1
1.4.3
TO_NUMBER
说明:转换成日期类型
实例
输入:select TO_DATE(‘1980-01-01‘,‘yyyy-mm-dd‘) from dual
输出: 1980/1/1
1.5其他函数
NVL(exp1,exp2):如果exp1的值为null,则返回exp2的值,否则返回exp1的值
NVL(exp1,exp2,exp3):如果exp1的值为null,则返回exp2的值,否则返回exp3的值
DECODE(value,if1,then1,if2,then2,....else):如果value的值为if1,则返回then1的值;如果value的值为if2,则返回then2的值,...,否则返回else的值
2.分析函数
语法
函数名([参数] over([分区子句] [排序子句]))
在语法中:
1.函数名表示分析函数的名称
2.参数表示函数需要传入的参数
3.分区子句(PARTITION BY)表示将查询结果分为不同的组,功能类似于GROUP BY 语句,是分析函数工作的基础。默认将所有结果作为一个分组。
1.ROW_NUMBER
ROW_NUMBER函数返回一个唯一的值,当遇到相同数据时,排名按照记录集中记录顺序依次递增。
2.DENSE_RANK
DENSE_RANK函数返回一个唯一的值,当遇到相同的数据时,所有相同数据的排名都是一样的。
3.RANK
RANK函数返回一个唯一的值,当遇到相同的数据时,所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
3.分组函数
- 1.分组函数作用于一组数据,并对一组数据返回一个值
- 2.组函数类型:
- avg 平均值
- count
- max
- min
- stddev
- sum
- 二 代码
- 1. 工资最高的:SQL> select max(sal) from emp;
- 工资最低的:SQL> select min(sal) from emp;
- 平均工资:SQL> select avg(sal) from emp;
- 工资总数:SQL> select sum(sal) from emp;
- 记录总数:SQL> select count(emptno) from emp; 返回的是不为空的记录
- SQL> select count(distinct emptno) from emp;
- 返回的是非空且不重复的记录总数 distinct是去掉重复的
- 也可以写为:
- SQL> select min(sal),max(sal),min(sal) from emp;
- 结果为:
- MIN(SAL) MAX(SAL) MIN(SAL)
- ---------- ---------- ----------
- 800 5000 800
- 注:(1)可以对数值型数据使用AVG 和 SUM 函数。
- (2)可以对任意数据类型的数据使用 MIN 和 MAX 函数。
- 也可以对日期使用
- SQL> select min(hiredate) from emp;
- SQL> select max(hiredate) from emp;
- 2.分组数据
- group by子句使用
- 例子:查询出employees中各部门的平均工资
- SQL> select department_id,avg(salary) from employees group by department_id;
- 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中,所以可以写为:
- SQL> select avg(salary) from employees group by department_id;
- 可以进行多列的分组:
- SQL> select department_id,job_id,sum(salary) from employees group by department_id,job_id;
- 注:(1)不能在 where 子句中使用组函数(注意)。
- (2)可以在 HAVING 子句中使用组函数。
- 3.过滤分组 having子句
- 例子:部门最高工资比 1000高的部门
- SQL> select department_id,max(salary) from employees group by department_id having max(salary)>1000;
- 每一个部门下有很多员工,通过对部门分组,把是一个部门员工分好在当前部门下,计算出最高的工资对比
- 4.嵌套组函数
- 例子:显示平均工资的最大值
- SQL> select max(avg(salary)) from employees group by department_id;
oracle函数