两个无序精简列表之间的区别(Oracle) [英] Difference between two unordered deliminted lists (Oracle)

问题描述

在Oracle中是否有一种简单而优雅的方法来返回两个无序，定界列表之间的差异?

Is there a simple elegant method to return the difference between two unordered, delimited lists in Oracle?

示例:

• 列表A:a1，b4，g3，h6，t8，a0
• 列表B:b4，h6，a0，t8，a1

差异:g3

推荐答案

如果可以访问APEX_UTIL，则可以将字符串解析为数组，将其转换为集合，然后使用MULTISET EXCEPT(相同为减号，但用于收藏):

If you have access to APEX_UTIL, you can parse the strings into an array, convert them to collections, then use MULTISET EXCEPT (which is the same as MINUS but for collections):

SET SERVEROUT ON
DECLARE
  TYPE set_t IS TABLE OF varchar2(100);
  listA APEX_APPLICATION_GLOBAL.vc_arr2;
  listB APEX_APPLICATION_GLOBAL.vc_arr2;
  excpt set_t;
  FUNCTION to_set_t (arr IN APEX_APPLICATION_GLOBAL.vc_arr2)
    RETURN set_t IS
    rset set_t := set_t();
  BEGIN
    rset.EXTEND(arr.COUNT);
    FOR i IN 1..arr.COUNT LOOP
      rset(i) := TRIM(arr(i));
    END LOOP;
    RETURN rset;
  END;
BEGIN
  -- parse lists into arrays
  listA := APEX_UTIL.string_to_table('a1, b4, g3, h6, t8, a0',',');
  listB := APEX_UTIL.string_to_table('b4, h6, a0, t8, a1',',');
  -- convert arrays to collections, then do the minus
  excpt := to_set_t(listA) MULTISET EXCEPT to_set_t(listB);
  -- display the results
  FOR i IN 1..excpt.COUNT LOOP
    DBMS_OUTPUT.put_line(excpt(i));
  END LOOP;
END;

结果:

g3

有关10g中引入的MULTISET运算符的更多信息: http://www.oracle-developer.net/display.php?id=303

More info on the MULTISET operators, which were introduced in 10g: http://www.oracle-developer.net/display.php?id=303

这篇关于两个无序精简列表之间的区别(Oracle)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！