Oracle常用函数

博主： Tehran
发布时间：2021 年 10 月 02 日
415 次浏览
暂无评论
2696字数
分类： 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
----------
         9


LENGTH:返回字符串的长度；
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 jin


SUBSTR(string,start,count)
取子字符串,从start开始（包括开始）,取count个
SCOTT@ORCL> select substr(13088888888,3,8) string from dual;

STRING
--------
08888888

REPLACE(string,s1,s2)
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SCOTT@ORCL> select replace('he love you','he','I') string from dual;

STRING
----------
I love you

ABS：返回指定值的绝对值
CEIL：返回大于或等于给出数字的最小整数
SCOTT@ORCL>  select ceil(3.1415927) from dual;

CEIL(3.1415927)
---------------
              4

FLOOR:对给定的数字取整数
SCOTT@ORCL> select floor(2345.67) from dual;

FLOOR(2345.67)
--------------
          2345

MOD(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          2

ROUND和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
------
202102

SQL> select last_day(sysdate) datum from dual;   --返回本月的最后一天
DATUM
-----------
2020/12/31

SQL> select  months_between(to_date('20000520','yyyymmdd'),to_date('20050520','yyyymmdd')) mon_betw from dual;  --月份差异
  MON_BETW
----------
       -60

SQL> select next_day( sysdate, 'MONDAY') from dual;   --当前日期的下个星期一
NEXT_DAY(SYSDATE,'MONDAY')
--------------------------
2020/12/21 6:52:42

GREATEST：返回一组表达式中的最大值,即比较字符的编码大小
LEAST：返回一组表达式中的最小值

STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.503223

pl/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' end

NVL(expr1, expr2)
expr1为NULL，返回expr2，不为NULL，返回expr1，注意两者的类型要一致

NVL2 (expr1, expr2, expr3) 
expr1不为NULL，返回expr2,为NULL,返回expr3,expr2和expr3类型不同的话，expr3会转换为expr2的类型

NULLIF (expr1, expr2) 
相等返回NULL，不等返回expr1

to_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（）时，每一行的平均值是那一行与前面所有行的平均值（此处用作开窗函数）

2.3. ROW 窗口

注：Windowing子句给出了一个定义变化或固定的数据窗口方法，分析函数将对这些数据进行操作，
在一组内基于任意变化或固定的窗口中，可以用该子句来让分析函数计算它的值，
ROW窗口是物理单元，是包括在窗口中的行的物理数。使用前面的列子作为ROW分区
select deptno,ename,sal,
sum(sal) over(
         partition by deptno
         order by ename
         rows 2 preceding
)sliding_total
from emp
order by deptno,ename

这将在一组内创建一个变化的窗口，并计算那一组中当前行的SAL列加上前两行SAL列的总和， 
请注意，要使用窗口，必须使用ORDER BY 子句

2.4.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_after
  from emp
 order 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：下一行

2.5.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

最后修改：2021 年 10 月 02 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

Oracle常用函数

Tehran • 2021 年 10 月 02 日

<div class="tip share">请注意，本文编写于 935 天前，最后修改于 935 天前，其中某些信息可能已经过时。</div>

<h2><strong>一、概览</strong></h2><pre><code>INITCAP
返回字符串并将字符串的第一个字母变为大写

INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 从哪个位置开始找,默认为1，
J 第几次出现,默认为1
SCOTT@ORCL&gt; select instr('oracle traning','ra',1,2) instring from dual;

INSTRING
----------
         9

LENGTH:返回字符串的长度；
LOWER: 返回字符串,并将所有的字符小写；
UPPER: 返回字符串,并将所有的字符大写；

RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SCOTT@ORCL&gt; select lpad(rpad('gao',10,'*'),17,'*') string from dual;

STRING
-----------------
*******gao*******

LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SCOTT@ORCL&gt; select ltrim(rtrim(' gao qian jing ',' g'),' ') string from dual;

STRING
------------
gao qian jin

SUBSTR(string,start,count)
取子字符串,从start开始（包括开始）,取count个
SCOTT@ORCL&gt; select substr(13088888888,3,8) string from dual;

STRING
--------
08888888

REPLACE(string,s1,s2)
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SCOTT@ORCL&gt; select replace('he love you','he','I') string from dual;

STRING
----------
I love you

ABS：返回指定值的绝对值
CEIL：返回大于或等于给出数字的最小整数
SCOTT@ORCL&gt;  select ceil(3.1415927) from dual;

CEIL(3.1415927)
---------------
              4

FLOOR:对给定的数字取整数
SCOTT@ORCL&gt; select floor(2345.67) from dual;

FLOOR(2345.67)
--------------
          2345

MOD(n1,n2)
返回一个n1除以n2的余数
SCOTT@ORCL&gt; select mod(10,3),mod(3,3),mod(2,3) from dual;

MOD(10,3)   MOD(3,3)   MOD(2,3)
---------- ---------- ----------
         1          0          2

ROUND和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&gt; select  to_char(add_months(to_date('202012','yyyymm'),2),'yyyymm') datum from dual;   --增加或减去月份
DATUM
------
202102

SQL&gt; select last_day(sysdate) datum from dual;   --返回本月的最后一天
DATUM
-----------
2020/12/31

SQL&gt; select  months_between(to_date('20000520','yyyymmdd'),to_date('20050520','yyyymmdd')) mon_betw from dual;  --月份差异
  MON_BETW
----------
       -60

SQL&gt; select next_day( sysdate, 'MONDAY') from dual;   --当前日期的下个星期一
NEXT_DAY(SYSDATE,'MONDAY')
--------------------------
2020/12/21 6:52:42

GREATEST：返回一组表达式中的最大值,即比较字符的编码大小
LEAST：返回一组表达式中的最小值

STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL&gt; select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.503223

pl/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 &lt;60 then 'd'
when score &gt;=60 and score &lt;70 then 'c'
when score &gt;=70 and score &lt;80 then 'b'
else 'a' end

NVL(expr1, expr2)
expr1为NULL，返回expr2，不为NULL，返回expr1，注意两者的类型要一致

NVL2 (expr1, expr2, expr3) 
expr1不为NULL，返回expr2,为NULL,返回expr3,expr2和expr3类型不同的话，expr3会转换为expr2的类型

NULLIF (expr1, expr2) 
相等返回NULL，不等返回expr1

to_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 ...)</code></pre><h2><strong>二、分析函数</strong></h2><h2>2.1 rank（等级函数，也指示排名函数）</h2><pre><code>ROW_NUMBER()
定义：ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号，一般多用于分页查询，
比如查询前10个 查询10-100个学生。
实例：
对学生成绩排序</code></pre><p><img src="https://gcore.jsdelivr.net/gh/546426894/typecho@master/usr/uploads/2021/10/2121940248.png" alt="image.png" title="image.png" style=""></p><pre><code>这里number就是每个学生的序号 根据studentScore(分数)进行desc倒序
获取第二个同学的成绩信息</code></pre><p><img src="https://gcore.jsdelivr.net/gh/546426894/typecho@master/usr/uploads/2021/10/3914945572.png" alt="image.png" title="image.png" style=""></p><pre><code>这里用到的思想就是 分页查询的思想 在原sql外再套一层select 
where t.number&gt;=1 and t.number&lt;=10 是不是就是获取前十个学生的成绩信息纳。
RANK()
定义：RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，这里为什么和ROW_NUMBER()不一样那，ROW_NUMBER()是排序，
当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()不一样出现相同的，他们的排名是一样的，看下面看例子:</code></pre><p><img src="https://gcore.jsdelivr.net/gh/546426894/typecho@master/usr/uploads/2021/10/2414112844.png" alt="image.png" title="image.png" style=""></p><pre><code>注：当出现两个学生成绩相同是里面出现变化。
RANK()是 1 2 2，而ROW_NUMBER()则还是1 2 3，这就是RANK()和ROW_NUMBER()的区别了

DENSE_RANK()
定义：DENSE_RANK()函数也是排名函数，和RANK()功能相似，也是对字段进行排名，那它和RANK()到底有什么不同那？看例子：</code></pre><p><img src="https://gcore.jsdelivr.net/gh/546426894/typecho@master/usr/uploads/2021/10/779853479.png" alt="image.png" title="image.png" style=""></p><pre><code>DENSE_RANK()密集的排名他和RANK()区别在于，排名的连续性，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，所以一般情况下用的排名函数就DENSE_RANK()
NTILE()
定义：NTILE()函数是将有序分区中的行分发到指定数目的组中， 就是按序号分组的意思，各个组有编号，编号从1开始，就像我们说的分区一样 ，分为几个区，一个区会有多少个。</code></pre><p><img src="https://gcore.jsdelivr.net/gh/546426894/typecho@master/usr/uploads/2021/10/3302699865.png" alt="image.png" title="image.png" style=""></p><pre><code>这里查询了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   --分几个组</code></pre><h2><strong>2.2 windowing函数</strong></h2><pre><code>Order By 子句
select ename,sal,avg(sal) over() from emp;  --所有值的平均值放在每行</code></pre><p><img src="https://gcore.jsdelivr.net/gh/546426894/typecho@master/usr/uploads/2021/10/3940069215.png" alt="image.png" title="image.png" style=""></p><pre><code>select ename,sal,avg(sal) over(order by ename) from emp;   --累积平均</code></pre><pre><code>注：在没有Order by子句时，在全部组上计算平均值，每一行给一个同样的值。
在用带有Order by的AVG（）时，每一行的平均值是那一行与前面所有行的平均值（此处用作开窗函数）</code></pre><h2><strong>2.3. ROW 窗口</strong></h2><pre><code>注：Windowing子句给出了一个定义变化或固定的数据窗口方法，分析函数将对这些数据进行操作，
在一组内基于任意变化或固定的窗口中，可以用该子句来让分析函数计算它的值，
ROW窗口是物理单元，是包括在窗口中的行的物理数。使用前面的列子作为ROW分区
select deptno,ename,sal,
sum(sal) over(
         partition by deptno
         order by ename
         rows 2 preceding
)sliding_total
from emp
order by deptno,ename

这将在一组内创建一个变化的窗口，并计算那一组中当前行的SAL列加上前两行SAL列的总和， 
请注意，要使用窗口，必须使用ORDER BY 子句</code></pre><h2>2.4.<strong>Range 窗口</strong></h2><pre><code>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_after
  from emp
 order 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：下一行</code></pre><h2><strong>2.5.KEEP函数</strong></h2><pre><code>功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），
因此完整的语法需要在开始处加上一个集合函数以从中取出记录

SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) &quot;Worst&quot;,
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) &quot;Best&quot;
FROM employees
WHERE department_id in (20,80)
ORDER BY department_id, salary;
LAST_NAME DEPARTMENT_ID SALARY Worst Best</code></pre>

Oracle常用函数

一、概览

二、分析函数

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

2.2 windowing函数

2.3. ROW 窗口

2.4.Range 窗口

2.5.KEEP函数

相关文章

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

handsome 换字体

Oracle 数据库常用安装包合集（包含基础版和最新补丁集202107）

超全 Linux 安装包合集(Centos Redhat Oracle Linux)

本站typecho 主题版本修改的东西(8.40版本和typecho开发版1.2)

影视资源站接口

oracle多用户并发及事务处理

Oracle精读48_数据字典分类

Oracle修改instance_name、db_name、db_unique_name、service_names

Oracle精读86_存储过程的参数(in，out)

获得Bilibili(哔哩哔哩)视频地址并挂载至网站(自适应)

Oracle常用函数

一、概览

二、分析函数

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

2.2 windowing函数

2.3. ROW 窗口

2.4.Range 窗口

2.5.KEEP函数

相关文章

发表评论 取消回复 # 请输入真实的邮箱地址便于接收回复

Oracle常用函数

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复