Oracle PL/SQL/Object Oriented Database/Object Table Insert

Материал из SQL эксперт
Перейти к: навигация, поиск

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>