19个常用Oracle内置函数

数据运维 2023-04-16 贤蛋大眼萌 手机阅读

  1. nvl(expression1, expression2) 函数作用：从两个表达式中返回一个非null值 用例：select nvl(father_name, mother_name) parent_name from student where student_id = 12345 注意： 如果expression1的值非空，优先取e

    1. nvl(expression1, expression2)<p>函数作用：从两个表达式中返回一个非null值</p>

    用例：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都有不同的含义，如下表所示

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

    用例：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函数是一个分析函数，和聚合函数搭配在一起使用可以简洁代码

    用例：

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

    11. nlssort

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

    用例：

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

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

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

    注意：

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

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

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

    注意：dense_rank()用法和rank()一样，区别在于排名是否跳跃

    14. substr(source, start [,length])

    函数作用：截取字符串

    用例：select substr('abcde', 2, 3) from dual

    注意：oracle字符串索引从1开始

    15. replace(field_name, sub_str, replace_str)

    函数作用：将指定的字符串替换为指定的字符串

    用例：select replace(name, 'hello', 'world') from student;

    注意：也可以用在update语句set部分

    16. trim

    函数作用：去掉左右两端的空白字符

    用例：select trim(' dsf ') from dual;

    注意：仅去掉左边空白字符用ltrim，仅去掉右边空白字符用rtrim

    17. sign

    函数作用：取数字n的符号,大于0返回1, 小于0返回-1, 等于0返回0

    用例：select sign(50),sign(-100),sign(0) from dual;

    18. round(number[,decimal])

    函数作用：对数字n进行四舍五入处理，保留decimal位小数

    用例：select round(123.34),round(123.51),round(123.56,1),round(123.34,-1) from dual;

    19. coalesce(expression1,expression2...)

    函数作用：返回表达式中第一个不为空的值，如果全为空则返回空值

    用例：select coalesce(null,3+5,4+6) value from dual;

    推荐教程：《Oracle教程》

    以上就是19个常用Oracle内置函数的详细内容，更多请关注每日运维其它相关文章！