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

[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）;