将逗号分隔列表转换为行有一个众所周知的sql技巧.只需使用该技巧,添加DISTINCT关键字,并将结果BULK COLLECT到您的数组中(我假设您的意思是集合).
DECLARE
p_test_string VARCHAR2 (4000) := 'A,B,C,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,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
