Emule las matrices PL/SQL asociativas de Oracle en Amazon Aurora PostgreSQL y Amazon RDS for PostgreSQL

Cree un PL/SQL bloque fuente en Oracle.

Cree un PL/SQL bloque de origen en Oracle que utilice la siguiente matriz asociativa:

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;

Ejecute el PL/SQL bloque.

Ejecute el PL/SQL bloque fuente en Oracle. Si hay brechas entre los valores de índice de una matriz asociativa, no se almacena ningún dato en esas brechas. Esto permite que el bucle de Oracle itere únicamente a través de las posiciones del índice.

Revise la salida.

Se insertaron cinco elementos en la matriz (cc) a intervalos no consecutivos. El recuento de matrices se muestra en el resultado siguiente:

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

Cree un PL/pgSQL bloque de destino en PostgreSQL.

Cree un PL/pgSQL bloque de destino en PostgreSQL que utilice la siguiente matriz asociativa:

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

Ejecute el bloque. PL/pgSQL

Ejecute el PL/pgSQL bloque de destino en PostgreSQL. Si hay brechas entre los valores de índice de una matriz asociativa, no se almacena ningún dato en esas brechas. Esto permite que el bucle de PostgreSQL itere solo a través de las posiciones del índice.

Revise la salida.

La longitud de la matriz es superior a 5 porque NULL se almacena en las brechas entre las posiciones del índice. Como se muestra en el siguiente resultado, el bucle completa 10 iteraciones para recuperar 5 valores de la 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

Cree un PL/pgSQL bloque de destino con una matriz y un tipo definido por el usuario.

Para optimizar el rendimiento y adaptarlo a la funcionalidad de Oracle, podemos crear un tipo definido por el usuario que almacene tanto las posiciones del índice como sus datos correspondientes. Este enfoque reduce las iteraciones innecesarias al mantener las asociaciones directas entre los índices y los 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;
$$;

Ejecuta el PL/pgSQL bloque.

Ejecuta el PL/pgSQL bloque objetivo. Si hay brechas entre los valores de índice de una matriz asociativa, no se almacena ningún dato en esas brechas. Esto permite que el bucle de PostgreSQL itere solo a través de las posiciones del índice.

Revise la salida.

Como se muestra en el siguiente resultado, el tipo definido por el usuario almacena solo los elementos de datos rellenados, lo que significa que la longitud de la matriz coincide con el número de valores. Como resultado, las iteraciones de LOOP se optimizan para procesar solo los datos existentes, lo que elimina la necesidad de rastrear las posiciones vacías.

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