oracle 11g新特性---WITH语句增强

--with语句增强，11gR2增强

SQL> select * from test;

        ID NAME                 ADDR
---------- -------------------- --------------------
         1 wzk                  chengdu
         2 cherry               chengdu
         3 wzk                  heilongjiang
         4 cherry               beijing

--with语句增加了别名，但是个数和顺序与as后面的子查询一一对应。
SQL> with c(id,name) as
  2  (select id,name,addr from test where id=1 or id=2)
  3  select * from c;
(select id,name,addr from test where id=1 or id=2)
                          *
ERROR at line 2:
ORA-32038: number of WITH clause column names does not match number of elements
in select list

--也可以看出select查询之后，返回的列名就是我们在with子句中定义的别名。
SQL> with c(no,name,address) as
  2  (select id,name,addr from test where id=1 or id=2)
  3  select * from c;

        NO NAME                 ADDRESS
---------- -------------------- --------------------
         1 wzk                  chengdu
         2 cherry               chengdu

--10g允许吗？可以看见10g是不允许的。
ODS>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

ODS>with c(no,name,address) as
  2  (select id,name,addr from test where id=1 or id=2)
  3  select * from c;
with c(no,name,address) as
      *
第 1 行出现错误:
ORA-32033: unsupported column aliasing

ODS>with c(id,name,addr) as
  2   (select id,name,addr from test where id=1 or id=2)
  3   select * from c;
with c(id,name,addr) as
      *
第 1 行出现错误:
ORA-32033: unsupported column aliasing

--测试with的递归查询
create table employees
(employee_id number,          --雇员编号
 employee_name varchar2(10),  --雇员名称
 manger_id number,            --经理编号
 manger_name varchar2(10)     --经理名称
);

insert into employees values(101,'wzk',100,'www'   );
insert into employees values(102,'cherry',100,'www');
insert into employees values(103,'hl',101,'wzk'    );
insert into employees values(104,'hw',101,'wzk'    );
insert into employees values(105,'dw',103,'hl'     );
insert into employees values(106,'wy',102,'cherry' );
insert into employees values(107,'ll',105,'dw'     );

commit;

--统计雇员101下的所有雇员信息。
SQL> with c(eid,ename,mid,mname,lv)
     as
     (select employee_id,employee_name,manger_id,manger_name,0 lv
      from employees
      where employee_id=101
      union all
      select e.employee_id,e.employee_name,e.manger_id,e.manger_name,c.lv+1
      from c,employees e
      where c.eid=e.manger_id
      )
      select * from c;

       EID ENAME             MID MNAME              LV
---------- ---------- ---------- ---------- ----------
       101 wzk               100 www                 0
       103 hl                101 wzk                 1
       104 hw                101 wzk                 1
       105 dw                103 hl                  2
       107 ll                105 dw                  3

--看来with可以用来取代connect by子句了啊。呵呵。
--depth是先返回儿子信息，在返回兄弟信息。（先儿子，后兄弟）
--可以看lv列，看lv为1的列。当查到employee_id为103的时候，他下面既有子女，身边又有兄弟，那么结果集是先返回兄弟数据哪？还是子女数据拿？
--searth自动给with语句添加了一列 order1
SQL> with c(eid,ename,mid,mname,lv)
     as
     (select employee_id,employee_name,manger_id,manger_name,0 lv
      from employees
      where employee_id=101
      union all
      select e.employee_id,e.employee_name,e.manger_id,e.manger_name,c.lv+1
      from c,employees e
      where c.eid=e.manger_id
      )
      search depth first by ename set order1
      select * from c
      order by order1;

       EID ENAME             MID MNAME              LV     ORDER1
---------- ---------- ---------- ---------- ---------- ----------
       101 wzk               100 www                 0          1
       103 hl                101 wzk                 1          2
       105 dw                103 hl                  2          3
       107 ll                105 dw                  3          4
       104 hw                101 wzk                 1          5

--breadth是先返回兄弟信息，后儿子信息（先兄弟，后儿子）
SQL> with c(eid,ename,mid,mname,lv)
     as
     (select employee_id,employee_name,manger_id,manger_name,0 lv
      from employees
      where employee_id=101
      union all
      select e.employee_id,e.employee_name,e.manger_id,e.manger_name,c.lv+1
      from c,employees e
      where c.eid=e.manger_id
      )
      search breadth first by ename set order1
      select * from c
      order by order1;

       EID ENAME             MID MNAME              LV     ORDER1
---------- ---------- ---------- ---------- ---------- ----------
       101 wzk               100 www                 0          1
       103 hl                101 wzk                 1          2
       104 hw                101 wzk                 1          3
       105 dw                103 hl                  2          4
       107 ll                105 dw                  3          5

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/728254/viewspace-620494/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/728254/viewspace-620494/