Oracle 那些可能会用到，但又不会写的语句【建议收藏】

在工作中，日常的数据库开发，其实大部分用到的数据库知识并不复杂，无非是CRUD【增删改查】，但是偶尔会有一些特殊的需求，看似合理，但是一时半会儿也想不起来如何下手，所以只能去百度查找。为了方便起见，这里列举了一些工作中日常用到但又稍微复杂的语句，仅供学习分享使用。如有不足之处，还请指正。

分区排序【partition by】

按指定列分组，同时另一列排序。如：成绩表中，按班级分组，成绩排序。排出每一班级的成绩顺序。

分区排序语法：

select row_number()[rank(),dense_rank()] OVER (PARTITION BY 分组字段1,分组字段2 ORDER BY 排序字段1) from table;

注意：此处不可以用group by ，因为group by 是分组进行汇总功能。

row_number示例：

select sno,cno,degree,
       row_number()over(partition by cno order by degree desc) mm 
       from score

rank示例：

SELECT    * 
FROM    (select sno,cno,degree,
          rank()over(partition by cno order by degree desc) mm 
          from score) 
where mm = 1;

rank和row_number的区别

由以上的例子得出，在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果。具体差异如下：

1. rownumber函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
2. rank函数返回一个唯一的值，除非遇到相同的数据，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）。
3. dense_rank函数返回一个唯一的值，除非当碰到相同数据，此时所有相同数据的排名都是一样的。dense_rank()是连续排序，有两个第二名时仍然跟着第三名。他和row_number的区别在于row_number是没有重复值的。

递归查询【start with】

如果表中存在层次数据，则可以使用层次化查询子句查询出表中行记录之间的层次关系。如：在一个表中，有两个字段：id，父id，则递归查询的意思是循环查询出具有递归关系的数据。

语法：

[ START WITH CONDITION1 ]
CONNECT BY [ NOCYCLE ] CONDITION2
[ NOCYCLE ]

start with 子句为可选项，用来标识哪行作为查找树型结构的第一行（即根节点，可指定多个根节点）。若该子句被省略，则表示所有满足查询条件的行作为根节点。2．关于PRIOR PRIOR置于运算符前后的位置，决定着查询时的检索顺序。

1. 从根节点自顶向下

1 select empno, mgr, level as lv

2 from scott.emp a

3 start with mgr is null

4 connect by (prior empno) = mgr

5 order by level;

--分析

层次查询执行逻辑:

1. 确定上一行（相对于步骤b中的当前行），若start with 子句存在，则以该语句确定的行为上一行，若不存在则将所有的数据行视为上一行。
2. 从上一行出发，扫描除该行之外所有数据行。
3. 匹配条件 (prior empno) = mgr

注意：

一元运算符 prior，意思是之前的，指上一行

当前行定义：步骤2中扫描得到的所有行中的某一行

匹配条件含义:当前行字段 mgr 的值等于上一行字段 empno中的值,若满足则取出该行，并将level + 1，

匹配完所有行记录后，将满足条件的行作为上一行，执行步骤 2，3。直到所有行匹配结束。

2. 从根节点自底向上

1 select empno, mgr, level as lv

2 from scott.emp a

3 start with empno = 7876

4 connect by (prior mgr ) = empno

5 order by level;

--分析

层次查询执行逻辑:

1. 确定上一行（相对于步骤b中的当前行），若start with 子句存在，则以该语句确定的行为上一行，若不存在则将所有的数据行视为上一行。
2. 从上一行出发，扫描除该行之外所有数据行。
3. 匹配条件 (prior mgr ) = empno

注意：

一元运算符 prior，意思是之前的，指上一行。

当前行定义：步骤2中扫描得到的所有行中的某一行。

匹配条件含义：当前行字段 empno 的值等于上一行字段 mgr 中的值,若满足则取出该行，并将 level + 1，

匹配完所有行记录后，将满足条件的行作为上一行，执行步骤2，3。直到所有行匹配结束。

3. 递归查询总结

自顶向下，自下向上口诀：

start with child_id = 10 connect by (prior child_id) = parent_id

prior 和 子列在一起，表示寻找它的子孙，即自顶向下，和父列在一起，表示开始寻找它的爸爸，即自下向上。

一列多行转换成一行【listagg】

LISTAGG是Oracle 11g推出的，listagg函数的语法结构如下：

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

listagg虽然是聚合函数，但可以提供分析功能（比如可选的OVER()子句）。使用listagg中，下列中的元素是必须的：

1. 需要聚合的列或者表达式
2. WITH GROUP 关键词
3. 分组中的ORDER BY子句

示例：

SELECT deptno,LISTAGG(ename, ',') WITHIN GROUP (ORDER BY deptno) AS employees FROM  emp GROUP BY deptno;

拆分字符串成多行【REGEXP_SUBSTR】

有一个问题，需要把一个带有,的字符串拆分成多行。通过查询资料，这个操作需要使用以下2个关键知识：

1. REGEXP_SUBSTR函数
2. 为了实现动态参数，使用 connect by

REGEXP_SUBSTR语法：

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

参数说明：

__srcstr ：需要进行正则处理的字符串

__pattern ：进行匹配的正则表达式

__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

__occurrence ：标识第几个匹配组，默认为1

__modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

示例：

select regexp_substr('1,2,3','[^,]+',1,1) result from dual;
select regexp_substr('1,2,3','[^,]+',1,2) result from dual;

可以通过connect by可以构造连续的值。如下所示：

select rownum from dual connect by rownum<=7;

结合REGEXP_SUBSTR 及 connect by 即可实现拆分字符串为多行的需求，最终的语句为：

SELECT REGEXP_SUBSTR ('1,2,3', '[^,]+', 1,rownum)
from dual connect by rownum<=LENGTH ('1,2,3') - LENGTH (regexp_replace('1,2,3', ',', ''))+1;

有则修改，无则插入【merge into】

当我们对一个表中数据执行操作：如果存在，进行修改；如果不存在，进行插入。此种情况下，采用merge into 语句最为合适。

merge info语法：

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
[UPDATE sql]
WHEN NOT MATCHED THEN
[INSERT sql]

merge into作用：

判断Ｂ表和Ａ表是否满足ON中条件，如果满足则用B表去更新A表，如果不满足，则将B表数据插入A表但是有很多可选项，如下:

1. 正常模式
2. 只update或者只insert
3. 带条件的update或带条件的insert
4. 全插入insert实现
5. 带delete的update(觉得可以用3来实现)

merge into示例：

merge into score a
using (select std_no, c.dept_no
         from student c
        where c.std_no in
              (select std_no from tmp_20210809)) b
on (a.std_no = b.std_no and a.balb_type = '01')
when matched then
  update set a.pre_bal = nvl(a.pre_bal, 0) + 5.8
WHEN NOT MATCHED THEN
  insert
    (a.bal_id, a.std_no, a.balb_type, a.pre_bal, a.dept_no)
  values
    (序列, b.std_no, '01', 5.8, b.dept_no);

备注

在这个世上，根本就没有所谓的一蹴而就。只有日积月累的努力，才有厚积薄发的可能。请沉下心来，不要好高骛远，也不要总是去艳羡别人。专心做好自己的事，当你的才华配得上梦想时，好运自会不期而遇。