Oracle 用户自定义数据类型

用户自定义数据类型（User-defined Data Type)
oracle支持对象类型（Object Type）、嵌套类型（Nested Table Type)和可变数组类型（Varray Data Type）三种用户自定义数据类型。
对象类型 用于描述客观世界中存在的事物，eg 客户、订单。对象类型是在其他内置数据类型基础上建立的，可以包括多个元素（属性），这些属性共同构成了结构化的数据单元，对象类型中还可以包含多种操作，称为（方法），用户可以对一个数据单元执行某种方法。这里的“方法”其实就是一些存储过程、函数。
create or replace type schoolType as OBJECT
(
name varchar2(30),
City varchar2(30),
Street varchar2(30)
);
对象有两种存储形式：
1，对象单独存储在关系表中，也就是整个表只存储对象，（对象表）。在这样的表中，每行表示一个对象，存储于对象表中的对象被称为行对象（Row Object).
2, 与表中其他数据一起存储，即对象只占表中的一列，这样的对象称为列对象（Column Object）
--创建对象表：
create table school_infor  of schoolType;
--创建含有对象的关系表：
create table student_info
(name varchar2(10),
id number,
school schoolType);
SQL> desc student_info
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------
NAME                                               VARCHAR2(10)
ID                                                 NUMBER
SCHOOL                                             SCHOOLTYPE

insert into school_infor values（
schoolType ('北大','北京','中关村'));
SQL>select * from school_infor;
NAME                           CITY                STREET
------------------------------ ------------------------
北大                           北京                中关村

SQL> select stu.name || stu.id||stu.school.name||stu.school.city||stu.school.street
  2  from student_info stu;

STU.NAME||STU.ID||STU.SCHOOL.NAME||STU.SCHOOL.CITY||STU.SCHOOL.STREET
---------------------------------------------------------------------------------------------
小花儿112北大北京中关村
drop TYPE schoolType;

可变数组类型(variable-size arrays Type)是一个有序的相同数据类型元素的集合。一个数组内所有数据元素的数据类型均相同，每一个元素均有一个索引编号（下标),代表元素在数组中的位置，通过下标访问数组元素。
可变数组数据通常采用内联存储（stored In Line)，即与其在同一表中的其他数据存储在同一表空间内。但如果数据很大，也可以存储在BLOB中。   

create Type address_list as VARRAY(10) of varchar2(80);--创建一种新的数据类型address_list
create TYPE phone_typ as OBJECT (
country_code varchar2(2),--国家代码
area_code varchar2(3),--区号
phone_number varchar2(7));--电话号码

create TYPE varray_phone as VARRAY(10) of phone_typ;

create table instructor (
name varchar2(10),
phone_list varray_phone);

insert into instructor values (
'张教授',
varray_phone(
phone_typ('86','010','6823323'),
phone_typ('86','020','2343130'),
phone_typ('86','029','2324575'))
);
SQL> select * from  instructor;

NAME
----------
PHONE_LIST(COUNTRY_CODE, AREA_CODE, PHONE_NUMBER)
--------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------
王教授
VARRAY_PHONE(PHONE_TYP('86', '010', '6623323'), PHONE_TYP('86', '020', '2323130'), PHONE_TYP('86', '029', '2353575'))

张教授
VARRAY_PHONE(PHONE_TYP('86', '010', '6823323'), PHONE_TYP('86', '020', '2343130'), PHONE_TYP('86', '029', '2324575'))
SQL> select  country_code,area_code,phone_number
  2  from table (select phone_list from instructor
  3  where name = '王教授')
  4  where area_code='029';

CO ARE PHONE_N
-- --- -------
86 029 2353575

嵌套表类型（nested Table Type）是一个无序的相同类型数据元素的集合，所有的元素具有相同的数据类型，定义嵌套表时无须指定元素的最大数量，元素的数据类型可以是标准的oracle数据类型，也可以是用户自定义的数据类型，使用create type ... as table of  定义嵌套表，注意：只是定义了一种新的数据类型，并未涉及任何空间的分配。
create or replace  TYPE bookOBJType as OBJECT
(
bookid varchar2(10),
bookname  varchar2(18),
describe varchar2(10))
not final;

create or replace  type  bookNESTType as TABLE OF bookOBJType;

create table studentbook
(
student_name varchar2(9) NOT NULL Primary Key,
home_address  varchar2(9),
booklist bookNESTType
）
TABLESPACE USERDB
Nested Table booklist store as booklist_table (TABLESPACE  USERS);

insert into studentbook values
(
'李晓晓',
'天津',
bookNESTType
(
bookOBJType ('113','《中国历史》','历史类'),
bookOBJType('114','《火星文》','科普类')
)
);
SQL> select * from studentbook;

STUDENT_N HOME_ADDR
--------- ---------
BOOKLIST(BOOKID, BOOKNAME, DESCRIBE)
---------------------------------------------------------------------------------------------------------
----------------------------------------------------------
王晓晓    上海
BOOKNESTTYPE(BOOKOBJTYPE('111', '《中国通史》', '历史类'), BOOKOBJTYPE('112', '《火星》', '科普类'))

李晓晓    天津
BOOKNESTTYPE(BOOKOBJTYPE('113', '《中国历史》', '历史类'), BOOKOBJTYPE('114', '《火星文》', '科普类'))
update table (select t.booklist from studentbook t where t.student_name ='王晓晓') tt
set tt.bookname ='《世界历史》'
where tt.bookname ='《中国通史》';
commit;
SQL> select * from studentbook;

STUDENT_N HOME_ADDR
--------- ---------
BOOKLIST(BOOKID, BOOKNAME, DESCRIBE)
--------------------------------------------------------------------------------------------------------------
----------------------------------------------------------
王晓晓    上海
BOOKNESTTYPE(BOOKOBJTYPE('111', '《世界历史》', '历史类'), BOOKOBJTYPE('112', '《火星》', '科普类'))

李晓晓    天津
BOOKNESTTYPE(BOOKOBJTYPE('113', '《中国历史》', '历史类'), BOOKOBJTYPE('114', '《火星文》', '科普类'))

转载于:https://www.cnblogs.com/happinessqi/p/3349564.html