oracle IF_码学院 | Oracle常用函数分享

栏目简介

“码学院”是嗨华软开设的一个新栏目，主要面向技术同事，供大家在此闻“机”起舞，策“码”奔腾。本栏目长期征稿，欢迎广大华软技术人来这里分享有品位的代码！

很早之前我在论坛上看到过一则言论：

“Tom Kyte’s full mantra:

1.You should do it in a single SQL statement if at all possible.

2.If you cannot do it in a single SQL Statement, then do it in PL/SQL.

3.If you cannot do it in PL/SQL, try a Java Stored Procedure.

4.If you cannot do it in Java, do it in a C external procedure.

5.if you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it.

如果你可以使用一句SQL解决的需求，就使用一句SQL；如果不可以，就考虑PL/SQL是否可以，如果PL/SQL实现不了，就考虑Java或着C存储过程是否可以；如果这些都不可能实现，那么就需要考虑你是否真要实现这个需求。”

虽然我没读过这位Oracle大师的著作，但是我觉得这句话挺有道理的，在不考虑性能的前提下，如果能一条SQL解决的需求尽量使用一条SQL解决，不行的话试下两三条，复杂点的话再使用PL/SQL，如果都不能满足的话那么再来考虑一下用后台语言来实现。

好了，废话不多说，现在来分享一下在Oracle中常用的几个函数和关键字。

row_number() 

有这么一个需求，发票表有发票的明细和使用情况，需要按号码区间统计出已经使用的发票号和数量，比如有1,4,7,8,9,10需要展示出[1,1],[4,4],[7,10]。

我们第一时间想到的就是用后台查询出数据再通过数组当前值与下一个下标值是否连续来判断出区间或者使用PL/SQL。这个方法是可行的，但是往往有些时候我们只能通过SQL来统计,比如要求某些报表查询工具展示出结果。来看一下下面的表格：

我们可以观察到，已经排好序的发票号和该数据的行号的差是有一定规律的，不难发现只要该差值相等，那么这几个号码就是连续的。因此我们可以按照差值分组获得每一组的最小号以及最大号就能获得区间了，所以关键就是获得行号，我们可以借助row_number()。

row_number()从1开始为每一行记录返回一个顺序的号码，即为行号，可通过开窗函数over()实现记录的分区、排序后再返回行号，row_number()over(partition by COL1 order by COL2)。

with testdata as(select 1 invoiceNo, 'A' operator from dualunion allselect 4 invoiceNo, 'B' operator from dualunion allselect 7 invoiceNo, 'A' operator from dualunion allselect 8 invoiceNo, 'B' operator from dualunion allselect 9 invoiceNo, 'A' operator from dualunion allselect 10 invoiceNo, 'A' operator from dual)select min(invoiceNo), max(invoiceNo)from(select invoiceNo, invoiceNo - row_number() over(order by invoiceNo) rn from testdata) group by rn order by min(invoiceNo);

左右滑动查看完整代码

结果：

我们再来看一下，发票都是不同操作员使用的，假设需求会变成这样，需要统计出每个业务员所使用的号码区间。

这里的行号有所区别是因为发票号是不同业务员使用的，需要在各业务员中排序。因此在开窗函数over()中我们需要对数据按照业务员进行分区,over(partition by operator order by invoiceNo)，这里的partition by就是将数据进行分区，(根据operator分区，在分区内再根据invoiceNo进行排序)。不同于group by，partition by是select之后的结果集进行分区。

需要注意第4行和第5行的差值虽然相等但是业务员不同，所以要group by operater和差值。

除了获取区间外，行号还能用于获取分区内前n条记录等，例如我们要获取每个人使用的前几张发票号。

connect by

还是发票的需求，刚才是组合成号码区间，这回就是反过来了。需要将号码区间拆成单个号码。用回上面的例子[1,1],[4,4],[7,10]，需要展示成1，4，7，8，9，10。

先来看[7,10]，可以看出7，8，9，10分别是7+0，7+1，7+2，7+3得到。因为我们可以将[7,10]与[0,1,2,3]做笛卡尔积，然后起始号码+辅表数据。

所以关键需要得到辅表数据[0,1,2,3]，我们可以利用connect by level <= 4递归关联自身表。

这里稍微说一下，其中level为伪列表示递归深度。他像一颗树一样往下递归。

当数据有一条A,则深度1的时候为一条记录, 深度2的时候，则有两条记录。

当数据有两条A,B,则深度2一共有六条记录，深度3时有14条记录。如图。

不难发现当有n条时，深度为m时，每一层都是上一层的数量乘以条数，因此结果集有n+n^2+n^3+…+n^m条记录。

回到正题，递归深度为4，即可获得4条数据，行号-1后就能得到辅表。只要递归深度取区间间隔最大的即可生成满足任一数据行的辅表。

with testdata as(select 1 startNo, 1 endNo from dualunion allselect 4 startNo, 4 endNo from dualunion allselect 7 startNo, 10 endNo from dual)select rownum-1 distance from(select max(endNo-startNo) + 1 gap from testdata) connect by level <= gap;

左右滑动查看完整代码

结果：

为了方便，就取数据源表进行递归，反正我们要拿的只是行号。最后将数据源表与辅表做笛卡尔积，当然还需要注意条件，相加后不能大于区间的终止号码。

with testdata as(select 1 startNo, 1 endNo from dualunion allselect 4 startNo, 4 endNo from dualunion allselect 7 startNo, 10 endNo from dual), disdata as(select rownum-1 distance from(select max(endNo-startNo) + 1 gap from testdata) connect by level <= gap)select testdata.startNo+disdata.distance result from testdata,disdatawhere testdata.endNo >= testdata.startNo+disdata.distanceorder by result;

左右滑动查看完整代码

结果：

通常我们会遇到这样的需求，要求列出某个月内每一天所使用的发票数量，那么这个时候也能利用上面的规律使用connect by，来列出该月份的所有日期作为主表数据。例如列出2020年5月份的日期，递归深度为5月1日至6月1日相差的天数，即connect by level <= 31。

select to_date('2020-05','yyyy-MM') + level - 1 from dualconnect by level <= add_months(to_date('2020-05','yyyy-MM'),1) - to_date('2020-05','yyyy-MM');

左右滑动查看完整代码

connect by除了用level来控制递归深度以外，还可以使用prior关键字。prior用于控制有父子层级关系数据的遍历方向，我们经常在一些场景能用到，例如展示菜单、组织机构等。具体的用法为 connect by prior 父/子节点属性=子/父节点属性。

prior紧跟子节点，表示从父节点出发，从上往下把每个节点当作根节点寻找其叶子节点生成一棵树，数据表里有多少条数据就会生成多少棵树。

假设有这么一个机构数据如图：

with testdata as(select 1 id, -1 parentId, '一级机构A' name from dualunion allselect 2 id, -1 parentId, '一级机构B' name from dualunion allselect 3 id, 1 parentId,'二级机构A' name from dualunion allselect 4 id, 2 parentId,'二级机构B' name from dualunion allselect 5 id, 1 parentId,'二级机构C' name from dualunion allselect 6 id, 3 parentId,'三级机构A' name from dual)select level, sys_connect_by_path(name,'->'),t.* from testdata tconnect by prior id = parentIdstart with parentId = -1;

左右滑动查看完整代码

这里使用oracle的sys_connect_by_path()函数可以较为直观地展示其树状结构。level伪列用来表示递归深度，connect by prior id = parentId，表示上一层记录的id等于该条记录的parentId, 后面加了start with parentId = -1表示从parentId为-1的节点往下扫描，parentId为-1的只有2条，因此会生成2棵树，如果不加则默认每个节点都往下扫描一遍。

结果：

prior如果紧跟父节点，则扫描方向是相反的，从子节点出发，对每个节点从下往上寻找其父节点。同样也是可以加start with来指定扫描开始的节点，通常搭配first_value(COL1)over(order by level asc/desc)用于寻找该节点最顶层的节点。这里就不列SQL了。

lag()、lead()

又是发票需求，有一张表定时记录着每个月的发票使用量，需要展示每个月的数据同时要展示环比涨幅。

环比涨幅需要用到本月与上月的数据做对比，我们可以使用lag()函数，它是一个偏移函数，具体用法lag(COL1,offset,defaultValue)over(partition by COL2 order by COL3)，以COL2分区在分区内按照COL3排序，COL1是需要取的列名，offset是以当前行所在分区的数据向上偏移，如果没有数据则默认为defaultValue。这里需要上个月的数据，即向上偏移1行数据，如果没有数据则默认用该行的值。

with testdata as(select '2020-01' month, 100 used from dualunion allselect '2020-02' month, 200 used from dualunion allselect '2020-03' month, 300 used from dualunion allselect '2020-04' month, 400 used from dual)select t.*, lag(used,1,used)over(order by month)from testdata t

左右滑动查看完整代码

结果：

lead()函数与lag()函数使用方法一样，但效果相反，是向下偏移的。这里就不再举例了。

好了，这次的分享就这么多，实际上Oracle中还有很多功能性丰富而且日常需求也能用上的函数、关键字，例如rank()、dense_rank()、listagg()、pivot()、unpivot()、decode()、range等等，有兴趣的同学不妨去看一下。

—————END—————

              文章 | 布吉岛的阿尔法

编辑 | 江春红

签发 | 李艺峰