×

oracle递归函数

oracle递归函数(oracle递归写法)

admin admin 发表于2023-04-11 22:04:06 浏览87 评论0

抢沙发发表评论

本文目录一览:

ORACLE select 递归查询

START WITH 定义数据行查询的初始起点;

CONNECT BY prior 定义表中的各个行是如何联系的;

connect by 后面的"prior" 如果缺省,则只能查询到符合条件的起始行,并不进行递归查询;

条件2:col_1 = col_2,col_1是父键(它标识父),col_2是子键(它标识子)。

条件3过滤递归前相应节点及其子节点,如果上级节点不满足则下级节点自动过滤掉;

条件4过滤递归后相应的节点或子节点,如果上级节点不满足则下级结点自动提升一级。

系统伪列:

CURRVAL AND NEXTVAL 使用序列号的保留字

ROWID 记录的唯一标识

ROWNUM 限制查询结果集的数量

LEVEL 显示层次树中特定行的层次或级别

CONNECT_BY_ROOT 返回当前层的根节点(当前行数据所对应的最高等级节点的内容)

SYS_CONNECT_BY_PATH(column, char) 函数实现将从父节点到当前行内容以"path"或者层次元素列表的形式显示出来

CONNECT_BY_ISCYCLE 须带参数NOCYCLE,当前行中引用了某个父亲节点的内容并在树中出现了循环,如果循环显示"1",否则就显示"0"。

CONNECT_BY_ISLEAF 判断当前行是不是叶子。如果是叶子显示"1",如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示"0"

而在 Oracle 10g 中,只要指定"NOCYCLE"就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE, 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示"1",否则就显示"0"。-oracle递归函数

【实例】

--创建测试表,增加测试数据

create table test(superid varchar2(20),id varchar2(20),mc varchar2(20));

insert into test values('0','1','A1');

insert into test values('0','2','A2');

insert into test values('1','11','A11');

insert into test values('1','12','A12');

insert into test values('2','21','A21');

insert into test values('2','22','A22');

insert into test values('11','111','A111');

insert into test values('11','112','A112');

insert into test values('12','121','A121');

insert into test values('12','122','A122');

insert into test values('21','211','A211');

insert into test values('21','212','A212');

insert into test values('22','221','A221');

insert into test values('22','222','A222');

commit;

--层次查询示例

select level||'级' jc,lpad(' ',(level-1)*4)||id id,mc

from test

start with superid = '0' connect by prior id=superid;

select level||'级' jc,connect_by_isleaf mxf,lpad(' ',(level-1)*4)||id id,mc

from test

start with superid = '0' connect by prior id=superid;

--给出两个以前在"数据库字符串分组相加之四"中的例子来理解start with ... connect by ...

--功能:实现按照superid分组,把id用";"连接起来

--实现:以下两个例子都是通过构造2个伪列来实现connect by连接的。

Oracle函数sys_connect_by_path用法

sys_connect_by_path函数是为了配合递归查询的函数,递归查询可以参考我之前的博客: , sys_connect_by_path函数是将递归查到的数据加上特定的符号,看起来更明显

connect by递归查询配合sys_connect_by_path基本语法是:

start with:表示以什么为根节点,不加限制可以写1=1,要以id为123的节点为根节点,就写为start with id =123

connect by:connect by是必须的,start with有些情况是可以省略的,或者直接start with 1=1不加限制

prior:prior关键字可以放在等号的前面,也可以放在等号的后面,表示的意义是不一样的,比如 prior id = pid,就表示pid就是这条记录的根节点了

sys_connect_by_path第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符!

然后给个例子,scott用户登录

可能遇到的报错,ORA-30004:使用SYS_CONNECT_BY_PATH函数时,不能将分隔符作为列值的一部分

原因是当使用SYS_CONNECT_BY_PATH 函数时,不能将分隔符作为字段值的一部分

假如这样的sql,ename里面有@这个字符,就会报错,方法是修改sql换个字符,或者改数据

ORACLE递归

about connect by

SELECT empno, ename, job, mgr, deptno, LEVEL, sys_connect_by_path(ename,'\'), connect_by_root(ename) FROM emp START WITH mgr IS NULL CONNECT BY mgr =  PRIOR empno-oracle递归函数

WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必须把结构写出来

SELECT empno, ename, job, mgr, deptno  ---- 先写锚点查询,用START WITH的条件

,1 AS the_level    ---- 递归起点,第一层

,'\'||ename        ---- 路径的第一截

,ename AS top_manager ---- 原来的CONNECT_BY_ROOT

FROM scott.EMP

WHERE mgr IS NULL ---- 原来的START WITH条件

UNION ALL  ---- 下面是递归部分

SELECT e.empno, e.ename, e.job, e.mgr, e.deptno  ---- 要加入的新一层数据,来自要遍历的emp表

,1 + t.the_level            ---- 递归层次,在原来的基础上加1。这相当于CONNECT BY查询中的LEVEL伪列

,t.path||'\'||e.ename        ---- 把新的一截路径拼上去

,t.top_manager              ---- 直接继承原来的数据,因为每个路径的根节点只有一个

FROM t, scott.emp e                    ---- 典型写法,把子查询本身和要遍历的表作一个连接

WHERE t.empno = e.mgr            ---- 原来的CONNECT BY条件

) ---- WITH定义结束

SELECT * FROM T

EMPNO ENAME      JOB        MGR DEPTNO  THE_LEVEL PATH                                                                            TOP_MANAGER-oracle递归函数

----- ---------- --------- ----- ------ ---------- -------------------------------------------------------------------------------- ------------oracle递归函数

7839 KING      PRESIDENT          10          1 \KING                                                                            KING-oracle递归函数

7566 JONES      MANAGER    7839    20          2 \KING\JONES                                                                      KING-oracle递归函数

7698 BLAKE      MANAGER    7839    30          2 \KING\BLAKE                                                                      KING-oracle递归函数

7782 CLARK      MANAGER    7839    10          2 \KING\CLARK                                                                      KING-oracle递归函数

7999 MIKE      ANALYST    7566    30          3 \KING\JONES\MIKE                                                                KING-oracle递归函数

7499 ALLEN      SALESMAN  7698    30          3 \KING\BLAKE\ALLEN                                                                KING-oracle递归函数

7521 WARD      SALESMAN  7698    30          3 \KING\BLAKE\WARD                                                                KING-oracle递归函数

7654 MARTIN    SALESMAN  7698    30          3 \KING\BLAKE\MARTIN                                                              KING-oracle递归函数

7788 SCOTT      ANALYST    7566    20          3 \KING\JONES\SCOTT                                                                KING-oracle递归函数

7844 TURNER    SALESMAN  7698    30          3 \KING\BLAKE\TURNER                                                              KING-oracle递归函数

7900 JAMES      CLERK      7698    30          3 \KING\BLAKE\JAMES                                                                KING-oracle递归函数

7902 FORD      ANALYST    7566    20          3 \KING\JONES\FORD                                                                KING-oracle递归函数

7934 MILLER    CLERK      7782    10          3 \KING\CLARK\MILLER                                                              KING-oracle递归函数

7369 SMITH      CLERK      7902    20          4 \KING\JONES\FORD\SMITH                                                          KING-oracle递归函数

7876 ADAMS      CLERK      7788    20          4 \KING\JONES\SCOTT\ADAMS                                                          KING-oracle递归函数