比较两个 Oracle 类型的每一列中的值

Question

我今天一直在玩 pluto-test-framework，我想将一些现有的功能放入测试工具中。

我有很多具有这种类型规范的函数。

   FUNCTION DO_SOME_STUFF   (pOldSchedule       IN      SCHEDULE_OBJ,
                             pNewSchedule          OUT  SCHEDULE_OBJ,
                             pLoggerContext     IN OUT  LOGGER_CONTEXT_OBJ)
   RETURN NUMBER;

它接受 pOldSchedule，对其执行一些操作，然后返回 pNewSchedule。 logger_context 只是进行日志记录。

作为测试的一部分，我希望能够比较该类型的每个列中的值，而不必编写单独的 IF 语句。

它需要返回布尔值来表示 pOldSchedule 和 pNewSchedule 是否匹配。

有什么想法吗？

Answer 1

直接的相等测试适用于嵌套表：

SQL> declare
  2      type nt is table of number;
  3      nt1 nt;
  4      nt2 nt;
  5      nt3 nt;
  6  begin
  7      nt1 := nt(1,2,3);
  8      nt2 := nt(1,2,3);
  9      if nt1 = nt2 then
 10          dbms_output.put_line('NT2 is the same nested table as NT1');
 11      else
 12          dbms_output.put_line('NT2 is a different nested table from NT1');
 13      end if;
 14      nt2 := nt(1,2,3,4);
 15      if nt1 = nt3 then
 16          dbms_output.put_line('NT3 is the same nested table as NT1');
 17      else
 18          dbms_output.put_line('E3 is a different nested table from NT1');
 19      end if;
 20  end;
 21  /
NT2 is the same nested table as NT1
E3 is a different nested table from NT1

PL/SQL procedure successfully completed.

SQL>

但是，对于完整的对象来说，情况并非如此：

SQL> create or replace type new_emp as object (
  2      ename varchar2(10)
  3      , sal number
  4      , deptno number
  5      , job varchar2(10))
  6  /

Type created.

SQL> declare
  2      e1 new_emp;
  3      e2 new_emp;
  4  begin
  5      e1 := new_emp('KESTELYN', 3700, 30, 'MARKETING');
  6      e2 := new_emp('KESTELYN', 3700, 30, 'MARKETING');
  7      if e1 = e2 then
  8          dbms_output.put_line('E2 is the same as E1');
  9      else
 10          dbms_output.put_line('E2 is different from E1');
 11      end if;
 12  end;
 13  /
    if e1 = e2 then
          *
ERROR at line 7:
ORA-06550: line 7, column 11:
PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.


SQL>

我们需要显式定义一个成员函数来执行比较。所以这是带有 MAP 函数的同一个对象。示例实现生成一个散列字符串，如果我们想要存储值以供以后比较，则该字符串很有用，但它只能返回连接的字符串（特别是默认情况下未授予 DBMS_CRYPTO 上的 EXECUTE 权限）。 NVL() 函数对于避免 (null, value) 和 (value, null) 被评估为相等是必要的。使用魔法值总是存在风险，所以我们需要谨慎选择它们。

SQL> create or replace type new_emp as object (
  2      ename varchar2(10)
  3      , sal number
  4      , deptno number
  5      , job varchar2(10)
  6      , map member function equals return raw)
  7  /

Type created.

SQL> create or replace type body new_emp as
  2      map member function equals return raw
  3      is
  4      begin
  5          return dbms_crypto.hash(
  6                     utl_raw.cast_to_raw(nvl(self.ename,'***')||
  7                                          nvl(self.sal,-99)||
  8                                          nvl(self.deptno,-99)||
  9                                          nvl(self.job,'***')
 10                                        )
 11                                   , 1);
 12      end equals;
 13  end;
 14  /

Type body created.

SQL>

现在我们有了比较对象实例的基础：

SQL> declare
  2      e1 new_emp;
  3      e2 new_emp;
  4  begin
  5      e1 := new_emp('KESTELYN', 3700, 30, 'MARKETING');
  6      e2 := new_emp('KESTELYN', 3700, 30, 'MARKETING');
  7      if e1 = e2 then
  8          dbms_output.put_line('E2 is the same as E1');
  9      else
 10          dbms_output.put_line('E2 is different from E1');
 11      end if;
 12  end;
 13  /
E2 is the same as E1

PL/SQL procedure successfully completed.

SQL>    

您可能想知道为什么 Oracle 默认情况下不这样做。好吧，TYPE 实现只允许一种比较方法（如果我们有 MAP 函数，我们就不能有 ORDER 函数），所以我们需要有能力选择我们自己的相等定义。例如，名为 rectangle 的类型可能有一个名为 area() 的 MAP 函数，该函数返回 self.width * self.length。