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

最新推荐文章于 2022-05-11 10:36:03 发布

原创最新推荐文章于 2022-05-11 10:36:03 发布 · 232 阅读

0 ·

CC 4.0 BY-SA版权

数据库（DB）专栏收录该内容

368 篇文章

订阅专栏

本文深入探讨PL/SQL中的集合操作，包括EXTEND、FIRST、LAST、LIMIT、NEXT、PRIOR和TRIM方法的使用，通过具体示例展示了如何在表集合和关联数组中进行元素的增加、查找和删除，是理解PL/SQL集合管理核心概念的实用指南。

1. EXTEND方法

这个方法比较陌生，先学习一下：

The EXTEND method is a procedure. Like DELETE, it’s also an overloaded procedure. (Chapter 9 covers overloading, if that’s a new concept to you.) There are three overloaded versions of the EXTEND procedure:

One takes no parameters. It allocates space for one element.
One takes one parameter. It allocates space for the number of elements designated by the parameter.
One takes two parameters. Like the one-parameter version, it allocates space for the number of elements designated by the first parameter. The second parameter must be a valid index value, and it’s used to copy the value from the referenced index into the newly allocated space.

The following program shows how to allocate new space with the EXTEND procedure (leveraging the anonymous block from the DELETE procedure example):

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 过程已成功完成。

2. FIRST方法（这个示例很有意思，足以作为PL/SQL的笔试题，可以考察候选人对于字符串索引的关联数组基本概念的掌握程度。反正我是踩坑了！）

SQL> ED
已写入 file afiedt.buf

  1  DECLARE
  2    /* Define an associative array. */
  3    TYPE LV_table IS TABLE OF INTEGER
  4      INDEX BY VARCHAR2(9 CHAR);
  5    /* Declare an associative array variable. */
  6    lv_list  LV_TABLE;
  7  BEGIN
  8    /* Add elements to associative array. */
  9    lv_list('Seven') := 7;
 10    lv_list('Eight') := 8;
 11    /* Print the element returned by the lowest string index. */
 12    dbms_output.put_line(
 13      'Index ['||lv_list.FIRST||']['||lv_list(lv_list.FIRST)||']');
 14* END;
SQL> /
Index [Eight][8]

PL/SQL 过程已成功完成。

3. LAST函数（与FIRST函数相反）

SQL> ed
已写入 file afiedt.buf

  1  DECLARE
  2    /* Define an associative array. */
  3    TYPE x_table IS TABLE OF INTEGER
  4      INDEX BY VARCHAR2(9 CHAR);
  5    /* Declare an associative array variable. */
  6    lv_list  X_TABLE;
  7  BEGIN
  8    /* Add elements to associative array. */
  9    lv_list('Seven') := 7;
 10    lv_list('Eight') := 8;
 11    /* Print the element returned by the lowest string index. */
 12    dbms_output.put_line(
 13      'Last element: Index ['||lv_list.LAST||'], Value ['||lv_list(lv_list.LAST)||']');
 14* END;
SQL> /
Last element: Index [Seven], Value [7]

4. LIMIT方法（仅适用于变长数组varray）

SQL> ed
已写入 file afiedt.buf

  1  DECLARE
  2    /* Define an associative array. */
  3    TYPE x_varray IS VARRAY(5) OF INTEGER;
  4    /* Declare an initialized table collection. */
  5    lv_array  X_VARRAY := x_varray(1,2,3);
  6  BEGIN
  7    /* Print the count and limit values. */
  8    dbms_output.put_line(
  9      'Count['||lv_array.COUNT||'], Limit['||lv_array.LIMIT||']');
 10* END;
 11  /
Count[3], Limit[5]

PL/SQL 过程已成功完成。

5. NEXT方法（Oracle集合像一个单链表而非环，所以当当前索引是集合的最后一个索引时，NEXT方法返回null）

遍历稀疏填充索引的集合时，使用NEXT函数是必要的。在由低位到高位遍历表集合和关联数组元素时，应该使用NEXT函数。

Navigating across a sparsely populated index is the essential use case for the NEXT function. You should navigate from the lowest to the highest index value in table collections and associative arrays with the NEXT function.

题外话：单看这句的翻译，该中文翻译的质量一般！可能是学生翻译的！

什么？应该怎么翻译？看看我的翻译：

遍历具有稀疏填充索引的集合时，使用NEXT函数是必要的。在使用NEXT函数时，应由低位到高位遍历表集合和关联数组元素。

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','Two','Three','Four','Five');
 10  BEGIN
 11    /* Set the starting index. */
 12    current := lv_table.FIRST;
 13
 14    /* Read through index values in ascending order. */
 15    WHILE (current <= lv_table.LAST) LOOP
 16      dbms_output.put_line(
 17        'Index ['||current||'] Value ['||lv_table(current)||']');
 18      /* Shift index to next higher value. */
 19      current := lv_table.NEXT(current);
 20    END LOOP;
 21  END;
 22  /
Index [1] Value [One]
Index [2] Value [Two]
Index [3] Value [Three]
Index [4] Value [Four]
Index [5] Value [Five]

PL/SQL 过程已成功完成。

6. PRIOR方法

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','Two','Three','Four','Five');
 10  BEGIN
 11    /* Set the starting index. */
 12    current := lv_table.LAST;
 13
 14    /* Read through index values in ascending order. */
 15    WHILE (current >= lv_table.FIRST) LOOP
 16      dbms_output.put_line(
 17        'Index ['||current||'] Value ['||lv_table(current)||']');
 18      /* Shift index to next higher value. */
 19      current := lv_table.PRIOR(current);
 20    END LOOP;
 21  END;
 22  /
Index [5] Value [Five]
Index [4] Value [Four]
Index [3] Value [Three]
Index [2] Value [Two]
Index [1] Value [One]

PL/SQL 过程已成功完成。

7. TRIM方法

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','Two','Three','Four','Five');
 10  BEGIN
 11    /* Remove one element with an index of 2. */
 12    lv_table.TRIM(3);
 13
 14    /* Set the starting index. */
 15    current := lv_table.FIRST;
 16
 17    /* Read through index values in ascending order. */
 18    WHILE (current <= lv_table.LAST) LOOP
 19      dbms_output.put_line(
 20        'Index ['||current||'] Value ['||lv_table(current)||']');
 21
 22        /* Shift index to next higher value. */
 23      current := lv_table.NEXT(current);
 24    END LOOP;
 25  END;
 26  /
Index [1] Value [One]
Index [2] Value [Two]

PL/SQL 过程已成功完成。