Oracle常用内置函数

Howe_me
60 阅读8分钟

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

NVL2(expr,expr1,expr2) 如果表达式 expr 不为null，则返回 expr1;若为 null，则返回expr2

NULLIF(expr1,expr2) 比较俩个表达式，如果相等返回空值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. to_date(source_string, formater_string)

函数作用：将字符串转换为日期类型
用例：select to_date('20190809','yyyyMMdd') from dual;
注意：
'yyyyMMdd','yyyymmdd','yyyy-MM-dd','yyyy-mm-dd'都可以

4. trunc

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

select trunc(sysdate) from dual -- 2017/6/13  返回当天的日期
 
select trunc(sysdate,'yyyy') from dual   -- 2017/1/1  返回当年第一天.
 
select trunc(sysdate,'mm') from dual  -- 2017/6/1  返回当月第一天.
 
select trunc(sysdate,'d') from dual  -- 2017/6/11 返回当前星期的第一天(以周日为第一天).
 
select trunc(sysdate,'dd') from dual  -- 2017/6/13  返回当前年月日
 
select trunc(sysdate,'hh') from dual  -- 2017/6/13 13:00:00  返回当前小时
 
select trunc(sysdate,'mi') from dual  -- 2017/6/13 13:06:00  返回当前分钟


select trunc(126.56, 0) from dual; -- 126
select trunc(126.56, 1) from dual; -- 126.5

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

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

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

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

实例：

select trunc(date '2022-04-01'),trunc(date '2024-04-25' + 1) from dual;
-- 结果 2022-04-01	2024-04-2

5. TIMESTAMP

TIMESTAMP数据类型用于存储日期和时间数据，包括年，月，日，时，分和秒。

另外，它存储小数秒，它不是由DATE数据类型存储的。

要定义TIMESTAMP列，请使用以下语法：

column_name TIMESTAMP[(fractional_seconds_precision)]

fractional_seconds_precision指定SECOND字段小数部分的位数。它的范围从0到9，这意味着可以使用TIMESTAMP数据类型来存储到纳秒的精度。
如果省略fractional_seconds_precision，则默认为6。

要指定TIMESTAMP文字，请使用以下格式：

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

以下示例说明了TIMESTAMP文字：

TIMESTAMP '1999-12-31 23:59:59.10'

6. 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带来的空格

7. wm_concat、listagg

-- 函数作用：行转列，将多行查询结果聚合到一行的某一列中
select wm_concat(distinct name) from student
-- 注意：高版本oracle可能会去掉

select listagg(F_ROLE_ID) within group ( order by HD_USER_ROLE.F_CREATE_TIME)
from HD_USER
         left join HD_USER_ROLE on F_USER_ID = HD_USER.F_ID
where HD_USER.F_ID = '1'

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. rank() over(partition by field_name order by field_name)

函数作用：让返回结果根据分区和排序字段产生排名关系
注意：dense_rank()用法和rank()一样，区别在于排名是否跳跃

select rank() over(partition by birthday order by score), s.* from student s;
select F_COMPANYID,rank() over (partition by F_COMPANYNAME order by F_COMPANYID) from T_E02_COMPANY;

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

函数作用：截取字符串
注意：oracle字符串索引从1开始

select substr('abcde', 2, 3) from dual; -- bcd

14. replace(field_name, sub_str, replace_str)

函数作用：将指定的字符串替换为指定的字符串
用例：select replace(name, 'hello', 'world') from student;
注意：也可以用在update语句set部分

15. trim

函数作用：去掉左右两端的空白字符
用例：select trim(' dsf ') from dual;
注意：仅去掉左边空白字符用ltrim，仅去掉右边空白字符用rtrim

16. sign

函数作用：取数字n的符号,大于0返回1, 小于0返回-1, 等于0返回0
用例：select sign(50),sign(-100),sign(0) from dual;
结果：1,-1,0

17. round(number[,decimal])

函数作用：对数字n进行四舍五入处理，保留decimal位小数
用例：select round(123.34), round(123.51), round(123.46), round(123.56, 1), round(123.34, -1) from dual;
结果：123,124,123,123.6,120

18. coalesce(expression1,expression2...)

函数作用：返回表达式中第一个不为空的值，如果全为空则返回空值
用例：select coalesce(null,3+5,4+6) value from dual;

19. 字符函数

LOWER ---> 转换为小写  select * from emp where lower(ename) = 'allen';
UPPER ---> 转换为大写select * from emp where ename = UPPER('smith');
INITCAP ---> 首字母大写。其他小写SELECT ENAME,initcap(ENAME) FROM EMP WHERE initcap(ENAME) = 'Smith';

20. row_number() over()分组排序功能

row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行

1、对查询结果进行排序（无分组）

select id,name,age,salary,row_number()over(order by salary desc) rn
from TEST_ROW_NUMBER_OVER t

结果：

2、对结果进行分组排序

select F_TABLE_NAME, F_CREATE_TIME, row_number() over (partition by F_TABLE_NAME order by F_CREATE_TIME desc)
from HD_BKYJ_SYNC_QUEUE
where F_BUS_TYPE like 'A01%'
   or F_BUS_TYPE like '%A05'

结果：

21. JSON_TABLE、JSON_VALUE、JSON_OBJECT、JSON_ARRAYAGG函数

JSON_TABLE：将数据库中存取的JSON或数组结构转换为表之后提取表中字段；

JSON_VALUE：将存储的JSON或数组结构【第一项】内容属性提取出来；

SELECT listagg(colum_1)
FROM 
 json_table('[
                   {
                     "id": "08b9619b31c44b219de97268aee3aa37",
                     "groupId": "233767f8475b4e19b23460c904ffa026",
                     "measureContent": "1.病室保持安静，避免一切不必要刺激，治疗、护理操作尽量集中进行，动作轻柔敏捷",
                     "problemId": "",
                     "creatorDeptCode": "",
                     "noleaf": false
                   },
                   {
                     "id": "d0b614390c784640bb317f0037a1035c",
                     "groupId": "233767f8475b4e19b23460c904ffa026",
                     "measureContent": "1.加强巡视病房，密切观察患儿病情变化，抽搐发作时防止碰伤。  ",
                     "problemId": "",
                     "creatorDeptCode": "",
                     "noleaf": false
                   }
                 ]', 
       '$[*]' columns (colum_1 VARCHAR2(2000) PATH '$.measureContent'));

SELECT JSON_ARRAYAGG(  
  JSON_OBJECT(  
    'employee_id' VALUE employee_id,  
    'first_name' VALUE first_name,  
    'last_name' VALUE last_name,  
    'email' VALUE email,  
    'hire_date' VALUE TO_CHAR(hire_date, 'YYYY-MM-DD')  
  )  
) AS employees_json  
FROM employees;

SELECT JSON_OBJECT(
    KEY 'eventNo' VALUE (SELECT COL_VALUE FROM ZOEPRES.PRES_NARCO_RECORD WHERE COL_NAME = 'eventNo' and PRES_NO = '50007468'),
    KEY 'patientName' VALUE (SELECT COL_VALUE FROM ZOEPRES.PRES_NARCO_RECORD WHERE COL_NAME = 'patientName' and PRES_NO = '50007468')
) AS json_result
FROM DUAL;
avatar
文章
阅读
粉丝