в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:02:37Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Object Oriented Database/Object Table Insert - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия