《Oracle PL/SQL开发指南》学习笔记28——源码调试——PL/SQL基础知识（第五部分）

最新推荐文章于 2018-12-01 20:47:05 发布

原创最新推荐文章于 2018-12-01 20:47:05 发布 · 223 阅读

0 ·

CC 4.0 BY-SA版权

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

368 篇文章

订阅专栏

本文深入探讨了PL/SQL中各种集合类型的使用方法，包括ADT集合、UDT集合和关联数组，通过实例展示了如何声明、初始化和操作这些集合，如遍历、赋值和打印元素。

1. SQL集合之ADT集合

SQL> /* Formatted on 2018/11/26 18:52:22 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     -- Declare and initialize a collection of grains.
  3     lv_string_table   STRING_TABLE
  4                          := string_table ('Drogo Baggins', 'Frodo Baggins');
  5     lv_hobbit_table   HOBBIT_TABLE
  6        := hobbit_table (hobbit ('Bungo Baggins'), hobbit ('Bilbo Baggins'));
  7  BEGIN
  8     -- Assign the members from one collection to the other.
  9     FOR i IN 1 .. lv_string_table.COUNT
 10     LOOP
 11        lv_hobbit_table.EXTEND;
 12        lv_hobbit_table (lv_hobbit_table.COUNT) := hobbit (lv_string_table (i));
 13     END LOOP;
 14
 15     -- Print the members of the hobbit table.
 16     FOR i IN 1 .. lv_hobbit_table.COUNT
 17     LOOP
 18        DBMS_OUTPUT.put_line (lv_hobbit_table (i).to_string ());
 19     END LOOP;
 20  END;
 21  /
Hello Bungo Baggins!
Hello Bilbo Baggins!
Hello Drogo Baggins!
Hello Frodo Baggins!

PL/SQL procedure successfully completed.

SQL> /* Formatted on 2018/11/26 18:55:25 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION get_hobbits
  2     RETURN HOBBIT_TABLE
  3  IS
  4     -- Declare a collection of hobbits.
  5     lv_hobbit_table   HOBBIT_TABLE
  6        := hobbit_table (hobbit ('Bungo Baggins'),
  7             hobbit ('Bilbo Baggins'),
  8             hobbit ('Drogo Baggins'),
  9             hobbit ('Frodo Baggins'));
 10  BEGIN
 11     RETURN lv_hobbit_table;
 12  END;
 13  /

Function created.

SQL> COLUMN hobbit_name FORMAT A14
SQL>
SQL>   SELECT name AS hobbit_name
  2   FROM TABLE (get_hobbits ())
  3  ORDER BY 1;

HOBBIT_NAME
--------------
Bilbo Baggins
Bungo Baggins
Drogo Baggins
Frodo Baggins

2. PL/SQL集合之ADT集合

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2     -- Declare a collection data type of numbers.
  3     TYPE number_table IS TABLE OF NUMBER;
  4     -- Declare a variable of the collection data types.
  5     lv_collection   NUMBER_TABLE := number_table (1, 2, 3);
  6  BEGIN
  7     -- Loop through the collection and print values.
  8     FOR i IN 1 .. lv_collection.COUNT
  9     LOOP
 10     DBMS_OUTPUT.put_line (lv_collection (i));
 11     END LOOP;
 12* END;
 13  /
1
2
3

PL/SQL procedure successfully completed.

3. 标量变量的关联数组（数字索引）

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2     -- Declare a collection data type of numbers.
  3     TYPE numbers IS TABLE OF NUMBER
  4     INDEX BY VARCHAR2(10);
  5     -- Declare a variable of the collection data types.
  6     lv_collection   NUMBERS;
  7  BEGIN
  8     -- Assign a value to the collection.
  9     lv_collection (0) := 1;
 10     dbms_output.put_line(lv_collection (0));
 11* END;
SQL> /
1

PL/SQL procedure successfully completed.

4. 标量变量的关联数组（变长字符串索引）

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2     -- Declare a collection data type of numbers.
  3     TYPE numbers IS TABLE OF NUMBER
  4     INDEX BY VARCHAR2(10);
  5     -- Declare a variable of the collection data types.
  6     lv_collection   NUMBERS;
  7  BEGIN
  8     -- Assign a value to the collection.
  9     lv_collection ('Two') := 1;
 10     dbms_output.put_line(lv_collection ('Two'));
 11* END;
SQL> /
1

PL/SQL procedure successfully completed.

5. PL/SQL集合之UDT集合

SQL> /* Formatted on 2018/11/26 22:04:42 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     -- Declare a local collection of hobbits.
  3     TYPE hobbit_table IS TABLE OF HOBBIT;
  4
  5     -- Declare and initialize a collection of grains.
  6     lv_string_table   STRING_TABLE
  7     := string_table ('Drogo Baggins', 'Frodo Baggins');
  8     lv_hobbit_table   HOBBIT_TABLE
  9     := hobbit_table (hobbit ('Bungo Baggins'),
 10          hobbit ('Bilbo Baggins'));
 11  BEGIN
 12     -- Print the first item in the array.
 13     FOR i IN 1 .. lv_string_table.COUNT
 14     LOOP
 15     lv_hobbit_table.EXTEND;
 16     lv_hobbit_table (lv_hobbit_table.COUNT) := hobbit (lv_string_table (i));
 17     END LOOP;
 18
 19     -- Print the members of the hobbit table.
 20     FOR i IN 1 .. lv_hobbit_table.COUNT
 21     LOOP
 22     DBMS_OUTPUT.put_line (lv_hobbit_table (i).to_string ());
 23     END LOOP;
 24  END;
 25  /
Hello Bungo Baggins!
Hello Bilbo Baggins!
Hello Drogo Baggins!
Hello Frodo Baggins!

PL/SQL procedure successfully completed.

6. 复合变量的关联数组

SQL> /* Formatted on 2018/11/26 22:16:07 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     -- Declare a local user-defined record structure.
  3     TYPE dwarf_record IS RECORD
  4     (
  5     dwarf_name   VARCHAR2 (20),
  6     dwarf_home   VARCHAR2 (20)
  7     );
  8
  9     -- Declare a local collection of hobbits.
 10     TYPE dwarf_table IS TABLE OF DWARF_RECORD
 11     INDEX BY PLS_INTEGER;
 12
 13     -- Declare and initialize a collection of grains.
 14     list   DWARF_TABLE;
 15  BEGIN
 16     -- Add two elements to the associative array.
 17     list (1).dwarf_name := 'Gloin';
 18     list (1).dwarf_home := 'Durin''s Folk';
 19     list (2).dwarf_name := 'Gimli';
 20     list (2).dwarf_home := 'Durin''s Folk';
 21
 22     -- Print the first item in the array.
 23     FOR i IN 1 .. list.COUNT
 24     LOOP
 25     DBMS_OUTPUT.put_line (
 26     '['
 27     || list (i).dwarf_name
 28     || ']'
 29     || '['
 30     || list (i).dwarf_home
 31     || ']');
 32     END LOOP;
 33  END;
 34  /
[Gloin][Durin's Folk]
[Gimli][Durin's Folk]

PL/SQL procedure successfully completed.