Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 |
|
|
View PDF |
A member_condition
is a membership condition that tests whether an element is a member of a nested table. The return value is TRUE
if expr
is a member of the specified nested table or varray. The return value is NULL
if expr
is null or if the nested table is empty.
expr
must be of the same type as the element type of the nested table.
The OF
keyword is optional and does not change the behavior of the condition.
The NOT
keyword reverses the boolean output: Oracle returns FALSE
if expr
is a member of the specified nested table.
The element types of the nested table must be comparable. Please refer to "Comparison Conditions " for information on the comparability of nonscalar types.
The following example selects from the table customers_demo
those rows in which the cust_address_ntab
nested table column contains the values specified in the WHERE
clause:
SELECT customer_id, cust_address_ntab FROM customers_demo WHERE cust_address_typ('8768 N State Rd 37', 47404, 'Bloomington', 'IN', 'US') MEMBER OF cust_address_ntab; CUSTOMER_ID CUST_ADDRESS_NTAB(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) ------------ --------------------------------------------------------------------------------- 103 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'))
The preceding example requires the table customers_demo
and a nested table column containing data. Please refer to "Multiset Operators " to create this table and nested table column.