--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