Oracle 常用函数

Oracle 常用函数

Oracle 数据库主要有两种类型的函数：

1.单行函数

操作一行数据，返回一个结果

2.聚合函数

操作多行数据，并返回一个结果

一、数值型函数

1.返回绝对值
abs（x）；
示例：
select abs( 100 ) , abs( -100) from dual;
2.返回正负值
sign（x）；
示例：
select sign( 100 ) , sign( -100) , sign( 0) from dual;
3.返回较大的最小整数
ceil（x）；返回大于等于X的最小整数值
示例：
select ceil( 3.1 ) , ceil( 2.8+1.3 ) , ceil( 0 ) from dual;
返回：4，5，0
4.返回较小的最大整数
floor（x）；返回小于等于X的最大整数值
示例：
select floor( 3.1 ) , floor( 2.8+1.3 ) , floor( 0 ) from dual;
返回：3，4，0
5.返回x的y次幂
power（x,y）；
示例：
select power(2,2) , power(1,0) ,power(10,-1 ) from dual;
返回：4，1，0.1
6.返回x除以y的余数
mod（x,y）；
示例：
select mod( 23,8) , mod( 24,8) from dual;
返回：7，0
7.返回四舍五入后的值
round（x）；
示例：
select round( 5.6) , round( 5.4) from dual;
返回：6，5
8.返回截取后的值
trunc（x,y）；返回x按精度y截取后的值
示例：
select trunc( 5.6) , trunc( 5.6，1) , trunc( 5.44，2) from dual;
返回：5，5.6，5.44
9.返回x的平方根
sqrt（x）；
示例：
select sqrt( 64) , sqrt( 9) from dual;
返回：8，3

二、字符类型函数

1.返回字符的ASCII码
ASCII（x）；
示例：
select ascii(‘A’) A,ascii(‘a’) a,ascii(’ ') space from dual;
返回 ：A a space
------ ------ ---------
65 97 32

2.返回ASCII码为x的字符
chr（x）；
示例：
select chr( 54740) zhao , chr( 65) c from dual;
返回：zhao c
---- —
赵 A

3.连接两个字符串
concat（c1,c2）；
示例：
select concat( ‘010-’ , ‘888888’) from dual;
返回：010-888888
4.把每个单词首字母变为大写
initcap（c1）；
示例：
select initcap(‘smith’) upp from dual;
返回：Smith
5.将整个字符串转换为小写
lower（c1）；
示例：
select lower( ‘AaBbCcDd’) AaBbCcDd from dual;
返回：aabbccdd
6.连接两个字符串
upper（c1）；
示例：
select upper( ‘AaBbCcDd’) upper from dual;
返回：AABBCCDD
7.字符串搜索字符位置
instr（c1,c2）；
示例：
select concat( ‘010-’ , ‘888888’) from dual;
返回：010-888888
8.返回字符串的长度
length（c1）；
示例：
select length( ‘aaaaa’) from dual;
返回：5
9.在左边添加字符
lpad（c1,n,c2）；在字符串c1的左边用字符串c2填充，直至长度为n时为止
示例：
select lpad( ‘10’ , 5,‘a’) from dual;
返回：aaa10
10.在右边添加字符
rpad（c1,n,c2）；
示例：
select rpad( ‘10’ , 5,‘a’) from dual;
返回：10aaa
11.删除左边字符串
ltrim（c1,c2）；
示例：
select ltrim( ‘10aaa’,‘10’) from dual;
返回：aaa
12.删除右边字符串
rtrim（c1,c2）；
示例：
select rtrim( ‘10aaa’,‘aaa’) from dual;
返回：10
13.替换子字符串
replace（c1,c2,c3）；
示例：
select replace( ‘aabbcc’,‘aa’,‘10’) from dual;
返回：10bbcc
14.截取子字符串
substr（c1,n1,n2）；
示例：
select substr( ‘12345’,2,4) from dual;
返回：2345
15.替换子字符串
translate（c1,c2，c3）；将字符表达式值中，指定字符替换为新字符
示例：
select translate( ‘abc’,‘ab’,‘e’) from dual;
返回：ec
16.删除左边和右边的字符串
trim（c1 from c2）；
示例：
select trim( ‘aa’ from ‘aa10bb’) from dual;
返回：10bb

三、日期函数

1.返回系统当前日期
sysdate；
示例：
select sysdate hz from dual;
返回：2021-8-24
2.返回指定月数后的日期
add_months（d1,n1）；
示例：
select sysdate ,add_months（sysdate ,3）hz from dual;
返回：2021-8-24,2021-11-24
3.返回本月最后一天的日期
last_day（d1）；
示例：
select sysdate, last_day(sysdate) hz from dual;
返回：2021-2-8-24,2021-8-31
4.返回两个日期间隔的月数
months_between（d1,d2）；
示例：
select months_between(sysdate,to_date(‘2006-01-01’,‘YYYY-MM-dd’)) from dual;
返回：187
5.四舍五入后的期间第一天
round（d1,c1）；
示例：
select sysdate 当时日期,
round(sysdate) 最近0点日期,
round(sysdate,‘day’) 最近星期日,
round(sysdate,‘month’) 最近月初,
round(sysdate,‘q’) 最近季初日期,
round(sysdate,‘year’) 最近年初日期 from dual;
6.返回日期所在期间的第一天
trunc（d1,c1）；
示例：
select sysdate 当时日期,
trunc(sysdate) 今天日期,
trunc(sysdate,‘day’) 本周星期日,
trunc(sysdate,‘month’) 本月初,
trunc(sysdate,‘q’) 本季初日期,
trunc(sysdate,‘year’) 本年初日期 from dual;
7.返回下周某一天的日期
next_day（d1,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）；
示例：
select
extract(hour from timestamp '2001-2-16 2:38:40 ’ ) 小时,
extract(minute from timestamp '2001-2-16 2:38:40 ’ ) 分钟,
extract(second from timestamp '2001-2-16 2:38:40 ’ ) 秒,
extract(DAY from timestamp '2001-2-16 2:38:40 ’ ) 日,
extract(MONTH from timestamp '2001-2-16 2:38:40 ’ ) 月,
extract(YEAR from timestamp '2001-2-16 2:38:40 ’ ) 年
from dual;
select extract (YEAR from date ‘2001-2-16’ ) from dual;
select sysdate 当前日期,
extract(hour from timestamp timestamp sysdate) 小时,
extract(DAY from sysdate ) 日,
extract(MONTH from sysdate ) 月,
extract(YEAR from sysdate ) 年
from dual;
9.返回当前会话时区中的当前日期
current_date；
示例：
select current_date from dual;
返回： 2021-8-24

四、转换函数

1.数字或者日期转换为字符串
to_char(x)；
2.字符串转化为日期型
to_date（x）；
示例：
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;
3.字符串转换为数字型
to_number(x)；
示例：
select to_number(‘199912’),to_number(‘450.05’) from dual;

五、聚组函数

1.平均值
avg（ ）；
2.合计值
sum（ ）；
3.标准误差
stddev( )；
4.方差
variance( )；
5.查询所得行数
count( )；
6.最大值
max（ ）；
7.最小值
min（ ）；

六、分析函数

1.连续求和分析函数
sum(…) over …；
2.排序值分析函数
rank()
dense_rank()；
3.排序后顺序号分析函数
row_number()；
4.取上下行数据分析函数
lag()和lead()；

七、其它函数

1.为空值赋值
nvl(expr1, expr2)；
若expr1为null，返回expr2；expr1不为null，返回expr1。
注意两者的类型要一致
2.条件取值
decode（）；
根据条件返回相应值
3.相等返回空
NULLIF (expr1, expr2)；
expr1和expr2相等返回NULL，不相等返回expr1
4.条件取值
case [<表达式>]
when <表达式条件值1> then <满足条件时返回值1>
[when <表达式条件值2> then <满足条件时返回值2>
……
[else <不满足上述条件时返回值>]]
end

5.返回系统数据
SYS_CONTEXT(c1,c2)；
返回系统c1对应的c2的值。可以使用在SQL/PLSQL中，但不可以用在并行查询或者RAC环境中
6.生成随机数值或者字符串dbms_random
1.dbms_random.value方法
2. dbms_random.string 方法
3. dbms_random.random 方法
4. dbms_random.normal方法
5. dbms_random.send方法