金仓数据库KingbaseES 集合类型之关联数组的使用

关键字：

KingbaseES、集合类型、关联数组、人大金仓、KingbaseES、

集合类型

集合类型属于复合数据类型，复合数据类型存储具有内部元素的值。您可以将整个复合变量作为参数传递给子程序，也可以单独访问复合变量的内部元素。内部元素可以是普通标量元素，也可以是另一个复合元素。您可以在任何可以使用标量和变量的地方使用复合数据类型的标量元素。也可以在使用相同类型的复合变量的地方使用复合数据类型的复合元素。

在集合类型中，内部元素始终具有相同的数据类型。您可以通过集合变量的唯一索引访问集合变量的每个元素，语法如下：variable_name(index)。要创建集合变量，需要先定义集合类型，然后创建该类型的变量，或者使用%TYPE创建。

PL/SQL有三种集合类型：关联数组、可变数组和嵌套表。下文将详细介绍集合类型中关联数组的使用。

二、关联数组

2.1 概述

• 元素数量：关联数组不指定元素数，集合中的最大元素数为索引类型的数量上限。
• 密度：关联数组为稀疏集合，稀疏集合的元素之间可以存在间隙。
• 未初始化状态：关联数组为空集合，一个没有元素的集合。
• 何处定义：PL/SQL 块中定义的集合类型属于本地类型。它仅支持在块中使用，并且仅当块位于独立子程序或包子程序中时才会被存储在数据库中。包规范中定义的集合类型是公共类型。可以通过包名限定 (package_name.type_name) 从包外部引用它。它会一直存储在数据库中，直到删除包。
• 作为复合类型的元素：要成为复合类型的元素类型，集合类型必须是独立集合类型，即定义在模式中的集合类型。

2.2 定义

关联数组是一组键值对。每个键都是一个唯一的索引，用于定位与之相关联的值，语法为：variable_name(index)。

索引的数据类型可以是字符串类型（TEXT、VARCHAR2、VARCHAR、STRING 或 LONG）或 PLS_INTEGER。其中数据是按索引排序顺序存储的，而不是按创建顺序存储的。

与数据库表一样，关联数组：

• 在填充之前为空（但不为 NULL）
• 可以容纳不定量的元素，您可以在不知道其位置的情况下访问这些元素

与数据库表不同，关联数组：

• • 不需要磁盘空间或网络操作。
  • 不能使用 DML 语句进行操作 。

关联数组不支持构造函数。

2.3 使用

• 以字符串为索引的关联数组

例：定义一种按text类型字符串索引的关联数组，并声明关联数组的变量，用三个元素填充关联数组变量，然后更改一个元素的值，并打印值（按字符串排序顺序，而不是按创建顺序）。

\set SQLTERM /

DECLARE

-- Associative array indexed by string

TYPE ass_type IS TABLE OF INT INDEX BY TEXT;

age ass_type; -- Associative array variable

i TEXT; -- Scalar variable

BEGIN

-- Add elements (key-value pairs) to associative array

age('zs') := 25;

age('ls') := 30;

age('ww') := 40;

-- Change value associated with key

age('zs') := 26;

-- Print associative array:

i := age.FIRST; -- Get first element of array

WHILE i IS NOT NULL LOOP

RAISE NOTICE '% is % years old', i, age(i);

i := age.NEXT(i); -- Get next element of array

END LOOP;

END;

/

运行结果：

NOTICE: ls is 30 years old

NOTICE: ww is 40 years old

NOTICE: zs is 26 years old

• 函数返回以PLS_INTEGER为索引的关联数组

\set SQLTERM /

DECLARE

---- Associative array indexed by PLS_INTEGER

TYPE area_of_circle IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

area area_of_circle; --result variable

num INT := 3;

--Assigining values to associative array through functions

FUNCTION get_area_of_circle(num INT)

RETURN area_of_circle

IS

s area_of_circle;

BEGIN

FOR i IN 1..num LOOP

s(i) := 2 * 3.14 * i * i;

END LOOP;

RETURN s;

END;

BEGIN

--get associative array through functions

area= get_area_of_circle (num);

for i in 1..num LOOP

RAISE NOTICE 'area(%) = %', i, area(i);

END LOOP;

END;

/

运行结果：

NOTICE: area(1) = 6.28

NOTICE: area(2) = 25.12

NOTICE: area(3) = 56.52

2.4 删除

使用完关联数组，需要释放其资源。例如，在一个插件中使用关联数组，它在包drop之前一直存在，用户不可能经常删除再创建包，因此，需要在每使用完一个索引值，来对该索引值的内容进行释放。Kingbase提供了DELETE 集合方法，DELETE 是从集合中删除元素的过程。

在关联数组中，如果要删除的元素存在，DELETE（n）删除索引为 n 的元素；如果元素不存在，什么都不做。如果 m 和 n 都存在且 m<=n ，DELETE（m，n）删除索引在 m 到 n 范围内的所有元素，否则，什么都不做。如果一次删除所有元素，直接使用DELETE ，DELETE可以从关联数组中删除所有元素，此操作会立即释放分配给已删除元素的内存。 例：字符串索引的关联数组使用 DELETE 方法

\set SQLTERM /

DECLARE

TYPE str_type IS TABLE OF INTEGER INDEX BY TEXT;

str str_type;

PROCEDURE show_str

IS

i TEXT;

BEGIN

i := str.FIRST;

IF i IS NULL THEN

DBMS_OUTPUT.PUT_LINE('str is empty');

ELSE

WHILE i IS NOT NULL LOOP

RAISE NOTICE 'str.(%) = %', i, str(i);

i := str.NEXT(i);

END LOOP;

END IF;

RAISE NOTICE '---';

END show_str;

BEGIN

str('A') := 1;

str('Z') := 26;

str('B') := 2;

show_str;

str.DELETE; -- Delete all elements

show_str;

str('N') := 14;

str('Z') := 26;

str('D') := 4;

str('X') := 24;

str('Y') := 25;

str('O') := 15;

str('P') := 16;

str('Q') := 17;

str('H') := 8;

show_str;

str.DELETE('D'); -- Delete one element

show_str;

str.DELETE('O','P'); -- Delete range of elements

show_str;

str.DELETE('J','K'); -- Does nothing

show_str;

END;

/

运行结果：

NOTICE: str.(A) = 1

NOTICE: str.(B) = 2

NOTICE: str.(Z) = 26

NOTICE: ---

NOTICE: ---

NOTICE: str.(D) = 4

NOTICE: str.(H) = 8

NOTICE: str.(N) = 14

NOTICE: str.(O) = 15

NOTICE: str.(P) = 16

NOTICE: str.(Q) = 17

NOTICE: str.(X) = 24

NOTICE: str.(Y) = 25

NOTICE: str.(Z) = 26

NOTICE: ---

NOTICE: str.(H) = 8

NOTICE: str.(N) = 14

NOTICE: str.(O) = 15

NOTICE: str.(P) = 16

NOTICE: str.(Q) = 17

NOTICE: str.(X) = 24

NOTICE: str.(Y) = 25

NOTICE: str.(Z) = 26

NOTICE: ---

NOTICE: str.(H) = 8

NOTICE: str.(N) = 14

NOTICE: str.(Q) = 17

NOTICE: str.(X) = 24

NOTICE: str.(Y) = 25

NOTICE: str.(Z) = 26

NOTICE: ---

NOTICE: str.(H) = 8

NOTICE: str.(N) = 14

NOTICE: str.(Q) = 17

NOTICE: str.(X) = 24

NOTICE: str.(Y) = 25

NOTICE: str.(Z) = 26

NOTICE: ---

三、总结

• 关联数组存放数据，会将数据按索引排序，而不是按插入顺序存放。
• 关联数组索引唯一，相同的索引值可以复用，可以修改已有数据的值。
• 值的注意的是，关联数组不能存放游标，游标放入关联数组会失效。

参考文档：

《KingbaseES_PLSQL过程语言参考手册》