| Note: The collection functions operate on nested tables and varrays |
| |
| CARDINALITY |
| Returns the number of elements in a nested table |
CARDINALITY(collection IN "<TABLE_1>")
return PLS_INTEGER; |
| See Nested Table and Collections Demos |
| |
| COLLECT |
| Takes a column of any type and creates a nested table of the input
type out of the rows selected |
COLLECT(<column>) |
CREATE TYPE phone_book_t AS TABLE OF phone_list_typ;
/
SELECT CAST(COLLECT(phone_numbers) AS phone_book_t)
H FROM customers; |
| |
| POWERMULTISET |
| Takes a nested table and returns a nested table of nested tables
containing all nonempty subsets (called submultisets) of the input nested table |
POWERMULTISET(<expression>) |
CREATE TYPE cust_address_tab_tab_typ
AS TABLE OF cust_address_tab_typ;
SELECT CAST(POWERMULTISET(cust_address_ntab)
AS cust_address_tab_tab_typ)
FROM customers_demo; |
| |
| POWERMULTISET_BY_CARDINALITY |
| Takes a nested table and a cardinality and returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the nested table of the specified
cardinality |
POWERMULTISET_BY_CARDINALITY(<expression>,
<cardinality>) |
UPDATE customers_demo
SET cust_address_ntab = cust_address_ntab MULTISET UNION cust_address_ntab;
SELECT CAST(POWERMULTISET_BY_CARDINALITY(cust_address_ntab, 2)
AS cust_address_tab_tab_typ)
FROM customers_demo; |
| |
| SET |
| Converts a nested table into a set by eliminating duplicates |
SET(<nested_table>) |
SELECT customer_id, SET(cust_address_ntab) address
FROM customers_demo; |
