Emule matrizes PL/SQL associativas Oracle no Amazon Aurora PostgreSQL e no Amazon RDS for PostgreSQL

Crie um PL/SQL bloco de origem no Oracle.

Crie um PL/SQL bloco de origem no Oracle que use a seguinte matriz associativa:

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;

Execute o PL/SQL quarteirão.

Execute o PL/SQL bloco de origem no Oracle. Se houver lacunas entre os valores dos índices de uma matriz associativa, nenhum dado será armazenado nessas lacunas. Isso permite que o loop da Oracle itere apenas pelas posições de índice.

Revise a saída.

Cinco elementos foram inseridos na matriz (cc) em intervalos não consecutivos. A contagem da matriz é mostrada na saída a seguir:

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

Crie um PL/pgSQL bloco de destino no PostgreSQL.

Crie um PL/pgSQL bloco de destino no PostgreSQL que use a seguinte matriz associativa:

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;
$$;

Execute o PL/pgSQL quarteirão.

Execute o PL/pgSQL bloco de destino no PostgreSQL. Se houver lacunas entre os valores dos índices de uma matriz associativa, nenhum dado será armazenado nessas lacunas. Isso permite que o loop da Oracle itere apenas pelas posições de índice.

Revise a saída.

O comprimento da matriz é maior que cinco porque o valor NULL é armazenado nas lacunas entre as posições de índice. Como mostrado na saída a seguir, o loop realiza dez iterações para recuperar cinco valores na matriz.

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

Crie um PL/pgSQL bloco de destino com uma matriz e um tipo definido pelo usuário.

Para otimizar a performance e corresponder à funcionalidade da Oracle, podemos criar um tipo definido pelo usuário que armazena tanto as posições de índice quanto os dados correspondentes. Essa abordagem reduz iterações desnecessárias ao manter associações diretas entre os índices e os valores.

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;
$$;

Execute o PL/pgSQL quarteirão.

Execute o PL/pgSQL bloco alvo. Se houver lacunas entre os valores dos índices de uma matriz associativa, nenhum dado será armazenado nessas lacunas. Isso permite que o loop da Oracle itere apenas pelas posições de índice.

Revise a saída.

Como mostrado na saída a seguir, o tipo definido pelo usuário armazena apenas elementos de dados preenchidos, o que significa que o comprimento da matriz corresponde ao número de valores. Como resultado, as iterações do LOOP são otimizadas para processar apenas dados existentes, eliminando a necessidade de monitorar posições vazias.

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