PL/SQL高级应用(3)-with子句-ZT

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 3.with 子句

9i新增语法

1.使用with子句可以让子查询重用相同的with查询块,通过select调用，一般在with查询用到多次情况下。

 

2.with子句的返回结果存到用户的临时表空间中，只做一次查询，提高效率。

 

3.有多个查询的时候，第1个用with，后面的不用with，并且用逗号隔开。

 

5.最后一个with子句与下面的查询之间不能有逗号，只通过右括号分割,查询必须用括号括起来

 

6.如果定义了with子句，而在查询中不使用，那么会报ora-32035错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询）

 

7.前面的with子句定义的查询在后面的with子句中可以使用。

With子句目的是为了重用查询。

 

语法：

With alias_name as (select1), --as和select中的括号都不能省略

alias_name2 as (select2),--后面的没有with，逗号分割

…

alias_namen as (select n) –与下面的查询之间没有逗号

Select ….

如查询销售部门员工的姓名：

  --with clause

with a as

     (select id from s_dept where name='Sales' order by id)

  select last_name,title

         from s_emp where dept_id in (select * from a);--使用select查询别名

 

使用with子句，可以在复杂的查询中预先定义好一个结果集，然后在查询中反复使用，不使用会报错。而且with子句获得的是一个临时表，如果在查询中使用，必须采用select  from with查询名，比如

With cnt as(select count(*) from table)

Select cnt+1 from dual;

是错误的。必须是

   With cnt as(select count(*) shumu from user_tables)

Select shumu+1 from cnt;

--直接引用with子查询中的列别名。

 

        一个with查询的实例：

        查询出部门的总薪水大于所有部门平均总薪水的部门。部门表s_dept，员工表s_emp。

分析：做这个查询，首先必须计算出所有部门的总薪水，然后计算出总薪水的平均薪水，再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。那么第1步 with查询查出所有部门的总薪水，第2步用with从第1步获得的结果表中查询出平均薪水，最后利用这两次的with查询比较总薪水大于平均薪水的结 果，如下：

with

--step1:查询出部门名和部门的总薪水

dept_costs as(

            select a.name,sum(b.salary) dept_total

              from

                      s_dept a,s_emp b

                     where a.id=b.dept_id

                     group by a.name

),

--step2:利用上一个with查询的结果，计算部门的平均总薪水

avg_costs as(

           select sum(dept_total)/count(*) dept_avg

            from dept_costs        

)

--step3:从两个with查询中比较并且输出查询结果

select name,dept_total

  from dept_costs

  where

   dept_total>

    (

     select dept_avg

      from

     avg_costs

    )

   order by name;

 

从上面的查询可以看出，前面的with查询的结果可以被后面的with查询重用，并且对with查询的结果列支持别名的使用，在最终查询中必须要引用所有with查询，否则会报错ora-32035错误。

 

 

 

再如有这样一个需求：一个查询，如果查询的结果行不满足是10的倍数，则补空行，直到是查询出的行数是10的倍数。例如：select * from trademark这个查询。

with cnt as (select 10-mod(count(*),10) shumu from trademark) –查询比10的倍数差几个空行

select id,name

  from trademark

union all        --空行加进去

select null,null  --补空行

from dual connect by rownum<=(select shumu from cnt); --10个中connect by可以使用子查询

10g之前的写法

with cnt as (select 10-mod(count(*),10) shumu from trademark) –查询比10的倍数差几个空行

select id,name

  from trademark

union all        --空行加进去

select null,null  --补空行

from all_objects where rownum<=(select shumu from cnt);-- 使用 all_objects 行比较多

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

转载于:http://blog.itpub.net/293106/viewspace-580567/