Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35973 Oracle Compatibility Project 1 for 2025
  3. MDEV-34319

Associative arrays: DECLARE TYPE .. TABLE OF .. INDEX BY in stored routines

Details

    Description

      Let's add support for INDEX BY tables (associative arrays) in the DECLARE section of stored routines and anonymous blocks, with this syntax:

      DECLARE
        TYPE type_name TABLE OF rec_type_name INDEX BY idx_type_name;
      

      • type_name should support explicit and anchored data types (e.g t1.col1%TYPE).
      • The INDEX BY clause should support at least integer and string data types.
      • rec_type_name should support both scalar types and records.

      An associative array of scalar elements

      Explicit type_name:

      DECLARE
        TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
        salary_list salary;
        name VARCHAR2(20);
      BEGIN
        salary_list('Rajnisj') := 62000;
        salary_list('James') := 78000;
        name:= salary_list.FIRST;
        WHILE name IS NOT NULL
        LOOP
          dbms_output.put_line(name || ' ' || TO_CHAR(salary_list(name)));
          name:= salary_list.NEXT(name);
        END LOOP;
      END;
      /
      

      Anchored type_name:

      CREATE TABLE t1 (a INT);
      DECLARE
        TYPE salary IS TABLE OF t1.a%TYPE INDEX BY VARCHAR2(20);
        salary_list salary;
        name VARCHAR2(20);
      BEGIN
        salary_list('Rajnisj') := 62000;
        salary_list('James') := 78000;
        name:= salary_list.FIRST;
        WHILE name IS NOT NULL
        LOOP
          dbms_output.put_line(name || ' ' || TO_CHAR(salary_list(name)));
          name:= salary_list.NEXT(name);
        END LOOP;
      END;
      /
      

      An associative array of records

      Using explicit data types:

      DECLARE
        TYPE person_t IS RECORD
        (
          first_name VARCHAR(64),
          last_name VARCHAR(64)
        );
        person person_t;
        TYPE table_of_peson_t IS TABLE OF person_t INDEX BY VARCHAR(20);
        person_by_nickname table_of_peson_t;
        nick VARCHAR(20);
      BEGIN
        person_by_nickname('Monty') := person_t('Michael', 'Widenius');
        person_by_nickname('Serg') := person_t('Sergei ', 'Golubchik');
        nick:= person_by_nickname.FIRST;
        WHILE nick IS NOT NULL
        LOOP
          person:= person_by_nickname(nick);
          dbms_output.put_line(nick || ' ' || person.first_name || ' '|| person.last_name);
          nick:= person_by_nickname.NEXT(nick);
        END LOOP;
      END;
      /
      

      Using anchored data types:

      DROP TABLE persons;
      CREATE TABLE persons (nickname VARCHAR(64), first_name VARCHAR(64), last_name VARCHAR(64));
      INSERT INTO persons VALUES ('Serg','Sergei ', 'Golubchik');
      INSERT INTO persons VALUES ('Monty','Michael', 'Widenius');
      DECLARE
        TYPE table_of_person_t IS TABLE OF persons%ROWTYPE INDEX BY persons.nickname%TYPE;
        person_by_nickname table_of_person_t;
        nickname persons.nickname%TYPE;
        person persons%ROWTYPE;
      BEGIN
        FOR rec IN (SELECT * FROM persons)
        LOOP
          person_by_nickname(rec.nickname):= rec;
        END LOOP;
       
        nickname:= person_by_nickname.FIRST;
        WHILE nickname IS NOT NULL
        LOOP
          person:= person_by_nickname(nickname);
          dbms_output.put_line(person.nickname || ' ' || person.first_name || ' '|| person.last_name);
          nickname:= person_by_nickname.NEXT(nickname);
        END LOOP;
      END;
      /
      

      Attachments

        Issue Links

          Activity

            Changing the priority to Critical as there is a pull request:

            https://github.com/MariaDB/server/pull/3797

            bar Alexander Barkov added a comment - Changing the priority to Critical as there is a pull request: https://github.com/MariaDB/server/pull/3797

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.