Use of Varray & Nested Table in Oracle SQL

1. Code For the Storing Varray Example:

Step-01 Create type to define RECORD as Object in SQL  for holding 2 fields data.

CREATE OR REPLACE TYPE t_phone_number AS OBJECT(p_type VARCHAR2(10), p_number VARCHAR2(50));

1. CREATE OR REPLACE TYPE v_phone_numbers AS VARRAY(3) OF t_phone_number;

1. 
   

1. Step-03 Use Varray Data type in table.

1. CREATE TABLE emps_with_phones(employee_id NUMBER,

1. first_name VARCHAR2(50),

1. last_name VARCHAR2(50),

1. phone_number v_phone_numbers);

1. 
   

1. Step-04 Verify Table.

1. SELECT * FROM emps_with_phones;

1. 
   

1. Step-05 Add data to Varray

1. INSERT INTO emps_with_phones VALUES(10,'Alex','Brown',

1. v_phone_numbers(t_phone_number('HOME','111.111.1111'),

1. t_phone_number('WORK','222.222.2222'),

1. t_phone_number('MOBILE','333.333.3333')));

1. INSERT INTO emps_with_phones VALUES(11,'Bob','Green',

1. v_phone_numbers(t_phone_number('HOME','000.000.000'),

1. t_phone_number('WORK','444.444.4444')));

1. Step-06 Query the data table to verify Varray.

1. 
   

1. SELECT e.first_name,

1. last_name,

1. p.p_type,

1. p.p_number

1. FROM emps_with_phones e, table(e.phone_number) p;

Step-02 Create type as Varray to store as Object.

1.  

1. Code For the Storing Nested Table Example

1. Step-01 Define Type as table.

1. CREATE OR REPLACE TYPE n_phone_numbers AS TABLE OF t_phone_number;

1. 
   

1. Step-02 Use Table Data Type in table column.

1. CREATE TABLE emps_with_phones2(employee_id NUMBER,

1. first_name VARCHAR2(50),

1. last_name VARCHAR2(50),

1. phone_number n_phone_numbers)

1. NESTED TABLE phone_number STORE AS phone_numbers_table;

1. 
   

1. Step-03 Verify Table.

1. SELECT * FROM emps_with_phones2;

1. 
   

1. Step-04 Add data to table using Nested Table.

1. INSERT INTO emps_with_phones2

1. VALUES(10,'Alex','Brown',n_phone_numbers(t_phone_number('HOME','111.111.1111'),

1. t_phone_number('WORK','222.222.2222'),

1. t_phone_number('MOBILE','333.333.3333'))

1. );

1. INSERT INTO emps_with_phones2

1. VALUES(11,'Bob','Green',n_phone_numbers(t_phone_number('HOME','000.000.000'),

1. t_phone_number('WORK','444.444.4444'))

1. );

1. Step-05 Verify data from table.

1. SELECT e.first_name, last_name, p.p_type, p.p_number

1. FROM emps_with_phones2 e, table(e.phone_number) p;

1.  

1. Step-06 Add and Modify data to table using Nested Table.

1. INSERT INTO emps_with_phones2

1. VALUES(11,'Bob','Green',n_phone_numbers(t_phone_number('HOME','000.000.000'),

1. t_phone_number('WORK','444.444.4444'),

1. t_phone_number('WORK2','444.444.4444'),

1. t_phone_number('WORK3','444.444.4444'),

1. t_phone_number('WORK4','444.444.4444'),

1. t_phone_number('WORK5','444.444.4444'))

1. );

1. SELECT * FROM emps_with_phones2;

1.  

1. UPDATE emps_with_phones2

1. SET phone_number = n_phone_numbers(t_phone_number('HOME','000.000.000'),

1. t_phone_number('WORK','444.444.4444'),

1. t_phone_number('WORK2','444.444.4444'),

1. t_phone_number('WORK3','444.444.4444'),

1. t_phone_number('WORK4','444.444.4444'),

1. t_phone_number('WORK5','444.444.4444'))

1. WHERE employee_id = 11;

1.  

1. Step-07 Add and Modify data to table using anonymous block:

1. 
   

1. DECLARE

1. p_num n_phone_numbers;

1. BEGIN

1. SELECT phone_number

1. INTO p_num

1. FROM emps_with_phones2

1.  WHERE employee_id = 10;

1. p_num.extend;

1. p_num(5) := t_phone_number('FAX','999.99.9999');

1. 
   

1. UPDATE emps_with_phones2

1. SET phone_number = p_num

1. WHERE employee_id = 10;

1. END;