Database Reference
In-Depth Information
VARRAY collection for the INSTRUMENTSCOLLECTION datatype
column. First, we make a copy of the ARTIST table, excluding the
INSTRUMENTS column:
CREATE TABLE ARTIST3 AS SELECT ARTIST_ID, NAME, STREET
, POBOX, CITY, STATE_PROVINCE, COUNTRY, ZIP
, EMAIL FROM ARTIST;
Now we create a new type to contain the instruments collection:
CREATE OR REPLACE TYPE NEWCOLLECTION AS TABLE OF
VARCHAR2(32);
/
Now we add the nested table collection, NEWCOLLECTION, to the
ARTIST3 copy table:
ALTER TABLE ARTIST3 ADD(INSTRUMENTS NEWCOLLECTION)
NESTED TABLE INSTRUMENTS STORE AS INSTRUMENTSTABLE;
Now we can add the instruments collection from the original ARTIST
table into the new ARTIST3 table:
UPDATE ARTIST3 SET INSTRUMENTS =
NEWCOLLECTION('Vocals','Acoustic Guitar','Electric Guitar')
WHERE ARTIST_ID = 1;
UPDATE ARTIST3 SET INSTRUMENTS =
NEWCOLLECTION('Piano','Vocals') WHERE ARTIST_ID = 2;
UPDATE ARTIST3 SET INSTRUMENTS = NEWCOLLECTION('Vocals')
WHERE ARTIST_ID = 3;
UPDATE ARTIST3 SET INSTRUMENTS =
NEWCOLLECTION('Vocals','Acoustic Guitar') WHERE ARTIST_ID =
8;
UPDATE ARTIST3 SET INSTRUMENTS =
NEWCOLLECTION('Vocals','Acoustic Guitar') WHERE ARTIST_ID =
10;
UPDATE ARTIST3 SET INSTRUMENTS = NEWCOLLECTION('Background
Vocals') WHERE ARTIST_ID = 11;
UPDATE ARTIST3 SET INSTRUMENTS = NEWCOLLECTION('Background
Vocals') WHERE ARTIST_ID = 12;
