Oracle中常用的的函数

1.数值型函数

1.ABS(x)
【功能】返回x的绝对值
【参数】x，数字型表达式
【返回】数字
2.ceil(x)
【功能】向上取整
【参数】x，数字型表达式
【返回】数字
3.floor(x)
【功能】向下取整
【参数】x，数字型表达式
【返回】数字
4.power(x,y)
【功能】返回x的y次幂
【参数】x，y 数字型表达式
【返回】数字
5mod(x,y)
【功能】返回x除以y的余数
【参数】x,y，数字型表达式
【返回】数字
6.round(x[,y])
【功能】返回四舍五入后的值
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。
【返回】数字
6trunc(x[,y])
【功能】返回x按精度y截取后的值，和round作用相似只是不用进行四舍五入
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。
【返回】数字

2.字符型函数

1.CONCAT(c1,c2)
【功能】连接两个字符串
【参数】c1，c2 字符型表达式
【返回】字符型
2INITCAP(c1)
【功能】返回字符串并将字符串的第一个字母变为大写，其它字母小写;
【参数】c1字符型表达式
【返回】字符型
eg: select initcap('accd fDS fS') from dual;
out:Accd Fds Fs
3.LOWER(c1)/UPPER(c1)
【功能】：将字符串全部转为小写/大写
【参数】：c1，字符表达式
【返回】：字符型
4.INSTR(C1,C2[,I[,J]])
【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
【参数】
    C1   被搜索的字符串
    C2   希望搜索的字符串
    I    搜索的开始位置,默认为1
    J    第J次出现的位置,默认为1
【返回】数值
eg:select instr('accd','c',1,2) from dual;
out:3
5.LENGTH(c1)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】C1 字符串
【返回】数值型
6.LPAD(c1,n[,c2])
【功能】在字符串c1的左边用字符串c2填充，直到长度为n时为止
【参数】
	C1 字符串
	n 追加后字符总长度
	c2 追加字符串,默认为空格
【返回】字符型
【说明】如果c1长度大于n，则返回c1左边n个字符;如果如果c1长度小于n，c2和c1连接后大于n，则返回连接后的右边n个字符
7.RPAD(c1,n[,c2])
【功能】在字符串c1的右边用字符串c2填充，直到长度为n时为止
【参数】C1 字符串
    n 追加后字符总长度
    c2 追加字符串,默认为空格
【返回】字符型
【说明】如果c1长度大于n，则返回c1左边n个字符;如果如果c1长度小于n，c1和c2连接后大于n，则返回连接后的左边n个字符
 如果如果c1长度小于n，c1和c2连接后小于n，则返回c1与多个重复c2连接(总长度>=n)后的左边n个字符
8.LTRIM(c1,[,c2])/RTRIM(c1,[,c2])
【功能】删除左/右边出现的字符串
【参数】
	C1 字符串
	c2 追加字符串,默认为空格
【返回】字符型
9.TRIM(c1 from c2)
【功能】删除左边和右边出现的字符串
【参数】
	C2 删除前字符串
	c1 删除字符串,默认为空格
【返回】字符型
10.REPLACE(c1,c2[,c3])
【功能】将字符表达式值中，部分相同字符串，替换成新的字符串
【参数】
    c1   希望被替换的字符或变量 
    c2   被替换的字符串（换谁）
    c3   要替换的字符串 （换成什么，默认是删除）
【返回】字符型
11.SUBSTR(c1,n1[,n2])
【功能】取子字符串
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第n1个字符直到结束
【返回】字符型
12.TRANSLATE(c1,c2,c3)
【功能】将字符表达式值中，指定字符替换为新字符
eg:select translate('abeda','ab','d') from dual;
out:dedd
具体解析请见我的博客：https://mp.csdn.net/mp_blog/creation/editor/119684830

3.日期函数

1.sysdate/current_date
【功能】：返回当前日期。
【参数】：没有参数，没有括号
【返回】：日期
2.add_months(d1,n1)
【功能】：返回在日期d1基础上再加n1个月后新的日期。
【参数】：d1，日期型，n1数字型
【返回】：日期
3.last_day(d1)
【功能】：返回日期d1所在月份最后一天的日期。注：没有first_day();
【参数】：d1，日期型
【返回】：日期
4.months_between(d1,d2)
【功能】：返回日期d1到日期d2之间的月数。
【参数】：d1，d2 日期型
【返回】：数字
如果d1>d2，则返回正数
如果d1<d2，则返回负数
5.round(d1[,c1])
【功能】：给出日期d1按期间(参数c1)四舍五入后的期间的第一天日期（与数值四舍五入意思相近）
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即最近0点日期)
【参数表】：
    c1对应的参数表:
    最近0点日期: 取消参数c1或j
    最近的星期日：day或dy或d
    最近月初日期：month或mon或mm或rm 
    最近季日期：q
    最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)  
    最近世纪初日期：cc或scc
【返回】：日期 
【示例】
    select sysdate 当时日期,
    round(sysdate) 最近0点日期,
    round(sysdate,'day') 最近星期日,
    round(sysdate,'month') 最近月初,
    round(sysdate,'q') 最近季初日期, 
    round(sysdate,'year') 最近年初日期 from dual;
6.trunc(d1[,c1])
【功能】：返回日期d1所在期间(参数c1)的第一天日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)
【参数表】：
    c1对应的参数表:
    最近0点日期: 取消参数c1或j
    最近的星期日：day或dy或d (每周顺序：日，一，二，三，四，五，六）
    最近月初日期：month或mon或mm或rm 
    最近季日期：q
    最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)  
    最近世纪初日期：cc或scc
【返回】：日期 
【示例】
    select sysdate 当时日期,
    trunc(sysdate) 今天日期,
    trunc(sysdate,'day') 本周星期日,
    trunc(sysdate,'month') 本月初,
    trunc(sysdate,'q') 本季初日期, 
    trunc(sysdate,'year') 本年初日期 from dual;
7.next_day(d1[,c1])
【功能】：返回日期d1的下个星期几(参数c1)的日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)
【参数表】：c1对应:星期一，星期二，星期三……星期日
【返回】：日期 
【示例】
    select sysdate 当时日期,
    next_day(sysdate,'星期一') 下个星期一,
    next_day(sysdate,'星期二') 下个星期二,
    next_day(sysdate,'星期三') 下个星期三,
    next_day(sysdate,'星期四') 下个星期四,
    next_day(sysdate,'星期五') 下个星期五,
    next_day(sysdate,'星期六') 下个星期六,
    next_day(sysdate,'星期日') 下个星期日 from dual;
8.extract(c1 from d1)
【功能】：日期/时间d1中，参数(c1)的值
【参数】：d1日期型(date)/日期时间型(timestamp),c1为字符型(参数)
【参数表】：
	c1对应的参数表详见示例
【返回】：字符
【示例】
只可以从一个date类型中截取年月日
    select sysdate 当前日期,
    extract(DAY from sysdate ) 日,
    extract(MONTH from sysdate ) 月,
    extract(YEAR from sysdate ) 年
    from dual;
从timestamp中获取年月日时分秒
    select 
    extract(year from systimestamp) year,
    extract(month from systimestamp) month,
    extract(day from systimestamp) day,
    extract(minute from systimestamp) minute,
    extract(second from systimestamp) second,
    extract(timezone_hour from systimestamp) th,
    extract(timezone_minute from systimestamp) tm,
    extract(timezone_region from systimestamp) tr,
    extract(timezone_abbr from systimestamp) ta
    from dual

4.转换函数

1.TO_CHAR(x[[,c2],C3])
【功能】将日期或数据转换为char数据类型
【参数】
x是一个date或number数据类型。
c2为格式参数
c3为NLS设置参数
如果x为日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。
如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。
NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"
【示例】1
    to_char(sysdate,'d') 每周第几天 
    to_char(sysdate,'dd') 每月第几天 
    to_char(sysdate,'ddd') 每年第几天 
    to_char(sysdate,'ww') 每年第几周 
    to_char(sysdate,'mm') 每年第几月 
    to_char(sysdate,'q') 每年第几季 
    to_char(sysdate,'yyyy') 年
2.TO_DATE(X[,c2[,c3]])
【功能】将字符串X转化为日期型
【参数】c2,c3,字符型，参照to_char()
【返回】字符串
如果x格式为日期型(date)格式时，则相同表达：date x
如果x格式为日期时间型(timestamp)格式时，则相同表达：timestamp x
【相反】 to_char(date[,c2[,c3]])
【示例】
    select to_date('199912','yyyymm'),
    to_date('2000.05.20','yyyy.mm.dd'),
    (date '2008-12-31') XXdate, 
    to_date('2008-12-31 12:31:30','yyyy-mm-dd hh24:mi:ss'),
    (timestamp '2008-12-31 12:31:30') XXtimestamp
    from dual;

5.聚合函数

1.AVG([distinct|all]x)
【功能】统计数据表选中行x列的平均值。
【说明】该函数有局限如果有空的话该列不参与计算所以通常nvl一起使用
eg ：avg(nvl(列名，0))
【参数】
    all表示对所有的值求平均值,distinct只对不同的值求平均值，默认为all
    如果有参数distinct或all，需有空格与x(列)隔开。
【参数】x，只能为数值型字段
【返回】数字值
2.SUM([distinct|all]x)
【功能】统计数据表选中行x列的合计值。
【参数】
    all表示对所有的值求合计值,distinct只对不同的值求合计值，默认为all
    如果有参数distinct或all，需有空格与x(列)隔开。
【参数】x，只能为数值型字段
【返回】数字值
3.count(*|[distinct|all]x)
【功能】统计数据表选中行x列的合计值。
【参数】
    *表示对满足条件的所有行统计，不管其是否重复或有空值(NULL)
    all表示对所有的值统计,默认为all
    distinct只对不同的值统计，
    如果有参数distinct或all，需有空格与x(列)隔开，均忽略空值(NULL)。
【参数】x，可为数字、字符、日期型及其它类型的字段
【返回】数字值
4.MAX/MIN([distinct|all]x)
【功能】统计数据表选中行x列的最大/小值。
【参数】all表示对所有的值求最大/小值,distinct只对不同的值求最大/小值，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。
【参数】x，可为数字、字符或日期型字段
【返回】对应x字段类型

6.其他函数

1.【语法】NVL (expr1, expr2)
 【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。注意两者的类型要一致 
 【语法】NVL2 (expr1, expr2, expr3) 
 【功能】expr1不为NULL，返回expr2；expr1为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型 
2.case [<表达式>]
when <表达式条件值1> then <满足条件时返回值1> 
[when <表达式条件值2> then <满足条件时返回值2> 
……
[else  <不满足上述条件时返回值>]]
end
【功能】当：<表达式>＝<表达式条件值1……n> 时，返回对应 <满足条件时返回值1……n> 
当<表达式条件值1……n>不为条件表达式时，与函数decode()相同，
decode(<表达式>,<表达式条件值1>,<满足条件时返回值1>,<表达式条件值2>,<满足条件时返回值2> ……,<不满足上述条件时返回值>)
【参数】
<表达式> 默认为true (逻辑型)
<表达式条件值1……n> 类型要与<表达式>类型一致，
若<表达式>为字符型，则<表达式条件值1……n>也要为字符型
eg: 
SELECT CASE 表达式
	WHEN 表达式条件值1 THEN 满足条件时返回值1
	WHEN 表达式条件值1 THEN 满足条件时返回值1
	WHEN 表达式条件值1 THEN  满足条件时返回值1
	ELSE 不满足上述条件时返回值
END  
from dual;
3.decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
【功能】根据条件返回相应值
【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null
注：值1……n 不能为条件表达式,这种情况只能用case when then end解决

4.dbms_random
  dbms_random.value方法这种用法没有参数，会返回一个具有38位精度的数值，范围从0.0到1.0，但不包括1.0。
  dbms.random,value(n1,n2),value带有两个参数，第一个指下限，第二个指上限，将会生成下限到上限之间的数字，但不包含上限。
dbms_random.string(opt CHAR, len NUMBER) 方法
opt ：

■ 'u', 'U' - returning string in uppercase alpha characters
									返回大写字母的字符串
■ 'l', 'L' - returning string in lowercase alpha characters
									返回小写字母的字符串
■ 'a', 'A' - returning string in mixed case alpha characters
									返回大小写混合的字符串
■ 'x', 'X' - returning string in uppercase alpha-numericcharacters
									返回大写字母数字字符串
■ 'p', 'P' - returning string in any printable characters.Otherwise the returning string is in uppercase alphacharacters.
									返回任意可打印字符的字符串。否则返回的字符串是大写字母。
len ：
表示返回的字符串长度。