文档首页/ 数据仓库服务 GaussDB(DWS)/ 开发指南/ 标准数仓开发指南(8.1.3.x)/ GaussDB(DWS)存储过程/ GaussDB(DWS)存储过程数组和record/ 数组
更新时间：2024-10-10 GMT+08:00
查看PDF
分享

数组

数组类型的使用

在使用数组之前，需要自定义一个数组类型。

在存储过程中紧跟AS关键字后面定义数组类型。定义方法为： 
TYPE array_type IS VARRAY(size) OF data_type [NOT NULL];

其中：

  • array_type：要定义的数组类型名。
  • VARRAY：表示要定义的数组类型。
  • size：取值为正整数，表示可以容纳的成员的最大数量。
  • data_type：要创建的数组中成员的类型。
  • NOT NULL: 可选约束，可以约束该数组中的元素均不为NULL。
  • 在GaussDB(DWS)中，数组会自动增长，访问越界会返回一个NULL，不会报错。越界写入数组会提示：Subscript outside of limit.
  • 在存储过程中定义的数组类型，其作用域仅在该存储过程中。
  • 建议选择上述定义方法的一种来自定义数组类型，当同时使用两种方法定义同名的数组类型时，GaussDB(DWS)会优先选择存储过程中定义的数组类型来声明数组变量。

GaussDB(DWS) 8.1.0之前版本， 由于数组可以自动增长，系统不会校验数组越界以及数组元素的长度限制。当前版本为了兼容Oracle的用法增加了相关约束。如果已经存在越界写入等场景，可通过在behavior_compat_options参数中配置varray_verification，来兼容之前不校验的行为。

示例：

 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
 
--演示在存储过程中对数组声明操作。
CREATE OR REPLACE PROCEDURE array_proc
AS 
       TYPE ARRAY_INTEGER IS VARRAY(1024) OF INTEGER;--定义数组类型
       TYPE ARRAY_INTEGER_NOT_NULL IS VARRAY(1024) OF INTEGER NOT NULL;--定义非空数组类型
       ARRINT ARRAY_INTEGER := ARRAY_INTEGER();  --声明数组类型的变量 
BEGIN 
       ARRINT.extend(10);  
       FOR I IN 1..10 LOOP  
               ARRINT(I) := I; 
       END LOOP; 
       DBMS_OUTPUT.PUT_LINE(ARRINT.COUNT);  
       DBMS_OUTPUT.PUT_LINE(ARRINT(1));  
       DBMS_OUTPUT.PUT_LINE(ARRINT(10)); 
       DBMS_OUTPUT.PUT_LINE(ARRINT(ARRINT.FIRST)); 
       DBMS_OUTPUT.PUT_LINE(ARRINT(ARRINT.last));
END;  
/

--调用该存储过程。
CALL array_proc();
10
1
10
1
10
--删除存储过程。
DROP PROCEDURE array_proc;

ROWTYPE类型数组的声明及使用

除了上述示例中普通数组以及not null数组的声明及使用，数组中同时支持rowtype类型数组的声明及使用。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
--演示在存储过程中对数组COUNT函数的用法。
CREATE TABLE tbl (a int, b int);
INSERT INTO tbl VALUES(1, 2),(2, 3),(3, 4);
CREATE OR REPLACE PROCEDURE array_proc
AS 
    CURSOR all_tbl IS SELECT * FROM tbl ORDER BY a; 
    TYPE tbl_array_type IS varray(50) OF tbl%rowtype; --定义rowtype类型的数组，tbl是任意表。
    tbl_array tbl_array_type;
    tbl_item tbl%rowtype;
    inx1 int;
BEGIN 
    tbl_array := tbl_array_type();
    inx1 := 0;
    FOR tbl_item IN all_tbl LOOP 
        inx1 := inx1 + 1;  
        tbl_array(inx1) := tbl_item; 
    END LOOP; 
    WHILE inx1 IS NOT NULL LOOP  
        DBMS_OUTPUT.PUT_LINE('tbl_array(inx1).a=' || tbl_array(inx1).a || ' tbl_array(inx1).b=' || tbl_array(inx1).b);  
        inx1 := tbl_array.PRIOR(inx1);
    END LOOP; 
END;
/

执行结果：

1
2
3
4
 
call array_proc();
tbl_array(inx1).a=3 tbl_array(inx1).b=4
tbl_array(inx1).a=2 tbl_array(inx1).b=3
tbl_array(inx1).a=1 tbl_array(inx1).b=2

数组相关函数使用

在GaussDB(DWS)中，提供了类似Oracle相关的数组函数的支持，可以通过以下函数获取数组的一些属性或者对数组内容进行操作。

COUNT

COUNT函数可以返回当前数组元素的数量，仅统计初始化过的元素或者经过EXTEND函数扩展后的元素。

用法如下：

varray.COUNT或varray.COUNT()

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
--演示在存储过程中对数组COUNT函数的用法。
CREATE OR REPLACE PROCEDURE test_varray
AS 
    TYPE varray_type IS VARRAY(20) OF INT; 
    v_varray varray_type; 
BEGIN 
    v_varray := varray_type(1, 2, 3);
    DBMS_OUTPUT.PUT_LINE('v_varray.count=' || v_varray.count); 
    v_varray.extend;
    DBMS_OUTPUT.PUT_LINE('v_varray.count=' || v_varray.count); 
END; 
/

执行结果：

1
2
3
 
call test_varray();
v_varray.count=3
v_varray.count=4

FIRST和LAST

FIRST函数可以返回第一个元素的下标。LAST函数可以返回最后一个元素的下标。

用法如下：

varray.FIRST或varray.FIRST()

varray.LAST或varray.LAST()

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
--演示在存储过程中对数组FIRST和LAST函数的用法。
CREATE OR REPLACE PROCEDURE test_varray
AS 
    TYPE varray_type IS VARRAY(20) OF INT; 
    v_varray varray_type; 
BEGIN 
    v_varray := varray_type(1, 2, 3);
    DBMS_OUTPUT.PUT_LINE('v_varray.first=' || v_varray.first); 
    DBMS_OUTPUT.PUT_LINE('v_varray.last=' || v_varray.last); 
END; 
/

执行结果：

1
2
3
 
call test_varray();
v_varray.first=1
v_varray.last=3

EXTEND

EXTEND函数主要是为了兼容Oracle的两种用法。在GaussDB(DWS)中，数组会自动增长，EXTEND函数不是必须的。如果是新写的存储过程，完全没有必要使用EXTEND函数。

EXTEND函数可以对数组进行扩展，EXTEND有两种调用方式。

  • 方式一：

    EXTEND包含一个整型入参，表示数组向后扩展size大小的长度，EXTEND后COUNT和LAST函数的值也会有相应的变化。

    用法如下：

    varray.EXTEND(size)

    其中varray.EXTEND这种无参的调用默认会向后扩展1位等价于varray.EXTEND(1)

  • 方式二：

    EXTEND包含两个整型入参，第一个参数代表向后扩展size大小的长度，第二个参数表示扩展后的数组元素值和之下标为index的元素相同。

    用法如下：

    varray.EXTEND(size, index)

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
--演示在存储过程中对数组EXTEND函数的用法。
CREATE OR REPLACE PROCEDURE test_varray
AS 
    TYPE varray_type IS VARRAY(20) OF INT; 
    v_varray varray_type; 
BEGIN 
    v_varray := varray_type(1, 2, 3);
    v_varray.extend(3);
    DBMS_OUTPUT.PUT_LINE('v_varray.count=' || v_varray.count); 
    v_varray.extend(2,3);
    DBMS_OUTPUT.PUT_LINE('v_varray.count=' || v_varray.count); 
    DBMS_OUTPUT.PUT_LINE('v_varray(7)=' || v_varray(7)); 
    DBMS_OUTPUT.PUT_LINE('v_varray(8)=' || v_varray(7)); 
END; 
/

执行结果：

1
2
3
4
5
 
call test_varray();
v_varray.count=6
v_varray.count=8
v_varray(7)=3
v_varray(8)=3

NEXT和PRIOR

NEXT函数和PRIOR函数主要用于数组的循环遍历中，NEXT函数会根据入参index值，返回下一个数组元素的下标，若已经到达数组下标最大值则返回NULL。PRIOR函数会根据入参index值，返回上一个数组元素的下标，若已经到达数组下标最小值则返回NULL。

用法如下：

varray.NEXT(index)

varray.PRIOR(index)

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
--演示在存储过程中对数组NEXT和PRIOR函数的用法。
CREATE OR REPLACE PROCEDURE test_varray
AS 
    TYPE varray_type IS VARRAY(20) OF INT; 
    v_varray varray_type; 
    i int;
BEGIN 
    v_varray := varray_type(1, 2, 3);

    i := v_varray.COUNT;  
    WHILE i IS NOT NULL LOOP  
        DBMS_OUTPUT.PUT_LINE('test prior v_varray('||i||')=' || v_varray(i));  
        i := v_varray.PRIOR(i);  
    END LOOP; 

    i := 1;  
    WHILE i IS NOT NULL LOOP  
        DBMS_OUTPUT.PUT_LINE('test next v_varray('||i||')=' || v_varray(i));  
        i := v_varray.NEXT(i);  
    END LOOP;
END; 
/

执行结果：

1
2
3
4
5
6
7
 
call test_varray();
test prior v_varray(3)=3
test prior v_varray(2)=2
test prior v_varray(1)=1
test next v_varray(1)=1
test next v_varray(2)=2
test next v_varray(3)=3

EXISTS

EXISTS函数可以判断数组下标是否存在。

用法如下：

varray.EXISTS(index)

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
--演示在存储过程中对数组EXISTS函数的用法。
CREATE OR REPLACE PROCEDURE test_varray
AS 
    TYPE varray_type IS VARRAY(20) OF INT; 
    v_varray varray_type; 
BEGIN 
    v_varray := varray_type(1, 2, 3);
    IF v_varray.EXISTS(1) THEN
        DBMS_OUTPUT.PUT_LINE('v_varray.EXISTS(1)');
    END IF;
    IF NOT v_varray.EXISTS(10) THEN
        DBMS_OUTPUT.PUT_LINE('NOT v_varray.EXISTS(10)');
    END IF;
END; 
/

执行结果：

1
2
3
 
call test_varray();
v_varray.EXISTS(1)
NOT v_varray.EXISTS(10)

TRIM

TRIM函数可以从数组尾部删除指定数量的元素。

用法如下：

varray.TRIM(size)

其中varray.TRIM这种无参的调用会默认入参为1，等价于varray.TRIM(1)

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
--演示在存储过程中对数组TRIM函数的用法。
CREATE OR REPLACE PROCEDURE test_varray
AS 
    TYPE varray_type IS VARRAY(20) OF INT; 
    v_varray varray_type; 
BEGIN 
    v_varray := varray_type(1, 2, 3, 4, 5);
    v_varray.trim(3);
    DBMS_OUTPUT.PUT_LINE('v_varray.count' || v_varray.count);
    v_varray.trim;
    DBMS_OUTPUT.PUT_LINE('v_varray.count:' || v_varray.count);
END; 
/

执行结果：

1
2
3
 
call test_varray();
v_varray.count:2
v_varray.count:1

DELETE

DELETE函数可以从数组删除数组中的所有元素。

用法如下：

varray.DELETE或varray.DELETE()

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
--演示在存储过程中对数组DELETE函数的用法。
CREATE OR REPLACE PROCEDURE test_varray
AS 
    TYPE varray_type IS VARRAY(20) OF INT; 
    v_varray varray_type; 
BEGIN 
    v_varray := varray_type(1, 2, 3, 4, 5);
    v_varray.delete;
    DBMS_OUTPUT.PUT_LINE('v_varray.count:' || v_varray.count);
END; 
/

执行结果：

1
2
 
call test_varray();
v_varray.count:0

LIMIT

LIMIT函数可以返回数组的最大长度限制。

用法如下：

varray.LIMIT或varray.LIMIT()

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
--演示在存储过程中对数组LIMIT函数的用法。
CREATE OR REPLACE PROCEDURE test_varray
AS 
    TYPE varray_type IS VARRAY(20) OF INT; 
    v_varray varray_type; 
BEGIN 
    v_varray := varray_type(1, 2, 3, 4, 5);
    DBMS_OUTPUT.PUT_LINE('v_varray.limit:' || v_varray.limit);
END; 
/

执行结果：

1
2
 
call test_varray();
v_varray.limit:20