Oracle 常用函数

Oracle 常用函数

这里主要是 oracle 中的一些常用函数及示例，一是为了和大家分享，二是可以在以后工作中忘记了随时查阅，希望可以帮助到大家哦！！！

一、单行函数

只处理单个行，并且为每行返回一个结果。

1.字符函数

（1）concat(str1,str2)

字符串拼接函数

select concat('Hello ','World') from dual;
--等价于
select 'Hello '||'World' from dual;

（2）initcap(str)

将每个单词首字母大写，其他字母小写

select initcap('hello world!') from dual; --返回结果为'Hello World!'
select initcap('HELLO WORLD!') from dual; --返回结果为'Hello World!'

（3）instr(x,find_string[,start][,occurrence])

返回指定字符串在某字符串中的位置，可以指定搜索的开始位置和返回第几次搜索出来的结果

----------搜索时下标从1开始计算
select instr('Hello World!','o') from dual;--从1位置开始搜索，返回第一次出现的o的位置，结果为5
select instr('Hello World!','o',6) from dual;--从6位置开始搜索，返回第一次出现的o的位置，结果为8
select instr('Hello World!','o',1,2) from dual;--从1位置开始搜索，返回第二次出现o的位置，结果为8

（4）length(str)

返回表达式中的字符数

select length('Hello World!') from dual;--返回结果为12
select length('张三') from dual;--返回结果为2

（5）lengthb(str)

返回表达式中的字节数

select lengthb('Hello World!') from dual;--返回结果为12
select lengthb('张三') from dual;--返回结果为6

（6）lower(str)

将字符串转换为小写

select lower('Hello World!') from dual;

（7）upper(str)

将字符串转换为大写

select upper('Hello World!') from dual;

（8）lpad(str,width[,pad_string])

当字符串长度不够时，左填充补齐，可以指定补齐时用什么字符补齐,若不指定，则以空格补齐

select lpad('Hello World!',20) from dual;--返回结果为'        Hello World!'
select lpad('Hello World!',20,'*') from dual;--返回结果为'********Hello World!'

（9）rpad(str,width[,pad_string])

当字符串长度不够时，右填充补齐，原理同左填充

select rpad('Hello World!',20) from dual;--返回结果为'Hello World!        '
select rpad('Hello World!',20,'*+') from dual;--返回结果为'Hello World!*+*+*+*+'

（10）ltrim(x[,trim_string])

从字符串左侧去除指定的所有字符串，若没有指定去除的字符串，则默认去除左侧空白符

select ltrim('   Hello World!    ') from dual;--返回结果为'Hello World!    '
select ltrim('***+*Hello World!***+*','*+') from dual;--返回结果为'Hello World!***+*'

（11）rtrim(x[,trim_string])

从字符串右侧去除指定的所有字符串，原理同ltrim()

select rtrim('   Hello World!    ') from dual;--返回结果为'    Hello World!'
select rtrim('***+*Hello World!***+*','*+') from dual;--返回结果为'***+*Hello World!'

（12）trim(trim_string from x)

从字符串两侧去除指定的所有字符串

select trim('*+' from '***+*Hello World!***+*') from dual;
-- 注意，ltrim()和rtrim()的截取集可以使多个字符，但trim的截取集只能有一个字符
select trim('*+' from '***+*Hello World!***+*') from dual;--错误，截取集只能有一个字符

（13）nvl(x,value)

将一个NULL转换为另外一个值，如果x为NULL,则返回value,否则返回x值本身

insert into student values(7,'猪猪',default,NULL);
select nvl(address,'北京市') from student;

（14）nvl2(x,value1,value2)

如果x不为NULL，返回value1，否则，返回value2

select nvl2(address,'有地址','无地址') from student;

（15）replace(x,search_string,replace_string)

从字符串x中搜索search_string字符串，并使用replace_string字符串替换。并不会修改数据库中原始值

select replace('Hello World!','o','HA') from dual;

（16）substr(x,start[,length])

格式1：substr(string string, int a, int b);
格式2：substr(string string, int a) ;

解释

• 格式1：
  1、string 需要截取的字符串
  2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
  3、b 要截取的字符串的长度

• 格式2：
  1、string 需要截取的字符串
  2、a 可以理解为从第a个字符开始截取后面所有的字符串。

注意:

返回字符串中的指定的字符，这些字符从字符串的第start个位置开始，长度为length个字符；如果start是负数，则从x字符串的末尾开始算起；如果 length 省略，则将返回一直到字符串末尾的所有字符

select substr('Hello World',3) from dual; --返回结果为'llo World'
select substr('Hello World',-3) from dual;--返回结果为'rld'
select substr('Hello World',3,2) from dual;--返回结果为'll'
select substr('Hello World',-7,4) from dual;--返回结果为'o Wo'

实例

1、select substr(‘HelloWorld’,0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
2、select substr(‘HelloWorld’,1,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
3、select substr(‘HelloWorld’,2,3) value from dual; //返回结果：ell，截取从“e”开始3个字符
4、select substr(‘HelloWorld’,0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
5、select substr(‘HelloWorld’,5,3) value from dual; //返回结果：oWo
6、select substr(‘Hello World’,5,3) value from dual; //返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)
7、select substr(‘HelloWorld’,-1,3) value from dual; //返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。原因：下面红色 第三个注解）
8、select substr(‘HelloWorld’,-2,3) value from dual; //返回结果：ld （从后面倒数第二位开始往后取2个字符，而不是3个。原因：下面红色 第三个注解）
9、select substr(‘HelloWorld’,-3,3) value from dual; //返回结果：rld （从后面倒数第三位开始往后取3个字符）
10、select substr(‘HelloWorld’,-4,3) value from dual; //返回结果：orl （从后面倒数第四位开始往后取3个字符）

注意：

1. 当a等于0或1时，都是从第一位开始截取(如：1和2)）
2. 假如HelloWorld之间有空格，那么空格也将算在里面(如：5和6)）
3. 虽然7、8、9、10截取的都是3个字符，结果却不是3 个字符； 只要 |a| ≤ b,取a的个数(如：7、8、9)；当 |a| ≥ b时，才取b的个数，由a决定截取位置(如：9和10)）

11、select substr(‘HelloWorld’,0) value from dual; //返回结果：HelloWorld，截取所有字符
12、select substr(‘HelloWorld’,1) value from dual; //返回结果：HelloWorld，截取所有字符
13、select substr(‘HelloWorld’,2) value from dual; //返回结果：elloWorld，截取从“e”开始之后所有字符
14、select substr(‘HelloWorld’,3) value from dual; //返回结果：lloWorld，截取从“l”开始之后所有字符
15、select substr(‘HelloWorld’,-1) value from dual; //返回结果：d，从最后一个“d”开始 往回截取1个字符
16、select substr(‘HelloWorld’,-2) value from dual; //返回结果：ld，从最后一个“d”开始 往回截取2个字符
17、select substr(‘HelloWorld’,-3) value from dual; //返回结果：rld，从最后一个“d”开始 往回截取3个字符

注意当只有两个参数时；不管是负几，都是从最后一个开始 往回截取(如：15、16、17)）

2.数值函数

（1）abs(value)

返回value的绝对值

select abs(-10) from dual;--返回结果为10

（2）ceil(value)

返回大于等于value的最小整数

select ceil(2.3) from dual; --返回结果为3

（3）floor(value)

返回小于等于value的最大整数

select floor(2.3) from dual; --返回结果为2

（4）trunc(value,n)

对value进行截断，如果n>0，保留n位小数；n<0，则保留-n位整数位；n=0，则去掉小数部分

select trunc(555.666) from dual; --返回结果为555，不加n时默认去掉小数部分
select trunc(555.666,2) from dual;--返回结果为555.66
select trunc(555.666,-2) from dual;--返回结果为500

（5）round(value,n)

对value进行四舍五入，保存小数点右侧的n位。如果n省略的话，相当于n=0的情况

select round(555.666) from dual;--返回结果为556，不加n时默认去掉小数部分
select round(555.666,2) from dual;--返回结果为555.67
select round(555.666,-2) from dual;--返回结果为600

注意

1. trunc和round用法类似，只不过trunc是硬生生截取，并不进行四舍五入，而round进行截取时四舍五入
2. 都还可以对日期的截取，可以参考写的日期函数笔记

select round(sysdate,'year') from dual;
select trunc(sysdate,'year') from dual;

3.转换函数

将值从一种类型转换成另外一种类型，或者从一种格式转换为另外一种格式

（1）to_char(x[,format]):

将x转化为字符串。 format为转换的格式，可以为数字格式或日期格式

select to_char('12345.67') from dual; --返回结果为12345.67
select to_char('12345.67','99,999.99') from dual; --返回结果为12,345.67

（2）to_number(x [, format])

将x转换为数字。可以指定format格式

select to_number('970.13') + 25.5 from dual;
select to_number('-$12,345.67', '$99,999.99') from dual;

（3）cast(x as type)

将x转换为指定的兼容的数据库类型

select cast(12345.67 as varchar2(10)),cast('05-7月-07' as date), cast(12345.678 as number(10,2)) from dual;

（4）to_date(x [,format])

将x字符串转换为日期

select to_date('2012-3-15','YYYY-MM-DD') from dual

二.聚集函数

1.常用函数

（1）avg(x)：

返回x的平均值

select avg(grade) from sc;

（2）count(x)

返回统计的行数

select count(name) from sc;

（3）max(x)

返回x的最大值

select max(grade) from sc;

（4）min(x)

返回x的最小值

select min(grade) from sc;

（5）sum(x)

返回x的总计值

select sum(grade) from sc;

2.对分组行使用聚集函数

对分组后的行使用聚集函数，聚集函数会统计每组中的值，对于每组分别统计后返回一个值。

示例

--按照职位分组，求出每个职位的最高和最低工资
select job ,max(sal),min(sal) from emp 
group by job 
order by job;

注意：

1. 分组时select子句后边的列名必须与group by子句后的列名一致，除非是聚合函数

select deptno,avg(sal) from EMP;--错误，因为deptno不是聚集函数，也不是group by后面跟的列名

2. 不能使用聚集函数作为WHERE子句的筛选条件

select deptno from emp where avg(sal)>1000;--错误

3. 分组后，需要使用条件进行筛选，则使用having过滤分组后的行，不能使用where，where只能放在group by前面。

select deptno, avg(sal) from emp where deptno<>10 
group by deptno  
having avg(sal) > 900;

关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。