oracle中multiset,集合操作符Multiset

男娘i 2023-01-18 05:26 215阅读 0赞

MULTISET和CAST联合可以将一个结果集转为嵌套表。

下面的信息摘自ORACLE 10G联机文档，仅供参考。

Multiset operators combine the results of two nested tables into a single nested table.

The examples related to multiset operators require that two nested tables be created and loaded with data as follows:

First, make a copy of the oe.customers table called customers\_demo. We will add the nested table columns to customers\_demo.

CREATE TABLE customers\_demo AS

SELECT \* FROM customers;

Next, create a table type called cust\_address\_tab\_typ. This type will be used when creating the nested table columns.

CREATE TYPE cust\_address\_tab\_typ AS

TABLE OF cust\_address\_typ

Now, create two nested table columns in the customers\_demo table:

ALTER TABLE customers\_demo

ADD (cust\_address\_ntab cust\_address\_tab\_typ,

cust\_address2\_ntab cust\_address\_tab\_typ)

NESTED TABLE cust\_address\_ntab STORE AS cust\_address\_ntab\_store

NESTED TABLE cust\_address2\_ntab STORE AS cust\_address2\_ntab\_store;

Finally, load data into the two new nested table columns using data from the cust\_address column of the oe.customers table:

UPDATE CUSTOMERS\_DEMO cd

SET cust\_address\_ntab =

CAST(MULTISET(SELECT cust\_address

FROM customers c

WHERE c.customer\_id =

cd.customer\_id) as cust\_address\_tab\_typ);

UPDATE CUSTOMERS\_DEMO cd

SET cust\_address2\_ntab =

CAST(MULTISET(SELECT cust\_address

FROM customers c

WHERE c.customer\_id =

cd.customer\_id) as cust\_address\_tab\_typ);

MULTISET EXCEPT

MULTISET EXCEPT takes as arguments two nested tables and returns a nested table whose elements are in the first nested table but not in the second nested table. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.

![0db341b78f19b5bb1146f52af189fab1.gif][]

The ALL keyword instructs Oracle to return all elements in nested\_table1 that are not in nested\_table2. For example, if a particular element occurs m times in nested\_table1 and n times in nested\_table2, then the result will have(m-n) occurrences of the element if m >n and 0 occurrences if m<=n. ALL is the default.

The DISTINCT keyword instructs Oracle to eliminate any element in nested\_table1 which is also in nested\_table2, regardless of the number of occurrences.

The element types of the nested tables must be comparable. Please refer to  for information on the comparability of nonscalar types.

Example

The following example compares two nested tables and returns a nested table of those elements found in the first nested table but not in the second nested table:

SELECT customer\_id, cust\_address\_ntab

MULTISET EXCEPT DISTINCT cust\_address2\_ntab multiset\_except

FROM customers\_demo;

CUSTOMER\_ID MULTISET\_EXCEPT(STREET\_ADDRESS, POSTAL\_CODE, CITY, STATE\_PROVINCE, COUNTRY\_ID)

\----------- --------------------------------------------------------------------------------

101 CUST\_ADDRESS\_TAB\_TYP()

102 CUST\_ADDRESS\_TAB\_TYP()

103 CUST\_ADDRESS\_TAB\_TYP()

104 CUST\_ADDRESS\_TAB\_TYP()

105 CUST\_ADDRESS\_TAB\_TYP()

. . .

The preceding example requires the table customers\_demo and two nested table columns containing data. Please refer to  to create this table and nested table columns.

MULTISET INTERSECT

MULTISET INTERSECT takes as arguments two nested tables and returns a nested table whose values are common in the two input nested tables. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.

![c2a64bbb5b5ef336b7bfc258a915e7af.gif][]

The ALL keyword instructs Oracle to return all common occurrences of elements that are in the two input nested tables, including duplicate common values and duplicate common NULL occurrences. For example, if a particular value occurs m times in nested\_table1 and n times in nested\_table2, then the result would contain the element min(m,n) times. ALL is the default.

The DISTINCT keyword instructs Oracle to eliminate duplicates from the returned nested table, including duplicates of NULL, if they exist.

The element types of the nested tables must be comparable. Please refer to  for information on the comparability of nonscalar types.

Example

The following example compares two nested tables and returns a nested table of those elements found in both input nested tables:

SELECT customer\_id, cust\_address\_ntab

MULTISET INTERSECT DISTINCT cust\_address2\_ntab multiset\_intersect

FROM customers\_demo;

CUSTOMER\_ID MULTISET\_INTERSECT(STREET\_ADDRESS, POSTAL\_CODE, CITY, STATE\_PROVINCE, COUNTRY\_ID

\----------- -----------------------------------------------------------------------------------

101 CUST\_ADDRESS\_TAB\_TYP(CUST\_ADDRESS\_TYP('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'))

102 CUST\_ADDRESS\_TAB\_TYP(CUST\_ADDRESS\_TYP('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US'))

103 CUST\_ADDRESS\_TAB\_TYP(CUST\_ADDRESS\_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'))

104 CUST\_ADDRESS\_TAB\_TYP(CUST\_ADDRESS\_TYP('6445 Bay Harbor Ln', '46254', 'Indianapolis', 'IN', 'US'))

105 CUST\_ADDRESS\_TAB\_TYP(CUST\_ADDRESS\_TYP('4019 W 3Rd St', '47404', 'Bloomington', 'IN', 'US'))

The preceding example requires the table customers\_demo and two nested table columns containing data. Please refer to  to create this table and nested table columns.

MULTISET UNION

MULTISET UNION takes as arguments two nested tables and returns a nested table whose values are those of the two input nested tables. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.

![3d0a755cab4d1a981bfb0db72bc2964d.gif][]

Example

The following example compares two nested tables and returns a nested table of elements from both input nested tables:

SELECT customer\_id, cust\_address\_ntab

MULTISET UNION cust\_address2\_ntab multiset\_union

FROM customers\_demo;

CUSTOMER\_ID MULTISET\_UNION(STREET\_ADDRESS, POSTAL\_CODE, CITY, STATE\_PROVINCE, COUNTRY\_ID)

\----------- -------------------------------------------------------------------------------

101 CUST\_ADDRESS\_TAB\_TYP(CUST\_ADDRESS\_TYP('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'),

CUST\_ADDRESS\_TYP('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'))

102 CUST\_ADDRESS\_TAB\_TYP(CUST\_ADDRESS\_TYP('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US'),

CUST\_ADDRESS\_TYP('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN','US'))

103 CUST\_ADDRESS\_TAB\_TYP(CUST\_ADDRESS\_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'),

CUST\_ADDRESS\_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'))

104 CUST\_ADDRESS\_TAB\_TYP(CUST\_ADDRESS\_TYP('6445 Bay Harbor Ln', '46254', 'Indianapolis', 'IN', 'US'),

CUST\_ADDRESS\_TYP('6445 Bay Harbor Ln', '46254', 'Indianapolis', 'IN', 'US'))

105 CUST\_ADDRESS\_TAB\_TYP(CUST\_ADDRESS\_TYP('4019 W 3Rd St', '47404', 'Bloomington', 'IN', 'US'),

CUST\_ADDRESS\_TYP('4019 W 3Rd St', '47404', 'Bloomington', 'IN', 'US'))

The preceding example requires the table customers\_demo and two nested table columns containing data. Please refer to  to create this table and nested table columns.

[0db341b78f19b5bb1146f52af189fab1.gif]: /images/20221021/38cc4a062f914e04ab41a2043e129256.png
[c2a64bbb5b5ef336b7bfc258a915e7af.gif]: /images/20221021/b36398df4ed540f5a3ce231b63d8deb2.png
[3d0a755cab4d1a981bfb0db72bc2964d.gif]: /images/20221021/f72ac99fe8bb4e76826e06cc9f45540c.png