5.1 VARRAYs
A VARRAY is an ordered set of elements. The maximum number of elements in a VARRAY is set when the VARRAY is created. You create a VARRAY by first declaring a type, and then using that type to define a column in a table. The following example creates a VARRAY type, named t_address2, that contains three VARCHAR2 strings:
CREATE TYPE t_address2 AS VARRAY(3) OF VARCHAR2(50); /
Each VARCHAR2 string in this type will be used to represent an address for a customer. Once you have created a VARRAY type, you can define a column that uses it. The following example creates a table named customers3, which contains a column named addresses that is based on the t_address2 type:
CREATE TABLE customers3 ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), addresses t_address2 );
In order to add a row containing a VARRAY to this table, you must place the elements for the VARRAY in a constructor for the VARRAY type. The following example adds a row to the customers3 table, placing two elements for the VARRAY in the constructor for the t_address2 type:
INSERT INTO customers3 VALUES ( 1, 'John', 'Smith', t_address2( '1 Anystreet, Anytown, CA, 12345', '2 Main Street, Big Town, NY, 54321' ) );
After this INSERT is performed, the customers3 table contains one row. The VARRAY stored in the addresses column contains two strings, one for each of the strings contained in the t_address2 constructor.
From the previous examples, you can see that the use of VARRAYs is similar to the use of database objects, which were described in Chapter 4. With VARRAYs, as with database objects, you must create a type, and just as you use a constructor to populate an object, you must use a constructor to populate a VARRAY.
5.1.1 Retrieving the Contents of a VARRAY
Now that you have seen how to create and populate a VARRAY, you may wonder how to retrieve a VARRAY using a SELECT statement. The answer is that you don't have to do anything special. You simply select the VARRAY column as you would any other. For example:
SQL> SELECT * 2 FROM customers3 3 WHERE id = 1; ID FIRST_NAME LAST_NAME --- ---------- --------- ADDRESSES --------------------------------------------- 1 John Smith T_ADDRESS2('1 Anystreet, Anytown, CA, 12345', '2 Main Street, Big Town, NY, 54321') Notice in this SQL*Plus output that the values for the addresses column appear in a constructor for the t_address2 type. Both the strings that were inserted into the VARRAY are contained within this constructor. This is similar to the way in which SQL*Plus displays database objects that you retrieve from object columns.
5.1.2 Modifying the Contents of a VARRAY
When you modify a VARRAY, you must rebuild the entire array: you can't go in and change just one element independently of the others. You have to retrieve the VARRAY, modify the elements you want to change, and then recreate the entire VARRAY, including the elements that haven't changed, in an UPDATE statement. The following UPDATE statement modifies the first string in the VARRAY that was created in the previous example:
UPDATE customers3 SET addresses = t_address2( '1 New Street, New Town, CA, 12345', '2 Main Street, Big Town, NY, 54321' ) WHERE id = 1;
Note that even though the second string in the VARRAY hasn't changed, it must still be included in the UPDATE statement. In effect, you are replacing the old VARRAY in the addresses column with a completely new VARRAY created in your UPDATE statement.
