PL/SQL三种集合类型的比较
集合是指在一个程序变量中包含多个值。PL/SQL提供的集合类型如下：
|
Associative Array: |
TYPE t IS TABLE OF something INDEX BY PLS_INTEGER; |
|
Nested Table: |
TYPE t IS TABLE OF something; |
|
VARRAY: |
TYPE t IS VARRAY (123) OF something; |
三种集合的各自使用范围
前提是在PL/SQL中希望某种类型的多个值，并且希望使用myarray[n]的形式访问。
范围：SQL or PL/SQL
最大的区别是首先需要确定使用范围，SQL和PL/SQL时两种不同的语言。PL/SQL是SQL的过程性语言。Associative Arrays仅存在于PL/SQL中。因此：
·只能在PL/SQL中使用的是：Associative Array；
·可以在SQL中使用：Nested Table, VARRAY；
同时需要注意的是：在SQL中创建一个类型和PL/SQL中声明一个类型是不同的。
SQL中创建的集合类型
集合是SQL的一部分，不需要到PL/SQL中就可以使用它们：
CREATE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(100)
/
SELECT column_value AS val
FROM TABLE(VARCHAR2_TT('Apple','Banana','Apricot'))
WHERE column_value NOT LIKE 'A%';
VAL
--------------------
Banana
在以上的例子中，我们创建了一个集合类型，并在查询中使用，完全在SQL中。这种形式创建的类型可以在任何查询和PL/SQL程序中使用。其他用户使用需要GRANT EXECUTE ON type_name TO other_account授权。
PL/SQL中声明的集合类型
在PL/SQL中，可以使用SQL中创建的集合类型，或者本地声明自己的类型。在PL/SQL中声明可以更加灵活的自定义包中专用的类型，允许声明他们使用%TYPE和%ROWTYPE引用特定的表的列，如TABLE OF emp.empno%TYPE。不过这仅仅是PL/SQL中的特征，不能在SQL中使用，甚至可以定义自己的类型，然后生命那种类型的集合。如下：
DECLARE
TYPE stats_rec IS RECORD
( batch_step batch_log.step_name%TYPE
, elapsed_time INTERVAL DAY(0) TO SECOND(0)
, rows_processed PLS_INTEGER );
TYPE stats_tt IS TABLE OF stats_rec; -- a locally-defined nested table collection type
由于仅在PL/SQL中使用, SQL并不知道：
CREATE OR REPLACE PACKAGE testtypes AS
TYPE varchar2_tt IS TABLE OF VARCHAR2(100);
END testtypes;
/
Package created.
SELECT column_value AS val
FROM TABLE(testtypes.VARCHAR2_TT('Apple','Banana','Apricot'))
WHERE column_value NOT LIKE 'A%';
FROM TABLE(testtypes.VARCHAR2_TT('Apple','Banana','Apricot'))
*
ERROR at line 2:
ORA-22905: cannot access rows from a non-nested table item
可以使用以下查询查看所有类型：
SELECT ct.owner, ct.type_name, ct.elem_type_name, ct.length
FROM all_coll_types ct
, all_types ot
WHERE ct.coll_type = 'TABLE'
AND ot.type_name(+) = ct.elem_type_name
AND ot.owner(+) = ct.elem_type_owner
AND ot.type_name IS NULL
ORDER BY ct.owner, ct.type_name;
Associative Arrays特征
如果在PL/SQL中只需要声明和填充，那么关联数组将是一种方便的方法。不需要独立的CREATE OR REPLACE TYPE，不需要初始化，不需要扩展，可以随意的放入各种值，如下：
DECLARE
TYPE number_index_by_string IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
TYPE string_index_by_number IS TABLE OF dept.loc%TYPE INDEX BY PLS_INTEGER;
v_country_codes NUMBER_INDEX_BY_STRING;
v_countries STRING_INDEX_BY_NUMBER;
BEGIN
v_country_codes('Ukraine') := 380;
v_country_codes('UAE') := 971;
v_country_codes('UK') := 44;
v_country_codes('USA') := 1;
v_countries(380) := 'Ukraine';
v_countries(971) := 'UAE';
v_countries(44) := 'UK';
v_countries(1) := 'USA';
END;
需要注意的是，在number_index_by_string中使用了INDEX BY VARCHAR2(30)，这使我们通过v_country_codes('UK')引用数组的UK元素。这是数组的唯一特征。关联数组没有构造体。也就是不能使用myarray := mytype(2,4,6,37)进行一次性填充四个值。
PL/SQL集合类型相对于SQL集合类型
可以在PL/SQL中声明任何类型。
主要区别如下：
|
范围 |
意义 |
集合类型 |
|
PL/SQL |
只能在PL/SQL中声明，没有"CREATE OR REPLACE TYPE". SQL 中不能使用。 不需要初始化和扩展，只需要分配值给特定的元素，甚至不需要元素顺序。 可以选择"index by" - PLS_INTEGER, BINARY_INTEGER或 VARCHAR2. 没有构造体，必须显示分配值。不能在查询中作为一个表处理，如SELECT * FROM TABLE(myarray) |
Associative Array |
|
SQL ,PL/SQL |
可以在PL/SQL中声明或者使用"CREATE OR REPLACE TYPE"； 使用前必须初始化：myarray mytype := mytype(); 具有构造体，可以使用mytype('x','y','z');分配值； 必须进行扩展以增加元素，myarray.EXTEND; 在查询中可以作为表处理SELECT * FROM TABLE(myarray) (如果使用CREATE TYPE创建). |
Nested Table |
|
VARRAY |
VARRAYs的缺点
可以看到，VARRAYs和Nested Tables的描述基本相同，并且两种类型都可以在SQL和PL/SQL的TABLE()中使用。VARRAYs的一些不具有的特征在nested table中可以使用的包括：
·集合函数，如CARDINALITY，COLLECT，POWERMULTISET，等；
·多重集条件，如IS A SET, MEMBER OF，SUBMULTISET；
·多重集操作符，如MULTISET INTERSECT；
如：
DECLARE
my_array VARCHAR2_TT :=
VARCHAR2_TT('Apple','Apple','Orange','Banana');
BEGIN
IF my_array IS A SET THEN
DBMS_OUTPUT.PUT_LINE('No duplicates found');
ELSE
DBMS_OUTPUT.PUT_LINE('Collection contains duplicates');
END IF;
END;
Collection contains duplicates
再如：
DECLARE
my_array VARCHAR2_TT :=
VARCHAR2_TT('Apple','Apple','Orange','Banana');
BEGIN
IF 'Orange' MEMBER OF my_array THEN
DBMS_OUTPUT.PUT_LINE('"Orange" exists in the collection');
ELSE
DBMS_OUTPUT.PUT_LINE('"Orange" does not exist in the collection');
END IF;
END;
"Orange" exists in the collection
DECLARE
my_array1 VARCHAR2_TT :=
VARCHAR2_TT('Apple','Orange','Cherry','Banana');
my_array2 VARCHAR2_TT :=
VARCHAR2_TT('Orange','Kumquat','Grape','Banana');
my_array3 VARCHAR2_TT := my_array1 MULTISET INTERSECT my_array2;
BEGIN
FOR i IN my_array3.FIRST..my_array3.LAST LOOP
DBMS_OUTPUT.PUT_LINE(my_array3(i));
END LOOP;
END;
Orange
Banana
从上面，我们可以看到使用集合的各种特征，可以节约很多代码。
再看VARRAY，如下：
CREATE TYPE varchar2_vtt AS VARRAY(100) OF VARCHAR2(100)
/
Type created.
DECLARE
my_array VARCHAR2_VTT :=
VARCHAR2_VTT('Apple','Apple','Orange','Banana');
BEGIN
IF my_array IS A SET THEN
DBMS_OUTPUT.PUT_LINE('No duplicates found');
ELSE
DBMS_OUTPUT.PUT_LINE('Collection contains duplicates');
END IF;
END;
IF my_array IS A SET THEN
*
ERROR at line 5:
ORA-06550: line 5, column 8:
PLS-00306: wrong number or types of arguments in call to 'IS A SET'
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored
在10G中的所有集合函数，多集合条件，操作符都不可以在VARRAYs下使用。
相关的集合函数：
CARDINALITY：返回嵌套表中元素的数量，返回NUMBER或NULL；
COLLECT：根据输入的列创建一个嵌套表，然后使用CAST函数得到结果集；
CAST(COLLECT(phone_numbers) AS phone_book_t)；
POWERMULTISET：使用一个嵌套表作为输入，并返回一个不包含空元素的嵌套表：
CAST(POWERMULTISET(cust_address_ntab) AS cust_address_tab_tab_typ)
POWERMULTISET_BY_CARDINALITY
SET：取出嵌套表中的重复值