对象表是基于一个TYPE创建的表,而不是列的集合
如:会话1
对象表的创建
SQL> conn scott/scott
Connected.
SQL> create or replace type address_type-----先创建类型
2 as object
3 (city varchar2(30),
4 street varchar2(30),
5 state varchar2(2),
6 zip number
7 )
8 /
Type created.
SQL> create or replace type person_type
2 as object
3 (name varchar2(20),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created.
SQL> desc person_type
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
DOB DATE
HOME_ADDRESS ADDRESS_TYPE
WORK_ADDRESS ADDRESS_TYPE
SQL> desc address_type;
Name Null? Type
----------------------------------------- -------- ----------------------------
CITY VARCHAR2(30)
STREET VARCHAR2(30)
STATE VARCHAR2(2)
ZIP NUMBER
SQL> create table people of person_type;---根据类型创建表
Table created.
SQL> desc person_type;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
DOB DATE
HOME_ADDRESS ADDRESS_TYPE
WORK_ADDRESS ADDRESS_TYPE
SQL> desc people
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
DOB DATE
HOME_ADDRESS ADDRESS_TYPE
WORK_ADDRESS ADDRESS_TYPE
QL> insert into people ---向对象表中插入数据
2 values ( 'Tom', '15-mar-1965',
3
address_type( 'Reston', '123 Main Street', 'Va', '45678' ),
4
address_type( 'Redwood', '1 Oracle Way', 'Ca', '23456' ) );
1 row created.
SQL> select name,p.home_address.city from people p;--查询对象表【列名.集合中属性】
NAME HOME_ADDRESS.CITY
-------------------- ------------------------------
Tom Reston
我们DESC PEOPLE看到只有4个属性,有两个是集合类型,下面在系统数据字典中看下对象表的结构
SQL> select name, segcollength
2 from sys.col$
3 where obj# = ( select object_id
4 from user_objects
5 where object_name = 'PEOPLE' )
6 /
NAME SEGCOLLENGTH
------------------------------ ------------
SYS_NC_OID$ 16
SYS_NC_ROWINFO$ 1
NAME 20
DOB 7
HOME_ADDRESS 1
SYS_NC00006$ 30
SYS_NC00007$ 30
SYS_NC00008$ 2
SYS_NC00009$ 22
WORK_ADDRESS 1
SYS_NC00011$ 30
NAME SEGCOLLENGTH
------------------------------ ------------
SYS_NC00012$ 30
SYS_NC00013$ 2
SYS_NC00014$ 22
14 rows selected.
这里看到对象表PEOPLE有14列!与前面的4列大不同。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-722213/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-722213/