| General |
| 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>) |
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; |
