Oracle 递归操作/树结构表操作学习笔记+实例

1.树结构表

树结构表是指表中存放的数据之间存在层次关系(父子关系),其本质为树形结构图。如员工表(employees)包含员工id(employee_id)和领导id(manager_id),其manager_id即为父节点,通过每一个员工的父节点即可确定整个树形结构。

如图示:
这里写图片描述

例表如下(建表语句与测试数据在文章末尾):
这里写图片描述

2.递归查询语句

递归查询语句格式:

SELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];

实例:

select employee_id,manager_id,level,lpad(last_name,length(last_name)+(level*2)-2,'_')
from employees
start with employee_id = 100
connect by prior employee_id = manager_id;

其含义为从员工id(employee_id)100的员工开始递归查询当前员工id(employee_id)等于领导id(manager_id)的员工,并按级别向名字(last_name)前拼接’_’以显示出树型结构。
运行结果:
这里写图片描述

3.START WITH

START WITH在递归查询中作用是定义递归的起点,关于START WITH的用法主要有两种情况

(一)省略START WITH

在使用递归查询时,可以省略START WITH即不指定递归起点,此时实际是按照默认当方式逐级递归
实例:

select employee_id,manager_id,level,lpad(last_name,length(last_name)+(level*2)-2,'_')
from employees
connect by prior employee_id =manager_id ;

运行结果:
这里写图片描述
看结果1行——19行可发现,先进行了CEO级别以下所有子节点的递归,20-25行是部长级别以下所有子节点的递归,25行之后由于图片过长没有截完,与之前观察推断一致。

(二)指定多个递归起点

START WITH 不但能够指定一个递归起点(根节点),还能够指定多个递归起点(根节点)。
实例:

select employee_id,manager_id,level,lpad(last_name,length(last_name)+(level*2)-2,'_')
from employees
start with employee_id = 101 or employee_id=201
connect by prior employee_id = manager_id;

这里仅从员工id为101、员工id为201的员工开始遍历查询
运行结果:
这里写图片描述

4.PRIOR

运算符PRIOR在递归查询中决定递归方向,起决定条件为PRIOR后对应的是父节点还是子节点,如

start with employee_id = 100
conncet by prior employee_id = manager_id;

PRIOR对应的是子节点,则是从起始节点向子节点遍历,即向下遍历
运行结果:
这里写图片描述

start with employee_id = 206
conncet by employee_id = prior manager_id;start with employee_id = 206
conncet by prior manager_id = employee_id;

PRIOR对应的是父节点,则从起始节点向根节点遍历,此处PRIOR运算符可以写在等号后面。
运行结果:
这里写图片描述

5.LEVEL

LEVLE关键字表示递归查询中的层级等级,即从起始节点(START WITH指定)开始为1,向上或向下一级为2(因存在向上遍历和向下遍历的区别),以此类推。如之前的每个实例中的LEVEL列都可以明确看出。

6.搭配WHERE使用

搭配WHERE子句来限定树型结构中的单个节点,以去掉树中的单个节点,但它却不影响其后节点的遍历。
实例:

select employee_id,manager_id,level,lpad(last_name,length(last_name)+(level*2)-2,'_')
from employees
where employee_id != 149
start with employee_id = 100
connect by prior employee_id =manager_id ;

这里表示不显示员工id149的员工(Zlotkey)
运行结果:
这里写图片描述
根据结果可发现,并没有显示员工ID为149的Zlotkey,但依旧显示了Zlotkey部门下面的员工Able,Taylor和Grant。

以下为建表语句与测试数据:

create table employees
(EMPLOYEE_ID NUMBER(6) NOT NULL,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25) NOT NULL,
PHONE_NUMBER VARCHAR(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4));

insert into employees values(100,'Steven','King','SKING','515.123.4567',to_date('1987-06-17','YYYY-MM-DD'),'AD_PRES',24000,NULL,NULL,90);
insert into employees values(101,'Neena','Kochhar','NKOCHHAR','515.123.4568',to_date('1989-09-21','YYYY-MM-DD'),'AD_VP',17000,NULL,100,90);
insert into employees values(102,'Lex','De Haan','LDEHAAN','515.123.4569',to_date('1993-01-13','YYYY-MM-DD'),'AD_VP',17000,NULL,100,90);
insert into employees values(103,'Alexander','Hunold','AHUNOLD','590.423.4567',to_date('1990-01-03','YYYY-MM-DD'),'IT_PROG',9000,NULL,102,60);
insert into employees values(104,'Bruce','Ernst','BERNST','590.423.4568',to_date('1991-05-21','YYYY-MM-DD'),'IT_PROG',6000,NULL,103,60);
insert into employees values(107,'Diana','Lorentz','DLORENTZ','590.423.5567',to_date('1999-02-07','YYYY-MM-DD'),'IT_PROG',4200,NULL,103,60);
insert into employees values(124,'Kevin','Mourgos','KMOURGOS','650.123.5234',to_date('1999-11-16','YYYY-MM-DD'),'ST_MAN',5800,NULL,100,50);
insert into employees values(141,'Trenna','Rajs','TRAJS','650.121.8009',to_date('1995-10-17','YYYY-MM-DD'),'ST_CLERK',3500,NULL,124,50);
insert into employees values(142,'Curtis','Davies','CDAVIES','650.121.2994',to_date('1997-01-29','YYYY-MM-DD'),'ST_CLERK',3100,NULL,124,50);
insert into employees values(143,'Randall','Matos','RMATOS','650.121.2874',to_date('1998-05-15','YYYY-MM-DD'),'ST_CLERK',2600,NULL,124,50);
insert into employees values(144,'Peter','Vargas','PVARGAS','650.121.2004',to_date('1998-07-09','YYYY-MM-DD'),'ST_CLERK',2500,NULL,124,50);
insert into employees values(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018',to_date('2000-06-29','YYYY-MM-DD'),'SA_MAN',10500,.2,100,80);
insert into employees values(174,'Ellen','Abel','EABEL','011.44.1644.429267',to_date('1996-05-11','YYYY-MM-DD'),'SA_REP',11000,.3,149,80);
insert into employees values(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265',to_date('1998-05-24','YYYY-MM-DD'),'SA_REP',8600,.2,149,80);
insert into employees values(178,'Kimberely','Grant','KGRANT','011.44.1644.429263',to_date('1999-05-24','YYYY-MM-DD'),'SA_REP',7000,.15,149,NULL);
insert into employees values(200,'Jennifer','Whalen','JWHALEN','515.123.4444',to_date('1987-09-17','YYYY-MM-DD'),'AD_ASST',4400,NULL,101,10);
insert into employees values(201,'Michael','Hartstein','MHARTSTE','515.123.5555',to_date('1996-02-17','YYYY-MM-DD'),'MK_MAN',13000,NULL,100,20);
insert into employees values(202,'Pat','Fay','PFAY','603.123.6666',to_date('1997-08-17','YYYY-MM-DD'),'MK_REP',6000,NULL,201,20);
insert into employees values(205,'Shelley','Higgins','SHIGGINS','515.123.8080',to_date('1994-06-07','YYYY-MM-DD'),'AC_MGR',12000,NULL,101,110);
insert into employees values(206,'William','Gietz','WGIETZ','515.123.8181',to_date('1994-06-07','YYYY-MM-DD'),'AC_ACCOUNT',8300,NULL,205,110);

以上为本人学习Oracle递归操作后的个人理解与总结,如有错误还望指正修改,欢迎交流~

转载留个言哈~

  • 4
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值