如何在VARCHAR2 Oracle 10g中使用COLLECT

admin

如何在VARCHAR2 Oracle 10g中使用COLLECT

sql

我正在尝试让COLLECT函数为我工作。我使用的是10g，因此发现LISTAGG和WM_CONCAT无法正常工作（无效的标识符错误）。我的数据例如如下。

Order  Lot
123    A23088
123    A23089
089    AABBCC
305    120848
305    CCDDYY

我需要退货的如下

Order   Lot
123     A23088, A23089
089     AABBCC
305     120848, CCDDYY

使用以下命令，我得到错误：TO_STRING是无效的标识符

TO_STRING ( CAST(COLLECT(DISTINCT LOT) AS varchar2(100)) ) AS LOT

使用以下命令，我得到错误：预期的CHAR“数据类型不一致：预期的％s得到了％s”

TO_CHAR ( CAST(COLLECT(DISTINCT LOT) AS varchar2(100)) ) AS LOT

使用以下命令，我得到错误：预期的NUMBER“数据类型不一致：预期的％s得到了％s”

COLLECT(DISTINCT WHSE_LOT)

有什么办法可以让我使用此功能？

阅读 143

2021-07-01

共1个答案

admin

该收集功能创建一个嵌套表，你的情况的字符串，你会再转换为特定类型的表-
也就是定义为VARCHAR2的表型。您不能将其转换为单个字符串。

有一些众所周知的字符串聚合技术列表，例如清单1。有一个使用collect的函数，但是您仍然需要表类型和一个函数来将生成的表转换为定界字符串。

逐字复制该示例：

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;
/

使用该类型和功能，您可以执行以下操作：

SELECT tab_to_string(CAST(COLLECT(DISTINCT lot) AS t_varchar2_tab)) AS lot FROM ...

有趣的是，collect的10g版本不支持DISTINCT；它不会抱怨（！？），但是会重复。

您可以通过set函数传递集合以删除重复项：

SELECT tab_to_string(SET(CAST(COLLECT(DISTINCT lot) AS t_varchar2_tab))) AS lot FROM ...

快速演示在10.2.0.5中运行：

create table table1(order_no number, lot varchar2(10));

insert into table1 values (590288, '2016538');
insert into table1 values (590288, '2016535');
insert into table1 values (590288, '6016535');
insert into table1 values (590288, '2016535');
insert into table1 values (590288, '2016538');

SELECT order_no, tab_to_string(SET(CAST(COLLECT(DISTINCT lot) AS t_varchar2_tab))) AS LOT
FROM table1 WHERE order_no = 590288 GROUP BY order_no;

  ORDER_NO LOT                                              
---------- --------------------------------------------------
    590288 2016538,2016535,6016535

2021-07-01