PL/SQL collection（4）— 方法

Oracle 所提供的集合方法如下：

Method (function or procedure)	Description
COUNT function	Returns the current number of elements in a collection.
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.
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.
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.
PRIOR, NEXT functions	Returns the subscript immediately before (PRIOR) or after (NEXT) a specified subscript. You should always use PRIOR and NEXT to traverse a collection, especially if you are working with sparse (or potentially sparse) collections.
TRIM procedure	Removes collection elements from the end of the collection (highest defined subscript). Reduces COUNT if elements are not DELETEd.

在这里，Oracle 把这些函数和过程称之为“方法（method）”，因为它们的使用方式不同与我们调用函数/过程的方法。比如使用 LAST 函数，作为函数调用，我们会使用这样的方式：

 

IF LAST (company_table) > 10 THEN ... /* 错误的语法 */

 

这是错误的，因为 LAST 已经成为 company_table 的一个方法了。就像我们用的 C++ 的语法一样：

 

IF company_table.LAST > 10 THEN ... /* 正确的语法*/

 

 

1. COUNT

 

定义：

 

FUNCTION COUNT RETURN PLS_INTEGER;

 

功能：

 

返回联合数组、嵌套表，或 VARRAY 数组的元素个数，不包括被删除的元素。

 

举例：

 

SQL> set serveroutput on

SQL> declare

  2    t_colors type_colors := type_colors('red');

  3  begin

  4    if t_colors.count > 0 then

  5      dbms_output.put_line( t_colors(1) );

  6    end if;

  7  end;

  8  /

red

 

PL/SQL 过程已成功完成。

 

SQL>

 

注意：

 

一个刚刚初始化的集合，COUNT 返回值为0；对于空的联合数组，返回值也是0：

 

SQL> declare

  2    t_colors type_colors := type_colors( );

  3  begin

  4    dbms_output.put_line( 'COUNT: ' || t_colors.count );

  5  end;

SQL> /

COUNT: 0

 

PL/SQL 过程已成功完成。

 

SQL>

 

若对未初始化的嵌套表或 VARRAY 数组使用 COUNT 方法，将会抛出 COLLECTION_IS_NULL 异常：

 

SQL> declare

  2    t_colors type_colors ;

  3  begin

  4    dbms_output.put_line( 'COUNT: ' || t_colors.count );

  5* end;

SQL> /

declare

*

ERROR 位于第 1 行:

ORA-06531: 引用未初始化的收集

ORA-06512: 在line 4

 

 

SQL> declare

  2    t_colors type_colors ;

  3  begin

  4    dbms_output.put_line( 'COUNT: ' || t_colors.count );

  5  exception

  6    when collection_is_null then

  7      dbms_output.put_line( 'The Collection is null.' );

  8  end;

SQL> /

The Collection is null.

 

PL/SQL 过程已成功完成。

 

SQL>

 

 

2. DELETE

 

定义：

 

PROCEDURE DELETE;

PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)]);

PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)],

    j [BINARY_INTEGER | VARCHAR2(size_limit)]);

 

功能：

 

删除联合数组、嵌套表，或 VARRAY 数组的元素。

不带参数的 DELETE 将删除集合的所有元素；带一个参数的 DELETE 将删除 联合数组 或 嵌套表 中的指定下标 i 的元素；带两个参数的 DELETE 将删除 联合数组 或 嵌套表 中的指定范围 i 和 j 之间的元素。

注意对于 VARRAY 数组，只能使用不带参数的 DELETE，这将删除该 VARRAY 数组的所有元素。不能删除 VARRAY 数组中单个的元素，使它变得稀疏。而使用 TRIM 方法可以从 VARRAY 数组的末尾开始删除元素。

从物理存储看，DELETE 一个元素不会立即释放该空间，Oracle 会等到删除元素后剩余的空间达到内存中的一页时，才会释放该页空间。而 DELETE 掉整个集合却会立即释放所占用的所有内存空间。空间的回收是 Oracle 自动管理的，你无法对此进行干预。

 

举例：

 

对于嵌套表的 DELETE，删除某元素后，该元素所在的空间仍可被使用：

 

SQL> declare

  2    type type_names is table of varchar2(20);

  3    t_names type_names := type_names();

  4  begin

  5    t_names.extend(2);

  6    t_names(1) := 'yuechaotian';

  7    t_names(2) := 'yuexingtian';

  8    t_names.delete(2);

  9    dbms_output.put_line( 'After delete, the COUNT is: ' || t_names.count );

 10    t_names(2) := 'yuexingtian';

 11    dbms_output.put_line( 'After re-insert, the COUNT is: ' || t_names.count );

 12  end;

SQL> /

After delete, the COUNT is: 1

After re-insert, the COUNT is: 2

 

PL/SQL 过程已成功完成。

 

SQL>

 

注意：

 

当使用 DELETE 方法时指定了错误的下标，则它会尽量去做正确的事情。比如 t_names.DELETE(-5)，下标 -5 中没有元素，则不会抛出异常；删除指定下标范围 t_names.DELETE(-3, 1) 的元素，而嵌套表 t_names 下标范围为 1-2，则只会删除第 1 个下标的元素，也不会抛出异常：

 

SQL> declare

  2    type type_names is table of varchar2(20);

  3    t_names type_names := type_names();

  4  begin

  5    t_names.extend(2);

  6    t_names(1) := 'yuechaotian';

  7    t_names(2) := 'yuexingtian';

  8    t_names.delete(-5);

  9    dbms_output.put_line( 'After delete, the COUNT is: ' || t_names.count );

 10    t_names.delete(-3, 1);

 11    dbms_output.put_line( 'After re-insert, the COUNT is: ' || t_names.count );

 12  end;

SQL> /

After delete, the COUNT is: 2

After re-insert, the COUNT is: 1

 

PL/SQL 过程已成功完成。

 

SQL>

 

试图删除未初始化的嵌套表或 VARRAY 数组，将抛出 COLLECTION_IS_NULL 异常。

 

 

3. EXISTS

 

定义： 

 

FUNCTION EXISTS (i IN [BINARY_INTEGER | VARCHAR2(size_limit)]) RETURN BOOLEAN;

 

功能：

 

判断联合数组、嵌套表或 VARRAY 数组的指定的行是否存在。若存在，则返回 TRUE，否则返回 FALSE。若使用 DELETE 方法删除了某行，则 EXISTS 返回 FALSE。

 

举例：

 

SQL> declare

  2    type type_names is table of varchar2(20);

  3    t_names type_names := type_names();

  4  begin

  5    t_names.extend(2);

  6    t_names(1) := 'yuechaotian';

  7    if t_names.exists(2) then

  8      dbms_output.put_line( 'There is an element in row 2.' );

  9    else

 10      dbms_output.put_line( 'There is no element in row 2.' );

 11    end if;

 12    t_names.delete(2);

 13    if t_names.exists(2) then

 14      dbms_output.put_line( 'There is a element in row 2.' );

 15    else

 16      dbms_output.put_line( 'There is no element in row 2.' );

 17    end if;

 18  end;

 19  /

There is an element in row 2.

There is no element in row 2.

 

PL/SQL 过程已成功完成。

 

SQL>

 

注意：

 

在未初始化（或已初始化但未赋值）的嵌套表或 VARRAY 数组中使用 EXISTS 方法，将返回 FALSE，而不会抛出异常。这跟 COUNT 方法是不同的。所以，在使用 COUNT 方法前，建议你先用 EXISTS 来判断一下，这样可以避免抛出异常。

 

SQL> declare

  2    type type_names is table of varchar2(20);

  3    t_names type_names := type_names();

  4  begin

  5    if t_names.exists(1) then

  6      dbms_output.put_line('COUNT: ' || t_names.count);

  7    else

  8      dbms_output.put_line( 'The nested table has no element or has not been initialized.' );

  9    end if;

 10  end;

SQL> /

The nested table has no element or has not been initialized.

 

PL/SQL 过程已成功完成。

 

SQL>

 

 

4. EXTEND

 

定义：

 

PROCEDURE EXTEND (n PLS_INTEGER:=1);

PROCEDURE EXTEND (n PLS_INTEGER, i PLS_INTEGER);

 

功能：

 

在嵌套表或 VARRAY 数组需要手工分配存储空间的情况下，使用 EXTEND 方法分配空间。它将在内存中分配一个空间，来存储数据。不要在联合数组中使用 EXTEND。

不带参数的 EXTEND 扩展一个空间；带一个参数 n 的 EXTEND 扩展 n 个空间；带两个参数 ( n, i ) 的 EXTEND 扩展 n 个空间，并且空间中的每个元素值设置为与 下标为 i 的元素值相同。

 

举例：

 

SQL> declare

  2    type type_names is table of varchar2(20) not null;

  3    t_names type_names := type_names();

  4  begin

  5    t_names.extend;

  6    t_names(1) := 'yuechaotian';

  7    t_names.extend(4, 1);

  8    for n_pointer in t_names.first..t_names.last loop

  9      dbms_output.put_line( 'element ' || n_pointer || ' : ' || t_names(n_pointer) );

 10    end loop;

 11  end;

SQL> /

element 1 : yuechaotian

element 2 : yuechaotian

element 3 : yuechaotian

element 4 : yuechaotian

element 5 : yuechaotian

 

PL/SQL 过程已成功完成。

 

SQL>

 

注意：

 

若你使用 DELETE 或 TRIM 删除了嵌套表或 VARRAY 数组的最后一个元素，则再使用 EXTEND 扩展空间时，将会“跳过”该下标。比如原来有 1-5 共5个元素，删除第5个，再扩展2个，则新扩展的空间下标为 6、7，而不是 5、6：

 

SQL> declare

  2    type type_names is table of varchar2(20) not null;

  3    t_names type_names := type_names();

  4  begin

  5    t_names.extend;

  6    t_names(1) := 'yuechaotian';

  7    t_names.extend(4, 1);

  8    t_names.delete(5);

  9    t_names.extend(2, 1);

 10    for n_pointer in t_names.first..t_names.last loop

 11      if t_names.exists(n_pointer) then

 12        dbms_output.put_line( 'element ' || n_pointer || ' : ' || t_names(n_pointer) );

 13      else

 14        dbms_output.put_line( 'element ' || n_pointer || ' is not exists. ' );

 15      end if;

 16    end loop;

 17* end;

SQL> /

element 1 : yuechaotian

element 2 : yuechaotian

element 3 : yuechaotian

element 4 : yuechaotian

element 5 is not exists.

element 6 : yuechaotian

element 7 : yuechaotian

 

PL/SQL 过程已成功完成。

 

SQL>

 

5. FIRST and  LAST

 

定义：

 

FUNCTION FIRST RETURN PLS_INTEGER;

FUNCTION LAST RETURN PLS_INTEGER;

 

功能：

 

返回联合数组、嵌套表或 VARRAY 数组的最小/最大下标。

 

举例：

 

在联合数组里，下面这种用法应该是最常见的，将游标中的记录按顺序赋值给一个联合数组：

 

1. 创建环境

 

SQL> create table test1 ( a varchar2(20) );

 

表已创建。

 

SQL> insert into test1 select 'yuechaotian' from dba_objects where rownum <6;

 

已创建5行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from test1;

 

A

--------------------

yuechaotian

yuechaotian

yuechaotian

yuechaotian

yuechaotian

 

SQL>

 

2. 使用 LAST 方法获得联合数组中的值：

 

SQL> declare

  2    type type_names is table of test1.a%type index by pls_integer;

  3    t_names type_names;

  4  begin

  5    for rec_names in( select * from test1 ) loop

  6      t_names( nvl(t_names.last, 0) + 1 ) := rec_names.a;

  7    end loop;

  8    for n_pointer in t_names.first..t_names.last loop

  9      dbms_output.put_line( n_pointer || ' : ' || t_names(n_pointer) );

 10    end loop;

 11  end;

 12  /

1 : yuechaotian

2 : yuechaotian

3 : yuechaotian

4 : yuechaotian

5 : yuechaotian

 

PL/SQL 过程已成功完成。

 

SQL>

 

3. 该功能同样可以用 COUNT 代替：

 

SQL> declare

  2    type type_names is table of test1.a%type index by pls_integer;

  3    t_names type_names;

  4  begin

  5    for rec_names in( select * from test1 ) loop

  6      t_names( t_names.count + 1 ) := rec_names.a;

  7    end loop;

  8    for n_pointer in t_names.first..t_names.last loop

  9      dbms_output.put_line( n_pointer || ' : ' || t_names(n_pointer) );

 10    end loop;

 11  end;

 12  /

1 : yuechaotian

2 : yuechaotian

3 : yuechaotian

4 : yuechaotian

5 : yuechaotian

 

PL/SQL 过程已成功完成。

 

SQL>

 

 

注意：

 

若该集合已经初始化但没有元素，则 FIRST 和 LAST 都将返回 NULL。VARRAY 数组中至少有一个元素，所以 FIRST 方法得到的值总是1，LAST 方法得到的值总是与 COUNT 方法得到的值相同。

 

 

6. LIMIT

 

定义：

 

FUNCTION LIMIT RETURN PLS_INTEGER;

 

功能：

 

返回 VARRAY 数组中定义的最大长度限制。对联合数组或嵌套表使用 LIMIT 方法将返回 NULL。

 

举例：

 

可以使用 LIMIT 方法判断是否可以扩展 VARRAY 数组：

 

IF my_list.LAST < my_list.LIMIT THEN

  my_list.EXTEND;

END IF;

 

 

7. PRIOR and NEXT

 

定义：

 

FUNCTION PRIOR (i [BINARY_INTEGER | VARCHAR2(size_limit)])

    RETURN [BINARY_INTEGER | VARCHAR2(size_limit)];

 

FUNCTION NEXT (i [BINARY_INTEGER | VARCHAR2(size_limit)])

    RETURN [BINARY_INTEGER | VARCHAR2(size_limit)];

 

功能：

 

帮助我们在联合数组、嵌套表或 VARRAY 数组中“穿梭”。PRIOR 返回比 i 小的最大下标；NEXT 返回比 i 大的最小下标。

 

举例：

 

比如获取稀疏的联合数组中的元素总和：

 

SQL> select * from test1;

A                             B
-------------------- ----------
yuechaotian                  99
yuechaotian                1010
yuechaotian                1111
yuechaotian                1212
yuechaotian                1313

 

SQL> declare

  2    type type_score is table of test1.b%type index by pls_integer;

  3    t_score type_score;

  4    n_total number(10) := 0;

  5    n_pointer pls_integer;

  6  begin

  7    for rec_test1 in ( select * from test1 ) loop

  8      t_score(rec_test1.b) := rec_test1.b;

  9    end loop;

 10    n_pointer := t_score.first;

 11    while n_pointer is not null loop

 12      n_total := n_total + t_score(n_pointer);

 13      n_pointer := t_score.next(n_pointer);

 14    end loop;

 15    dbms_output.put_line( 'Total: ' || n_total );

 16  end;

SQL> /

Total: 4745

 

PL/SQL 过程已成功完成。

 

SQL>

 

也可以使用 PRIOR 方法实现该功能：

 

10    n_pointer := t_score.last;

 11    while n_pointer is not null loop

 12      n_total := n_total + t_score(n_pointer);

 13      n_pointer := t_score.prior(n_pointer);

 14    end loop;

 

注意：

通过上面的例子可以看到，当在集合的最后一个元素上使用 NEXT 方法时，将返回 NULL；在集合的第一个元素上使用 PRIOR 方法也将返回NULL。

当 i 大于集合的 LAST 时，PRIOR( i ) 的返回值与 LAST 相同；当 i 小于集合的 FIRST 时，NEXT( i ) 的返回值与 FIRST 相同：

 

SQL> declare

  2    type type_names is table of varchar2(20) index by pls_integer;

  3    t_names type_names;

  4  begin

  5    t_names(-53) := 'yuechaotian';

  6    t_names(0) := 'yuexingtian';

  7    t_names(43) := 'tianyc';

  8    dbms_output.put_line( 'PRIOR 100: ' || t_names.prior(100) );

  9    dbms_output.put_line( 'NEXT -100: ' || t_names.next(-100) );

 10* end;

SQL> /

PRIOR 100: 43

NEXT -100: -53

 

PL/SQL 过程已成功完成。

 

SQL>

 

 

8. TRIM

 

定义：

 

PROCEDURE TRIM (n PLS_INTEGER:=1);

 

功能：

 

从嵌套表或 VARRAY 数组的末尾删除 1 个或 n 个元素。在联合数组中使用 TRIM 将导致编译错误。

 

举例：

 

在介绍 DELETE 方法时，我们说对 VARRAY 做 DELETE 操作时，不能带有参数，这样将删除该 VARRAY 数组中的所有元素。如果想删除 VARRAY 数组中的单个元素，只能先 TRIM，再 EXTEND：

 

SQL> declare
  2    type type_names is varray(3) of varchar2(20);
  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';
  9    t_names_mine.delete(2);
 10    for n_pointer in t_names_mine.first..t_names_mine.last loop
 11      if t_names_mine.exists( n_pointer ) then
 12        dbms_output.put_line( n_pointer || ' : ' || t_names_mine(n_pointer) );
 13      else
 14        dbms_output.put_line( n_pointer || ' : no data found' );
 15      end if;
 16    end loop;
 17  end;
 18  /
  t_names_mine.delete(2);
  *
ERROR 位于第 9 行:
ORA-06550: 第 9 行, 第 3 列:
PLS-00306: 调用 'DELETE' 时参数个数或类型错误
ORA-06550: 第 9 行, 第 3 列:
PL/SQL: Statement ignored

 

SQL> declare
  2    type type_names is varray(3) of varchar2(20);
  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';
  9    t_names_mine.trim(2);
 10    t_names_mine.extend;
 11    t_names_mine(2) :=  'tianyc';
 12    for n_pointer in t_names_mine.first..t_names_mine.last loop
 13      if t_names_mine.exists( n_pointer ) then
 14        dbms_output.put_line( n_pointer || ' : ' || t_names_mine(n_pointer) );
 15      else
 16        dbms_output.put_line( n_pointer || ' : no data found' );
 17      end if;
 18    end loop;
 19  end;
 20  /
1 : yuechaotian
2 : tianyc

 

PL/SQL 过程已成功完成。

 

SQL>

 

注意：

 

当在嵌套表上同时使用 DELETE 和 TRIM 时，很容易出错。比如长度为 3 的 VARRAY 数组，DELETE 第 2 个元素，再 TRIM 末尾 2 个 元素，是什么结果呢：

 

SQL> declare

  2    type type_names is table of varchar2(20);

  3    t_names type_names := type_names();

  4  begin

  5    t_names.extend(3);

  6    t_names(1) := 'yuechaotian';

  7    t_names(2) := 'yuexingtian';

  8    t_names(3) := 'tianyc';

  9    t_names.delete(2);

 10    t_names.trim(2);

 11    for n_pointer in 1..3 loop

 12      if t_names.exists( n_pointer ) then

 13        dbms_output.put_line( 'element ' || n_pointer || ': ' || t_names(n_pointer) );

 14      else

 15        dbms_output.put_line( 'element ' || n_pointer || ' is not exists. '  );

 16      end if;

 17    end loop;

 18* end;

SQL> /

element 1: yuechaotian

element 2 is not exists.

element 3 is not exists.

 

PL/SQL 过程已成功完成。

 

SQL>

 

元素 2 被 DELETE 后，只剩下元素 1 和元素 3。再进行 TRIM(2) 时，只删除了第 3 个元素。所以尽量不要同时使用 DELETE 和 TRIM。

 

而 TRIM( n ) 中的 n 大于该集合的元素总数时，将会抛出 SUBSCRIPT_BEYOUND_COUNT 异常。