Oracle常用函数【建议收藏】

article/2025/11/10 2:22:53 
作者：IT邦德
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
（Web\java\Python）工作，主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase（OBCA）认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证
从业8年DBA工作，在数据库领域有丰富的经验
B站主播Oracle、Mysql、PG实战课程，请搜索：jeames007擅长Oracle数据库运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。

在这里插入图片描述

一、概览

INITCAP
返回字符串并将字符串的第一个字母变为大写INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 从哪个位置开始找,默认为1，
J 第几次出现,默认为1
SCOTT@ORCL> select instr('oracle traning','ra',1,2) instring from dual;INSTRING
----------9LENGTH:返回字符串的长度；
LOWER: 返回字符串,并将所有的字符小写；
UPPER: 返回字符串,并将所有的字符大写；RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SCOTT@ORCL> select lpad(rpad('gao',10,'*'),17,'*') string from dual;STRING
-----------------
*******gao*******LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SCOTT@ORCL> select ltrim(rtrim(' gao qian jing ',' g'),' ') string from dual;STRING
------------
gao qian jinSUBSTR(string,start,count)
取子字符串,从start开始（包括开始）,取count个
SCOTT@ORCL> select substr(13088888888,3,8) string from dual;STRING
--------
08888888REPLACE(string,s1,s2)
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SCOTT@ORCL> select replace('he love you','he','I') string from dual;STRING
----------
I love youABS：返回指定值的绝对值
CEIL：返回大于或等于给出数字的最小整数
SCOTT@ORCL>  select ceil(3.1415927) from dual;CEIL(3.1415927)
---------------4FLOOR:对给定的数字取整数
SCOTT@ORCL> select floor(2345.67) from dual;FLOOR(2345.67)
--------------2345MOD(n1,n2)
返回一个n1除以n2的余数
SCOTT@ORCL> select mod(10,3),mod(3,3),mod(2,3) from dual;MOD(10,3)   MOD(3,3)   MOD(2,3)
---------- ---------- ----------1          0          2ROUND和TRUNC
按照指定的精度进行舍入
round(55.5) = 56
round(55.4) = 55
trunc(sysdate,'yyyy') --返回当年第一天
trunc(sysdate,'mm') --返回当月第一天
trunc(sysdate,'day') --返回当前星期的第一天（周日）
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89 (即取整)
TRUNC（89.985，-1）=80日期函数
SQL> select  to_char(add_months(to_date('202012','yyyymm'),2),'yyyymm') datum from dual;   --增加或减去月份
DATUM
------
202102SQL> select last_day(sysdate) datum from dual;   --返回本月的最后一天
DATUM
-----------
2020/12/31SQL> select  months_between(to_date('20000520','yyyymmdd'),to_date('20050520','yyyymmdd')) mon_betw from dual;  --月份差异MON_BETW
-----------60SQL> select next_day( sysdate, 'MONDAY') from dual;   --当前日期的下个星期一
NEXT_DAY(SYSDATE,'MONDAY')
--------------------------
2020/12/21 6:52:42GREATEST：返回一组表达式中的最大值,即比较字符的编码大小
LEAST：返回一组表达式中的最小值STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.503223pl/sql中的case语句
select  (case  when  DUMMY='X'  then  0  else  1  end)   as  flag  from  dual;
1.case的第1种用法：
case col when 'a' then 1
when 'b' then 2
else 0 end
这种用法跟decode一样没什么区别
2.case的第2种用法：
case when score <60 then 'd'
when score >=60 and score <70 then 'c'
when score >=70 and score <80 then 'b'
else 'a' endNVL(expr1, expr2)
expr1为NULL，返回expr2，不为NULL，返回expr1，注意两者的类型要一致NVL2 (expr1, expr2, expr3) 
expr1不为NULL，返回expr2,为NULL,返回expr3,expr2和expr3类型不同的话，expr3会转换为expr2的类型NULLIF (expr1, expr2) 
相等返回NULL，不等返回expr1to_char 中fm
有9的地方如果有数字就显示如果没有数字就不显示，有0的地方在没有数字的时候也会有０来占位
select to_char(9999.09556,'fm99999.0900'),to_char(9999.09556,'fm00099.0900') from dual；分析函数
分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行,常用的分析函数如下所列:row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

二、分析函数

2.1 rank（等级函数，也指示排名函数）

ROW_NUMBER()
定义：ROW_NUMBER()函数作用就是将select查询到的数据进行排序，
每一条数据加一个序号，一般多用于分页查询，
比如查询前10个 查询10-100个学生。
实例：
对学生成绩排序

在这里插入图片描述

这里number就是每个学生的序号 根据studentScore(分数)进行desc倒序
获取第二个同学的成绩信息

在这里插入图片描述

这里用到的思想就是 分页查询的思想 在原sql外再套一层select 
where t.number>=1 and t.number<=10 是不是就是获取前十个学生的成绩信息纳。
RANK()
定义：RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，这里为什么和ROW_NUMBER()不一样那，ROW_NUMBER()是排序，
当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()不一样出现相同的，他们的排名是一样的，看下面看例子:

在这里插入图片描述

注：当出现两个学生成绩相同是里面出现变化。
RANK()是 1 2 2，而ROW_NUMBER()则还是1 2 3，这就是RANK()和ROW_NUMBER()的区别了DENSE_RANK()
定义：DENSE_RANK()函数也是排名函数，和RANK()功能相似，也是对字段进行排名，那它和RANK()到底有什么不同那？看例子：

在这里插入图片描述

DENSE_RANK()密集的排名他和RANK()区别在于，排名的连续性，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，所以一般情况下用的排名函数就DENSE_RANK()
NTILE()
定义：NTILE()函数是将有序分区中的行分发到指定数目的组中， 就是按序号分组的意思，各个组有编号，编号从1开始，就像我们说的分区一样 ，分为几个区，一个区会有多少个。

在这里插入图片描述

这里查询了3次,第一次分为1个区,所以查询结果number全是1，
第二次分为2个区，查询结果为 1 1 2，意思就是 第一个区为 1 1 （两个编号的数据），第二个区只有2这个数据。
总结：
select  row_number() over(order by a.deptno desc) rak,a.* from emp a  --排序，用于分页查询
select  rank() over(order by a.deptno desc) rak,a.* from emp a  --跳跃的排名
select  dense_rank() over(order by a.deptno desc) rak,a.* from emp a  --排名连续
select  NTILE(4) over(order by a.deptno desc) rak,a.* from emp a   --分几个组

2.2 windowing函数

Order By 子句
select ename,sal,avg(sal) over() from emp;  --所有值的平均值放在每行

在这里插入图片描述

select ename,sal,avg(sal) over(order by ename) from emp;   --累积平均

在这里插入图片描述

注：在没有Order by子句时，在全部组上计算平均值，每一行给一个同样的值。
在用带有Order by的AVG（）时，每一行的平均值是那一行与前面所有行的平均值（此处用作开窗函数）select ename,deptno,sum(sal) over(order by ename, deptno) sum_ename_deptno,  --但它仅仅在分区内对行进行排序，逻辑计算sum(sal) over(order by deptno, ename) sum_deptno_enamefrom emp order by ename, deptno    --只是排序，不影响逻辑计算

在这里插入图片描述

ROW 窗口
注：Windowing子句给出了一个定义变化或固定的数据窗口方法，分析函数将对这些数据进行操作，
在一组内基于任意变化或固定的窗口中，可以用该子句来让分析函数计算它的值，
ROW窗口是物理单元，是包括在窗口中的行的物理数。使用前面的列子作为ROW分区
select deptno,ename,sal,
sum(sal) over(partition by deptnoorder by enamerows 2 preceding
)sliding_total
from emp
order by deptno,ename这将在一组内创建一个变化的窗口，并计算那一组中当前行的SAL列加上前两行SAL列的总和， 请注意，要使用窗口，必须使用ORDER BY 子句Range 窗口
select ename,hiredate,sal,avg(sal) over(order by hiredate asc range 100 preceding) avg_sal_100_days_before,avg(sal) over(order by hiredate desc range 100 preceding) avg_sal_100_days_afterfrom emporder by hiredate asc
如果有“range 5 preceding”，将产生一个滑动的窗口，它在组中拥有所有当前行以前5行的集合
range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内
rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）
指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化
over（order by salary rows between 50 preceding and 150 following）  --每行对应的数据窗口是之前50行，之后150行FIRST_VALUE：返回组中的第一个值
LAST_VALUE：返回组中的最后一个值
LAG：  上一行
LEAD：下一行KEEP函数
功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id in (20,80)
ORDER BY department_id, salary;
LAST_NAME DEPARTMENT_ID SALARY Worst Best

三、常用日期函数

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') strDateTime from dual; --获取年-月-日 时:分:秒
--显示结果为：2018-09-20 12:35:21select to_char(sysdate,'yyyy') strYear from dual; --获取年
--显示结果为：2018select to_char(to_date('2018-09-20','yyyy-mm-dd'),'day') strDay from dual;  --查询某天是星期几
--显示结果为：hursdayselect floor(sysdate - to_date('20201210','yyyymmdd'))  strTime from dual;  --两个日期间的天数select months_between(date'2018-04-23',date'2017-04-23') days from dual;  --月份差select TO_CHAR(SYSDATE,'DDD'),sysdate from dual  --查询某天是一年的第几天select add_months(sysdate,1) from dual;  --下个月的今天select trunc(sysdate, 'mm') from dual  --返回当月第一天select trunc(sysdate,'yy') from dual  --返回当年第一天select trunc(sysdate,'d') from dual  --返回当前星期的第一天（周日）select trunc(sysdate,'IW') from dual;  --返回当前星期的第一天（周一）SELECT last_day(SYSDATE) FROM dual;  --返回指定日期对应月份的最后一天sysdate - 10 as "10天前",
sysdate - 10 / 24 as "10小时前",
sysdate - 10 / (24 * 60) as "10分钟前",
sysdate - 10 / (24 * 3600) as "10秒钟前"select to_char(TO_DATE('20190308', 'YYYYMMDD'),  'yyyyiw') as week, --oracle求当年的第几周to_char(TO_DATE('20190308', 'YYYYMMDD'),  'yyyyww') as week2, --oracle求当年的第几周to_char(TO_DATE('20190308', 'YYYYMMDD'), 'yyyy')  as year, --oracle求第几年to_char(TO_DATE('20190308', 'YYYYMMDD'),  'yyyymm') as month, --oracle求当年的第几月to_char(TO_DATE('20190308', 'YYYYMMDD'),  'yyyyddd') as day, --oracle求当年的第几天to_char(TO_DATE('20190308', 'YYYYMMDD'), 'yyyyq')  as quarter -- oracle求当年的第几季度
from dual非ISO: 永远以新年的第一天为第一周的星期一，第一周一定有七天，而且最后一周不一定有七天。
ISO: 轮到星期几就是星期几，新年的第一天是星期几则为第一周的周期几，第一周不一定有七天，而且一定会要最后一周满七天，如果这年最后一周未满七天，则这一周会持续到下一年的前几天。每个日历星期从星期一开始，星期日为第7天。

本文如有错误或不完善的地方请大家多多指正，留言或 QQ 皆可，
您的批评指正是我写作的最大动力!


http://chatgpt.dhexx.cn/article/x4418qTZ.shtml

相关文章

Oracle常用函数大全

Oracle常用函数大全

说明&#xff1a;新文章地址转为 Oracle数据库函数大全_長安社-王于铭.YuMing的博客-CSDN博客https://hevnchin.blog.csdn.net/article/details/132054755 MySQL数据库系统函数大全_長安社-王于铭.YuMing的博客-CSDN博客year&#xff1a;年份、month&#xff1a;月份、day&am…
阅读更多...
Oracle中的函数（详细！！！）

Oracle中的函数（详细！！！）

文章目录 前言一、SQL中的函数两种SQL函数单行函数单行函数的分类1. 字符型函数LOWER函数UPPER函数INITCAP函数CONCAT函数SUBSTR函数INSTR函数LPAD|RPAD函数REPLACE函数 2. 数字函数ROUND函数TRUNC函数MOD函数 3. 使用日期查看系统时间根据时间查询信息日期的运算MONTHS_BETWEE…
阅读更多...
OpenDaylight通过netconf对接netopeer2

OpenDaylight通过netconf对接netopeer2

目的 利用OpenDaylight(client)的南向接口netconf对接netopeer2(server) 搭建netopeer2 启动Ubuntu20的docker&#xff0c;将内部830端口映射为主机的22830端口 adminubuntu20:~$ docker run -d --name netopeer2_server --privileged -v /sys/fs/cgroup:/sys/fs/cgroup:ro…
阅读更多...
ORAN专题系列-16：5G O-RAN FrontHaul前传接口的网络配置管理协议netconf

ORAN专题系列-16：5G O-RAN FrontHaul前传接口的网络配置管理协议netconf

前言 前传接口&#xff08;FrontHual&#xff09;是传统的BBU与RU之间的接口&#xff0c;在O-RAN之前&#xff0c;前传接口虽然定义了物理连接的CPRI接口规范标准&#xff0c;但CPRI之上承载的M plane的配置管理数据格式&#xff0c;却是设备厂家私有的。 有基于TCP的、有基于…
阅读更多...
实验三：Netconf 接口配置实验(基于Schema API)

实验三：Netconf 接口配置实验(基于Schema API)

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 文章目录 前言一、Netconf简介1.基本网络架构2.协议框架3.报文格式4.会话建立过程 二、实验步骤1.设备预配2.运维代码编写3.实验结果 前言 云时代对网络的关键诉求之一是网络…
阅读更多...
Java NetConf 使用

Java NetConf 使用

1. 参考资料 工具包GIT地址 : https://github.com/Juniper/netconf-java 使用教程 : https://www.juniper.net/documentation/cn/zh/software/junos/netconf-java-toolkit/topics/task/netconf-java-toolkit-program-creating-and-executing.html 2. 下载&编译工具包 # 1. …
阅读更多...
ComNet

ComNet

1 ComNet 简介 ComNet设计的核心思想就是用深度神经网络来代替OFDM接收机&#xff0c;和FC-DNN类似。但是最大的不同之处&#xff0c;对接收机进行细化&#xff0c;将接收机分为了为信道估计子网和信号检测子网。每个子网由一个DNN构造&#xff0c;使用现有的简单、传统的解决…
阅读更多...
「Python 网络自动化」NETCONF —— Python 使用 NETCONF 管理配置 H3C 网络设备

「Python 网络自动化」NETCONF —— Python 使用 NETCONF 管理配置 H3C 网络设备

「Python 网络自动化」系列文章总目录 Nornir 中文手册——基于 Nornir3.0 官方文档的不完全翻译 文章目录 NETCONF 简单介绍NETCONF 协议结构NETCONF 报文结构请求报文格式报文回复格式 NETCONF 配置数据库NETCONF 支持的操作 实验操作基础环境配置网络环境设备配置代码环境 …
阅读更多...
OF-CONFIG和NETCONF协议

OF-CONFIG和NETCONF协议

一、OF-CONFIG协议 1.1 OF-CONFIG设计需求 1.1.1 实现对OF v1.3.1协议设备进行配置的设计需求 1.1.2 实现操作运维的设计需求 1.1.3交换机管理协议需求 1.2 OF-CONFIG协议的数据模型 1.3 OF-CONFIG的传输协议 二、NETCONF协议 2.1 NETCONF协议相对SNMP协议的优点 2.2 …
阅读更多...
netconf是啥

netconf是啥

netconf是啥 1 历史路由器配置方法&#xff1a;2 问题来了3 解决4 Netconf 是什么参考 1 历史路由器配置方法&#xff1a; 传统路由器配置方法中&#xff0c;以路由器来看&#xff0c;那就要去官网上查看文档&#xff0c;学习产品文档&#xff0c;然后在交换机上输入命令&…
阅读更多...
从NETCONF/YANG看网络配置自动化

从NETCONF/YANG看网络配置自动化

阅读冗长的NETCONF/YANG的RFC文档是相当乏味枯燥的&#xff0c;结合开发实践&#xff0c;本文试图删其繁&#xff0c;撮其要&#xff0c;给出NETCONF/YANG的轮廓和要点。 引子 NETCONF和YANG的目的是以可编程的方式实现网络配置的自动化&#xff0c;从而简化和加快网络设备和服…
阅读更多...
SDN之NETCONF Call Home

SDN之NETCONF Call Home

本文主要内容都来自于今年二月发布的RFC8071 - NETCONF Call Home and RESTCONF Call Home&#xff0c;该RFC从2015年4月提出到最终发布一共修改了17个版本&#xff0c;其间修改内容可以点击查看详细内容。 介绍 NETCONF Call Home支持两种安全传输网络配置协议分别是Secure …
阅读更多...
NETCONF原理

NETCONF原理

NETCONF简介 网络配置协议NETCONF&#xff08;Network Configuration Protocol&#xff09;提供一套管理网络设备的机制&#xff0c;用户可以使用这套机制增加、修改、删除网络设备的配置&#xff0c;获取网络设备的配置和状态信息。通过NETCONF协议&#xff0c;网络设备可以提…
阅读更多...
NETCONF YANG原理

NETCONF YANG原理

NETCONF YANG原理 网络管理技术背景NETCONF协议介绍YAGN建模语言介绍RESTCONF协议 网络管理技术背景 前言 对于设备的配置管理&#xff0c;工程师更习惯使用CLI命令行的方式与设备交互。该方式简单直接&#xff0c;便于理解。但是在网络自动化领域&#xff0c;CLI方式与设备交…
阅读更多...
NetConf Brower使用

NetConf Brower使用

1.下载MG-SOFT NETCONF Browser Professional Edition 官方地址 2. 安装MG-SOFT NETCONF Browser 下一步下一步操作即可。 3.连接设备 打开的时候选择Continue即可&#xff0c;无须license。 点击File->Connect ,如下&#xff1a; 输入设备用户名称&#xff1a; 这…
阅读更多...
Netconf协议学习笔记

Netconf协议学习笔记

Netconf简介 网络配置协议NETCONF&#xff08;Network Configuration Protocol&#xff09;提供一套管理网络设备的机制&#xff0c;用户可以使用这套机制增加、修改、删除网络设备的配置&#xff0c;获取网络设备的配置和状态信息。通过NETCONF协议&#xff0c;网络设备可以提…
阅读更多...
NETCONF、RESTCONF和YANG

NETCONF、RESTCONF和YANG

目录 一、NETCONF、RESTCONF和YANG是之间什么关系&#xff1f; 二、Netconf简介 2.1、一般使用工具&#xff1a;MG-Soft 简介 三、Netconf YANG 原理与实践 3.1、NETCONF协议 3.2、YANG建模语言 3.3、RESTCONF协议 网管协议&#xff1a; SNMP&#xff08;基于UDP&#…
阅读更多...
NETCONF--从NETCONF/YANG看网络配置自动化

NETCONF--从NETCONF/YANG看网络配置自动化

阅读冗长的NETCONF/YANG的RFC文档是相当乏味枯燥的&#xff0c;结合开发实践&#xff0c;本文试图删其繁&#xff0c;撮其要&#xff0c;给出NETCONF/YANG的轮廓和要点。 引子 NETCONF和YANG的目的是以可编程的方式实现网络配置的自动化&#xff0c;从而简化和加快网络设备和服…
阅读更多...
Netconf

Netconf

Netconf属于软件定义网络的管理和控制协议。 ETCONF协议&#xff0c;由RFC 6241定义&#xff0c;用以替代命令行界面(command line interface, CLI)、简单网络管理协议(Simple Network Management Protocol, SNMP)以及其它专有配置机制。管理软件可以使用NETCONF协议将配置数据…
阅读更多...
NetConf简介之一篇文章读懂NetConf

NetConf简介之一篇文章读懂NetConf

一、背景 有的人早上不起床刷手机&#xff0c;有的人起床之后再卫生间刷手机&#xff0c;有的人在上班的地铁上刷手机&#xff0c;有的人在下班之后的班车上刷手机&#xff0c;有的人在晚上不睡觉刷手机。这其中有许多离不开技术&#xff0c;其中有一个技术就是网络。 网络互…
阅读更多...
推荐文章

Copyright @ 2022~2023