查阅代码整理(五)之oracle 特殊函数用法（行转列，树查询等）

今天在修改代码时发现一个公司未知人员写的sql,然后学习了一下午，贴出供自己查询学习。

 

部分脚本：

(case when dmid is null then '无' else

( select to_char(wm_concat(dm.dmmc)) dmmc from gg_test dm where dm.id in( select dmid from (

select id, regexp_substr(dmid , '[^,]+', 1, level) dmid

from jd_test

connect by level <= regexp_count(dmid , ',') + 1

and id = prior id

and prior dbms_random.value is not null

) end )

 

其中使用了case when , to_char, wm_concat, regexp_substr, level, regexp_count, connect by prior, dbms_random.value 其中有的地方很理解后感觉学无止境呀。

直接上大招。

脚本中：

and id = prior id

and prior dbms_random.value is not null

问了一大圈许多人都不知道什么原因，或者只知道个大概，查了好久才明白了此处。

原贴：原贴15楼答复

15楼做出解答

 

简单而言：

使用prior id=id来identify同一行，用dbms_random.values来返回不同值以欺骗Oracle从而规避报错。

CONNECT BY它相当于一个递归的自连接，不断地把每层的连接结果叠加到结果集中。两层之间的连接条件和递归出口都写在CONNECT BY中。在这里我们的数据并无父子关系，只是要让同一行数据重复出现，因此我们的连接的条件只用到了表的主键id=PRIOR id, 此外再用LEVEL控制层数作为递归出口。但ORACLE有个检查，如果你有前后连接条件(id=PRIOR id)，但是同一行数据再次出现，它就会报一个错：
ERROR:
ORA-01436: CONNECT BY loop in user data
为了欺骗它，这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果，所以它认为两行数据不一样，所以不报错了。

 

其他函数解读：

1 case when else end

 

类似我们的if ...else ,判断语句

语法如下:

CASE expr WHEN expr1 THEN return_expr1

         [WHEN expr2 THEN return_expr2

          ...

          WHEN exprn THEN return_exprn

          ELSE else_expr]

END

 

第二种延伸用法:

CASE

       WHEN  expr1 THEN return_expr1

       [WHEN expr2 THEN return_expr2

       ....

       WHEN exprn THEN return_exprn

       ELSE else_expr]

END

2 decode函数

 

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

语法:

DECODE(col|expression, search1, result1

[, search2, result2,...,]

...

 [, searchn, resultn,...,]

[, default])

如果 条件=值1,那么显示结果1

如果 条件=值2,那么显示结果2

....

如果 条件=值n,那么显示结果n

都不符合，则显示缺省值

注：sign()函数根据参数1的值是0、正数还是负数，分别返回0、1、-1

 

3 start with  connect by prior 递归查询

 

用于B树结构类型的数据递归查询，给出B树结构类型中的任意一个结点，遍历其最终父结点或者子结点

SELECT ... FROM+ 表名

START WITH+ 条件1

CONNECT BY PRIOR+ 条件2

WHERE + 条件3

条件1：是根节点的限定语句，当然可以放宽限定条件，以取得多个根节点，也就是多棵树；在连接关系中，除了可以使用列明外，还允许使用列表达式。

START WITH 子句为可选项，用来标识哪个节点作为查找树形结构的根节点。若该子句省略，则表示所有满足查询条件的行作为根节点。

条件2：是连接条件，其中用PRIOR表示上一条记录，例如CONNECT BY PRIOR STUDENT_ID = GRADE_ID，意思就是上一条记录的STUDENT_ID是本条记录的GRADE_ID，即本记录的父亲是上一条记录。CONNECT BY子句说明每行数据将是按照层次顺序检索，并规定将表中的数据连入树形结构的关系中。

PRIOR运算符必须放置在连接关系的2列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查

找树结构的顺序是自顶向下，还是自底向上。

条件3：是过滤条件，用于对返回的记录进行过滤。

此段原博客： https://www.pianshen.com/article/81771258372/

4 REGEXP_SUBSTR函数

 

格式如下：

REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier)

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

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

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

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

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

5  EGEXP_COUNT函数

 

统计字符串出现的次数。

6 wm_concat函数

 

是一个聚合函数，其作用是将一列数据转换成一行，也就是我们常用的行转列。

这里有个坑，有时wm_concat会报错，好像是格式不正确，这个时候可以使用to_char()。

该函数是一个undocument函数，所以不推荐大家使用这个函数。因为在后续的版本中还提不提供这个函数都不好说呢。