Listagg函数在遇到nvarchar2数据类型时触发的BUG

最新推荐文章于 2024-09-29 10:33:45 发布

oomdb

最新推荐文章于 2024-09-29 10:33:45 发布

阅读量1k

点赞数

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/pujiao5201314/article/details/52055289

版权

Oracle 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了在使用Oracle的Listagg函数处理nvarchar2数据类型时遇到的BUG，通过案例分析帮助读者理解该问题，并提供解决思路，以避免类似问题导致的困扰。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天在帮助开发人员解决一个SQL问题时，触发了oracle的一个bug。

下面对此次遇到的问题，演示如下,希望下次遇到相似问题的人可以少走弯路。

创建测试表：
CREATE TABLE SCOTT.EMP_TEMP AS  SELECT * FROM SCOTT.EMP;

查看表结构：
SQL> desc SCOTT.EMP_TEMP;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

查看表的数据：
SQL> set lines 200 pages 1000
SQL> select * from SCOTT.EMP_TEMP;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980:12:17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981:02:20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981:02:22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981:04:02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981:09:28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981:05:01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981:06:09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987:04:19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981:11:17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981:09:08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987:05:23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981:12:03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981:12:03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982:01:23 00:00:00       1300                    10

14 rows selected.

正常的查询如下：
SQL> COL DENAME FOR A40
SQL> SELECT DEPTNO, LISTAGG(ENAME,',') WITHIN GROUP(ORDER BY DEPTNO) AS DENAME
  2    FROM SCOTT.EMP_TEMP
  3   GROUP BY DEPTNO;

    DEPTNO DENAME
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

发现显示OK，因为ename是varchar2类型。下面把其数据类型修改为nvarchar2：

SQL> ALTER TABLE  SCOTT.EMP_TEMP MODIFY ENAME NVARCHAR2(10);

Table altered.

继续查询：
a. SQLPLUS查询结果如下：
SQL> SELECT DEPTNO, LISTAGG(ENAME,',') WITHIN GROUP(ORDER BY DEPTNO) AS DENAME
  2    FROM SCOTT.EMP_TEMP
  3   GROUP BY DEPTNO;

    DEPTNO DENAME
---------- ----------------------------------------
        10  C L A R K, K I N G, M I L L E R
        20  A D A M S, F O R D, J O N E S, S C O T
           T, S M I T H

        30  A L L E N, B L A K E, J A M E S, M A R
           T I N, T U R N E R, W A R D

b. PL/SQL查询没有结果。

现在对其类型进行转换：
SQL> SELECT DEPTNO,
  2         LISTAGG(TO_CHAR(ENAME), ',') WITHIN GROUP(ORDER BY DEPTNO) AS DENAME
  3    FROM SCOTT.EMP_TEMP
  4   GROUP BY DEPTNO;

    DEPTNO DENAME
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
显示正常。

注意:TO_CHAR函数或者CASR函数均可.

从上面的现象说明，在数据库设计阶段，就应该设计好字段数据类型。不然说不定哪天就被坑给埋了。