浅谈oracle复合数据类型

浅谈oracle复合数据类型

--本文档可以直接拷贝运行。

/*

oracle复合数据类型

PL/SQL有两种复合数据结构：记录和集合。记录由不同的域组成，集合由不同的元素组成。*/

/*

一、记录类型

类似C语言中的结构，有两种定义方式：显式定义和隐式定义。

*/

create table test

(

id varchar2(20),

mc varchar2(60)

);

insert into test values('111','11111');

insert into test values('222','22222');

insert into test values('333','33333');

insert into test values('444','44444');

insert into test values('555','55555');

insert into test values('666','66666');

commit;

/*

1.1、显示定义记录

*/

set serverout on

declare

type t_record is record

(

id test.id%type,

mc test.mc%type

);

var_record t_record;

counter number default 0;

begin

for row_test in (select id,mc from test) loop

counter := counter + 1;

var_record.id := row_test.id;

var_record.mc := row_test.mc;

dbms_output.put_line('var_record:'||var_record.id||'---'||var_record.mc);

dbms_output.put_line('row_test:'||row_test.id||'---'||row_test.mc);

dbms_output.put_line('================loop '||counter||' times.');

end loop;

exception when others then

dbms_output.put_line(sqlcode||sqlerrm);

end;

/

/*有一些PL/SQL指令在使用隐式定义记录时没有使用%ROWTYPE属性，比如游标FOR循环或触发器中的ld 和:new记录。*/

/*

1.2、隐式定义记录

*/

隐式定义记录中，我们不用描述记录的每一个域，在声明记录变量时使用%ROWTYPE命令定义与数据库表，视图，游标有相同结构的记录。

declare

t_record1 test%rowtype;

cursor cur_test(v_id in varchar2) is

select id,mc from test

where id <= v_id;

t_record2 cur_test%rowtype;

begin

for row_test in cur_test('333') loop

t_record1.id := row_test.id;

t_record1.mc := row_test.mc;

t_record2.id := row_test.id;

t_record2.mc := row_test.id;

dbms_output.put_line('t_record1:'||t_record1.id||'---'||t_record1.mc);

dbms_output.put_line('t_record2:'||t_record2.id||'---'||t_record2.mc);

dbms_output.put_line('row_test:'||row_test.id||'---'||row_test.mc);

dbms_output.put_line('================loop '||cur_test%rowcount||' times.');

end loop;

exception when others then

dbms_output.put_line(sqlcode||sqlerrm);

end;

/

/*

二、集合

类似C语言中的数组，在ORACLE7.3及以前的版本中只有一种集合称为PL/SQL表，这种类型的集合依然保留，就是索引（INDEX_BY）表。

PL/SQL有三种类型的集合a、Index_by表;b、嵌套表; c、VARRAY

三种类型的集合之间的差异，包括数据绑定、稀疏性(sparsity)、数据库中的存储能力都有不相同。

数据绑定：

绑定涉及到集合中元素数量的限制，VARRAY集合中的元素的数量是有限，Index_by和嵌套表则是没有限制的。

稀疏性(sparsity)：

稀疏性描述了集合的下标是否有间隔，Index_by表和嵌套表可以是稀疏的，VARRAY类型的集合则是紧密的，它的下标之间没有间隔。

存储：

Index_by表不能存储在数据库中，但嵌套表和VARRAY可以被存储在数据库中。

Index_by表定义语法如下：

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTEGER;

关键字是INDEX BY BINARY_INTEGER，没有这个关键字，那么集合将是一个嵌套表。由于不存储在数据库中，

element_type可以是任何合法的PL/SQL数据类型，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。

嵌套表定义语法如下：

嵌套表非常类似于Index_by表，创建的语法也非常相似。只是没有INDEX BY BINARY_INTEGER子串TYPE type_name IS TABLE OF element_type [NOT NULL];

存储在一个数据库中的嵌套表并不与表中的其它数据存放在同一个数据块中，它们实际上被存放在第二个表中。

从数据库中取回的嵌套表也不保证元素的顺序。集合数据是离线存储的，所以嵌套表适合大型集合。

VARRAY定义语法如下：

TYPE type_name IS [VARRAY|VARYING ARRAY] (max_size) OF element_type [NOT NULL];

max_size是一个整数，用于标示VARRAY集合拥有的最多元素数目。VARRAY集合的元素数量可以低于max_size,但不能超过max_size。

element_type是一维元素的数据类型，如果element_type是记录，那么这个记录只能使用标量数据字段（与嵌套标相似）。

VARRAY存储在数据库中时与表中的其他数据存放在同一个数据块中，元素的顺序保存在VARRAY中。集合是在线存储的，VARRAY很适合于小型集合。