ORACLE集合数据类型知识整理.sql

-- 定义数组的大小200，以及所能存储的最大字节数2000
CREATE OR REPLACE TYPE va_planguage IS VARRAY(200) OF VARCHAR2(2000);

DECLARE
 lang va_planguage;
BEGIN
 lang:=va_planguage('可变数组中的数据ONE','可变数组中的数据TWO','可变数组中的数据THREE');
 FOR i IN 1..lang.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(lang(i));
 END LOOP;
END;

-- 嵌套表Nested table的声明及初始化（一个汉字两个字节）
CREATE OR REPLACE TYPE nt_planguage IS TABLE OF VARCHAR2(19);

DECLARE
 lang nt_planguage;
BEGIN
 lang:=nt_planguage('嵌套表测试数据ONE','嵌套表测试数据TWO','嵌套表测试数据THREE');
 FOR i IN 1..lang.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(lang(i));
 END LOOP;
END;

-- 关联数组Associative array(Index_by table)声明与初始化测试
DECLARE
 TYPE ib_planguage IS TABLE OF VARCHAR2(1000) INDEX BY PLS_INTEGER;
 lang ib_planguage;
 idx PLS_INTEGER;
BEGIN
 lang(1):='关联数组ONE';
 lang(9):='关联数组TWO';
 lang(3):='关联数组THREE';
 idx:=lang.FIRST;
 WHILE(idx IS NOT NULL) LOOP
    DBMS_OUTPUT.PUT_LINE(lang(idx));
    idx:=lang.NEXT(idx);
 END LOOP;
END;
/*

Oracle支持3种类型的集合：
关联数组（Associative array，即Index-by table）
嵌套表（Nested table）
变长数组（VARRAY）
它们的区别之一是: Nested table与VARRY既可以被用于PL/SQL，也可以被直接用于数据库中，
但是Associative array不行，
也就是说，Associative array是不能通过CREATE TYPE语句进行单独创建，
只能在PL/SQL块（或Package）中进行定义并使用（即适用范围是PL/SQL Block级别），
而Nested table与VARRAY则可以使用CREATE TYPE进行创建（即适用范围是Schema级别），
它们还可以直接作为数据库表中列的类型。

Associative array的索引可以是稀疏的（即可以不连续）。
Nested table既可以通过CREATE TYPE声明成全局类型，也可以在PL/SQL块中声明块级别的类型。
与Associative array不同，Nested table变量需要显式初始化
Nested table初始化之后还需要调用EXTEND过程，扩展此集合的“容量”。

1)VARRAY可以在声明时限制集合的长度，
EXTEND的长度不能大于集合声明时的长度，
但是在给集合成员赋值时，以EXTEND为准
(2)其索引总是连续的，而Nested table的索引在初始化赋值时是连续的，
不过随着集合元素被删除，可能变得不连续。

通常来说，对集合类型的第一选择应该是Associative array，
因为它不需要初始化或者EXTEND操作，并且是迄今为止最高效的集合类型。
唯一不足的一点是它只能用于PL/SQL而不能直接用于数据库。

如果需要允许使用负数索引，应该选择Associative array；

如果需要使用10g，11g中的那些集合操作，应该选择Nested table；

如果需要限制集合元素的个数，应该选择VARRAY

*/