Nested table and variable length array example (SQL)

-- Nested table and variable length array example:

 

Create Type animal_ty As Object (
Breed Varchar2 ( 25 ),
Names Varchar2 ( 25 ),
Birthdate date );
/**/ /*
Create or replace type mytabletype as table of varchar2 (30 );*/

Create Type animals_nt As   Table   Of Animal_ty;

Create   Table Breeder
(Breedername Varchar2 ( 25 ),
Animals animals_nt)
Nested Table Animals store As Animals_nt_tab;


Insert   Into Breeder
Values ( ' Mary ' , Animals_nt (animal_ty ( ' Dog ' , ' Butch ' , To_date ( ' 3-12-12 ' , ' DD-mm-yy ' )),
Animal_ty ( ' Dog ' , ' Rover ' , To_date ( ' 03-12-12 ' , ' DD-mm-yy ' )),
Animal_ty ( ' Dog ' , ' Julio ' , To_date ( ' 03-12-12 ' , ' DD-mm-yy ' ))));

Insert   Into Breeder
Values ( ' Jane ' , Animals_nt (animal_ty ( ' Cat ' , ' An ' , To_date ( ' 31-03-97 ' , ' DD-mm-yy ' )),
Animal_ty ( ' Cat ' , ' Jame ' , To_date ( ' 31-03-97 ' , ' DD-mm-yy ' )),
Animal_ty ( ' Cat ' , ' Killer ' , To_date ( ' 31-03-97 ' , ' DD-mm-yy ' ))));


  Select   *   From Breeder;
 
  -- Query nested tables
Select Names, birthdate
From   Table ( Select Animals From Breeder );


Select Names, birthdate
From   Table ( Select Animals From Breeder Where Breedername =   ' Mary ' )
  Where Names =   ' Rover ' ;
 
  Delete   From   Table ( Select Animals From Breeder) Where Breed = ' Cat '
  Drop   Table Breeder;

 
 
-- Variable Length Array

Create Type comm_info As Object
( -- This type is a set of communication methods.
No Number ( 3 ), -- Communication Model
Comm_type Varchar2 ( 20 ), -- Communication type
Comm_no Varchar2 ( 30 ) -- Number
);

-- 2. Create a variable array comm_info_list

Create Type comm_info_list As  
Varray ( 50 ) Of Comm_info;

-- 3. Create a table

Create   Table User_info
( User_id   Number ( 6 ), -- User ID
User_name   Varchar2 ( 20 ), -- User Name
User_comm comm_info_list -- Contact user communication method
);

-- 4. insert records into a variable array

Insert   Into User_info
Values ( 1 , ' Mary ' , Comm_info_list (comm_info ( 1 , ' Mobile phone ' , ' 13651401919 ' ),
Comm_info ( 2 , ' Pager ' , ' 1281234567 ' ))
);
Insert   Into User_info
Values ( 2 , ' Carl ' , Comm_info_list (comm_info ( 1 , ' Mobile phone ' , ' 13901018888 ' ),
Comm_info ( 2 , ' Pager ' , ' 1281234567 ' ))
);
Insert   Into User_info
Values ( 2 , ' Badboy ' , Comm_info_list (comm_info ( 1 , ' Mobile phone ' , ' 133546898888 ' ),
Comm_info ( 2 , ' Pager ' , ' 12545541 ' ),
Comm_info ( 3 , ' Email ' , ' Changhai-xuri@163.com ' ))
);
Commit ;

-- 5. query variable Arrays

Select User_comm From User_info Where   User_id   =   2 ;
Select Comm_type, comm_no
From   Table ( Select User_comm From User_info Where   User_id   =   1 )
  Where No =   1 ;