Emulación de las matrices asociativas PL/SQL de Oracle en Amazon Aurora PostgreSQL y Amazon RDS para PostgreSQL

Cree un bloque PL/SQL de origen en Oracle.

Cree un bloque PL/SQL 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 bloque PL/SQL.

Ejecute el bloque PL/SQL de origen 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 bloque PL/pgSQL de destino en PostgreSQL.

Cree un bloque PL/pgSQL 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 bloque PL/pgSQL 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 Oracle itere únicamente 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 bloque PL/pgSQL 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;
$$;

Ejecute el bloque PL/pgSQL.

Ejecute el bloque PL/pgSQL de destino. 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.

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