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