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