[20241227]标量子查询返回多值问题.txt

--//一看标题，以为写错了，实际上标量子查询不可能返回多值的，有时候需要返回多个值，一般我的建议是拼接在一起输出。
--//通过例子说明：

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.例子：
SCOTT@book01p> select empno,ename,sal,(select dname,loc from dept where deptno=emp.deptno) dname from emp where sal>=3000;
select empno,ename,sal,(select dname,loc from dept where deptno=emp.deptno) dname from emp where sal>=3000
                        *
ERROR at line 1:
ORA-00913: too many values

--//标量子查询不可能返回多值.在实际的生产系统中，可以发现开发这样写：

SCOTT@book01p> select empno,ename,sal,(select dname from dept where deptno=emp.deptno) dname ,(select loc from dept where deptno=emp.deptno) loc from emp where sal>=3000;
     EMPNO ENAME             SAL DNAME                          LOC
---------- ---------- ---------- ------------------------------ -------------
      7788 SCOTT            3000 RESEARCH                       DALLAS
      7839 KING             5000 ACCOUNTING                     NEW YORK
      7902 FORD             3000 RESEARCH                       DALLAS
--//当然我的例子很简单，一般这样的情况采用连接方式，实际上生产系统的查询要很复杂，这样写明显扫描dept表。

Plan hash value: 3707356765
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |       |    12 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    13 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          |
|   3 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    11 |     1   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          |
|*  5 |  TABLE ACCESS FULL          | EMP     |      7 |   119 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2 / "DEPT"@"SEL$2"
   2 - SEL$2 / "DEPT"@"SEL$2"
   3 - SEL$3 / "DEPT"@"SEL$3"
   4 - SEL$3 / "DEPT"@"SEL$3"
   5 - SEL$1 / "EMP"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=:B1)
   4 - access("DEPTNO"=:B1)
   5 - filter("SAL">=3000)

--//一般我的建议多少情况下为了显示，可以拼接在一起输出。
SCOTT@book01p> select empno,ename,sal,(select rpad(dname,13,' ')||loc from dept where deptno=emp.deptno) dname_loc from emp where sal>=3000;
     EMPNO ENAME             SAL DNAME_LOC
---------- ---------- ---------- ---------------------------------------
      7788 SCOTT            3000 RESEARCH     DALLAS
      7839 KING             5000 ACCOUNTING   NEW YORK
      7902 FORD             3000 RESEARCH     DALLAS

3.也可以定义1个type变成单值输出。

SCOTT@book01p> CREATE OR REPLACE TYPE t_dept AS OBJECT ( b_DNAME  VARCHAR2(14), b_loc  VARCHAR2(13) )
  2  /
Type created.

SCOTT@book01p> select empno,ename,sal,(select t_dept(dept.dname,dept.loc) from dept where deptno=emp.deptno) c40 from emp where sal>=3000;
     EMPNO ENAME             SAL C40(B_DNAME, B_LOC)
---------- ---------- ---------- ----------------------------------------
      7788 SCOTT            3000 T_DEPT('RESEARCH', 'DALLAS')
      7839 KING             5000 T_DEPT('ACCOUNTING', 'NEW YORK')
      7902 FORD             3000 T_DEPT('RESEARCH', 'DALLAS')

--//这样并没有满足真实生产系统的需求，改写如下：

SCOTT@book01p> select x.empno,x.ename,x.sal,x.c40.b_dname,x.c40.b_loc from (select empno,ename,sal,(select t_dept(dept.dname,dept.loc) from dept where deptno=emp.deptno) c40 from emp where sal>=3000) x;

     EMPNO ENAME             SAL C40.B_DNAME    C40.B_LOC
---------- ---------- ---------- -------------- -------------
      7788 SCOTT            3000 RESEARCH       DALLAS
      7839 KING             5000 ACCOUNTING     NEW YORK
      7902 FORD             3000 RESEARCH       DALLAS
COTT@book01p> @hash

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1884089577 8pz1ugts4tv79            0      60649      2981343222  704cece9  2024-12-28 10:11:44    16777218

SCOTT@book01p> @ expand_sql_text 8pz1ugts4tv79
SELECT "A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."SAL" "SAL",("A1"."C40")."B_DNAME" "C40.B_DNAME",("A1"."C40")."B_LOC" "C40.B_LOC" FROM  (SELECT "A2"."EMPNO" "EMPNO","A2"."ENAME" "ENAME","A2"."SAL" "SAL", (SELECT "T_DEPT"("A3"."DNAME","A3"."LOC")
"T_DEPT(DEPT.DNAME,DEPT.LOC)" FROM "SCOTT"."DEPT" "A3" WHERE "A3"."DEPTNO"="A2"."DEPTNO") "C40" FROM "SCOTT"."EMP" "A2" WHERE "A2"."SAL">=3000) "A1"

PL/SQL procedure successfully completed.

--//格式化如下：
SELECT "A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."SAL" "SAL",
       ("A1"."C40")."B_DNAME" "C40.B_DNAME",("A1"."C40")."B_LOC" "C40.B_LOC"
  FROM (SELECT "A2"."EMPNO" "EMPNO",
       "A2"."ENAME" "ENAME","A2"."SAL" "SAL",
       (SELECT "T_DEPT"("A3"."DNAME","A3"."LOC") "T_DEPT(DEPT.DNAME,DEPT.LOC)"
  FROM "SCOTT"."DEPT" "A3"
 WHERE "A3"."DEPTNO" =  "A2"."DEPTNO") "C40"
  FROM "SCOTT"."EMP" "A2"
 WHERE "A2"."SAL"    >= 3000) "A1"

--//当然实际的情况估计很少开发愿意这样写代码。整体看采用拼接的方式还是不错的选择。
--//如果需要分开两个字段，可以使用substr+instr函数分开，例子：

SELECT empno
      ,ename
      ,sal
      ,SUBSTR (dname_loc,1,INSTR(dname_loc ,',')-1) dname
      ,SUBSTR (dname_loc,INSTR(dname_loc ,',')+1)    loc
  FROM (SELECT empno
              ,ename
              ,sal
              , (SELECT dname || ',' || loc FROM dept WHERE deptno = emp.deptno) dname_loc
          FROM emp
         WHERE sal >= 3000);

--//这种模式拼接2个还简单一些，如果多个字段相对就复杂了。
--//多个字段可以采用前面rpad函数格式化字符串，这样切分位置固定。

/* Formatted on 2024/12/28 10:28:25 (QP5 v5.277) */
SELECT empno
      ,ename
      ,sal
      ,SUBSTR ( dname_loc ,1 ,13) dname
      ,SUBSTR ( dname_loc ,14) loc
  FROM (SELECT empno
              ,ename
              ,sal
              ,(SELECT RPAD (dname ,13 ,' ')||loc FROM dept WHERE deptno = emp.deptno) dname_loc
          FROM emp
         WHERE sal >= 3000）;