Insert statement with nested type : Insert « Object Oriented « Oracle PL/SQL Tutorial





SQL> create type addressType as object
  2  (Street   VARCHAR2(50),
  3  City      VARCHAR2(25),
  4  State     CHAR(2),
  5  Zip       NUMBER);
  6  /
SQL>
SQL> create type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL>
SQL> create or replace type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL>
SQL> create table myemp
  2  (cid    NUMBER,
  3   Person         personType);
SQL>
SQL>
SQL> insert into myemp values(1,personType('SomeName',addressType('StreetValue','CityValue','ST',11111)));
SQL>
SQL>
SQL>
SQL> select C.cid, C.Person.Name
  2    from myemp C;
                                                              

       CID  PERSON.NAME
----------  -------------------------
         1  SomeName
                   
SQL>
SQL>
SQL> drop table myemp;
SQL> drop type personType;
SQL> drop type addressType;
SQL>










32.12.Insert
32.12.1.Inserting Rows into the Table with object type column
32.12.2.Inserting a row of object into an object table
32.12.3.Inserting a row into an object table by supplying the values in the same way in a relational
32.12.4.INSERT Values into a Table with the Column Type in It
32.12.5.Object Relational tables
32.12.6.INSERT object instance by calling its constructor
32.12.7.Reference column name in an object table
32.12.8.Insert value to a table with 'table of custom type' as table column type
32.12.9.Reference type constructor in insert statement
32.12.10.Use user-defined type in insert statement
32.12.11.Insert statement with nested type