PL/SQL 逗号分隔列表；删除重复项并放入数组

2021-12-24 00:00:00 oracle plsql

我有一个逗号分隔的列表作为 PL/SQL 过程中的字符串.我需要

I have a comma delimited list as a string in a PL/SQL procedure. I need to

  1. 删除重复项
  2. 将列表放入数组中.

我找到了多种方法可以做到这两者，但不能两者兼而有之.有什么帮助吗?

I have found multiple ways to do either, just not both. Any help?

推荐答案

有一个众所周知的 SQL 技巧可以将逗号分隔的列表转换为行.只需使用该技巧，添加一个 DISTINCT 关键字，然后 BULK COLLECT 将结果放入您的数组中(我假设您的意思是集合).

There is a well-known SQL trick for turning comma-separated lists into rows. Just use that trick, add a DISTINCT keyword, and BULK COLLECT the results into your array (I assume you mean collection).

DECLARE
  p_test_string   VARCHAR2 (4000) := 'A,B,C,B,B,D';

  TYPE string_array_type IS TABLE OF VARCHAR2 (4000);

  l_array         string_array_type;
BEGIN
  SELECT DISTINCT REGEXP_SUBSTR (p_test_string,
                        '[^,]+',
                        1,
                        LEVEL)
  BULK   COLLECT INTO l_array
  FROM   DUAL
  CONNECT BY REGEXP_SUBSTR (p_test_string,
                            '[^,]+',
                            1,
                            LEVEL)
               IS NOT NULL
  ORDER BY 1;

  DBMS_OUTPUT.put_line ('l_array.count = ' || l_array.COUNT);
  DBMS_OUTPUT.put_line ('l_array(2) = ' || l_array (2));
END;

输出:

l_array.count = 4
l_array(2) = B

相关文章