| Oracle Collection Functions Version 11.2.0.3 |
|---|
| General Information | |
| 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>) |
| conn sh/sh CREATE OR REPLACE TYPE phone_book_t AS TABLE OF VARCHAR2(25); / SELECT CAST(COLLECT(cust_main_phone_number) AS phone_book_t) FROM customers WHERE cust_id BETWEEN 1 AND 5; SELECT CAST(COLLECT(cust_credit_limit) AS phone_book_t) FROM customers WHERE cust_id BETWEEN 1 AND 5; WITH q AS (SELECT CAST(COLLECT(cust_credit_limit) AS phone_book_t) FROM customers WHERE cust_id BETWEEN 1 AND 5) SELECT COUNT(*) FROM TABLE(q); |
|
| POWERMULTISET | |
| Takes a nested table and returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the input nested table Note: This demo, and the following two, are copied from the tahiti docs but put onto a single page to make using them easier |
POWERMULTISET(<expression>) |
| conn oe/oe CREATE TABLE customers_demo AS SELECT * FROM customers; CREATE OR REPLACE TYPE cust_address_tab_typ AS TABLE OF cust_address_typ; / 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; 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); COMMIT; SELECT parent_table_name, table_name, table_type_name FROM user_nested_tables; CREATE OR REPLACE 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 non-empty 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; |
|
| Related Topics |
| Built-in Functions |
| Collections |
| Date Functions |
| Nested Tables |
| Numeric Functions |
| String Functions |
| Timestamp |
| Types |
| This site is maintained by Dan Morgan. Last Updated: | This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved | |||||||||
|
|
||||||||||