Émulez des baies PL/SQL associatives Oracle dans Amazon Aurora PostgreSQL et Amazon RDS for PostgreSQL

Créez un PL/SQL bloc source dans Oracle.

Créez un PL/SQL bloc source dans Oracle qui utilise le tableau associatif suivant :

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;

Exécutez le PL/SQL bloc.

Exécutez le PL/SQL bloc source dans Oracle. S'il existe des écarts entre les valeurs d'index d'un tableau associatif, aucune donnée n'est stockée dans ces espaces. Cela permet à la boucle Oracle d'itérer uniquement sur les positions de l'index.

Vérifiez la sortie.

Cinq éléments ont été insérés dans le tableau (cc) à des intervalles non consécutifs. Le nombre de tableaux est indiqué dans le résultat suivant :

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

Créez un PL/pgSQL bloc cible dans PostgreSQL.

Créez un PL/pgSQL bloc cible dans PostgreSQL qui utilise le tableau associatif suivant :

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

Exécutez le PL/pgSQL bloc.

Exécutez le PL/pgSQL bloc cible dans PostgreSQL. S'il existe des écarts entre les valeurs d'index d'un tableau associatif, aucune donnée n'est stockée dans ces espaces. Cela permet à la boucle PostgreSQL d'itérer uniquement sur les positions d'index.

Vérifiez la sortie.

La longueur du tableau est supérieure à 5 car elle NULL est stockée dans les espaces entre les positions d'index. Comme indiqué dans le résultat suivant, la boucle effectue 10 itérations pour récupérer 5 valeurs dans le tableau.

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

Créez un PL/pgSQL bloc cible avec un tableau et un type défini par l'utilisateur.

Pour optimiser les performances et correspondre aux fonctionnalités d'Oracle, nous pouvons créer un type défini par l'utilisateur qui stocke à la fois les positions de l'indice et les données correspondantes. Cette approche réduit les itérations inutiles en maintenant des associations directes entre les indices et les valeurs.

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

Exécutez le PL/pgSQL bloc.

Exécutez le PL/pgSQL bloc cible. S'il existe des écarts entre les valeurs d'index d'un tableau associatif, aucune donnée n'est stockée dans ces espaces. Cela permet à la boucle PostgreSQL d'itérer uniquement sur les positions d'index.

Vérifiez la sortie.

Comme indiqué dans le résultat suivant, le type défini par l'utilisateur ne stocke que les éléments de données renseignés, ce qui signifie que la longueur du tableau correspond au nombre de valeurs. Par conséquent, LOOP les itérations sont optimisées pour traiter uniquement les données existantes, éliminant ainsi le besoin de suivre les positions vides.

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