Oracle PL/SQL - Assigning Values to Collection Variables

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Collection Types

Introduction

The SQL MULTISET operators combine two nested tables into a single nested table.

The elements of the two nested tables must have comparable data types.

The SQL SET function takes a nested table argument and returns a nested table of the same data type whose elements are distinct.

The following code shows how to use the set operation with nested table variable.

Demo

SQL>
SQL> DECLARE--  www.  j av a 2s.com
  2    TYPE NumberTable IS TABLE OF NUMBER;
  3
  4    nt1    NumberTable := NumberTable(1,2,3);
  5    nt2    NumberTable := NumberTable(3,2,1);
  6    nt3    NumberTable := NumberTable(2,3,1,3);
  7    nt4    NumberTable := NumberTable(1,2,4);
  8    answer NumberTable;
  9
 10    PROCEDURE print_nested_table (nt NumberTable) IS
 11      output VARCHAR2(128);
 12    BEGIN
 13      IF nt IS NULL THEN
 14        DBMS_OUTPUT.PUT_LINE('Result: null set');
 15      ELSIF nt.COUNT = 0 THEN
 16        DBMS_OUTPUT.PUT_LINE('Result: empty set');
 17      ELSE
 18        FOR i IN nt.FIRST .. nt.LAST LOOP  -- For first to last element
 19          output := output || nt(i) || ' ';
 20        END LOOP;
 21        DBMS_OUTPUT.PUT_LINE('Result: ' || output);
 22      END IF;
 23    END print_nested_table;
 24
 25  BEGIN
 26    answer := nt1 MULTISET UNION nt4;
 27    print_nested_table(answer);
 28    answer := nt1 MULTISET UNION nt3;
 29    print_nested_table(answer);
 30    answer := nt1 MULTISET UNION DISTINCT nt3;
 31    print_nested_table(answer);
 32    answer := nt2 MULTISET INTERSECT nt3;
 33    print_nested_table(answer);
 34    answer := nt2 MULTISET INTERSECT DISTINCT nt3;
 35    print_nested_table(answer);
 36    answer := SET(nt3);
 37    print_nested_table(answer);
 38    answer := nt3 MULTISET EXCEPT nt2;
 39    print_nested_table(answer);
 40    answer := nt3 MULTISET EXCEPT DISTINCT nt2;
 41    print_nested_table(answer);
 42  END;
 43  /

Related Topic