首页 > 代码库 > 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函数