Slide 10.10: VARRAY (cont.)

VARRAY (Cont.)

We can not access any numbered subscript extension, except by mapping a VARRAY into a programming language array.

Question: Find the number of extensions that the person with social security number 123897766 has in the phonebook table.

The COUNT function returns the number of rows in the query.

Question: List the people in the phonebook table who have extension 123.

The CAST function converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.
MULTISET is the opposite of TABLE.
The following query will output one row per employee with eid and an ssno_set nested table value.

SQL> CREATE TYPE  ssno_set  AS TABLE OF INT; 

Type created.

SQL> SELECT  e.eid, CAST ( MULTISET ( 
  2      SELECT  d.ssno FROM  TABLE( e.dependents ) d 
  3        WHERE  d.age < 16 ) AS ssno_set ) AS dep_ssno 
  4    FROM  employees e; 

       EID                  DEP_SSNO
-----------------------     -------------------------------
       101                  SSNO_SET(32245776, 123822332)
       102                  SSNO_SET(565534555)