Oracle嵌套表的使用2008-04-24 09:24 A.M.从别人那里搜来的，放这里，方便学习之用
嵌套表
一、嵌套表的定义：
嵌套表是表中之表。一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行。在某种意义上，它是在一个表中存储一对多关系的一种方法。考查一个包含部门信息的表，在任何时间内每个部门会有很多项目正在实施。在一个严格的关系模型中，将需要建立两个独立的表department和project。
嵌套表允许在department表中存放关于项目的信息。勿需执行联合操作，就可以通过department表直接访问项目表中的记录。这种不经联合而直接选择数据的能力使得用户对数据访问更加容易。甚至在并没有定义方法来访问嵌套表的情况下，也能够很清楚地把部门和项目中的数据联系在一起。在严格的关系模型中，department和project两个表的联系需要通过外部关键字（外键）关系才能实现。
二、举例说明嵌套表的使用：
假设有一个关于动物饲养员的表，希望其中具有他们饲养的动物的信息。用一个嵌套表，就可以在同一个表中存储饲养员和其饲养的全部动物的信息。
1、创建类型animal_ty：此类型中，对于每个动物都包含有一个记录，记载了其品种、名称和出生日期信息。
CREATE TYPE animal_ty AS OBJECT (
breed varchar2(25),
name varchar2(25),
birthdate date);
2、创建animals_nt：此类型将用作一个嵌套表的基础类型。
CREATE TYPE animals_nt as table of animal_ty;
3、创建表breeder：饲养员的信息表
create table breeder
(breedername varchar2(25),
animals animal_nt)
nested table animals store as animals_nt_tab;
4、向嵌套表中插入记录
insert into breeder
values('mary',animal_nt(animal_ty('dog','butch','31-MAR-97'),
animal_ty('dog','rover','31-MAR-97'),
animal_ty('dog','julio','31-MAR-97')));
insert into breeder
values('jane',animal_nt(animal_ty('cat','an','31-MAR-97'),
animal_ty('cat','jame','31-MAR-97'),
animal_ty('cat','killer','31-MAR-97')));
commit;
5、查询嵌套表
select name,birthdate from
table(select animals from breeder);
select name,birthdate from
table(select animals from breeder
where breedername=’mary’)
where name=’dog’;
三、嵌套表的特点：
1、对象复用：如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。
2、标准支持：如果创建标准的对象，那么它们被重用的机会就会提高。如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。
3、定义访问路径：对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性。
可变数组
一、可变数组的定义：
可变数组与嵌套表相似，也是一种集合。一个可变数组是对象的一个集合，其中每个对象都具有相同的数据类型。可变数组的大小由创建时决定。在表中建立可变数组后，可变数组在主表中作为一个列对待。从概念上讲，可变数组是一个限制了行集合的嵌套表。
可变数组，允许用户在表中存储重复的属性。例如：假设用户有一个project表，并在项目中指定了工作人员，一个项目可以有多个工人，而一个工人也可以为多个项目工作。在严格的关系模型中，用户可以创建一个project表，一个worker表和存储它们之间关系的交叉表project_worker。
用户可使用可变数组在project表中存储工人的名字。如果项目限定的工人数不超过10人，可以建立一个以10个数据项为限的可变数组。接下来就可处理此可变数组，从而对于每一个项目，可以选取其中所有工人的名字，而勿需查询表worker。
二、举例说明可变数组的使用：
1、创建类型comm_info
CREATE TYPE comm_info AS OBJECT ( /*此类型为通讯方式的集合
no number(3), /*通讯类型号
comm_type varchar2(20), /*通讯类型
comm_no varchar2(30)); /*号码
2、创建可变数组comm_info_list
CREATE TYPE comm_info_list AS
VARRAY(50) OF comm_info;
3、创建表
create table user_info
(user_id number(6), /*用户ID号
user_name varchar2(20), /*用户名称
user_comm comm_info_list); /*与用户联系的通讯方式
4、向可变数组插入记录
insert into user_info
values(1,'mary',comm_info_list(comm_info(1,'手机','13651401919'),
comm_info(2,'呼机','1281234567')));
insert into user_info
values(2,'carl',comm_info_list(comm_info(1,'手机','13901018888'),
comm_info(2,'呼机','1281234567')));
commit;
5、查询可变数组
select user_comm from user_info
where user_id=1;
select comm_type,comm_no
from table(select user_comm from user_info
where user_id=1)
where no=1;
与一位用户联系的方式有很多种，比如：手机、呼机、座机等。在一个严格的关系模型中，将需要两个独立的表：用户信息和通讯方式，而在可变数组中，允许在表user_info中直接访问用户的联系方式，这种不经联合而直接选择数据的能力使得用户对数据的访问更加容易。
三、可变数组的特点：
1、对象复用：如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。
2、标准支持：如果创建标准的对象，那么它们被重用的机会就会提高。如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。
3定义访问路径：对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性。
1、cast:强制转换成指定的数据类型。
2、multiset:转换成定义好的类型。
3、 table()函数
----------------------------------------------
PL/SQL表---table()函数用法
PL/SQL表---table()函数用法：
利用table()函数，我们可以将PL/SQL返回的结果集代替table。
simple example：
1、table()结合数组：
*/
create or replace type t_test as object(
id integer,
rq date,
mc varchar2(60)
);
create or replace type t_test_table as table of t_test;
create or replace function f_test_array(n in number default null) return t_test_table
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
v_test. extend(); -- append one null element
-- 为增加的元素赋值，如果没用 EXTEND，这里会出错
v_test(v_test.count) := t_test(i,sysdate,'mc'||i);
end loop;
return v_test;
end f_test_array;
/
select * from table(f_test_array(10));
/*
2、table()结合PIPELINED函数：
*/
create or replace function f_test_pipe(n in number default null) return t_test_table PIPELINED
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
pipe row(t_test(i,sysdate,'mc'||i));
end loop;
return;
end f_test_pipe;
/
select * from table(f_test_pipe(20));
/*
3、table()结合系统包：
*/
create table test (id varchar2(20));
insert into test values('1');
commit;
explain plan for select * from test;
select * from table(dbms_xplan.display);
tablespace由segments(段)构成
段是由extends构成 中文叫作区 或者数据区
区是由一个一个的数据块构成 数据块的大小由操作系统决定。
A
B
C
D
E
在实际的应用中，为了让PL/SQL 函数返回数据的多个行，必须通过返回一个 REF CURSOR 或一个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据，而整个集合在可以返回前，必须进行具体化。
Oracle 9i 通过引入的管道化表函数纠正了后一种情况。表函数是返回整个行的集（通常作为一个集合）的函数，可以直接从 SQL 语句中进行查询，就好像它是一个真正的数据库表一样。管道化表函数与之相似，但是它像在构建时一样返回数据，而不是一次全部返回。管道化表函数更加有效，因为数据可以尽可能快地返回。
管道化表函数必须返回一个集合。在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成。一旦我们创建了上述函数，我们就可以使用 TABLE 操作符从 SQL 查询中调用它。
管道化表函数经常被用来把数据从一种类型转化成另一种类型。
下面是用Pipelined Table实现split函数的示例：
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN ty_str_split PIPELINED
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i); -----instr(待匹配的字符串，匹配字符串，开始位置，匹配次数)
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
PIPE ROW (str);
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i); ----取得字符串中指定起始位置和长度的字符串 substr( string, start_position, [ length ] )
i := j + len1;
PIPE ROW (str);
END IF;
END LOOP;
RETURN;
END fn_split;
/
测试：
SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;'));
结果：
1
12
123
1234
12345
又一个简单的例子：
CREATE TYPE mytype AS OBJECT (
field1 NUMBER,
field2 VARCHAR2 (50)
);
CREATE TYPE mytypelist AS TABLE OF mytype;
CREATE OR REPLACE FUNCTION pipelineme
RETURN mytypelist PIPELINED
IS
v_mytype mytype;
BEGIN
FOR v_count IN 1 .. 20
LOOP
v_mytype := mytype (v_count, 'Row ' || v_count);
PIPE ROW (v_mytype);
END LOOP;
RETURN;
END pipelineme;
SELECT * FROM TABLE (pipelineme);
FIELD1 FIELD2
------ ------------------------
1 Row 1
2 Row 2
3 Row 3
4 Row 4
5 Row 5
6 Row 6
7 Row 7
8 Row 8
9 Row 9
10 Row 10
11 Row 11
12 Row 12
13 Row 13
14 Row 14
15 Row 15
16 Row 16
17 Row 17
18 Row 18
19 Row 19
20 Row 20
20 rows selected
2565
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
