oracle - 比较对象是否相等时为 "PLS-00526: A MAP or ORDER function is required"

Question

在 Oracle 11gR2 上，我创建了一个简单的 PL/SQL 对象类型。当尝试比较两个实例的相等性/不等性时，我得到一个 PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL 错误，即使 Oracle documentation明确指出

If neither a MAP nor an ORDER method is specified, then only comparisons for equality or inequality can be performed.

这是我用来重现错误的 PL/SQL 代码示例:

create or replace type point_t is object (x number, y number);
/

declare
  p1 point_t := point_t(1, 2);
  p2 point_t := point_t(1, 2);
  p3 point_t := point_t(2, 1);
begin
  dbms_output.put_line('p1 = p1 ' || case when p1 = p1 then 'OK' else 'FAIL' end);
  dbms_output.put_line('p2 = p1 ' || case when p2 = p1 then 'OK' else 'FAIL' end);
  dbms_output.put_line('p3 <> p1 ' || case when p3 <> p1 then 'OK' else 'FAIL' end);
end;
/

Answer 1

是的，如果既未指定 MAP 也未指定 ORDER 方法，您可以比较对象的相等性或不相等性，但只能在 SQL 语句中进行比较，不能直接在 PL/SQL block 中进行比较。

引自 Database Object-Relational Developer's Guide

if you do not declare one of these methods, you can only compare objects in SQL statements, and only for equality or inequality.

create or replace type point_t is object (x number, y number);
/

select case
        when point_t(1,1) = point_t(1,1) then 'OK'
        else 'FAIL'
       end as cmp_res
  from dual;


set serveroutput on;
declare
  l_p1 point_t := point_t(1,2);
  l_p2 point_t := point_t(1,2);
  l_res varchar2(7) := 'OK';
begin
  select 'FAIL'
    into l_res
   from dual
  where l_p1 != l_p2;  -- put it in the where clause just for the sake 
                       -- of demonstration. Can do comparison in the 
                       -- select list as well.
  dbms_output.put_line('p1 = p2 : ' || l_res);
end;

结果:

Type created.

CMP_RES
-------
OK     

1 row selected.

p1 = p2 : FAIL
PL/SQL procedure successfully completed.

但是如果需要直接在 PL/SQL block 中比较对象，则需要定义对象比较规则(当一个对象等于/不等于，大于或小于另一个对象时，尤其是当一个对象有很多属性时)需要实现 MAP 或 ORDER 方法。