arrays - 从 PL/SQL 中的多列关联数组中删除重复项

Question

我见过几个使用 MULTISET UNION DISTINCT 和 SET 删除 Oracle 关联数组中重复项的好例子。当只有一列时效果很好。我有一个基于 RECORD 类型的关联数组，其中包含 3 列。是否可以使用上述方法？

DECLARE
  TYPE rec_type IS RECORD(
     column1 VARCHAR2(5)
    ,column2 VARCHAR2(5));
  TYPE my_aa IS TABLE OF rec_type;
  p_tbl my_aa := my_aa();
  q_tbl my_aa := my_aa();
BEGIN
  p_tbl.extend(4);
  p_tbl(1).column1 := 'A1';
  p_tbl(1).column2 := 'a';
  --
  p_tbl(2).column1 := 'A1';
  p_tbl(2).column2 := 'b';
  --
  p_tbl(3).column1 := 'A1'; -- Dup 
  p_tbl(3).column2 := 'a'; -- Dup
  --
  p_tbl(4).column1 := 'A1';
  p_tbl(4).column2 := 'c';
  --
  dbms_output.put_line('-- First output contains duplicated');
  --
  FOR a IN p_tbl.first .. p_tbl.last LOOP
    dbms_output.put_line(a || ' = ' || p_tbl(a).column1 || ' / ' || p_tbl(a).column2);
  END LOOP;
  --
  --
  q_tbl := p_tbl MULTISET UNION DISTINCT p_tbl;
  --
  --
  dbms_output.new_line;
  dbms_output.put_line('-- Duplicates have been removad');
  FOR a IN q_tbl.first .. q_tbl.last LOOP
    dbms_output.put_line(a || ' = ' || q_tbl(a).column1|| ' / '||q_tbl(a).column2);
  END LOOP;
END;

Answer 1

由于文档原因，无法通过MULTISET UNION DISTINCT与底层RECORD类型进行区分

The element types of the nested tables must be comparable. Please refer to "Comparison Conditions " for information on the comparability of nonscalar types. https://docs.oracle.com/cd/B12037_01/server.101/b10759/operators006.htm

记录没有可比性。您可以将用户定义的对象与 MAP 方法用于此

Two objects of nonscalar type are comparable if they are of the same named type and there is a one-to-one correspondence between their elements. In addition, nested tables of user-defined object types, even if their elements are comparable, must have MAP methods defined on them to be used in equality or IN conditions. https://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions002.htm#i1033286

如果您仍然想对记录执行此操作，请尝试使用 SELECT DISTINCT ... FROM TABLE() 方法，但您的类型必须在 oracle 12c 之前的服务器的架构级别定义。