两个集合的差异

Question

我试图获得两个集合的差异，但每次都会出现异常。请帮忙。我的示例代码也在下面。不确定这里出了什么问题。。。MULTISET EXCEPT不能应用于RECORD类型吗？

PLS-00306:调用中的参数数量或类型错误'MULTISET_EXCEPT_ALL'

DECLARE
   TYPE FRND_OBJ IS RECORD
   (
      FrndID      NUMBER,
      JOBID       NUMBER,
      REGIONID    NUMBER,
      COLLEGEID   NUMBER
   );
   TYPE FRND_LIST IS TABLE OF FRND_OBJ;
   CURSOR c_date_rolling
   IS
      SELECT 1,
             2,
             3,
             4
        FROM DUAL;
   lv_roll_date         FRND_LIST;
   lv_roll_date_full    FRND_LIST;
   lv_roll_date_delta   FRND_LIST;
   g_error_msg          VARCHAR2 (1500);
BEGIN
   SELECT 5,
          6,
          7,
          8
     BULK COLLECT INTO lv_roll_date_full
     FROM DUAL;
   OPEN c_date_rolling;
   FETCH c_date_rolling BULK COLLECT INTO lv_roll_date;
   CLOSE c_date_rolling;
   DBMS_OUTPUT.put_line ('Full Count: ' || lv_roll_date_full.COUNT);
   DBMS_OUTPUT.put_line ('Rolling Count: ' || lv_roll_date.COUNT);
   lv_roll_date_delta := lv_roll_date_full MULTISET EXCEPT lv_roll_date;
   DBMS_OUTPUT.put_line (lv_roll_date_delta.COUNT);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      g_error_msg :=
            SUBSTR (SQLERRM, 1, 200)
         || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 20);
      DBMS_OUTPUT.PUT_LINE (g_error_msg);
   WHEN OTHERS
   THEN
      g_error_msg :=
            SUBSTR (SQLERRM, 1, 200)
         || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 20);
      DBMS_OUTPUT.PUT_LINE (g_error_msg);
END;
/

Answer 1

这里解释了您将需要一个MAP或ORDER方法。在您的样品中：

CREATE OR REPLACE TYPE frnd_obj
   AUTHID DEFINER AS OBJECT
(
   frndid NUMBER,
   jobid NUMBER,
   regionid NUMBER,
   collegeid NUMBER,
   MAP MEMBER FUNCTION tostring
      RETURN VARCHAR2
)
/
SHOW ERRORS;
CREATE OR REPLACE TYPE BODY frnd_obj AS
   MAP MEMBER FUNCTION tostring
      RETURN VARCHAR2 IS
   BEGIN
      RETURN TO_CHAR (frndid) || ';' || TO_CHAR (jobid) || ';' || TO_CHAR (regionid) || ';' || TO_CHAR (collegeid);
   END tostring;
END;
/
CREATE TYPE FRND_LIST IS TABLE OF FRND_OBJ;

现在你可以使用它了：

DECLARE
--   TYPE FRND_OBJ IS RECORD
--   (
--      FrndID      NUMBER,
--      JOBID       NUMBER,
--      REGIONID    NUMBER,
--      COLLEGEID   NUMBER
--   );
--   TYPE FRND_LIST IS TABLE OF FRND_OBJ;
   CURSOR c_date_rolling
   IS
      SELECT FRND_OBJ(1,
             2,
             3,
             4)
        FROM DUAL; 
--        union all
--      SELECT FRND_OBJ(5,
--          6,
--          7,
--          8)
--        FROM DUAL;
   lv_roll_date         FRND_LIST;
   lv_roll_date_full    FRND_LIST;
   lv_roll_date_delta   FRND_LIST;
   g_error_msg          VARCHAR2 (1500);
BEGIN
   SELECT FRND_OBJ(5,
          6,
          7,
          8)
     BULK COLLECT INTO lv_roll_date_full
     FROM DUAL;
   OPEN c_date_rolling;
   FETCH c_date_rolling BULK COLLECT INTO lv_roll_date;
   CLOSE c_date_rolling;
   DBMS_OUTPUT.put_line ('Full Count: ' || lv_roll_date_full.COUNT);
   DBMS_OUTPUT.put_line ('Rolling Count: ' || lv_roll_date.COUNT);
   lv_roll_date_delta := lv_roll_date_full MULTISET EXCEPT ALL lv_roll_date;
   DBMS_OUTPUT.put_line (lv_roll_date_delta.COUNT);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      g_error_msg :=
            SUBSTR (SQLERRM, 1, 200)
         || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 20);
      DBMS_OUTPUT.PUT_LINE (g_error_msg);
   WHEN OTHERS
   THEN
      g_error_msg :=
            SUBSTR (SQLERRM, 1, 200)
         || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 20);
      DBMS_OUTPUT.PUT_LINE (g_error_msg);
END;
/