Oracle之函数

一、单行函数（对列中某个值处理）

1.数值函数

函数	描述	示例
ABS(X)	X的绝对值	ABS(-8)=8
ACOS(X)	X的反余弦	ACOS(1)=0
COS(X)	余弦	COS(1)=0.54030230586814
CEIL(X)	大于或等于X的最小值	CEIL(45.25)=46
FLOOR(X)	小于或等于X的最大值	FLOOR(9.98)=9
LOG(X,Y)	X为底Y的对数	LOG(2，4)=2
MOD(X,Y)	X除以Y的余数	MOD(8，3)=2
POWER(X,Y)	X的Y次幂	POWER(2，3)=8
ROUND(X[,Y])	X在第Y位四舍五入(在缺省 y 时,默认 y=0；y 是负整数，四舍五入到小数点左边|y|位)	ROUND(3.456，2)=3.46  ROUND(351.654,-2)=400
SQRT(X)	X的平方根	SQRT(4)=2
TRUNC(X[,Y])	X在第Y位截断(在缺省 y 时,默认 y=0；y 是负整数，从到小数点左边|y|位开始截断)	TRUNC(3.456，2)=3.45 TRUNC (351.654,-2)=300

2.字符函数

函数	描述
ASCII(X)	返回字符X的ASCII码
CHR(number)	返回数字number的ASCII值
CONCAT(X,Y)	连接字符串X和Y
INSTR(X,STR[,START][,N)	从X中查找子串str中的位置，可以指定从start开始，也可以指定从n开始
INITCAP(char)	首字母转换成大写
LENGTH(X)	返回X的长度
LOWER(X)	X转换成小写
UPPER(X)	X转换成大写
LTRIM(X[,TRIM_STR])	把X的左边截去trim_str字符串，缺省截去空格
RTRIM(X[,TRIM_STR])	把X的右边截去trim_str字符串，缺省截去空格
LPAD(char1,n,char2)	在字符串char1的左端填充字符串char2直到长度达到n;char2默认为空格，如果char1.length>n，则返回char1左端的n个字符
RPAD(char1,n,char2)	在字符串char1的右端填充字符串char2直到长度达到n;char2默认为空格，如果char1.length>n，则返回char1左端的n个字符
TRIM([TRIM_STR  FROM]X)	把X的两边截去trim_str字符串，缺省截去空格
REPLACE(X,old,new)	在X中查找old，并替换成new
SUBSTR(X,start[,length])	返回X的字串，从start处开始，截取length个字符（下标从1开始，缺省length，默认到结尾）

    -- 返回字符ASCII码
    select ASCII('A') from dual; -- 65

    -- 返回数字的ASCII码CHR
    select CHR(65) from dual; -- A

    -- CONCAT字符拼接
    select CONCAT('hello', 'mark') from dual; -- hellomarko*******

    -- INSTR返回子串的位置
    select INSTR('hellotomandmark', 'm', 1) from dual; -- 8

    -- INITCAP首字母转大写
    select INITCAP('hello mark') from dual; -- Hello Mark

    -- LENGTH字符长度
    select LENGTH('mark') from dual; -- 4

    -- LOWER大写转小写
    select LOWER('MARK') from dual;--mark 

    -- UPPER小写转大写
    select UPPER('mark') from dual; -- MARK

    -- LTRIM左截取
    select LTRIM('*******hello', '*') from dual; -- hello

    -- RTRIM右截取
    select RTRIM('hello*******', '*') from dual; -- hello

    -- LPAD左填充
    select LPAD('hello', 12, '*') from dual; -- *******hello

    -- RPAD右填充
    select RPAD('hello', 12, '*') from dual; -- hello*******

    -- TRIM两端截取
    select TRIM('*' from '**hellomark**') from dual; -- hellomark
    select TRIM('  hellomark  ') from dual; -- hellomark(注：默认去掉空格)

    -- REPLACE字符替换
    select REPLACE('hellomark', 'mark', ' tom') from dual; -- hello tom

    -- SUBSTR字符截取
    select SUBSTR('marktom', 2, 3) from dual; -- ark(注：下标从1开始)

 3.日期函数

函数	描述
SYSDATE	返回系统当前日期和时间
NEXT_DAY(day,char)	返回指定日期day后的第一个工作日char所对应的日期
LAST_DAY(day)	返回day日期所指定月份中最后一天所对应的日期
ADD_MONTHS(day,n)	返回day日期在n个月后(n为正数)或前(n为负数)的日期
MONTHS_BETWEEN(day1,day2)	返回day1日期和day2日期之间相差得月份
ROUND(day[,fmt])	返回日期的四舍五入结果。如果fmt指定年度，则7月1日为分界线；如果fmt指定月，则16日为分界线；如果指定天，则中午12:00为分界线，默认舍入到日
TRUNC(day,[,fmt])	日期截断函数。如果fmt指定年度，则结果为本年度的1月1日；如果为月，则将结果为本月1日，默认截断到日
CURRENT_DATE	返回当前会话时区所对应日期时间
EXTRACT	从日期中获取所需要的特定数据（年|月|日）

    --当前时间 2019-01-07  21:07:56
    
    --查询3天后的日期
    select SYSDATE + 3 from dual; --2019-01-10 21:07:56

    -- NEXT_DAY,下一个char对应日期
    select NEXT_DAY(SYSDATE, '星期一') from dual; -- 2019-01-14 21:07:56

    -- LAST_DAY,指定day对应月份中最后一天
    select LAST_DAY(SYSDATE) from dual; -- 2019-01-31 21:09:09

    -- ADD_MONTHS,指定day对应月份加减n的日期
    select ADD_MONTHS(SYSDATE, 2) from dual; -- 2019-03-07 21:10:17

    -- MONTHS_BETWEEN,日期1和日期2相差月份
    select MONTHS_BETWEEN(SYSDATE, SYSDATE) from dual; -- 0
    select MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, -2), ADD_MONTHS(SYSDATE, 2)) from dual; -- -4

    -- ROUND,日期的四舍五入
    select ROUND(SYSDATE) from dual; -- 2019-01-08 00:00:00
    select ROUND(SYSDATE, 'YEAR') from dual; -- 2019-01-01 00:00:00
    select ROUND(SYSDATE, 'MONTH') from dual; -- 2019-01-01 00:00:00

    -- TRUNC,日期截断
    select TRUNC(SYSDATE) from dual; -- 2019-01-07 00:00:00
    select TRUNC(SYSDATE, 'YEAR') from dual; -- 2019-01-01 00:00:00
    select TRUNC(SYSDATE, 'MONTH') from dual; -- 2019-01-01 00:00:00

    -- CURRENT_DATE,当前会话时区所对应日期时间
    select CURRENT_DATE from dual; -- 2019-01-07 21:14:26

    -- EXTRACT,从日期获取年|月|日
    select EXTRACT(YEAR from SYSDATE) from dual; -- 2019
    select EXTRACT(MONTH from SYSDATE) from dual; -- 1
    select EXTRACT(DAY from SYSDATE) from dual; -- 7

 4.转换函数

函数	描述
TO_CHAR(d|n[,fmt])	将一个数字或日期转换成字符串
TO_NUMBER(X,[,fmt])	将字符型数据转换成数字型数据
TO_DATE(X,[,fmt])	将字符型数据转换为日期型数据
CAST	将一种built-in类型转换成另一种built-in类型

    -- TO_CHAR,数值或日期转字符
    select TO_CHAR(sal,'$9,999.99') from emp; -- 88
    select TO_CHAR(SYSDATE, 'yyyy-mm-dd hh:mm:ss') from dual; -- 2019-01-07 09:24:17
    select TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mm:ss') from dual;-- 2019-01-07 21:24:17

    -- TO_NUMBER,字符转数值
    select 100 + '10' from dual; --110(默认已经帮我们转换)
    select 100 + TO_NUMBER('10') from dual; -- 110
    select TO_NUMBER('mark') from dual; -- 无效数字

    -- TO_DATE,字符转日期
    select TO_DATE('2015-9-29', 'yyyy-mm-dd') from dual; -- 2015-09-29 00:00:00

    -- CAST,将一种字符转换成另一种字符
    select CAST('100' as NUMBER) + 10 from dual; -- 110
    select CAST(2 as char) || 'mark' from dual; -- 2mark
    select 2 || 'mark' from dual; -- 2mark(已经默认帮我们从数字转换成字符)

参数	示例	说明
9	999	指定位置处显示数字
.	9.9	指定位置返回小数点
,	99,99	指定位置返回一个逗号
$	$999	数字开头返回一个美元符号
EEEE	9.99EEEE	科学计数法表示
L	L999	数字前加一个本地货币符号
PR	999PR	如果数字式负数则用尖括号进行表示

5.通用函数

函数	描述
SIGN(number)	如果number大于0，SIGN则返回1；如果number小于0，SIGN则返回-1；如果number等于0，SIGN则返回0
decode(expression , search , result [, search , result]… [, default])	IF语句的另一形式。将输入数值与参数列表比较，返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合
TRUNC(number, [ decimal_places ])	number是要截取的数字，decimal_places是要保留的小数位。这个参数必须是个整数。 如果此参数缺省，默认保留0位小数
GREATEST(expr1[,expr2]…)	返回表达式中值最大的一个
LEAST(expr1[,expr2]…)	返回表达式中值最小的一个
NULLIF(expr1,expr2)	如果expr1=expr2；则返回null，否则返回expr1
NVL(expr1,expr2)	如果expr1=null；则返回expr2，否则返回expr1
NVL2(expr1,expr2,expr3)	如果expr1!=null；则返回expr2；如果expr1=null；则返回expr3

　decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)　　
　　该函数的含义如下：　　
　　IF 条件=值1 THEN
　　RETURN(翻译值1)
　　ELSIF 条件=值2 THEN
　　RETURN(翻译值2)
　　......
　　ELSIF 条件=值n THEN
　　RETURN(翻译值n)　　
　　ELSE
　　RETURN(缺省值)
　　END IF

    -- SIGN,大于0返回1,小于0返回-1,等于0返回0
    select SIGN(5) from dual; -- 1
    select SIGN(-8) from dual; -- -1
    select SIGN(0) from dual; -- 0

    -- TRUNC,数字截取
    select TRUNC(20.2183, 2) from dual; -- 20.21
    select TRUNC(20.1, 4) from dual; -- 20.1

    -- GREATEST,求最大值
    select GREATEST(54, 757, 80, 2, 4, 478) from dual; -- 757

    -- LEAST,求最小值
    select LEAST(5, 70, 54, 15, 35, 45) from dual; -- 5

    -- NULLIF,如果两个参数相等返回null,否则返回第一个
    select NULLIF(8, 8) from dual; -- NULL
    select NULLIF(20, 10) from dual; -- 20

    -- NVL,如果第一个参数等于null,返回第二个参数,否则返回第一个参数
    select NVL(20, 30) from dual; -- 20
    select NVL(NULL, 30) from dual; -- 30

    -- NVL2,如果第一个参数等于null,返回第三个参数。否则,返回第二个参数
    select NVL2(NULL, 20, 30) from dual; -- 30
    select NVL2('Hellomark', 20, 30) from dual; -- 20

 二、多行函数（对某列所有行进行处理）

函数	描述
AVG	计算某一列值的平均值
COUNT	统计某一列中值的个数
MAX	求某一列值中的最大值
MIN	求某一列值中的最小值
SUM	计算某一列值的总和

    --统计员工奖金总和
    select sum(comm) from emp; --会直接忽略空值
    --统计员工人数
    select count(1) from emp;
    --统计员工平均奖金
    select ceil(avg(comm)/count(1)) from emp;