在 Amazon Aurora PostgreSQL 和 Amazon RDS for PostgreSQL 中模擬 Oracle PL/SQL 關聯陣列

在 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

在 PostgreSQL 中建立使用以下關聯陣列的目標 PL/pgSQL 區塊： PostgreSQL

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 區塊。 PostgreSQL 如果關聯陣列的索引值之間存在間隙，則不會在這些間隙中存放任何資料。這可讓 PostgreSQL 迴圈僅逐一查看索引位置。

檢閱輸出。

陣列長度大於 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 區塊。如果關聯陣列的索引值之間存在間隙，則不會在這些間隙中存放任何資料。這可讓 PostgreSQL 迴圈僅逐一查看索引位置。

檢閱輸出。

如下列輸出所示，使用者定義的類型只會存放填入的資料元素，這表示陣列長度符合值的數量。因此，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