Oracle with子句的简单介绍.

「爱情、让人受尽委屈。」 2022-09-18 14:56 157阅读 0赞

在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.

##     ##