ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PL/SQL高级应用(3)-with子句-ZT

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

原创 Linux操作系统 作者:spritesong 时间:2009-03-29 22:40:40 0 删除 编辑
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), --asselect中的括号都不能省略

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-28

  • 博文量
    62
  • 访问量
    23784