MULTISET Operator : MULTISET « Collections « Oracle PL/SQL Tutorial

Home
Oracle PL/SQL Tutorial
1.Introduction
2.Query Select
3.Set
4.Insert Update Delete
5.Sequences
6.Table
7.Table Joins
8.View
9.Index
10.SQL Data Types
11.Character String Functions
12.Aggregate Functions
13.Date Timestamp Functions
14.Numerical Math Functions
15.Conversion Functions
16.Analytical Functions
17.Miscellaneous Functions
18.Regular Expressions Functions
19.Statistical Functions
20.Linear Regression Functions
21.PL SQL Data Types
22.PL SQL Statements
23.PL SQL Operators
24.PL SQL Programming
25.Cursor
26.Collections
27.Function Procedure Packages
28.Trigger
29.SQL PLUS Session Environment
30.System Tables Data Dictionary
31.System Packages
32.Object Oriented
33.XML
34.Large Objects
35.Transaction
36.User Privilege
Oracle PL/SQL Tutorial » Collections » MULTISET 
26.14.1.MULTISET Operator

MULTISET operator gets a nested table whose elements are set to certain elements of two nested tables that are input to MULTISET. There are three MULTISET operators:

MULTISET UNION Returns a nested table whose elements are set to the elements of the two input nested tables.

MULTISET INTERSECT Returns a nested table whose elements are set to the elements that are common to the two input nested tables.

MULTISET EXCEPT Returns a nested table whose elements are in the first input nested table but not in the second.

You may also use one of the following options with MULTISET:

ALL Indicates that all applicable elements in the input nested tables are set in the returned nested table. ALL is the default.

DISTINCT Indicates that only the distinct non-duplicate elements in the input nested tables are set in the returned nested table.

SQL>
SQL> CREATE OR REPLACE PROCEDURE multiset_example AS
  2    TYPE nestedTableType IS TABLE OF VARCHAR2(10);
  3    myTable1 nestedTableType;
  4    myTable2 nestedTableType;
  5    myTable3 nestedTableType;
  6    count_var INTEGER;
  7  BEGIN
  8    myTable1 := nestedTableType('F''G''S');
  9    myTable2 := nestedTableType('G''S''R');
 10
 11    myTable3 := myTable1 MULTISET UNION myTable2;
 12    DBMS_OUTPUT.PUT('UNION: ');
 13    FOR count_var IN 1..myTable3.COUNT LOOP
 14      DBMS_OUTPUT.PUT(myTable3(count_var|| ' ');
 15    END LOOP;
 16    DBMS_OUTPUT.PUT_LINE(' ');
 17
 18    myTable3 := myTable1 MULTISET UNION DISTINCT myTable2;
 19    DBMS_OUTPUT.PUT('UNION DISTINCT: ');
 20    FOR count_var IN 1..myTable3.COUNT LOOP
 21      DBMS_OUTPUT.PUT(myTable3(count_var|| ' ');
 22    END LOOP;
 23    DBMS_OUTPUT.PUT_LINE(' ');
 24
 25    myTable3 := myTable1 MULTISET INTERSECT myTable2;
 26    DBMS_OUTPUT.PUT('INTERSECT: ');
 27    FOR count_var IN 1..myTable3.COUNT LOOP
 28      DBMS_OUTPUT.PUT(myTable3(count_var|| ' ');
 29    END LOOP;
 30    DBMS_OUTPUT.PUT_LINE(' ');
 31
 32    myTable3 := myTable1 MULTISET EXCEPT myTable2;
 33    DBMS_OUTPUT.PUT_LINE('EXCEPT: ');
 34    FOR count_var IN 1..myTable3.COUNT LOOP
 35      DBMS_OUTPUT.PUT(myTable3(count_var|| ' ');
 36    END LOOP;
 37  END multiset_example;
 38  /

Procedure created.

SQL> CALL multiset_example();
UNION: F G S G S R
UNION DISTINCT: F G S R
INTERSECT: G S
EXCEPT:

Call completed.

SQL>
26.14.MULTISET
26.14.1.MULTISET Operator
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.