Assignment Nested Table

NESTED TABLE A Nested Table is a type of collector, which has no limit on the number of entries per row. It is in fact a table within a table and is represented as a column within another table

SRNO 1 2 3

FIELD NAME CAR_ID CAR_NAME ATTRIBUTES

SUB FIELD MODEL_NO COLOR MFG COST

DATA TYPE NUMBER VARCHAR2 NUMBER VARCHAR2 VARCHAR2 NUMBER

1)CREATE OBJECT CREATE OR REPLACE TYPE car_attributes AS OBJECT (MODEL_NO varchar2(10), COLOR VARCHAR2(20), MFG VARCHAR2(10), COST NUMBER(10)) / 2)CREATE TYPE CREATE OR REPLACE TYPE C_ATTRIB AS TABLE OF CAR_ATTRIBUTES; /

3)CREATE NESTED TABLES

CREATE TABLE tbl_car_nested (car_id number(5) primary key, car_name varchar2(10) , attributes c_attrib default c_attrib() ) NESTED TABLE attributes STORE AS mydata2 /

PERFORMING DML ON NESTED TABLES EXAMPLES: 1)INSERT insert into tbl_car_nested(car_id,car_name,attributes) values( 4,'skoda', C_ATTRIB(CAR_ATTRIBUTES('51','orange','hyundai',7880))) insert into tbl_car_nested(car_id,car_name,attributes) values( 3,'santro', C_ATTRIB(CAR_ATTRIBUTES('51','orange','hyundai',7880)))

insert into tbl_car_nested (car_id,car_name,attributes) values ( 10,'santro', C_ATTRIB ( CAR_ATTRIBUTES ('58','orange','hyundai',7880), CAR_ATTRIBUTES ('60','blue','tata',454) ) )

insert into tbl_car_nested(car_id,car_name,attributes) select 11,'santro gx',attributes from tbl_car_nested

INSERT INTO TABLE(select attributes from tbl_car_nested where car_id = 10) values (59,'black','hyundai',334);

2)UPDATING ALL ROWS IN REFERENCE TABLE FOR CAR 10

update table (

select attributes FROM tbl_car_nested WHERE car_id=10

)ca SET ca.MODEL_NO=23, ca.color='black', ca.mfg='toyota', ca.cost=56000

3)UPDATE BY REPALCING ALL ROWS IN REFRENCE TABLE BY ROW SPECIFIED IN SET CLAUSE update tbl_car_nested set attributes= C_ATTRIB(CAR_ATTRIBUTES( 99,'voilet','tata',899)) where car_id=4

4)

update table (

select attributes FROM tbl_car_nested WHERE car_id=10

)ca SET ca.MODEL_NO=59, ca.color='black', ca.mfg='BMW', ca.cost=909

5) UPDATING PERTICULAR ROW update table (

select attributes FROM tbl_car_nested WHERE car_id=10

)ca SET WHERE

ca.color='RED' ca.MODEL_NO=60

6)USING CAST-MULTISET OPERATOR

This is the example that insert into the table from table car that have the structure nested table column INSERT INTO tbl_car_nested(car_id,car_name,attributes) VALUES(198,'MERCED',CAST(MULTISET(SELECT modelno,color,mfg,cost FROM CAR ) AS c_attrib ) )

NOTE:The cast multiset operator converts the rows return by the sql into nested table type i.e c_attrib and insert into the table; similarly we can do it reverse while selecting USING CAST MULTISET OPERATOR

desc car

desc car Name Null Type ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------MODELNO VARCHAR2(4000) COLOR VARCHAR2(4000) MFG VARCHAR2(4000) COST NUMBER(20)

7)SELECTING THE DATA

SELECT NT.* FROM THE (SELECT ATTRIBUTES FROM TBL_CAR_NESTED WHERE CAR_ID =11) NT where nt.color='blue' select car_id from tbl_car_nested n where exists( select attributes from tbl_car_nested where car_id>n.car_id )

select car_id,car.* from tbl_car_nested,table(tbl_car_nested.attributes ) car

8)DELETING delete from the(select attributes from tbl_car_nested where car_id=198 ) nt where nt. color is not null

SELECTING THE DATA DIFFERENT TABLE THAN CAR CREATE OR REPLACE TYPE SUB_MODULE AS OBJECT(sub_module_name VARCHAR2(250), sub_module_desc VARCHAR2(250), url VARCHAR2(50)) / CREATE OR REPLACE TYPE type_sub_module AS TABLE OF SUB_MODULE / CREATE TABLE MODULE_MASTER( module_id VARCHAR2(2) PRIMARY KEY, module_name VARCHAR2(250), module_desc VARCHAR2(250), sub_module_details type_sub_module) NESTED TABLE sub_module_details STORE AS my_submodule /

select module_id,module_name,nt.* from module_master, table( module_master.sub_module_details) nt where module_id=1