Oracle with子句的简单介绍.

原创 2013年12月03日 15:42:10

在ocp题库中有一题是关于with语句的.


Which statements are true regarding the usage of the WITH clause in complex correlated subqueries?
(Choose all that apply.)
A. It can be used only with the SELECT clause.
B. The WITH clause can hold more than one query.
C. If the query block name and the table name were the same, then the table name would take
precedence.
D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to
the main query block.


下面会以这题作个例子简单介绍下with子句的作用与用法.


1.with子句的作用

实际上, Oracle可以利用with子句为子查询的数据集作为1个内存临时表.  在内存中解析,提高了执行效率. 而且提高了SQL语句的可读性.


2.with语句的用法与特性

语法很简单:

下面是1个简单的例子:

with 
   Q1 as (select product_id, product_name from products where rownum <= 10)

select * from Q1 ;

PRODUCT_ID PRODUCT_NAME                                                                                                                
---------- -----------------------------------------------------------------------------------------------------------------------------
      1797 Inkjet C/8/HQ                                                                                                                 
      2459 LaserPro 1200/8/BW                                                                                                            
      3127 LaserPro 600/6/BW                                                                                                             
      2254 HD 10GB /I                                                                                                                    
      3353 HD 10GB /R                                                                                                                    
      3069 HD 10GB /S                                                                                                                    
      2253 HD 10GB @5400 /SE                                                                                                             
      3354 HD 12GB /I                                                                                                                    
      3072 HD 12GB /N                                                                                                                    
      3334 HD 12GB /R 

可以见到,  上面的Q1为括号的子查询的别名, 相当于在内存里建立了1张临时表.  下面的select 语句就直接检索这张临时表.


下面是一些比较重要的特性.

2.1 with子句后必须接着select 语句, 否则出错

例如下面的例子

with 
   Q1 as (select product_id, product_name from products where rownum <= 10)
   
   delete from products where 1 =2 ;
   
   select * from products where 1 =2;

在行 14 上开始执行命令时出错:
with 
   Q1 as (select product_id, product_name from products where rownum <= 10)
   
   delete from products where 1 =2 
命令出错, 行: 17 列: 4
错误报告:
SQL 错误: ORA-00928: 缺失 SELECT 关键字
00928. 00000 -  "missing SELECT keyword"
*Cause:    
*Action:
未选择任何行

2.2 1个with子句里可以定义多个内存临时表, 而且可以互相使用.

如下面的经典例子:

WITH  
Q1 AS (SELECT 3 + 5 S FROM DUAL),  
    Q2 AS (SELECT 3 * 5 M FROM DUAL),  
    Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)  
SELECT * FROM Q3; 

         S          M        S+M        S*M
---------- ---------- ---------- ----------
         8         15         23        120 


可以简单上面定义了3个那次临时表Q1, Q2, Q3, 其中Q3 使用了临时表Q1 和 Q2



2.3 with定义的内存临时表一旦被select 语句检索一次, 那么系统就会在内存中清理掉这张临时表

如上面的例子增加一句select * from Q2.
WITH  
Q1 AS (SELECT 3 + 5 S FROM DUAL),  
    Q2 AS (SELECT 3 * 5 M FROM DUAL),  
    Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)  
SELECT * FROM Q3; 
select * from Q2;

ORA-00942: 表或视图不存在
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
行 42 列 15 出错

出错了, 因为Q2 已经被with里的Q3检索过一次, 被删掉了.  实际上再select 一次 Q3也是会出错的.

所以网上所讲, with定义的临时表能多次使用我觉得是错的.


可能内存空间珍贵的原因, oracle为with语句设定这个机制, 请允许我做1个悲伤的表情囧.


2.4 with子句定义的临时表命不能于已存在的表名重复.

例子

with
   products as (select product_id, product_name from products where rownum <= 10)

select * from products ;

在行 21 上开始执行命令时出错:
with
   products as (select product_id, product_name from products where rownum <= 10)

select * from products 
命令出错, 行: 22 列: 54
错误报告:
SQL 错误: ORA-32039: 递归 WITH 子句必须具有列别名列表



3. 最后解析下开头的题目选项

选项A的意思是with子句必须用于select语句中.

根据2.1 和 2.3 的特性, 可见这个选项是正确的, with定义的内存临时表不能用于除 select 语句外的DML语句.


选项B的意思是, 1个with子句中可以定义多个内存临时表.

根据2.2的特性, 也是正确的.


选项C的意思是, 如果with定义的内存临时表名与真实表名重复, 会被优先认为是真实表名.

根据2.4 特性, 是错误的.


选项D的意思是,  with子句里其中1个内存临时表能被这个with子句内的其他内存临死表使用,

根据2.3特性, 也是正确的.


所以答案是ABD.









版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

Oracle 11g的递归with语句

废话不多说,直接上例子感受一下: with emps (employee_id, name, job_id, salary, lvl) as ( select employee_id, first...

高级sql学习——with子句!!!

使用WITH AS 语句可以为一个子查询语句块定义一个名称,使用这个子查询名称可以在查询语句的很多地方引用这个子查询。Oracle 数据库像对待内联视图或临时表一样对待被引用的子查询名称,从而起到一定...

关于oracle with table as 创建临时表的用法示例以及使用with as 的优点

关于oracle with table as 创建临时表的用法示例 1、with table as 相当于建个临时表(用于一个语句中某些中间结果放在临时表空间的SQL语句),Oracle 9i 新增...
  • haiross
  • haiross
  • 2013年12月26日 14:00
  • 10098

oracle中用start with...connect by prior子句实现递归查询[例子不错]

今天在做权限这一块,碰到要读取oracle中的树形结构,所以就用到了start with...connect by prior。所以留个脚印以后碰到可以看看。     在oracle中的s...

Oracle中start with...connect by prior子句用法

connect by 是结构化查询中用到的,其基本语法是: select ... from tablename start with 条件1 connect by 条件2 where 条件3;例:se...
  • ben1247
  • ben1247
  • 2011年07月19日 10:18
  • 390

oracle中用START WITH...CONNECT BY PRIOR子句实现递归查询

转载于:http://qingfeng825.iteye.com/blog/197099 http://www.iteye.com/topic/287749     今天发现在orac...
  • zgmzyr
  • zgmzyr
  • 2011年08月15日 07:58
  • 492

Oracle中start with...connect by prior子句用法

Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的,其基本语法是:  select ... from tablenam...

[层次结构|树状结构] oracle中用start with...connect by prior子句实现递归查询

oracle 家族树 语法: select column from table_name start with column=value connect by prior 父主键=子主键 --自顶向...

Oracle中start with...connect by prior子句用法

http://www.cnblogs.com/ZHF/archive/2008/09/10/1288101.html   Oracle中start with...connect by prior子...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle with子句的简单介绍.
举报原因:
原因补充:

(最多只允许输入30个字)