Sunday, August 19, 2012

Multiset Operations with Associative Arrays


It would be convenient to use multiset operations not only with nested tables but with associative arrays too. According to Oracle® Database SQL Language Reference multiset operations apply to nested tables only. For now. Some help would be possible to get from the package show below. The function AA_STRNDX build associative array as intersection or not intersection of two arrays. The OPER_ID parameter can take value 'INTERSECT' or 'NOT INTERSECT'.
create or replace
PACKAGE compare
IS

  SUBTYPE strndx_type IS VARCHAR2(1000);
  TYPE aa_strndx_type IS TABLE OF PLS_INTEGER
                          INDEX BY strndx_type;

  FUNCTION aa_strndx(
    array_in_1  IN  aa_strndx_type,
    array_in_2  IN  aa_strndx_type,
    array_out   OUT aa_strndx_type,
    oper_id     IN  VARCHAR2 := 'INTERSECT'
  ) RETURN PLS_INTEGER;

END;

create or replace
PACKAGE BODY compare
IS
  FUNCTION aa_strndx(
    array_in_1  IN  aa_strndx_type,
    array_in_2  IN  aa_strndx_type,
    array_out   OUT aa_strndx_type,
    oper_id     IN  VARCHAR2 := 'INTERSECT'
  ) RETURN PLS_INTEGER
  IS
    loc VARCHAR2(1000);      

    PROCEDURE first_minus_second(p1 IN aa_strndx_type, p2 IN aa_strndx_type)
    IS
    BEGIN
      loc := p1.FIRST;
      WHILE loc IS NOT NULL
      LOOP
        IF NOT p2.EXISTS(loc) THEN
          array_out(loc) := p1(loc);            
        END IF;
        loc := p1.NEXT(loc);
      END LOOP;
    END;

    PROCEDURE first_intersect_second(p1 IN aa_strndx_type, p2 IN aa_strndx_type)
    IS
    BEGIN
      loc := p1.FIRST;
      WHILE loc IS NOT NULL
      LOOP
        IF p2.EXISTS(loc) THEN
          array_out(loc) := p1(loc);            
        END IF;
        loc := p1.NEXT(loc);
      END LOOP;
    END;

  BEGIN

    IF oper_id NOT IN ('INTERSECT','NOT INTERSECT') THEN
      RETURN -1;
    END IF;
    
    IF oper_id = 'NOT INTERSECT' THEN
      first_minus_second(array_in_1,array_in_2);
      first_minus_second(array_in_2,array_in_1);
    END IF;

    IF oper_id = 'INTERSECT' THEN
      first_intersect_second(array_in_1,array_in_2);
      first_intersect_second(array_in_2,array_in_1);
    END IF;
    
    RETURN 0;
    
  END;

END;

No comments:

Post a Comment