时间戳

首页 > 解决方案 > clob 内的不同值

oracle - clob 内的不同值

问题描述

我有一些 SQL 可以从多个地方拉回数据，然后将其显示在一行、两个单元格中。每个单元格中都有一个数据列表。如果我分解 SQL，最初数据会显示如下：

姓名 颜色类型
橙子 金子
黄色 香蕉 [CHR(10)] 金

很抱歉这个奇怪的表，但基本上 Name 列每行有一个值，但 Colortype 可以有 0 到多个值。我的 SQL 中的问题是 ColorType 字段是一个由换行符 CHR(10) 分隔的值的 blob 列表。因此，在黄色名称下，颜色类型包含由换行符分隔的香蕉和金色。但您也可以看到 Name:Orange 也有 Gold 颜色类型。

在我的 SQL 结束时，我将单独的行汇总起来，以便最后得到一行，每列包含一个使用此值的列表。

SELECT distinct RTRIM(XMLAGG(XMLELEMENT(E, (myTableX.uo_unicode_to_sch (colortype)), CHR (10) ).EXTRACT('//text()')  ORDER BY pct_vu).GetClobVal(),',') as risk_tx,
                RTRIM(XMLAGG(XMLELEMENT(E, myTableY.uo_unicode_to_sch (name)), CHR(10)).EXTRACT('//text()') ORDER BY pct_vu desc).GetClobVal(),',') AS name

这给了我一行，它的名称已经是唯一的 19 个值。还有颜色类型，它是 1+x 非唯一值的列表。像这样，但不是单元格中值之间的空格，而是换行符。

姓名 颜色类型
橙黄 金香蕉金

这是大部分原始sql：

SELECT *
FROM (SELECT distinct  to_char(colortype),  name
    FROM (SELECT DISTINCT DBMS_LOB.substr(x.name, 4000) as name ,
                        DBMS_LOB.substr(x.colortype, 4000, 1) as colortype
          FROM (SELECT distinct RTRIM(XMLAGG(XMLELEMENT(E, (myTableA.uo_unicode_to_A (colortype)), CHR (10) ).EXTRACT('//text()')  ORDER BY w).GetClobVal(),',') as colortype,
                                RTRIM(XMLAGG(XMLELEMENT(E, myTableA.uo_unicode_to_A (name)|| ' '  || CASE WHEN w < 0.1 THEN '< 0.1' ELSE TRIM (TO_CHAR (w, CASE WHEN w < 1 THEN '0.9' ELSE '999'END)) END
                                        || '%', CHR (10)).EXTRACT('//text()') ORDER BY w desc).GetClobVal(),',') AS name,
                                RTRIM(XMLAGG(XMLELEMENT(E,CHR (13) , CHR (10) ).EXTRACT('//text()') ).GetClobVal(),',') AS rz
                FROM (SELECT MAX (CASE WHEN z.item_type = 'Name' THEN z.value_tx END) AS name,
                             MAX (CASE WHEN z.item_type = 'Exact %' THEN TO_NUMBER(REGEXP_SUBSTR(z.value_tx,'^\d+')) END) AS w,                             
                             MAX (CASE WHEN z.item_type = 'Class' THEN z.value_tx END) AS colortype
                      FROM myTableA.product p
                        JOIN TABLE (myTableA.z_data.get_unique_name ('r@'|| TO_CHAR (p.prod_id)))z
                                ON z.country_cd = 'US' AND z.locale_cd = 'en' AND z.section_vu = 3
                                        AND z.item_type IN ('Name','Exact %','Class')
                      GROUP BY z.sub_section_nm))x
                      JOIN TABLE (CAST (MULTISET(SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=(LENGTH (x.colortype)- LENGTH (REPLACE (x.colortype,x.rz)))
                                            / LENGTH (x.rz) + 1)  AS SYS.odcinumberlist)) y
                ON 1 = 1
                )
        );

我无法提供完整的 sql，但我正在寻找的是任何人对最终如何区分 clob 字段的想法？我正在使用 12.1 甲骨文。

标签： oracleplsql

解决方案

以下两种方法都使用LISTAGG，因此如果您的结果中包含超过 4000 个字符，您可能需要找到另一种聚合方法。

由于您使用的是 Oracle 12.1，因此您需要这样的查询：

WITH
    colors
    AS
        (SELECT 'Orange' AS name, 'Gold' AS colortype FROM DUAL
         UNION ALL
         SELECT 'Yellow', 'Banana' || CHR (10) || 'Gold' FROM DUAL),
    split_colors
    AS
        (SELECT name, COLUMN_VALUE AS color
           FROM colors  c
                CROSS JOIN
                TABLE (
                    CAST (
                        MULTISET (
                                SELECT SUBSTR (TRIM (REGEXP_SUBSTR (c.colortype,
                                                                    '[^' || CHR (10) || ']+',
                                                                    1,
                                                                    LEVEL)),
                                               1,
                                               4000)
                                  FROM DUAL
                            CONNECT BY LEVEL <=
                                       REGEXP_COUNT (c.colortype, '[^' || CHR (10) || ']+'))
                            AS SYS.ODCIVARCHAR2LIST)) c1)
SELECT (SELECT LISTAGG (name, CHR (10)) WITHIN GROUP (ORDER BY name)
          FROM (SELECT DISTINCT name
                  FROM split_colors))    AS names,
       (SELECT LISTAGG (color, CHR (10)) WITHIN GROUP (ORDER BY color)
          FROM (SELECT DISTINCT color
                  FROM split_colors))    AS colors
  FROM DUAL;

如果您使用的是 Oracle 19c，则可以使用LISTAGG DISTINCT

WITH
    colors
    AS
        (SELECT 'Orange' AS name, 'Gold' AS colortype FROM DUAL
         UNION ALL
         SELECT 'Yellow', 'Banana' || CHR (10) || 'Gold' FROM DUAL),
    split_colors
    AS
        (SELECT name, COLUMN_VALUE AS color
           FROM colors  c
                CROSS JOIN
                TABLE (
                    CAST (
                        MULTISET (
                                SELECT SUBSTR (TRIM (REGEXP_SUBSTR (c.colortype,
                                                                    '[^' || CHR (10) || ']+',
                                                                    1,
                                                                    LEVEL)),
                                               1,
                                               4000)
                                  FROM DUAL
                            CONNECT BY LEVEL <=
                                       REGEXP_COUNT (c.colortype, '[^' || CHR (10) || ']+'))
                            AS SYS.ODCIVARCHAR2LIST)) c1)
SELECT LISTAGG (DISTINCT name, CHR (10)) WITHIN GROUP (ORDER BY name)       AS names,
       LISTAGG (DISTINCT color, CHR (10)) WITHIN GROUP (ORDER BY color)     AS colors
  FROM split_colors;

推荐阅读