Oracle PL/SQL/Object Oriented Database/Object Table Insert
Insert data to table containing object column
<source lang="sql">
SQL> SQL> SQL> --User-defined types SQL> SQL> CREATE TYPE address_typ AS OBJECT
2 (StreetNo NUMBER(10), 3 StreetName VARCHAR2(100), 4 AptNo NUMBER(5), 5 City VARCHAR2(100), 6 State VARCHAR2(100), 7 ZipCode NUMBER(9), 8 Country VARCHAR2(100)); 9 /
Type created. SQL> SQL> CREATE TABLE people
2 (ID NUMBER(5), 3 FirstName VARCHAR2(100), 4 LastName VARCHAR2(100), 5 Address address_typ);
Table created. SQL> SQL> SQL> INSERT INTO people
2 VALUES(10, 3 "John", 4 "Smith", 5 address_typ(123,"Happy Lane", NULL, 6 "Smalltown","Alaska", 12345,"USA") );
1 row created. SQL> SQL> SQL> SQL> SELECT * FROM people;
ID FIRSTNAME
----------------------------------------------------------------------------------------------------
LASTNAME
ADDRESS(STREETNO, STREETNAME, APTNO, CITY, STATE, ZIPCODE, COUNTRY)
10.00 John
Smith ADDRESS_TYP(123.00, "Happy Lane", NULL, "Smalltown", "Alaska", 12345.00, "USA")
SQL> SQL> drop table people; Table dropped. SQL> SQL>
</source>
Insert statement with nested type
<source lang="sql">
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>
</source>
Use user-defined type in insert statement
<source lang="sql">
SQL> create type addressType as object
2 (Street VARCHAR2(50), 3 City VARCHAR2(25), 4 State CHAR(2), 5 Zip NUMBER); 6 /
SQL> create type personType as object
2 (Name VARCHAR2(25), 3 Address addressType); 4 /
SQL> SQL> SQL> create table CUSTOMER(
2 cid NUMBER, 3 Person personType 4 );
SQL> SQL> insert into CUSTOMER values(1,personType("SomeName",addressType("Street","City","ST",11111))); SQL> insert into CUSTOMER values(1,personType("SomeName",addressType("Street2","City2","ST",11111))); SQL> SQL> drop type personType force; SQL> SQL> drop type addressType force; SQL> SQL> drop table CUSTOMER;
</source>