SQL 在Oracle中把标量子查询转换为复合子查询|极客教程

SQL 在Oracle中把标量子查询转换为复合子查询，一个标量子查询中只允许返回一个值，你想绕过该限制。例如，你尝试执行如下所示的查询。

SQL 在Oracle中把标量子查询转换为复合子查询问题描述

一个标量子查询中只允许返回一个值，你想绕过该限制。例如，你尝试执行如下所示的查询。

select e.deptno,
       e.ename,
       e.sal,
       (select d.dname,d.loc,sysdate today
          from dept d
         where e.deptno=d.deptno)
  from emp e

上述查询会因为报错而无法执行，因为 SELECT 列表里的子查询只允许返回一个值。

SQL 在Oracle中把标量子查询转换为复合子查询解决方案

诚然，上述问题似乎有些不切实际，因为只要把 EMP 表和 DEPT 表连接起来，我们就能方便地从 DEPT 表中提取出任意值。但是，我的本意是希望你关注技巧，并认识到本问题在某些场景下有其实用性。当在 SELECT 中放入了另一个 SELECT（标量子查询）时，要想绕过只返回一个值的限制，就需要利用 Oracle 的对象类型。我们可以定义一个拥有多个属性的对象，然后把它作为一个单独的实体来处理，并且可以访问其中的每一个属性。实际上，我们并没有真正地打破那个规则。它仍然只返回了一个值，只不过该返回值是一个对象，它里面包含了许多属性。
本解决方案使用到了下面的对象类型。

create type generic_obj
    as object (
    val1 varchar2(10),
    val2 varchar2(10),
    val3 date
);

有了以上对象类型，就可以执行下面的查询。

 1  select x.deptno,
 2         x.ename,
 3         x.multival.val1 dname,
 4         x.multival.val2 loc,
 5         x.multival.val3 today
 6    from (
 7  select e.deptno,
 8         e.ename,
 9         e.sal,
10         (select generic_obj(d.dname,d.loc,sysdate+1)
11            from dept d
12           where e.deptno=d.deptno) multival
13    from emp e
14         ) x
　
DEPTNO ENAME      DNAME      LOC        TODAY
------ ---------- ---------- ---------- -----------
    20 SMITH      RESEARCH   DALLAS     12-SEP-2005
    30 ALLEN      SALES      CHICAGO    12-SEP-2005
    30 WARD       SALES      CHICAGO    12-SEP-2005
    20 JONES      RESEARCH   DALLAS     12-SEP-2005
    30 MARTIN     SALES      CHICAGO    12-SEP-2005
    30 BLAKE      SALES      CHICAGO    12-SEP-2005
    10 CLARK      ACCOUNTING NEW YORK   12-SEP-2005
    20 SCOTT      RESEARCH   DALLAS     12-SEP-2005
    10 KING       ACCOUNTING NEW YORK   12-SEP-2005
    30 TURNER     SALES      CHICAGO    12-SEP-2005
    20 ADAMS      RESEARCH   DALLAS     12-SEP-2005
    30 JAMES      SALES      CHICAGO    12-SEP-2005
    20 FORD       RESEARCH   DALLAS     12-SEP-2005
    10 MILLER     ACCOUNTING NEW YORK   12-SEP-2005

SQL 在Oracle中把标量子查询转换为复合子查询扩展知识

本解决方案的关键在于使用上述对象的构造函数（默认情况下构造函数和对象同名）。因为对象本身是一个标量值，它并不会违反标量子查询的规则，如下所示。

select e.deptno,
       e.ename,
       e.sal,
       (select generic_obj(d.dname,d.loc,sysdate-1)
          from dept d
         where e.deptno=d.deptno) multival
  from emp e
　
DEPTNO ENAME    SAL MULTIVAL(VAL1, VAL2, VAL3)
------ ------ ----- -------------------------------------------------------
    20 SMITH    800 GENERIC_OBJ('RESEARCH', 'DALLAS', '12-SEP-2005')
    30 ALLEN   1600 GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')
    30 WARD    1250 GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')
    20 JONES   2975 GENERIC_OBJ('RESEARCH', 'DALLAS', '12-SEP-2005')
    30 MARTIN  1250 GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')
    30 BLAKE   2850 GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')
    10 CLARK   2450 GENERIC_OBJ('ACCOUNTING', 'NEW YORK', '12-SEP-2005')
    20 SCOTT   3000 GENERIC_OBJ('RESEARCH', 'DALLAS', '12-SEP-2005')
    10 KING    5000 GENERIC_OBJ('ACCOUNTING', 'NEW YORK', '12-SEP-2005')
    30 TURNER  1500 GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')
    20 ADAMS   1100 GENERIC_OBJ('RESEARCH', 'DALLAS', '12-SEP-2005')
    30 JAMES    950 GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')
    20 FORD    3000 GENERIC_OBJ('RESEARCH', 'DALLAS', '12-SEP-2005')
    10 MILLER  1300 GENERIC_OBJ('ACCOUNTING', 'NEW YORK', '12-SEP-2005')

接下来，只要把上述查询包裹进一个内嵌视图里，并提取各个属性即可。