国内最全IT社区平台 联系我们 | 收藏本站
华晨云阿里云优惠2
您当前位置:首页 > 数据库 > 数据库应用 > Oracle 递归查询详解

Oracle 递归查询详解

来源:程序员人生   发布时间:2015-02-27 07:53:43 阅读次数:4618次

创建表和主外键

CREATE TABLE SC_DISTRICT ( ID NUMBER(10) NOT NULL, PARENT_ID NUMBER(10), NAME VARCHAR2(255 BYTE) NOT NULL ); ALTER TABLE SC_DISTRICT ADD ( CONSTRAINT SC_DISTRICT_PK PRIMARY KEY (ID)); ALTER TABLE SC_DISTRICT ADD ( CONSTRAINT SC_DISTRICT_R01 FOREIGN KEY (PARENT_ID) REFERENCES SC_DISTRICT (ID) );

插入数据

insert into A_DISTRICT (id, parent_id, name) values (1, null, '河南省'); insert into A_DISTRICT (id, parent_id, name) values (2, null, '山东省'); insert into A_DISTRICT (id, parent_id, name) values (3, 1, '郑州市'); insert into A_DISTRICT (id, parent_id, name) values (4, 1, '开封市'); insert into A_DISTRICT (id, parent_id, name) values (5, 1, '商丘市'); insert into A_DISTRICT (id, parent_id, name) values (6, 3, '中原区'); insert into A_DISTRICT (id, parent_id, name) values (7, 3, '金水区'); insert into A_DISTRICT (id, parent_id, name) values (8, 3, '27区'); insert into A_DISTRICT (id, parent_id, name) values (9, 3, '城管回族区'); insert into A_DISTRICT (id, parent_id, name) values (10, 3, '上街区'); insert into A_DISTRICT (id, parent_id, name) values (11, 3, '惠济区'); insert into A_DISTRICT (id, parent_id, name) values (12, 4, '开封县'); insert into A_DISTRICT (id, parent_id, name) values (13, 4, '杞县'); insert into A_DISTRICT (id, parent_id, name) values (14, 4, '兰考县'); insert into A_DISTRICT (id, parent_id, name) values (15, 4, '通许县'); insert into A_DISTRICT (id, parent_id, name) values (16, 13, '城关镇'); insert into A_DISTRICT (id, parent_id, name) values (17, 13, '5里河镇'); insert into A_DISTRICT (id, parent_id, name) values (18, 13, '邢口镇'); insert into A_DISTRICT (id, parent_id, name) values (19, 13, '柿园乡'); insert into A_DISTRICT (id, parent_id, name) values (20, 13, '城郊乡');
生成数据以下

递归语法和主要使用函数

      select * from 表 start with 条件入口 connect by prior id =  parent_id(向下)

      select * from 表 start with 条件入口 connect by prior parent_id = id(向上)

如果不写prior,表明前序遍历 未指明遍历方向,所以不进行递归,习惯上会在语句后面加上order by排序和group by分组

      connect_by_root(列名)  查询根节点

      connect_by_isleaf   查询是不是叶子节点,0是,1不是

      sys_connect_by_path  查询递归路径

实战

查询根节点

select id, parent_id, name from a_district start with parent_id is null connect by id = parent_id order by id

递归查询 杞县 的所有上级节点(包括当前节点)

select id, parent_id, name from a_district start with name='杞县' connect by prior parent_id = id order by id

递归查询 杞县 的所有上级节点(包括当前节点和根节点)

select id, parent_id, name,connect_by_root(id) city_id, connect_by_root(name) city_name from a_district start with name='杞县' connect by prior parent_id = id order by id


递归查询 杞县 的所有子城镇乡(包括当前节点)

select id, parent_id, name from a_district start with name='杞县' connect by prior id = parent_id order by id

递归查询 开封市 的所有城镇乡(包括当前节点和根节点)

select id, parent_id, name, connect_by_root(id) city_id, connect_by_root(name) city_name from a_district start with name='开封市' connect by prior id = parent_id order by id

上行递归查询 城郊乡 深度、是不是包括叶子节点

select id, name, parent_id, level, connect_by_isleaf isleaf from a_district start with name = '城郊乡' connect by prior parent_id = id order by id

下行递归查询 河南省 深度、是不是包括叶子节点

select id, name, parent_id, level, connect_by_isleaf isleaf from a_district start with name = '河南省' connect by prior id = parent_id order by id

上行递归查询 杞县 路径

select id, name, parent_id, substr(sys_connect_by_path(name,'->'),3) name_path from a_district start with name='杞县' connect by prior parent_id = id order by id

下行递归查询 河南省 路径

select id, name, parent_id, substr(sys_connect_by_path(name,'->'),3) name_path from a_district start with name='河南省' connect by prior id = parent_id order by id

综合使用

select id, name, parent_id, level, connect_by_isleaf isleaf, substr(sys_connect_by_path(name,'->'),3) name_path from a_district start with name='河南省' connect by prior id = parent_id order by id

SYS_CONNECT_BY_PATH函数

        在Oracle中,SYS_CONNECT_BY_PATH函数主要作用是可以把1个父节点下的所有子节点通过某个字符进行辨别,然后连接在1个列中显示。
        sys_connect_by_path(字段名, 2个字段之间的连接符号),注意这里的连接符号不要使用逗号,oracle会报错,如果1定要用,可使用replace替换1下,REPLACE(字段名,原字符,',')。
        还有,这个函数使用之前必须先建立1个树,否则无用。



生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------
分享到:
------分隔线----------------------------
关闭
程序员人生