《Oracle PL/SQL开发指南》学习笔记29——源码调试——Oracle集合API（第七部分）

在调试代码的过程中，才发现很多代码没法直接执行成功，需要做些修改方可成功执行。

不知道是作者有意为之，还是疏忽造成的呢？我宁愿相信是前者。通过不断的代码调试，可以提高我们解决实际问题的能力！

1. Oracle Database 12C的集合API总结

Method	方法	Definition
COUNT	元素个数	Returns the number of elements that have been allocated space in varray and table  data types, and returns the total of all elements in associative arrays. The return value  of the COUNT method can be smaller than the return value of LIMIT for the varray  collection.  It has the following prototype:  PLS_INTEGER COUNT
DELETE	删除元素	lets you remove elements from table collections and associative arrays. It doesn’t  work with varray collections, and an attempt to remove an element from a varray  raises a PLS-00306 exception.  The DELETE method takes two formal parameters; one is mandatory and the other is  optional. Both parameters are index values and must occur in ascending order. The  DELETE procedure deletes everything from the parameter n to m, inclusively, when  you supply two parameters.  The prototypes are as follows:  void DELETE(n)  void DELETE(n,m)
EXISTS	指定元素是否存在	Checks to find an element with the supplied index in a collection. It returns true when  the element is found and null when an initialized varray or table is empty. It has one  mandatory parameter, which is a valid index value.  It has the following prototype:  Boolean EXISTS(n)
EXTEND	分配空间	Allocates space for one or more new elements in a varray or table collection. It has two  optional parameters. It adds space for one element by default without a parameter.  The first parameter designates how many physical spaces should be allocated in  memory, the only constraint being the limit (or maximum value) of the varray.  The second parameter is an index value. When the function receives two parameters,  the first determines how many elements to add and the second is an index value. The  EXTEND procedure uses the index value to copy a value into the newly added space.  It has the following prototypes:  void EXTEND  void EXTEND(n)  void EXTEND(n,i)
FIRST	最小下标	Returns the lowest subscript value in a collection. It can return a  PLS_INTEGER, VARCHAR2, or LONG type.  It has the following prototype:  mixed FIRST
LAST	最大下标	Returns the highest subscript value in a collection. It can return a  PLS_INTEGER, VARCHAR2, or LONG type.  It has the following prototype:  mixed LAST
LIMIT	变长数组最大长度（最大元素个数）	Returns the highest possible subscript value in a collection. It can only return a  PLS_INTEGER type, and can only be used by a VARRAY data type.  It has the following prototype:  mixed LIMIT
NEXT(n)	下一下标	Returns the next higher subscript value in a collection when successful; otherwise  returns false. The return value is a PLS_INTEGER, VARCHAR2, or LONG type. It requires  a valid index value as an actual parameter and raises an exception when the index is  invalid.  It has the following prototype:  mixed NEXT(n)
PRIOR(n)	上一下标	Returns the next lower subscript value in a collection when successful; otherwise  returns false. The return value is a PLS_INTEGER, VARCHAR2, or LONG type. It requires  a valid index value as an actual parameter and raises an exception when the index is  invalid.  It has the following prototype:  mixed PRIOR(n)
TRIM	移除下标（从集合末尾删除元素）	Removes a subscripted value from a collection. It has one optional parameter. Without  an actual parameter, it removes the highest element from the array. An actual parameter  is interpreted as the number of elements removed from the end of the collection.  It has the following prototype:  void TRIM  void TRIM(n)

2.  EXISTS方法

1）EXISTS方法的局限性

SQL> ed
已写入 file afiedt.buf

  1  DECLARE
  2    /* Define the table collection */
  3    TYPE empty_table IS TABLE OF prominent_object;
  4    /* Declare a table collection variable */
  5    lv_array EMPTY_TABLE := empty_table(null);
  6  BEGIN
  7    /* Check whether the element is allocate in memory. */
  8    IF lv_array.EXISTS(1) THEN
  9      dbms_output.put_line('Valid collection');
 10    ELSE
 11     dbms_output.put_line('Invalid collection');
 12    END IF;
 13* END;
SQL> /
Valid collection

PL/SQL 过程已成功完成。

2 )EXISTS最佳使用方法

SQL> ED
已写入 file afiedt.buf

  1  DECLARE
  2    /* Define table. */
  3    TYPE x_table IS TABLE OF VARCHAR2(10);
  4    /* Declare an index counter. */
  5    lv_index  NUMBER := 1;
  6    /* Declare a local collection variable. */
  7    lv_table  X_TABLE := x_table();
  8  BEGIN
  9    IF lv_table.EXISTS(lv_index) AND NOT lv_table.COUNT = 0 AND lv_table(lv_index) IS NOT NULL THEN
 10      dbms_output.put_line(lv_table(lv_index));
 11    ELSIF lv_table.EXISTS(lv_index) AND NOT lv_table.COUNT = 0 AND lv_table IS NOT EMPTY THEN
 12      dbms_output.put_line('lv_table('||lv_index||') is NOT EMPTY?');
 13    ELSE
 14      dbms_output.put_line('lv_table('||lv_index||') is EMPTY? NULL? NOT EMPTY? NOT NULL?');
 15    END IF;
 16    --dbms_output.put_line(lv_table IS EMPTY);
 17    dbms_output.put_line('lv_table COUNT? '|| lv_table.COUNT);
 18* END;
SQL> /
lv_table(1) is EMPTY? NULL? NOT EMPTY? NOT NULL?
lv_table COUNT? 0

PL/SQL 过程已成功完成。

 

SQL> ED
已写入 file afiedt.buf

  1  DECLARE
  2    /* Define table. */
  3    TYPE x_table IS TABLE OF VARCHAR2(10);
  4    /* Declare an index counter. */
  5    lv_index  NUMBER := 1;
  6    /* Declare a local collection variable. */
  7    lv_table  X_TABLE := x_table('Something');
  8  BEGIN
  9    IF lv_table.EXISTS(lv_index) AND lv_table.COUNT > 0 THEN
 10      dbms_output.put_line(lv_table(lv_index));
 11    END IF;
 12* END;
 13  /
Something

PL/SQL 过程已成功完成。

3. COUNT方法

SQL> ed
已写入 file afiedt.buf

  1  DECLARE
  2    /* Define a table collection. */
  3    TYPE NUMBER_table IS TABLE OF INTEGER;
  4    /* Declare an initialized table collection. */
  5    lv_table  NUMBER_TABLE := number_table(1,2,3,4,5);
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE('How many? ['||lv_table.COUNT||']');
  8* END;
  9  /
How many? [5]

PL/SQL 过程已成功完成。

4. DELETE方法

SQL> ed
已写入 file afiedt.buf

  1  DECLARE
  2    /* Declare variable with meaningful name. */
  3    current INTEGER;
  4    /* Define a table collection. */
  5    TYPE x_table IS TABLE OF VARCHAR2(6);
  6    /* Declare an initialized table collection. */
  7    lv_table  X_TABLE := x_table('One','Two','Three','Four','Five');
  8  BEGIN
  9    /* Remove one element with an index of 2. */
 10    lv_table.DELETE(2,2);
 11    /* Remove elements for an inclusive range of 4 to 5. */
 12    lv_table.DELETE(4,5);
 13    /* Set the starting index. */
 14    current := lv_table.FIRST;
 15    /* Read through index values in ascending order. */
 16    WHILE (current <= lv_table.LAST) LOOP
 17      dbms_output.put_line(
 18        'Index ['||current||'] Value ['||lv_table(current)||']');
 19      /* Shift index to next higher value. */
 20      current := lv_table.NEXT(current);
 21    END LOOP;
 22* END;
 23  /
Index [1] Value [One]
Index [3] Value [Three]

PL/SQL 过程已成功完成。

5. EXTEND方法

SQL> DECLARE
  2    /* Declare variable with meaningful name. */
  3    current INTEGER;
  4
  5    /* Define a table collection. */
  6    TYPE x_table IS TABLE OF VARCHAR2(6);
  7
  8    /* Declare an initialized table collection. */
  9    lv_table  X_TABLE := x_table('One');
 10  BEGIN
 11    /* Extend space, and assign a value. */
 12    lv_table.EXTEND;
 13
 14    /* Assign a value to the last allocated element. */
 15    lv_table(lv_table.COUNT) := 'Two';
 16
 17    /* Set the starting index. */
 18    current := lv_table.FIRST;
 19
 20    /* Read through index values in ascending order. */
 21    WHILE (current <= lv_table.LAST) LOOP
 22      dbms_output.put_line(
 23        'Index ['||current||'] Value ['||lv_table(current)||']');
 24      /* Shift index to next higher value. */
 25      current := lv_table.NEXT(current);
 26    END LOOP;
 27  END;
 28  /
Index [1] Value [One]
Index [2] Value [Two]

PL/SQL 过程已成功完成。