牛肉烧板黎

好好写SQL

0%

PL/SQL数据类型——COLLECTION

发表于 分类于

      PL/SQL中我们可以定义两种属性数据类型：COLLECTION和RECORDS。属性数据类型的内部成员可以是标量数据类型也可以是属性数据类型。
      集合数据类型的内部由相同数据类型的元素组成，我们可以通过它的唯一索引以variable_name(index)的方式去访问其中的元素。集合数据类型包含以下几种：关联数据(associative array)、可变数组(Varray)、嵌套表(Nested table)。

Associative array

      关联数组有点类似于java中的Map，只是在PL/SQL中，关联数组索引的数据类型只能是string类型(VARCHAR2,VARCHAR,STRING,LONG)和PLS_INTEGER。关联数组不存在为NULL的情况，所以你不能使用IS NULL和IS NOT NULL去测试它，否则无法通过编译。
定义关联数组的方式如下：

TYPE 关联数组类型名称 IS TABLE OF 值类型 INDEX BY 索引类型;

来看下面的例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DECLARE
  TYPE visits IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(20);
  outpatient_visits visits;
  i_index VARCHAR2(20);
BEGIN
  
  outpatient_visits('medical') := 200;
  outpatient_visits('surgical') := 120;
  outpatient_visits('dermatological') := 67;
  outpatient_visits('emergency'):=176;
  
  
  i_index := outpatient_visits.FIRST; -- Get first element of array
  
  WHILE i_index IS NOT NULL LOOP
    DBMS_Output.PUT_LINE
      ('outpatient visits of ' || i_index || ' is ' || outpatient_visits(i_index));
    i_index := outpatient_visits.NEXT(i_index);  -- Get next element of array
  END LOOP;
  
END;
/

outpatient visits of dermatological is 67
outpatient visits of emergency is 176
outpatient visits of medical is 200
outpatient visits of surgical is 120

      从输出可以看出关联数组存储的顺序和键值插入顺序无关，它依赖于键的排序，而键的排序规则由初始化参数NLS_SORT和NLS_COMP决定。FIRST获取第一个元素的索引，NEXT(index)获取index的下一个索引。

Varray

      可变数组可以存储的元素个数为0到定义的最大元素个数。定义关联数组的方式如下：

TYPE 可变数组类型名称 IS VARRAY(元素最大个数) OF 值类型;

我们首先看个例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
  TYPE array_list IS VARRAY(10) OF VARCHAR2(20);
  team array_list := array_list('A','B','C',NULL,NULL,NULL);
BEGIN
  
  team(4) := 'D';
  DBMS_OUTPUT.put_line(team.COUNT);
  FOR I IN 1..team.COUNT LOOP
    EXIT WHEN team(i) IS NULL;
    DBMS_OUTPUT.put_line(i||':'||team(i));
  END LOOP;
  
END;
/

6
1:A
2:B
3:C
4:D

我们虽然定义array_list的容量为10，但是在初始化team这个数组的时候，只初始化了6个元素，所以team的容量为6，如果你执行team(7):='E';那么将抛出异常。看上去数组有点像key为整数的关联数组，但是他们的差距还是很大的，后面会详细讨论。
数组一般适用于：1、知道最大元素的数量；2、对顺序访问的速度有要求。

Nested Table

对于嵌套表，可以将其理解为只有一个列的表。

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
 TYPE N_TABLE IS TABLE OF VARCHAR2(20);
 TN N_TABLE := N_TABLE('A','B','C');
BEGIN
  TN.EXTEND();
  TN(4) := 'D';
  
  FOR I IN TN.FIRST..TN.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(TN(I));
  END LOOP;
END;
/

A
B
C
D

EXTEND()方法在tn嵌套表的末尾追加了一个空元素。
嵌套表数据存储在一个单独的存储表中，它是一个系统生成的数据库表。访问嵌套表时，数据库将嵌套表与其存储表连接起来。这使得嵌套表适合查询和更新。
下面用一个oracle官方文档的例子介绍嵌套表的集合操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
DECLARE
  TYPE nested_typ IS table OF NUMBER;
 
  nt1    nested_typ := nested_typ(1,2,3);
  nt2    nested_typ := nested_typ(3,2,1);
  nt3    nested_typ := nested_typ(2,3,1,3);
  nt4    nested_typ := nested_typ(1,2,4);
  answer nested_typ;
 
  PROCEDURE print_nested_table (nt nested_typ) IS
    output VARCHAR2(128);
  BEGIN
    IF nt IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Result: null set');
    ELSIF nt.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Result: empty set');
    ELSE
      FOR i IN nt.FIRST .. nt.LAST LOOP  -- For first to last element
        output := output || nt(i) || ' ';
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('Result: ' || output);
    END IF;
  END print_nested_table;
 
BEGIN
  answer := nt1 MULTISET UNION nt4;
  print_nested_table(answer);
  answer := nt1 MULTISET UNION nt3;
  print_nested_table(answer);
  answer := nt1 MULTISET UNION DISTINCT nt3;
  print_nested_table(answer);
  answer := nt2 MULTISET INTERSECT nt3;
  print_nested_table(answer);
  answer := nt2 MULTISET INTERSECT DISTINCT nt3;
  print_nested_table(answer);
  answer := SET(nt3);
  print_nested_table(answer);
  answer := nt3 MULTISET EXCEPT nt2;
  print_nested_table(answer);
  answer := nt3 MULTISET EXCEPT DISTINCT nt2;
  print_nested_table(answer);
END;
/

Result: 1 2 3 1 2 4
Result: 1 2 3 2 3 1 3
Result: 1 2 3
Result: 3 2 1
Result: 3 2 1
Result: 2 3 1
Result: 3
Result: empty set

嵌套表还有很多高级的用途，可以查看oracle的官方文档。

内置的集合操作方法

在前面的例子中已经介绍了一些方法，下面简单介绍下集合中更多的操作

方法 类型 描述
DELETE PROCEDURE 删除元素。用法：1、DELETE,删除集合中的所有元素；2、DELETE(n)，删除索引为N的元素；3、DELETE(m,n)，删除索引从m到n的元素
TRIM PROCEDURE 从数组或嵌套表的末尾删除元素。用法：1、TRIM，最后一个元素；2、TRIM(n)，删除最后n个元素
EXTEND PROCEDURE 从数组或嵌套表的末尾增加元素。用法：1、EXTEND，在末尾增加一个空元素；2、EXTEND(n)，在末尾增加n个空元素；3、EXTEND(n,i)，在末尾增加n个索引为i的元素的拷贝。
EXISTS FUNCTION 如果数组或嵌套表中存在该元素则返回TRUE。用法：EXISTS(n)
FIRST FUNCTION 返回第一个元素的索引
LAST FUNCTION 返回最后一个元素的索引
COUNT FUNCTION 返回集合中元素的个数
LIMIT FUNCTION 返回collection的最大容量
PRIOR FUNCTION 返回指定索引的前一个索引
NEXT FUNCTION 返回指定索引的下一个索引

Associative Array、Varray和Nested Table的比较

下表是三种类型的比较：

类型 元素数量 索引类型 稀疏？稠密？ 未初始化的状态 定义位置 是否可成为属性数据类型
关联数组 无限制 String或PLS_INTEGER 未定 empty PL/SQL块或包中 否
数组 需指定 INTEGER 始终稠密 null PL/SQL块或包中或模式级别 定义在模式级别
嵌套表 无限制 INTEGER 最开始为稠密，随着删除修改可能变为稀疏 null PL/SQL块或包中或模式级别 定义在模式级别

关联数组只能在PL/SQL块或包中定义，而数组以及嵌套表可以在模式级别定义。所以，当你想在程序之间传递关联数组，那么这两个程序只能是在通一个包下的子程序，并将关联数组定义在pl/sql包的级别，数组以及嵌套表就没有这个限制。因为这个原因，关联数组也不能作为属性数据类型。
当需要对其中的元素进行快速的顺序访问时，数组时最优的选择，当需要对其中的元素进行更新、删除操作时，代价就相对较大了。嵌套表数据存储在一个单独的数据库系统自动生成的表中。访问嵌套表时，数据库将嵌套表与其存储表连接起来。这使得嵌套的表适合查询和更新。