Thursday 30 July 2015

NESTED-TABLE-IN-ORACLE

NESTED TABLE EXAMPLES IN ORACLE:

(1)FIRST CREATE TYPE:

CREATE TYPE  address_t  AS   OBJECT (
   street  VARCHAR2(30),
   city    VARCHAR2(20),
   state   CHAR(2),
   zip     CHAR(5) );
/
(2)Creating  type  as   subtable:

CREATE TYPE address_tab IS TABLE OF address_t;


(3)CREATE MAIN TABLE:


CREATE TABLE customers (
   custid  NUMBER,
   address address_tab )
NESTED TABLE address STORE AS customer_addresses;

(4)INSET VALUES INTO NESTED TABLES:

INSERT INTO customers VALUES (1,
            address_tab(
              address_t('101 First', 'Redwood Shores', 'CA', '94065'),
              address_t('123 Maple', 'Mill Valley',    'CA', '90952')
            ));


(5)SELECTING VALUES:


Select * from  customers;




No comments:

Post a Comment