PLSQL 集合

2 type type_names is table of varchar2(20) index by pls_integer;
3 t_names type_names;
4 begin
5 t_names(1) := 'yuechaotian';
6 t_names(2) := 'guoguo';
7 t_names(3) := 'oratea';
2 /
类型已创建。
SQL> create table my_family(
2 province varchar2(20),
3 parent type_parent,
4 children type_children
5 );
表已创建。
SQL> declare
2 t_parent type_parent := type_parent();
CHILDREN
---------------------------------------------------------------
Hebei
TYPE_PARENT('my father', 'my mother')
TYPE_CHILDREN('my sister', 'my brother', 'yuechaotian')
这三种类型都是一维的，如果需要多维的数组类型，可以嵌套定义。
1.联合数组（associative array）
SQL> set serveroutput on
SQL> declare
2 type type_names is table of varchar2(20) index by pls_integer;
14 commit;
15 end;
16 /
PL/SQL过程已成功完成。
SQL> select * from my_family;
PROVINCE
--------------------
PARENT
---------------------------------------------------------------
SQL> set serveroutput on
SQL> declare
2 type type_names is table of varchar2(20);
3 t_names_parent type_names := type_names();
4 t_names_children type_names := type_names();
my mother
PL/SQL过程已成功完成。
SQL>
因为上面定义的内嵌表是紧密的，所以可以使用FOR循环。使用内嵌表存储数据前，必须初始化。也必须手工分配存储空间。
初始的内嵌表是紧密的，但可以通过删除操作使得它变得稀疏：
SQL> set serveroutput on
SQL> declare
2 type type_names is table of varchar2(20);
8 t_names(4) := 'hot_dog';
9 for n_pointer in t_names.first..t_st loop
10 dbms_output.put_line( t_names(n_pointer) );
11 end loop;
12 end;
13 /
yuechaotian
20 for n_pointer in t_names_parent.first..t_names_st loop
21 dbms_output.put_line( t_names_parent(n_pointer) );
22 end loop;
23 end;
24 /
my father
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
DELETE procedure
Removes one or more elements from the collection. Reduces COUNT if the element is not already DELETEd. With VARRAYS, you can delete only the entire contents of the collection.
9 t_children(1) := 'my sister';
10 t_children(2) := 'my brother';
11 t_children(3) := 'yuechaotian';
12 insert into my_family
13 values( 'Hebei', t_parent, t_children );
EXISTS function
Returns TRUE or FALSE to indicate whether the specified element exists.
EXTEND procedure
Increases the number of elements in a nested table or VARRAY. Increases COUNT.
guoguo
oratea
hot_dog
PL/SQL过程已成功完成。
有时候，使用稀疏数组很方便。比如，你可以使用表中某列作为稀疏数组的下标，来存储数据。
2.内嵌表（nested table）
从Oracle10g Release 1开始，内嵌表支持MULTISET EXCEPT，功能与SQL中的MINUS类似：
3 t_names_mine type_names := type_names();
4 begin
5 t_names_mine.extend(3);
6 t_names_mine(1) := 'yuechaotian';
7 t_names_mine(2) := 'yuechaotiao';
8 t_names_mine(3) := 'tianyc';
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
begin
VARRAY有边界。定义时必须指定最大长度。同嵌套表一样，使用VARRAY前必须首先初始化。
PL/SQL collection（4）—方法
Oracle所提供的集合方法如下：
Method (function or procedure)
Description
COUNT function
Returns the current number of elements in a collection.
13 else
14 dbms_output.put_line( n_pointer || ' : no data found' );
15 end if;
16 end loop;
17 end;
18 /
1 : yuechaotian
2 : no data found
3 : tianyc
PL/SQL过程已成功完成。
3 t_children type_children := type_children();
4 begin
5 t_parent.extend(2);
6 t_parent(1) := 'my father';
7 t_parent(2) := 'my mother';
8 t_children.extend(3);
SQL>
既然VARRAY可以做为列的定义类型，当然可以使用变量锚定了。比如上面的1-4行代码可以改为：
declare
t_parent my_family.parent%type := type_parent();
t_children my_family.children%type := type_children();
5 t_names_family type_names := type_names();
6 begin
7 t_names_family.extend(5);
8 t_names_family(1) := 'my father';
9 t_names_family(2) := 'my mother';
10 t_names_family(3) := 'my sister';
FIRST, LAST functions
Returns the smallest (FIRST) and largest (LAST) subscripts in use.
LIMIT function
Returns the maximum number of elements allowed in a VARRAY.