Emula gli array PL/SQL associativi Oracle in Amazon Aurora PostgreSQL e Amazon RDS per PostgreSQL

Crea un PL/SQL blocco sorgente in Oracle.

Crea un PL/SQL blocco sorgente in Oracle che utilizza il seguente array associativo:

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;

Esegui il PL/SQL blocco.

Esegui il PL/SQL blocco sorgente in Oracle. Se ci sono degli spazi tra i valori di indice di un array associativo, in tali spazi non viene memorizzato alcun dato. Ciò consente al ciclo Oracle di scorrere solo attraverso le posizioni dell'indice.

Esamina l'output.

Cinque elementi sono stati inseriti nell'array (cc) a intervalli non consecutivi. Il conteggio degli array è mostrato nel seguente output:

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

Crea un PL/pgSQL blocco di destinazione in PostgreSQL.

Crea un PL/pgSQL blocco di destinazione in PostgreSQL che utilizza il seguente array associativo:

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

Esegui il PL/pgSQL blocco.

Esegui il PL/pgSQL blocco di destinazione in PostgreSQL. Se ci sono degli spazi tra i valori dell'indice di un array associativo, nessun dato viene memorizzato in tali spazi. Ciò consente al ciclo PostgreSQL di iterare solo attraverso le posizioni dell'indice.

Esamina l'output.

La lunghezza dell'array è maggiore di 5 perché NULL è memorizzata negli spazi tra le posizioni dell'indice. Come mostrato nell'output seguente, il ciclo completa 10 iterazioni per recuperare 5 valori nell'array.

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

Crea un PL/pgSQL blocco di destinazione con un array e un tipo definito dall'utente.

Per ottimizzare le prestazioni e soddisfare le funzionalità di Oracle, possiamo creare un tipo definito dall'utente che memorizza sia le posizioni dell'indice che i dati corrispondenti. Questo approccio riduce le iterazioni non necessarie mantenendo associazioni dirette tra indici e valori.

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

Esegui il PL/pgSQL blocco.

Esegui il PL/pgSQL blocco bersaglio. Se ci sono degli spazi tra i valori dell'indice di un array associativo, in tali spazi non viene memorizzato alcun dato. Ciò consente al ciclo PostgreSQL di iterare solo attraverso le posizioni dell'indice.

Esamina l'output.

Come illustrato nell'output seguente, il tipo definito dall'utente memorizza solo gli elementi di dati popolati, il che significa che la lunghezza dell'array corrisponde al numero di valori. Di conseguenza, LOOP le iterazioni sono ottimizzate per elaborare solo i dati esistenti, eliminando la necessità di tenere traccia delle posizioni vuote.

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