Oracle 函数大全

-- ************************* 数学函数 *******************************
-- abs(x) 返回绝对值

select abs(-100),abs(100) from dual;

-- sign(x) 返回正负值  数字，若为正值返回1，负值返回-1，0返回0

select sign(100),sign(-100),sign(0) from dual;

-- ceil(x) 返回大于或等于x的最小整数

select ceil(3.1),ceil(2.8+3.1),ceil(0) from dual;

-- floor(x) 返回小于或等于x的最大整数
select floor(3.1),floor(2.8+3.1),floor(0) from dual;


-- power(x,y) 返回x的y次幂
select power(2,4),power(2.5,2),power(1.1,0),power(2,-1) from dual;


-- exp(y) 返回e的y次幂
select exp(3),exp(0),exp(-3) from dual;


-- ln(y) 返回以e为底的y的对数
select ln(20.0855369),ln(100) from dual;


-- log(x,y) 返回以x为底的y的对数 x和y都大于0
select log(2,16) from dual;


-- mod(x,y) 返回x除以y的余数
select mod(5,3) from dual;


-- round(x[,y]) 返回四舍五入后的值  
-- x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位
select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666,-2),round(5555.6666) from dual;
select round(12.5),round(11.2),round(-11) from dual;


-- trunc(x[,y]) 返回x按精度y截取后的值
-- x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。
select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333)  from dual;
select trunc(123),trunc(123.56),trunc(-156.56,-2) from dual;


-- sqrt(x) 返回x的平方根
select sqrt(64),sqrt(18) from dual;


-- sin(x) 返回一个数字的正弦值
select sin(1.57079) from dual;


-- cos(x) 返回一个数字的余弦值
select cos(30) from dual;


-- tan(x) 返回一个数字的正切值
select tan(20) from dual;


-- asin(x) 返回一个数字的反正弦值
select asin(0.5) from dual;


-- acos(x) 返回一个数字的反余弦值
select acos(-1) from dual;


-- atan(x) 返回一个数字的反正切值
select atan(1) from dual;


-- sinh(x) 返回一个数字的双曲正弦值
select sinh(20) from dual;


-- cosh(x) 返回一个数字的双曲余弦值
select cosh(20) from dual;


-- tanh(x) 返回一个数字的双曲正切值
select tanh(20) from dual;
-- ************************* 数学函数 *******************************


-- ************************ 字符串函数 ******************************
-- ASCII(x) 返回字符表达式最左端字符的ASCII码值
select ascii('A') "大写",ascii('a') "小写",ascii(' ') "空格",ascii('玲') "汉字" from dual;


-- chr(x) 将ASCII码转换为字符
select chr(49633),chr(65) from dual;


-- concat(x,y) 连接两个字符串
select concat('010','12345678') from dual;


-- initcap(x) 返回字符串并将字符串的第一个字母变为大写，其他字母小写
select initcap('I love HCL') from dual;


-- lower(x) 将字符串全部转换为小写
select lower('HHC HCL aa') from dual;


-- upper(x) 将字符串全部转换为大写
select upper('i love hcl') from dual;


-- NLS_INITCAP(x[,y]) 返回字符串并将字符串的第一个字母变为大写，其它字母小写;
-- y 指定排序的方式(nls_sort=)
-- nls_sort=SCHINESE_RADICAL_M（部首、笔画）
-- nls_sort=SCHINESE_STROKE_M（笔画、部首  SCHINESE_PINYIN_M（拼音））
select nls_initcap('ab cde') "test",nls_initcap('a c d e f b','nls_sort=SCHINESE_PINYIN_M') "test1" from dual;
select nls_initcap('ab cde') "test",nls_initcap('a c b d e','NLS_LANGUAGE=AMERICAN') "test1" from dual;


-- NLS_LOWER(x[,y]) 返回字符串并将字符串都变为小写
-- 指定排序的方式(nls_sort=) 。
-- SCHINESE_RADICAL_M（部首、笔画）
-- SCHINESE_STROKE_M（笔画、部首 SCHINESE_PINYIN_M（拼音））
select nls_LOWER('ab cde') "test",nls_LOWER('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;


-- NLS_UPPER(x[,y]) 返回字符串并将字符串都变为小写
-- 指定排序的方式(nls_sort=) 。
-- SCHINESE_RADICAL_M（部首、笔画）
-- SCHINESE_STROKE_M（笔画、部首 SCHINESE_PINYIN_M（拼音））
select NLS_UPPER('ab cde') "test",NLS_UPPER('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;


-- INSTR(C1,C2[,I[,J]]),INSTRB(C1,C2[,I[,J]]) 在一个字符串中搜索指定字符，返回发现指定的字符的位置
/*
INSTR 多字节符(汉字、全角符等)，按1个字符计算
INSTRB 多字节符(汉字、全角符等)，按2个字符计算
C1    被搜索的字符串
C2    希望搜索的字符串
I     搜索的开始位置,默认为1
J     第J次出现的位置,默认为1
*/
select instr('I love oracle','ra',1,1) from dual;
select instr('我爱广西外国语学院','语',1,1) "instr",instrb('我爱广西外国语学院','语',1,1) "instrb" from dual;


-- length(x),lengthb(x) 返回字符串的长度
-- length 多字节符(汉字、全角符等)，按1个字符计算
-- lengthb 多字节符(汉字、全角符等)，按2个字符计算
select length('我爱广西外国语学院'),lengthb('我爱广西外国语学院')from dual;


-- LENGTHC(c1).LENGTH2(c1).LENGTH4(c1) 返回字符串的长度
-- 多字节符(汉字、全角符等)，按1个字符计算
/*
Length函数返回字符的个数，使用定义是给定的字符集来计算字符的个数
LENGTHB给出该字符串的byte
LENGTHC使用纯Unicode
LENGTH2使用UCS2
LENGTH4使用UCS4
*/
select length('你好'),lengthb('你好'),lengthc('你好'),length2('你好'), length4('你好')  from dual;


-- lpad(c1,n[,c2]) 在字符串c1的左边用字符串c2填充，直到填充长度为n时为止
/*
C1 字符串
n 追加后字符总长度
c2 追加字符串,默认为空格
如果c1长度大于n，则返回c1左边n个字符
如果如果c1长度小于n，c2和c1连接后大于n，则返回连接后的右边n个字符
*/
select lpad('abcd',10,'*') from dual;
select lpad('abcdfdsafdsa',10,'*') from dual;


-- 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个字符
*/
select rpad('abcd',10,'*') from dual;
select rpad('abcdefghijk',10,'*') from dual;


-- LTRIM(c1,[,c2]) 删除左边出现的字符
-- C1 字符串，c2 追加字符串,默认为空格
select ltrim('   abcdefg fdsa',' ') from dual;
select ltrim('  abcdefg fdsa') from dual;


-- RTRIM(c1,[,c2]) 删除左边出现的字符
-- C1 字符串，c2 追加字符串,默认为空格
select rtrim('   abcdefg fdsa****','*') from dual;
select rtrim('abcdefg fdsa   ') from dual;


-- replace(c1,c2[,c3]) 将字符表达式值中，部分相同字符串，替换成新的字符串
/*
c1   希望被替换的字符或变量 
c2   被替换的字符串
c3   要替换的字符串，默认为空(即删除之意，不是空格)
*/
select replace('he love you','he','I') from dual;


-- SOUNDEX(c1) 返回字符串参数的语音表示形式
/*
计算语音的算法： 
　　1.保留字符串首字母，但删除a、e、h、i、o、w、y 
　　2.将下表中的数字赋给相对应的字母 
　　(1) 1：b、f、p、v 
　　(2) 2：c、g、k、q、s、x、z 
　　(3) 3：d、t 
　　(4) 4：l 
　　(5) 5：m、n 
　　(6) 6：r 
　　3. 如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f），或者只有h或w，则删除其他的，只保留1个 
　　4.只返回前4个字节，不够用0填充 
　　示例： 
　　soundex('two'),soundex('too'),soundex('to')，他们的结果都是T000 
　　soundex('cap'),soundex('cup')，他们的结果都是C100 
　　soundex('house'),soundex('horse')，他们的结果都分别是H200，H620
*/
select soundex('two'),soundex('too'),soundex('to') from dual;
select soundex('cap'),soundex('cup') from dual;
select soundex('house'),soundex('horse') from dual;


-- SUBSTR(c1,n1[,n2])
/*
多字节符(汉字、全角符等)，按1个字符计算
在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第y个字符直到结束的字串.
*/
select substr('1387843147856',3,8) from dual; -- 从位置3开始，取8个字符


-- SUBSTRB(c1,n1[,n2]) 取子字符串
/*
多字节符(汉字、全角符等)，按2个字符计算
在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第y个字符直到结束的字串.
如果从多字符右边开始,则用空格表示。
*/
select substr('我手机13012345678',4,11),substrb('我手机13012345678',4,11),substrb('我手机13012345678',3,11) test from dual;


-- TRANSLATE(c1,c2,c3) 将字符表达式值中，指定字符替换为新字符
/*
c1   希望被替换的字符或变量 
c2   查询原始的字符集
c3   替换新的字符集，将c2对应顺序字符，替换为c3对应顺序字符
如果c3长度大于c2，则c3长出后面的字符无效
如果c3长度小于c2，则c2长出后面的字符均替换为空(删除)
如果c3长度为0，则返回空字符串。
如果c2里字符重复，按首次位置为替换依据
*/
select translate('he love you','he','I') "test",
translate('梧州的人','梧州的','梧州男') "test1",
translate('贺州的人','贺州的贺州','贺州男士们') "test2",
translate('钦州的人','钦州的钦州','1钦州男士们') "test3",
translate('桂林的人','1桂林的桂林','桂林男士们') "test4" from dual;


select translate('中国人',substr('中国人',1,length('中国人') - 1),rpad('*',length('中国人'),'*')) "License" from dual;


-- TRIM(c1 from c2) 删除左边和右边出现的字符串
/*
C2 删除前字符串
c1 删除字符串,默认为空格
*/
select TRIM('X' from 'XXXgao qian jingXXXX'),TRIM('X' from 'XXXgaoXXjingXXXX') text from dual;
-- ************************ 字符串函数 ******************************




-- ************************* 日期函数 *******************************
-- sysdate 返回系统当前日期和时间
select sysdate from dual;


-- add_months(d1,n1) 在日期d1基础上再加n1个月后新的日期
select add_months(sysdate,3) from dual;


-- last_day(d1) 返回日期d1所在月份最后一天的日期
select last_day(sysdate) from dual;


-- months_between(d1,d2) 返回d1,d2两个日期之间相隔的月数
/*
如果d1>d2，则返回正数
如果d1<d2，则返回负数
*/
select months_between(sysdate,to_date('2017-10-25','yyyy-MM-dd')) from dual;


-- NEW_TIME(dt1,c1,c2) 给出时间dt1，在c1和c2时区的日期和时间
/*
  c1,c2对应的 时区及其简写   
  大西洋标准时间：AST或ADT   
  阿拉斯加_夏威夷时间：HST或HDT   
  英国夏令时：BST或BDT   
  美国山区时间：MST或MDT   
  美国中央时区：CST或CDT   
  新大陆标准时间：NST   
  美国东部时间：EST或EDT   
  太平洋标准时间：PST或PDT   
  格林威治标准时间：GMT   
  Yukou标准时间：YST或YDT 
*/
select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') "北京时间",to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') "洛杉矶" from dual;
select sysdate "北京时间",new_time(sysdate,'PDT','GMT') "洛杉矶" from dual;


-- 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;


-- 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;


-- 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;


-- extract(c1 from d1) 日期/时间d1中，参数(c1)的值
/*
d1日期型(date)/日期时间型(timestamp),c1为字符型(参数)
c1对应的参数表详见示例
*/
select 
extract(hour from timestamp '2018-2-18 14:38:40' ) 小时,
extract(minute from timestamp '2018-2-18 14:38:40' ) 分钟,
extract(second from timestamp '2018-2-18 14:38:40' ) 秒,
extract(day from timestamp '2018-2-18 14:38:40' ) 日,
extract(month from timestamp '2018-2-18 14:38:40' ) 月,
extract(year from timestamp '2018-2-18 14:38:40' ) 年
from dual;


select extract (year from date '2018-2-18' ) from dual; 


select sysdate 当前日期,
extract(hour from timestamp '2018-2-18 14:38:40' ) 小时,
extract(day from sysdate ) 日,
extract(month from sysdate ) 月,
extract(year from sysdate ) 年
from dual;


-- localtimestamp 返回会话中的日期和时间
select localtimestamp from dual;


-- current_timestamp 以timestamp with time zone数据类型返回当前会话时区中的当前日期
select current_timestamp from dual;


-- current_date 返回当前会话时区中的当前日期 
select current_date from dual;


-- dbtimezone 返回时区
select dbtimezone from dual;


-- sessiontimezone 返回当前会话时区
select sessiontimezone from dual;


-- interval c1 set1 变动日期时间数值
/*
c1为数字字符串或日期时间字符串，set1为日期参数
日期时间格式的数值,前面多个+号
以天或天更小单位时可用数值表达式借用，如1表示1天，1/24表示1小时，1/24/60表示1分钟
*/
select
trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)
trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)
trunc(sysdate)+(interval '1' hour), --加1小时(1/24)
trunc(sysdate)+(interval '1' day),  --加1天(1)
trunc(sysdate)+(interval '1' month), --加1月
trunc(sysdate)+(interval '1' year), --加1年
trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒
trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒
trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟
trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟
from dual;
-- ************************* 日期函数 *******************************


-- ******************************************************************


-- ******************************************************************
-- chartorowid(c1) 转换varchar2类型为rowid值
/*
c1,字符串，长度为18的字符串，字符串必须符合rowid格式
返回rowid值
【说明】
在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。
在重复的记录中，可能所有列的内容都相同，但rowid不会相同.
*/
select chartorowid('AAAADeAABAAAAZSAAA') "rowid" from dual;


-- rowidtochar(rowid) 转换rowid值为varchar2类型
/*
rowid,固定参数
返回长度为18的字符串
【说明】
在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。
在重复的记录中，可能所有列的内容都相同，但rowid不会相同.
*/
select rowidtochar(rowid) "str" from dual;


-- 
/*
convert(c1,set1,set2)
将源字符串c1 从一个语言字符集set2转换到另一个目的set1字符集
c1,字符串，set1,set2为字符型参数
*/
select convert('strutz','we8hp','f7dec') "conversion" from dual;
select convert('name','us7ascii','zhs16cgb231280') "conversion" from dual;


-- hextoraw(c1) 将一个十六进制构成的字符串转换为二进制
-- c1,十六进制的字符串
select hextoraw('A123') from dual;


-- rawtohext(c1) 将一个二进制构成的字符串转换为十六进制
-- c1,二进制的字符串
select hextoraw('10111001') from dual;


-- to_date(X[,c2[,c3]]) 将字符串X转化为日期型
/*
c2,c3,字符型，参照to_char()
如果x格式为日期型(date)格式时，则相同表达：date x
如果x格式为日期时间型(timestamp)格式时，则相同表达：timestamp x
*/
select sysdate,
to_date('201802','yyyymm'),
to_date('2018.03.15','yyyy.mm.dd'),
(date '2018-03-18') XXdate, 
to_date('2018-03-18 17:31:30','yyyy-mm-dd hh24:mi:ss'),
(timestamp '2018-03-18 17:31:30') XXtimestamp
from dual;


-- to_char(date[,c2[,c3]]) 将日期型转化为字符串
select sysdate,
to_char(sysdate,'yyyy-mm-dd HH24:mi:ss') 
from dual;


-- to_number(X[[,c2],c3])
/*
将字符串X转化为数字型
c2,c3,字符型，参照to_char()
*/
select to_number('199912'),to_number('450.05') from dual;


-- avg([distinct|all]x) 统计数据表选中行x列的平均值
/*
all表示对所有的值求平均值,distinct只对不同的值求平均值，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。
*/
select avg(distinct sal),avg(all sal),avg(sal) from emp;


-- sum([distinct|all]x) 统计数据表选中行x列的合计值
select sum(distinct sal),sum(all sal),sum(sal) from emp;


-- max([distinct|all]x) 统计数据表选中行x列的最大值
select max(sal) from emp;


-- min([distinct|all]x) 统计数据表选中行x列的最小值
select min(sal) from emp;


-- stddev([distinct|all]x) 统计数据表选中行x列的标准误差。
select stddev(distinct sal),stddev(all sal),stddev(sal) from emp;


-- variance([distinct|all]x) 统计数据表选中行x列的方差。
/*
【参数】all表示对所有的值求方差,distinct只对不同的值求方差，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。
【参数】x，只能为数值型字段
【返回】数字值
【示例】
环境：
create table varian(xm varchar(8),sal number(7,2));
insert into varian values('gao',1111.11);
insert into varian values('gao',1111.11);
insert into varian values('zhu',5555.55);
commit;
*/
select variance(distinct sal),variance(all sal),variance(sal) from varian;


-- greatest(exp1,exp2,exp3,……,expn) 返回表达式列表中值最大的一个。如果表达式类型不同，会隐含转换为第一个表达式类型
/*
exp1……n，各类型表达式
*/
select greatest(10,32,'123','2006') from dual;
select greatest('kdnf','dfd','a','206') from dual;


-- least(exp1,exp2,exp3,……,expn) 返回表达式列表中值最小的一个。如果表达式类型不同，会隐含转换为第一个表达式类型
select least(10,32,'123','2006') from dual;
select least('kdnf','dfd','a','206') from dual;


-- nvl (expr1, expr2) 若expr1为NULL，返回expr2；expr1不为NULL，返回expr1
-- 注意两者的类型要一致 
select nvl(null,2) from dual;
select nvl(1,2) from dual;


-- nvl2 (expr1, expr2, expr3) expr1不为NULL，返回expr2；expr2为NULL，返回expr3
-- expr2和expr3类型不同的话，expr3会转换为expr2的类型 
select nvl2(null,2,3) from dual;
select nvl2(1,2,3) from dual;


-- nullif (expr1, expr2) expr1和expr2相等返回NULL，不相等返回expr1
select nullif(2,2) from dual;
select nullif(1,2) from dual;


-- decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值) 根据条件返回相应值
/*
c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null
注：值1……n 不能为条件表达式,这种情况只能用case when then end解决
含义解释：　　
　　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
*/
select e.ename,d.deptno,d.deptname,
  decode(d.deptno,10,'部门10',20,'部门20',30,'部门30',40,'部门40') 
  from emp e,dept d
where e.deptno = d.deptno;


-- coalesce(c1, c2, ...,cn) 返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值
/* 作用跟nullif相似
【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null
【返回】同参数类型
【说明】从Oracle 9i版开始，coalesce函数在很多情况下就成为替代CASE语句的一条捷径
*/
select coalesce(null,3*5,44) hz from dual; -- 返回15
select coalesce(0,3*5,44) hz from dual; -- 返回0
select coalesce(null,'','AAA') hz from dual; -- 返回AAA
select coalesce('','AAA') hz from dual; -- 返回AAA


-- rownum 返回当前行数
select rownum,ename,sal from emp;


-- UTL_INADDR.get_host_name返回环境中主机名 返回本机主机名
select utl_inaddr.get_host_name() from dual;
select UTL_INADDR.get_host_address('www.qq.com') from dual;