《Oracle PL/SQL开发指南》学习笔记29——源码调试——集合（第二部分——标量表集合（Table Collection））

表集合（Table Collection）

表集合时一维结构，不同于变长数组，它没有存储元素个数的上限——除了数据库资源的限制（SGA、PGA）。

创建SQL表集合的原型如下：

TYPE type_name IS TABLE OF data_type [NOT NULL];

1. 标量表集合（Scalar Table Collections）

SQL> CREATE OR REPLACE
  2    TYPE sql_table IS TABLE OF VARCHAR2(20);
  3  /

类型已创建。

SQL> SELECT   column_value AS "Duedain"
  2  FROM     TABLE(sql_table('Aragorn','Faramir','Boromir'))
  3  ORDER BY 1;

Duedain
--------------------
Aragorn
Boromir
Faramir

SQL> ed
已写入 file afiedt.buf

  1  SELECT   column_value AS "Duedain"
  2  FROM     TABLE(sql_table('Aragorn','Faramir','Boromir','PD_test'))
  3* ORDER BY 1
SQL> /

Duedain
--------------------
Aragorn
Boromir
Faramir
PD_test

2. 示例：

SQL> CREATE OR REPLACE FUNCTION add_element
  2  ( pv_table    SQL_TABLE
  3  , pv_element  VARCHAR2 ) RETURN SQL_TABLE IS
  4
  5    /* Declare a local table collection. */
  6    lv_table    SQL_TABLE := sql_table();
  7  BEGIN
  8    /* Check for an initialized collection parameter. */
  9    IF pv_table.EXISTS(1) THEN
 10      lv_table := pv_table;
 11    END IF;
 12
 13    /* Check for a not null element before adding it. */
 14    IF pv_element IS NOT NULL THEN
 15      /* Extend space and add an element. */
 16      lv_table.EXTEND;
 17      lv_table(lv_table.COUNT) := pv_element;
 18    END IF;
 19
 20    /* Return the table collection with its new member. */
 21    RETURN lv_table;
 22  END;
 23  /

函数已创建。

3. 索引缺失的情况

SQL> DECLARE
  2    /* Declare a meaning-ladden variable name and exclude the
  3       lv_ preface from the variable name. */
  4    current  INTEGER := 1;
  5
  6    /* Declare a local table collection. */
  7    lv_table    SQL_TABLE :=
  8                  sql_table('Aragorn','Faramir','Boromir');
  9  BEGIN
 10    /* Remove the lead element of a table collection. */
 11    lv_table.DELETE(1);
 12
 13    /* Set the starting point. */
 14    current := lv_table.FIRST;
 15
 16    /* Check pseudo index value less than last index value. */
 17    WHILE (current <= lv_table.LAST) LOOP
 18      /* Print current value. */
 19      dbms_output.put_line(
 20        'Values ['||current||']['||lv_table(current)||']');
 21
 22      /* Shift the index to the next value. */
 23      current := lv_table.NEXT(current);
 24    END LOOP;
 25  END;


Values [2][Faramir]
Values [3][Boromir]

PL/SQL 过程已成功完成。

SQL> DECLARE
  2    /* Declare a local counter variable. */
  3    lv_counter  INTEGER := 0;
  4
  5    /* Declare a local table collection. */
  6    lv_table    SQL_TABLE :=
  7                  sql_table('Aragorn','Faramir','Boromir');
  8  BEGIN
  9    /* Remove the lead element of a table collection. */
 10    lv_table.DELETE(1);
 11
 12    /* Check pseudo index value less than last index value. */
 13    WHILE (lv_counter <= lv_table.LAST) LOOP
 14      /* Increment the index counter. */
 15      lv_counter := lv_counter + 1;
 16
 17      /* Check whether the index returns a value. */
 18      IF lv_table.EXISTS(lv_counter) THEN
 19        dbms_output.put_line(
 20          'Values ['||lv_counter||']['||lv_table(lv_counter)||']');
 21      END IF;
 22    END LOOP;
 23  END;
 24  /
Values [2][Faramir]
Values [3][Boromir]

PL/SQL 过程已成功完成。

4. 局部表集合

SQL> DECLARE
  2    /* Define a local table collection. */
  3    TYPE plsql_table IS TABLE OF VARCHAR2(20);
  4
  5    /* Declare a local table collection. */
  6    lv_table    PLSQL_TABLE :=
  7                  plsql_table('Aragorn','Faramir','Boromir');
  8  BEGIN
  9    /* Loop through the collection and print the results. */
 10    FOR i IN lv_table.FIRST..lv_table.LAST LOOP
 11      dbms_output.put_line(lv_table(i));
 12    END LOOP;
 13  END;
 14  /
Aragorn
Faramir
Boromir

PL/SQL 过程已成功完成。