复合类型练习

一、记录类型

1.显示定义

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;
/

 

执行结果

var_record:111----11111
row_test: 111----11111
=======loop1times.
var_record:222----22222
row_test: 222----22222
=======loop2times.
var_record:333----33333
row_test: 333----33333
=======loop3times.
var_record:444----44444
row_test: 444----44444
=======loop4times.
var_record:555----55555
row_test: 555----55555
=======loop5times.
var_record:666----66666
row_test: 666----66666
=======loop6times.

 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.id;
t_record2.id := row_test.id;
t_record2.mc := row_test.id;
dbms_output.put_line('row_test:' || row_test.id || '----' || row_test.mc);
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('=======loop' || cur_test%rowcount || 'times.');
end loop;
exception when others then
dbms_output.put_line(sqlcode || sqlerrm);
end;

 

执行结果

row_test:111----11111
t_record1:111----111
t_record2:111----111
=======loop1times.
row_test:222----22222
t_record1:222----222
t_record2:222----222
=======loop2times.
row_test:333----33333
t_record1:333----333
t_record2:333----333
=======loop3times.

二、集合

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

• Index_by表
• 嵌套表
  
• 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。

 

嵌套表定义语法如下：

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很适合于小型集合。

 

嵌套表和VARRAY都能作为列存储在数据库表中，所以集合自身可以为NULL,当集合为NULL时，用户也不能引用集合中的元素。

• index_by表：

declare
cursor cur_test is select id,mc from test;
type t_test1 is table of varchar2(60) index by binary_integer;
type t_test2 is table of test%rowtype index by binary_integer;
var_test1 t_test1;
var_test2 t_test2;
var_new t_test2;
begin
select id,mc into var_test2(0) from test where id='111';
dbms_output.put_line('var_test2(0):' || var_test2(0).id || '----' || var_test2(0).mc);
select id,mc into var_test2(8) from test where id='333';
dbms_output.put_line('var_test2(8):' || var_test2(8).id || '----' || var_test2(8).mc);
var_new :=var_test2;
dbms_output.put_line('===== copy var_test2 to var_new =====');
dbms_output.put_line('var_new(0):'||var_new(0).id||'---'||var_new(0).mc);
dbms_output.put_line('var_new(8):'||var_new(8).id||'---'||var_new(8).mc);
end;

 

执行结果

var_test2(0):111----11111
var_test2(8):333----33333
===== copy var_test2 to var_new =====
var_new(0):111---11111
var_new(8):333---33333

•   嵌套表和VARRAY

DECLARE
TYPE t_test1 IS TABLE OF test.id%TYPE;
TYPE t_test2 IS VARRAY (10) OF test.id%TYPE;
var_test1 t_test1;
var_test2 t_test2;
begin
var_test1 := t_test1('test1.1','test1.2','test1.3');
dbms_output.put_line('var_test1: '||var_test1(1)||','||var_test1(2)||','||var_test1(3));
var_test2 := t_test2('test2.1','test2.2','test2.3');
dbms_output.put_line('var_test2: '||var_test2(1)||','||var_test2(2)||','||var_test2(3));
var_test1(2) := 'test1.2_update';
dbms_output.put_line('==== modify var_test1(2) ====');
dbms_output.put_line('var_test1: '||var_test1(1)||','||var_test1(2)||','||var_test1(3));
dbms_output.put_line(var_test1.next(3));
dbms_output.put_line('the length of var_test2''s element: '||var_test2.limit());
end;
/

 

执行结果

var_test1: test1.1,test1.2,test1.3
var_test2: test2.1,test2.2,test2.3
==== modify var_test1(2) ====
var_test1: test1.1,test1.2_update,test1.3

the length of var_test2's element: 10

  除了构造函数外,集合还有很多内建函数，按照面向对象编成的叫法称之为方法。
方法                                    描述                                                                                   使用限制
COUNT                               返回集合中元素的个数
DELETE                              删除集合中所有元素
DELETE(x)                          删除元素下标为x的元素                                                         对VARRAY非法
DELETE(x,y)                       删除元素下标从X到Y的元素                                                    对VARRAY非法
EXIST(x)                            如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE
EXTEND                             在集合末尾添加一个元素                                                        对Index_by非法
EXTEND(x)                         在集合末尾添加x个元素                                                         对Index_by非法
EXTEND(x,n)                      在集合末尾添加元素n的x个副本                                              对Index_by非法
FIRST                                 返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
LAST                                  返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT.
LIMIT                                 返回VARRY集合的最大的元素个数                                           Index_by集合和嵌套表无用
NEXT(x)                             返回在第x个元素之后及紧挨着它的元素的值，如果x是最后一个元素，返回null.
PRIOR(x)                           返回在第x个元素之前紧挨着它的元素的值，如果x是第一个元素，则返回null。
TRIM                                  从集合末端开始删除一个元素                                                  对于index_by不合法
TRIM(x)                              从集合末端开始删除x个元素                                                   对index_by不合法

 

综合练习：

set serverout on
DECLARE
TYPE t_record IS RECORD (
id number(18,0),
mc varchar2(50)
);
var_record t_record;
type t_test is table of t_record;
var_test t_test := t_test();
cursor cur_test is select id,mc from test;
begin
open cur_test;
fetch cur_test BULK COLLECT INTO var_test;
for i in 1..var_test.count() loop
dbms_output.put_line(var_test(i).id||'---'||var_test(i).mc);
end loop;
end;
/