oracle集合

最新推荐文章于 2024-07-29 21:25:21 发布

原创最新推荐文章于 2024-07-29 21:25:21 发布 · 128 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #SQL

本文介绍了在PL/SQL中如何使用三种不同的集合类型：关联数组（Associative Arrays）、嵌套表（Nested Tables）和变长数组（VARRAY）。通过具体示例展示了每种集合类型的声明、填充和遍历过程，帮助读者理解不同场景下集合类型的应用。

[color=brown][size=x-large]1.Using an associative array[/size][/color]

      1  DECLARE
      2     TYPE list_of_names_t IS TABLE OF person.first_name%TYPE
      3        INDEX BY PLS_INTEGER;
      4     happyfamily   list_of_names_t;
      5     l_row PLS_INTEGER;
      6  BEGIN
      7     happyfamily (2020202020) := 'Eli';
      8     happyfamily (-15070) := 'Steven';
      9     happyfamily (-90900) := 'Chris';
     10     happyfamily (88) := 'Veva';
     11
     12     l_row := happyfamily.FIRST;
     13
     14     WHILE (l_row IS NOT NULL)
     15     LOOP
     16        DBMS_OUTPUT.put_line (happyfamily (l_row));
     17        l_row := happyfamily.NEXT (l_row);
     18     END LOOP;
     19* END;

[color=red] Chris
Steven
Veva
Eli[/color]
--------------------------------------------------------------------------------------------------------------------------------------------
[color=brown][size=x-large]2.Using a nested table[/size][/color]

    REM Section A
    SQL> CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
      2  /
    Type created.

    REM Section B
    SQL>
      1  DECLARE
      2     happyfamily   list_of_names_t := list_of_names_t (  );
      3     children      list_of_names_t := list_of_names_t (  );
      4     parents       list_of_names_t := list_of_names_t (  );
      5  BEGIN
      6     happyfamily.EXTEND (4);
      7     happyfamily (1) := 'Eli';
      8     happyfamily (2) := 'Steven';
      9     happyfamily (3) := 'Chris';
     10     happyfamily (4) := 'Veva';
     11
     12     children.EXTEND;
     13     children (1) := 'Chris';
     14     children.EXTEND;
     15     children (2) := 'Eli';
     16
     17     parents := happyfamily MULTISET EXCEPT children;
     18
     19     FOR l_row IN parents.FIRST .. parents.LAST
     20     LOOP
     21        DBMS_OUTPUT.put_line (parents (l_row));
     22     END LOOP;
     23* END;

[color=red] Steven
Veva[/color]
--------------------------------------------------------------------------------------------------------------------------------------------
[color=brown][size=x-large]3.Using a VARRAY[/size][/color]

    REM Section A
    SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);
      2  /
    Type created.

    SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
      2  /
    Type created.

    REM Section B
    SQL> CREATE TABLE family (
      2     surname VARCHAR2(1000)
      3   , parent_names first_names_t
      4   , children_names child_names_t
      5   );

    Table created.

    REM Section C
    SQL>
      1  DECLARE
      2     parents    first_names_t := first_names_t (  );
      3     children   child_names_t := child_names_t (  );
      4  BEGIN
      5     parents.EXTEND (2);
      6     parents (1) := 'Samuel';
      7     parents (2) := 'Charina';
      8     --
      9     children.EXTEND;
     10     children (1) := 'Feather';
     11
     12     --
     13     INSERT INTO family
     14                 (surname, parent_names, children_names
     15                 )
     16          VALUES ('Assurty', parents, children
     17                 );
     18  END;
    SQL> /

    PL/SQL procedure successfully completed.

    SQL> SELECT * FROM family
      2  /

[color=red] SURNAME
PARENT_NAMES
CHILDREN_NAMES
--------------------------------------------
Assurty
FIRST_NAMES_T('Samuel', 'Charina')
CHILD_NAMES_T('Feather')[/color]