在 Amazon Aurora PostgreSQL 中模拟 Oracle PL/SQL 关联数组和适用于 PostgreSQL 的亚马逊 RDS

在 Oracle 中创建源 PL/SQL 块。

在 Oracle 中创建使用以下关联数组的源 PL/SQL 块：

DECLARE
    TYPE country_codes IS TABLE OF VARCHAR2(100) INDEX BY pls_integer;
    cc country_codes;
    cc_idx NUMBER := NULL;
BEGIN
    cc(7) := 'India';
    cc(3) := 'UK';
    cc(5) := 'USA';
    cc(0) := 'China';
    cc(-2) := 'Invalid';
    dbms_output.put_line('cc_length:' || cc.COUNT);
    IF (cc.COUNT > 0) THEN
        cc_idx := cc.FIRST;
        FOR i IN 1..cc.COUNT LOOP
            dbms_output.put_line('cc_idx:' || cc_idx || ' country:' || cc(cc_idx));
            cc_idx := cc.next(cc_idx);
        END LOOP;
    END IF;
END;

运行方 PL/SQL 块。

在 Oracle 中运行源代码 PL/SQL 块。如果关联数组的索引值之间存在间隙，也不会在这些间隙中存储任何数据。这样，Oracle 循环就可以仅迭代索引位置。

检查输出。

五个元素会以非连续的间隔插入到数组 (cc) 中。数组计数如以下输出所示：

cc_length:5
cc_idx:-2 country:Invalid
cc_idx:0 country:China
cc_idx:3 country:UK
cc_idx:5 country:USA
cc_idx:7 country:India

在 PostgreSQL 中创建一个目标 PL/pgSQL 区块。

在 PostgreSQL 中创建一个使用以下关联数组的目标 PL/pgSQL 块：

DO $$
DECLARE
    cc character varying(100)[];
    cc_idx integer := NULL;
BEGIN
    cc[7] := 'India';
    cc[3] := 'UK';
    cc[5] := 'USA';
    cc[0] := 'China';
    cc[-2] := 'Invalid';
    RAISE NOTICE 'cc_length: %', ARRAY_LENGTH(cc, 1);
    IF (ARRAY_LENGTH(cc, 1) > 0) THEN
        FOR i IN ARRAY_LOWER(cc, 1)..ARRAY_UPPER(cc, 1)
        LOOP
            RAISE NOTICE 'cc_idx:% country:%', i, cc[i];
        END LOOP;
    END IF;
END;
$$;

运行方 PL/pgSQL 块。

在 PostgreSQL 中运行目标 PL/pgSQL 区块。如果关联数组的索引值之间存在间隙，也不会在这些间隙中存储任何数据。这样，Oracle 循环就可以仅迭代索引位置。

检查输出。

数组长度大于 5，因为 NULL 存储在索引位置之间的空隙中。如以下输出所示，该循环会完成 10 次迭代，以检索数组中的 5 个值。

cc_length:10
cc_idx:-2 country:Invalid
cc_idx:-1 country:<NULL>
cc_idx:0 country:China
cc_idx:1 country:<NULL>
cc_idx:2 country:<NULL>
cc_idx:3 country:UK
cc_idx:4 country:<NULL>
cc_idx:5 country:USA
cc_idx:6 country:<NULL>
cc_idx:7 country:India

使用数组和用户定义类型创建目标 PL/pgSQL 块。

为了优化性能并匹配 Oracle 的功能，我们可以创建用户定义的类型，进而存储索引位置及其对应的数据。这种方法可以保持索引和值之间的直接关联，进而减少不必要的迭代。

DO $$
DECLARE
    cc country_codes[];
    cc_append country_codes := NULL;
    i record;
BEGIN
    cc_append.idx = 7;
    cc_append.val = 'India';
    cc := array_append(cc, cc_append);
    cc_append.idx = 3;
    cc_append.val = 'UK';
    cc := array_append(cc, cc_append);
    cc_append.idx = 5;
    cc_append.val = 'USA';
    cc := array_append(cc, cc_append);
    cc_append.idx = 0;
    cc_append.val = 'China';
    cc := array_append(cc, cc_append);
    cc_append.idx = - 2;
    cc_append.val = 'Invalid';
    cc := array_append(cc, cc_append);
    RAISE NOTICE 'cc_length: %', ARRAY_LENGTH(cc, 1);
    IF (ARRAY_LENGTH(cc, 1) > 0) THEN
        FOR i IN (
            SELECT
                *
            FROM
                unnest(cc)
            ORDER BY
                idx)
                LOOP
                    RAISE NOTICE 'cc_idx:% country:%', i.idx, i.val;
                END LOOP;
    END IF;
END;
$$;

运行方 PL/pgSQL 块。

运行目标 PL/pgSQL 方块。如果关联数组的索引值之间存在间隙，也不会在这些间隙中存储任何数据。这样，Oracle 循环就可以仅迭代索引位置。

检查输出。

如以下输出所示，用户定义的类型仅存储已填充的数据元素，这意味着数组长度与值的数量相匹配。因此，LOOP 迭代经过优化，仅会处理现有数据，而无需跟踪空位置。

cc_length:5
cc_idx:-2 country:Invalid
cc_idx:0 country:China
cc_idx:3 country:UK
cc_idx:5 country:USA
cc_idx:7 country:India