oracle之with

oracle之with

2009-07-21 13:19

with as 在 select 语句前面

with
tmp_aa as (
select 1 aa from dual
),
tmp_bb as (
select 'bb' bb from dual
)
,
tmp_cc as (
select sysdate cc from dual
)
select * from tmp_aa,tmp_bb,tmp_cc

with
tmp_aa as (
   select user_no from bs_gift_detail where bus_id = 'dghdz007'
   and gift_count = 30
   group by user_no
)
select a.user_no,a.gift_count,c.user_no from thz_dghdz007_prize_user a
left join tmp_aa c on a.user_no = c.user_no
where c.user_no is not null

with
tmp_aa as (
   select user_no,sum(gift_count) gift_count from bs_gift_detail where bus_id = 'dghdz007'
   group by user_no
)
select u.gift_count,s.user_no,s.last_score,t.d_value,decode(c.gift_count,null,0,c.gift_count),decode(i.user_no,null,'否','是')
from thz_dghdz007_prize_score s
left join thz_dghdz007_prize_user u on s.user_no = u.user_no
left join num_layout n on n.seg = substr(s.user_no,1,7) --and n.city = 769
left join club.tmp_dictionary t on t.d_key = n.number_type
left join tmp_aa c on s.user_no = c.user_no
left join club.members_info i on i.user_no = s.user_no

这样的SQL可以在不使用临时表或者视图的时候快速的进行查询结果集再次查询。

--这样的修改 with as 的绝妙用法
SQL> update test2 set spc = (
2         with myque as (select rn, skycode id,
3                          decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + 1 sp,
4                          decode(instr(skycode, '-', 1, rn), 0, length(skycode) + 1,
5                                                                instr(skycode, '-', 1, rn)) ep
6                       from (select rownum rn from dual connect by rownum<=20) a, test2 b
7                       where decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + instr(skycode, '-', 1, rn) > 0 )
8         select substr(max(sys_connect_by_path(b.name, '-')),2) name
9            from myque a, test b
10            where b.citycode = substr(a.id, sp, ep-sp) and a.id=test2.skycode
11            start with rn=1 connect by rn-1=prior rn and a.id = prior a.id );

Oracle9i新增WITH语法，可以将查询中的子查询命名，放到SELECT语句的最前面。

　　一个简单的例子:

SQL> WITH
2 SEG AS (SELECT SEGMENT_NAME, SUM(BYTES)/1024 K FROM USER_SEGMENTS GROUP BY SEGMENT_NAME),
3 OBJ AS (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS)
4 SELECT O.OBJECT_NAME, OBJECT_TYPE, NVL(S.K, 0) SIZE_K
5 FROM OBJ O, SEG S
6 WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+)
7 ;
OBJECT_NAME OBJECT_TYPE SIZE_K
------------------------------ ------------------- ----------
DAIJC_TEST TABLE 128
P_TEST PROCEDURE 0
IND_DAIJC_TEST_C1 INDEX 128

　　通过WITH语句定义了两个子查询SEG和OBJ，在随后的SELECT语句中可以直接对预定义的子查询进行查询。从上面的例子也可以看出，使用WITH语句，将一个包含聚集、外连接等操作SQL清晰的展现出来。

　　WITH定义的子查询不仅可以使查询语句更加简单、清晰，而且WITH定义的子查询还具有在SELECT语句的任意层均可见的特点。

　　即使是在WITH的定义层中，后定义的子查询都可以使用前面已经定义好的子查询:

SQL> WITH
2 Q1 AS (SELECT 3 + 5 S FROM DUAL),
3 Q2 AS (SELECT 3 * 5 M FROM DUAL),
4 Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
5 SELECT * FROM Q3;
S M S+M S*M
---------- ---------- ---------- ----------
8 15 23 120

　　利用WITH定义查询中出现多次的子查询还能带来性能提示。Oracle会对WITH进行性能优化，当需要多次访问WITH定义的子查询时，Oracle会将子查询的结果放到一个临时表中，避免同样的子查询多次执行，从而有效的减少了查询的IO数量。

可惜的是，WITH语句只能用在SELECT语句中，UPDATE和DELETE语句不支持WITH语法:

SQL> SET AUTOT OFF
SQL> SET TIMING OFF
SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)
2 SELECT ID, NAME FROM T_WITH WHERE ID IN (SELECT * FROM SUBQ);

ID NAME
---------- ------------------------------
1 STANDARD

SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)
2 UPDATE T_WITH SET ID = 1 WHERE ID IN (SELECT * FROM SUBQ);
UPDATE T_WITH SET ID = 1 WHERE ID IN (SELECT * FROM SUBQ)
*第 2 行出现错误:
ORA-00928: 缺失 SELECT 关键字

SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)
2 DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ);
DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ)

*第 2 行出现错误:
ORA-00928: 缺失 SELECT 关键字