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...
  • chncaesar
  • chncaesar
  • 2014年01月06日 22:58
  • 3866

oracle中with的用法及用处

========WITH========用于一个语句中某些中间结果放在临时表空间的SQL语句如WITH  channel_summary AS (SELECT channels.channel_des...
  • chenjinlin1
  • chenjinlin1
  • 2011年06月28日 14:07
  • 8569

Oracle with语句的用法

Oracle with语句的用法 Oracle数据库中,使用with语句可以实现子查询,提高语句执行的效率,下文对with语句的用法作了详细的介绍,供您参考学习。 Orac...
  • haiross
  • haiross
  • 2015年01月26日 13:52
  • 3465

oracle with子句

以例子学习with: with --查询部门和部门的总薪水 dept_costs as ( select d.department_name,sum(e....
  • linwaterbin
  • linwaterbin
  • 2012年09月18日 10:17
  • 2240

Oracle 11g的递归with语句

废话不多说,直接上例子感受一下: with emps (employee_id, name, job_id, salary, lvl) as ( select employee_id, first...
  • chncaesar
  • chncaesar
  • 2014年01月06日 22:58
  • 3866

mysql group by子句 having子句 用法解析(详细)

group by 用法解析 group by语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。 SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子...
  • g11d111
  • g11d111
  • 2016年06月17日 11:18
  • 287

GROUP BY 子句中 选择列表中的列无效,因为该列没有包含在聚合函数或

T-SQL核心语句形式: SELECT     --指定要选择的列或行及其限定  [INTO ]      --INTO子句,指定结果存入新表 FROM      --FROM子句,指定...
  • liujiayu2
  • liujiayu2
  • 2015年06月01日 16:12
  • 1389

Oracle-18-select语句初步&SQL中用算术表达式&别名的使用&连接运算符%distinct&where子句

一、一般SELECT语句的格式如下: 1.查询指定表的所有列 select * from 表名 [where 条件] [group by 分组列名] [having 聚合函数] [order...
  • wy_0928
  • wy_0928
  • 2016年04月14日 18:52
  • 5415

oracle model子句学习笔记例 解析

SELECT PRODUCT, COUNTRY, YEAR, WEEKS, RECEIPTS, SALE, INVENTORY FROM SALES_FACT WHERE /*COUNTRY =...
  • jgmydsai
  • jgmydsai
  • 2013年08月26日 17:51
  • 1068

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名

刚才在网上看到如下,   ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表...
  • qq_26222859
  • qq_26222859
  • 2016年08月23日 14:13
  • 719
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle with子句的简单介绍.
举报原因:
原因补充:

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