19个常用Oracle内置函数_非关系型数据库

19个常用Oracle内置函数 1. nvl(expression1, expression2)

函数作用：从两个表达式中返回一个非null值

用例：select nvl(father_name, mother_name) parent_name from student where student_id = '12345'

注意：

如果expression1的值非空，优先取expression1的值；

如果expression1的值空且expression2的值非空，则取expression2的值；

如果expression1和expression2均为空，则结果为NULL

2. decode(field_name, value1, new_value1, value2, new_value2, default_value)

函数作用：类似if...else...语句块，针对某个字段，如果它的值为value1,则转换为newValue1，如果值为value2，则转换为newValue2，其他情况显示默认值

用例：select decode(id,'1','A','2','B',id) from A;

注意：

decode(field_name, value1, new_value1, value2, new_value2, default_value)其中的value1，newValue1等可以是一个表达式

3. row_number(order by field_name)

函数作用：将数据集按照某个字段排序，并产生序号字段

用例：select row_number() over(order by name) no,id,name from a;

4. to_date(source_string, formater_string)

函数作用：将字符串转换为日期类型

用例：select to_date('20190809','yyyyMMdd') from dual;

注意：

'yyyyMMdd','yyyymmdd','yyyy-MM-dd','yyyy-mm-dd'都可以

5. to_char()

函数作用：将其他类型转换为字符串类型

用例1：select to_char(sysdate, 'yyyymmdd') from dual

用例2：select to_char(99, 'fm999.00') from dual

注意：

用例1中还有很多其他的日期格式，如yyyy,mm,dd,D,DD,DDD等

用例2中fm,9,0都有不同的含义，如下表所示

字符标志含义9如果存在数字则显示数字，不存在则显示空格0如果存在数字则显示数字，不存在则显示0，即占位符fm删除因为9带来的空格

6. wm_concat

函数作用：行转列，将多行查询结果聚合到一行的某一列中

用例：select wm_concat(distinct name) from student

注意：高版本oracle可能会去掉

7. listagg() within group(order by field_name) over(partition by field_name)

函数作用：同wm_concat

用例：select listagg(distinct name) within group(order by name desc) from student

8. concat(expression1, expression2)

函数作用：字符串拼接函数

用例：select concat('left', 'Right') from dual

注意：也可以利用 || 进行拼接，select 'a'||'b' from dual

9. sys_guid()

函数作用：产生并返回一个全球唯一的标识符(原始值)由16个字节组成，32个字符

用例：select sys_guid() from dual;

注意：经常用来做表的主键

10. over(partition by field_name, order by field_name)

函数作用：over函数是一个分析函数，和聚合函数搭配在一起使用可以简洁代码

用例：

select name, job, sal, deptno,
sum(sal) over(partition by deptno) sum_sal,     --统计某组中的总计值
avg(sal) over(partition by deptno) avg_sal,     --统计某组中的平均值
from emp;

注意：按照以前的写法先进分组统计产生临时表关联原表才可以取到其他信息，现在则不需要了

通常和max(),min(),avg(),sum()等聚合函数一起使用

11. nlssort

函数作用：提供简体中文的特殊排序

用例：

select * from student order by nlssort(name, 'nls_sort = schinese_pinyin_m') --拼音
select * from team order by nlssort(name, 'nls_sort = schinese_stroke_m')    --笔画
select * from team order by nlssort(name, 'nls_sort = schinese_radical_m')   --部首

12. trunc

函数作用：是截取日期或数字，根据规则返回指定的值

用例1：select trunc(sysdate, 'yyyy') from dual

用例2：select trunc(126.56, 0) from dual

注意：

用例1还有其他规则代表不同的含义

规则含义mm返回当月第一天yy返回当年第一天dd返回当前年月日yyyy返回当年第一天d返回当前星期的第一天

用例2还有其他规则代表不同含义

规则含义0默认值，取下取整正数a保留小数点后面a位小数，其他的抹掉，如果a比小数点后的位数多，则保留原值负数b小数点后面的全部去掉，小数点向左移动abs(b)位，用0代替被抹掉的数字，如果所有数字都被抹掉，则返回0

13. rank() over(partition by field_name order by field_name)

函数作用：让返回结果根据分区和排序字段产生排名关系

用例：select rank() over(partition by birthday order by score), s.* from student s;