Oracle 11GR2的递归WITH子查询方法
下面给大家详细介绍Oracle 11GR2的递归WITH子查询方法,具体内容如下所示: with emp_data(ename,empno,mgr,l) as (select ename,1 lvl from emp where mgr is null union all select emp.ename,emp.empno,emp.mgr,ed.l+1 from emp,emp_data ed where emp.mgr = ed.empno ) SEARCH DEPTH FIRST BY ename SET order_by select l,lpad('*',2*l,'*')||ename nm from emp_data order by order_by / L NM 14 rows selected. 不知道真用起来怎么样,按我的想象可以比原来的SYS_CONNECT_BY_PATH多玩出很多新花样,比如按路径累加,更灵活的剪枝条件, WITH子查询也称为CTE (Common Table Expression),是ANSI SQL-99标准的一部分。ORACLE从9i开始引入WITH子查询,把它被称作SUBQUERY FACTORING(分解子查询)。 WITH子查询的作用类似于内联视图(INLINE VIEW)。内联视图的定义写作SQL的FROM 后面,只能够引用一次;而WITH子查询需要在引用之前先定义,一旦定义了在整个查询的后续部分就可以按名称来反复引用,从这点来看又很像临时表。 从版本11GR2开始,ORACLE支持递归的WITH,即允许在WITH子查询的定义中对自身引用。这不是什么新鲜事,其他数据库如DB2,Firebird,Microsoft SQL Server,PostgreSQL 都先于ORACLE支持这一特性。但对于ORACLE用户来说,这一递归特性还是很令人期待的,利用它可以轻易实现以往做不到的、或者很难做到的许多新功能。这一章我们就来探索这一令人兴奋的新特性,并把它和以往的实现手段(主要是CONNECT BY层次查询)作比较。 我们先来看看这个递归WITH子查询的语法: WITH ① query_name ([c_alias [,c_alias]...]) ①这是子查询的名称,和以往不同的是,必须在括号中把这个子查询的所有列名写出来。 subquery部分由两个成员组成:anchor member(锚点成员) 和 recursive member(递归成员)。它们之间必须用union all联合起来,anchor member 必须写在recursive member前面。 递归成员也是一个SELECT语句,用于定义上下级的关系,它必须包含自身名称(即query_name),而且仅仅只能引用一次。递归正是体现在对于自身的引用。典型的做法就是把query_name和其他表(一般来说就是你要遍历的表)做一个连接,连接条件表明了上下级的关系。必须注意,在这个query_name中,并不是截止目前为止的所有数据都是可见的,可见的只是上次递归新加入的最近的一层数据。对query_name列的引用相当于CONNECT BY中的PRIOR操作符。当找不到满足条件的下级,遍历就会停止;如果你还有其他的递归出口条件,也可以一起写在WHERE中,当WHERE不满足时,遍历就会停止,这就是在遍历树、图时候的剪枝操作。越早停止则效率越高。 这个递归成员就是程序员发挥创造力的地方,以往在CONNECT BY中做不到的事情,比如沿路径求和、求积等运算,现在都轻而易举。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'来实现。 搜索子句(search_clause)和循环子句(cycle_clause)我们后面的例子中会见到。 下面我们就来看看递归WITH子查询的用法实例。 例1: 先来一个简单例子,从scott/tiger的emp表来查找上下级关系: 传统的CONNECT BY写法: 新的递归WITH写法: 查询结果: 14 rows selected. 从结果集的THE_LEVEL和PATH列可以清楚地看到数据是如何被一层一层叠加上去的。 例2: 构造等差数列: CONNECT BY写法: 这是一个非常特殊的用法,因为没有上下级关系,只有遍历的终止条件。像这类CONNECT BY我强烈推荐在只有一行的结果集上运行(比如FROM DUAL,比如从一个聚合后的子查询),在多行的集合上运行比较难以控制,头脑必须很清醒。 (以下ROWNUM全部可以改成 LEVEL,效果一样): 结果: N N2 DT MON 10 rows selected. 这个简洁优雅的写法最早由Mikito Harakiri(从名字看是个日本人)在asktom网站()发表,现在已经风靡全世界的ORACLE社区。在这个方法被发现之前,一般采用的是从一个大的集合(表或视图)中获取ROWNUM的方法: 下面尝试用递归WITH的写法: 一切都按规矩来,竟然还是出错了: 改为字符串型看看: 我很惊奇地看到这个结果: N N2 DT MON 10 rows selected. 这是ORACEL 11.2.0.1.0版本的BUG,后续版本应该会改正。 没办法,只好想其他招数绕过去: 这下子对了: N N2 DT MON 10 rows selected. 看来对日期的运算有BUG。解决办法就是先构造整数序列,然后在最终的查询中再利用这个整数序列来构造日期序列。 从一个单行结果集CONNECT BY的例子: 结果: RN CNT 14 rows selected. 递归WITH写法: 结果同上(略)。 例3: 独立事件的排列组合:一个布袋中装有数量相同的四种颜色的小球。随机从布袋中取四次,每次取完都放回去。现在问四次结果总颜色数等于3的概率是多少? 传统的CONNECT BY写法: 结果: PROB 这个例子展示了CONNECT BY来模拟排列组合的技巧。每一层遍历表示一次抽取的动作,因为每次都是完全独立的,在CONNECT BY 里面仅仅限制了抽取次数(遍历层数)而没有其他条件。SYS_CONNECT_BY_PATH可以把截至当前为止所访问到的各层次的数据串起来,在LEVEL=N就包含了前N层的排列组合情况。你可以用这个查询来看看中间生成的结果集t2: ID PATH CNT 256 rows selected. 由此看到PATH列已经包含了四次抽取的所有可能结果,每个结果都被赋予一个唯一的编号ID。 如果你好奇的话可以看看下一步的结果集t3: ID CNT COLO 1024 rows selected. 可以看到t2集合中的每一行都被拆成了四行,这是为了后面的聚合运算。 最后看看算概率的主查询: COUNT(DISTINCT color)可以算出每个ID中包含不重复的颜色数目,放在HAVING中过滤了数目不为3的那些ID。 GROUP BY id,cnt 表示按照id来分组。因为所有行的cnt都是一样的(都等于256),我们在分组加入它并不会改变分组的结果,加入cnt的目的是为了在查询中引用。 本例是一个在多行的结果集上进行无条件遍历的例子,前面说过了要特别小心,因为没有上下级关系,随着层数递增,数据量的增长十分可观。 递归WITH写法: 在递归WITH子查询t2中,我们看到它用了一个CASE表达式把以前没出现过的颜色拼接到distinct_colors中。这个CASE是递归WITH的妙处,用SYS_CONNECT_BY_PATH没办法做到有条件的拼接。 而最后在计算颜色数的时候用了一个技巧,把颜色数转换为斜杠的个数,因为我们构造数据的时候每种颜色前面都带一个斜杠。为了求出字符串中某字符出现的次数,我们用了这样的办法: 先求出字符串的总长度; 用REPLACE函数从串中去除这个字符,然后再求一次长度; 两个长度之差就是被去除的字符个数。 CASE函数把出现满足条件的标记置为1,不满足则为NULL,那么再套一个COUNT函数就能算出满足条件的行数,因为NULL是不被COUNT计入的。 COUNT和CASE的嵌套使用,也是在聚合运算中常用的技巧。 这个颜色数的计算,我们也可以在递归的过程中进行有条件累加,这样最后就可以直接使用: 例4: 构造一个二阶等差数列:这个数列的各项之差是一个等差数列 比如:1,6,10,15,21,... 用CONNECT BY: 结果: LEVEL N 10 rows selected. 因为只有一条路径,所以用分析函数SUM很轻易做到了。 递归WITH写法: 结果: LVL N 例5: 构造斐波那契数列: 指的是这样一个数列,从第三项开始,每一项都等于前两项之和。 传统的CONNECT BY方法做不出来,但是用10G以上所支持的MODEL可以轻松构造: RN N 10 rows selected. 用递归WITH的写法: N 10 rows selected. 例6: 排列组合: 从5个数中取3个的所有组合C(3,5): CONNECT BY写法: XMLPATH 递归WITH写法: XMLPATH 10 rows selected. 如果要的不是组合而是排列,比如P(3,5)可以这么写: PRIOR rn AND LEVEL<=3;XMLPATH 60 rows selected. 和刚才的组合写法相比,rn 如果不写这个NOCYCLE会怎么样? PRIOR rn AND LEVEL<=3;ERROR: 可以看到,这个NOCYCLE是很重要的,ORACLE不允许遍历顺序中出现循环。 在递归WITH中,NOCYCLE的写法: t.rn AND t2.lvl<3 ) CYCLE rn SET cycle_flag TO 'Y' DEFAULT 'N' ---- 这个cycle_flag是自己定义的伪列名和值,可以起到CONNECT_BY_ISCYCLE同样的作用 SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';结果: XMLPA L NM 14 rows selected. 不知道真用起来怎么样,按我的想象可以比原来的SYS_CONNECT_BY_PATH多玩出很多新花样,比如按路径累加,更灵活的剪枝条件, WITH子查询也称为CTE (Common Table Expression),是ANSI SQL-99标准的一部分。ORACLE从9i开始引入WITH子查询,把它被称作SUBQUERY FACTORING(分解子查询)。 WITH子查询的作用类似于内联视图(INLINE VIEW)。内联视图的定义写作SQL的FROM 后面,只能够引用一次;而WITH子查询需要在引用之前先定义,一旦定义了在整个查询的后续部分就可以按名称来反复引用,从这点来看又很像临时表。 从版本11GR2开始,ORACLE支持递归的WITH,PostgreSQL 都先于ORACLE支持这一特性。但对于ORACLE用户来说,这一递归特性还是很令人期待的,利用它可以轻易实现以往做不到的、或者很难做到的许多新功能。这一章我们就来探索这一令人兴奋的新特性,并把它和以往的实现手段(主要是CONNECT BY层次查询)作比较。 我们先来看看这个递归WITH子查询的语法: WITH ① query_name ([c_alias [,c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]... ①这是子查询的名称,和以往不同的是,必须在括号中把这个子查询的所有列名写出来。 subquery部分由两个成员组成:anchor member(锚点成员) 和 recursive member(递归成员)。它们之间必须用union all联合起来,anchor member 必须写在recursive member前面。 anchor member用来定位递归的入口,锚点成员是一个SELECT语句,它不可以包含自身名称(query_name)。这相当于CONNECT BY查询中的START WITH,典型写法就是: SELECT ... FROM 要遍历的表 WHERE ... (起始条件) 递归成员也是一个SELECT语句,用于定义上下级的关系,它必须包含自身名称(即query_name),而且仅仅只能引用一次。递归正是体现在对于自身的引用。典型的做法就是把query_name和其他表(一般来说就是你要遍历的表)做一个连接,连接条件表明了上下级的关系。必须注意,在这个query_name中,并不是截止目前为止的所有数据都是可见的,可见的只是上次递归新加入的最近的一层数据。对query_name列的引用相当于CONNECT BY中的PRIOR操作符。当找不到满足条件的下级,遍历就会停止;如果你还有其他的递归出口条件,也可以一起写在WHERE中,当WHERE不满足时,遍历就会停止,这就是在遍历树、图时候的剪枝操作。越早停止则效率越高。 这个递归成员就是程序员发挥创造力的地方,以往在CONNECT BY中做不到的事情,比如沿路径求和、求积等运算,现在都轻而易举。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'来实现。 搜索子句(search_clause)和循环子句(cycle_clause)我们后面的例子中会见到。 下面我们就来看看递归WITH子查询的用法实例。 例1: 先来一个简单例子,从scott/tiger的emp表来查找上下级关系: 传统的CONNECT BY写法: 新的递归WITH写法: 查询结果: EMPNO ENAME JOB MGR DEPTNO THE_LEVEL PATH TOP_MANAGE 14 rows selected. 从结果集的THE_LEVEL和PATH列可以清楚地看到数据是如何被一层一层叠加上去的。 例2: 构造等差数列: CONNECT BY写法: 这是一个非常特殊的用法,因为没有上下级关系,只有遍历的终止条件。像这类CONNECT BY我强烈推荐在只有一行的结果集上运行(比如FROM DUAL,比如从一个聚合后的子查询),在多行的集合上运行比较难以控制,头脑必须很清醒。 下面尝试用递归WITH的写法: 改为字符串型看看: 我很惊奇地看到这个结果: 10 rows selected. 这是ORACEL 11.2.0.1.0版本的BUG,后续版本应该会改正。 没办法,只好想其他招数绕过去: 这下子对了: N N2 DT MON 10 rows selected. 看来对日期的运算有BUG。解决办法就是先构造整数序列,然后在最终的查询中再利用这个整数序列来构造日期序列。 从一个单行结果集CONNECT BY的例子: 结果: RN CNT 递归WITH写法: 结果同上(略)。 例3: 独立事件的排列组合:一个布袋中装有数量相同的四种颜色的小球。随机从布袋中取四次,每次取完都放回去。现在问四次结果总颜色数等于3的概率是多少? 传统的CONNECT BY写法: 结果: PROB 这个例子展示了CONNECT BY来模拟排列组合的技巧。每一层遍历表示一次抽取的动作,因为每次都是完全独立的,在CONNECT BY 里面仅仅限制了抽取次数(遍历层数)而没有其他条件。SYS_CONNECT_BY_PATH可以把截至当前为止所访问到的各层次的数据串起来,在LEVEL=N就包含了前N层的排列组合情况。你可以用这个查询来看看中间生成的结果集t2: ID PATH CNT 由此看到PATH列已经包含了四次抽取的所有可能结果,每个结果都被赋予一个唯一的编号ID。 如果你好奇的话可以看看下一步的结果集t3: ID CNT COLO 可以看到t2集合中的每一行都被拆成了四行,这是为了后面的聚合运算。 最后看看算概率的主查询: 最后的连续两层COUNT函数的意思是要把分组结果再聚合为一行,算出满足条件的id的行数。除以cnt就得到了我们要的概率。 本例是一个在多行的结果集上进行无条件遍历的例子,前面说过了要特别小心,因为没有上下级关系,随着层数递增,数据量的增长十分可观。 递归WITH写法: 在递归WITH子查询t2中,我们看到它用了一个CASE表达式把以前没出现过的颜色拼接到distinct_colors中。这个CASE是递归WITH的妙处,用SYS_CONNECT_BY_PATH没办法做到有条件的拼接。 而最后在计算颜色数的时候用了一个技巧,把颜色数转换为斜杠的个数,因为我们构造数据的时候每种颜色前面都带一个斜杠。为了求出字符串中某字符出现的次数,我们用了这样的办法: 先求出字符串的总长度; 用REPLACE函数从串中去除这个字符,然后再求一次长度; 两个长度之差就是被去除的字符个数。 CASE函数把出现满足条件的标记置为1,那么再套一个COUNT函数就能算出满足条件的行数,因为NULL是不被COUNT计入的。 COUNT和CASE的嵌套使用,也是在聚合运算中常用的技巧。 这个颜色数的计算,我们也可以在递归的过程中进行有条件累加,这样最后就可以直接使用: 例4: 构造一个二阶等差数列:这个数列的各项之差是一个等差数列 比如:1,SUM(LEVEL) OVER(ORDER BY LEVEL) n FROM DUAL CONNECT BY LEVEL<=10; 结果: LEVEL N 10 rows selected. 因为只有一条路径,所以用分析函数SUM很轻易做到了。 递归WITH写法: 结果: LVL N 例5: 构造斐波那契数列: 指的是这样一个数列,从第三项开始,每一项都等于前两项之和。 1,...... 传统的CONNECT BY方法做不出来,但是用10G以上所支持的MODEL可以轻松构造: / 用递归WITH的写法: N 例6: 排列组合: 从5个数中取3个的所有组合C(3,5): CONNECT BY写法: 递归WITH写法: 如果要的不是组合而是排列,比如P(3,') xmlpath FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) WHERE LEVEL=3 CONNECT BY rn<>PRIOR rn AND LEVEL<=3; ERROR: ORA-01436: CONNECT BY loop in user data 可以看到,这个NOCYCLE是很重要的,ORACLE不允许遍历顺序中出现循环。 在递归WITH中,NOCYCLE的写法: t.rn AND t2.lvl<3 ) CYCLE rn SET cycle_flag TO 'Y' DEFAULT 'N' ---- 这个cycle_flag是自己定义的伪列名和值,可以起到CONNECT_BY_ISCYCLE同样的作用 SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';结果: XMLPA 以上所述是小编给大家介绍的Oracle 11GR2的递归WITH子查询方法,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对编程之家网站的支持! (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |