Details
-
Technical task
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
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
- blocks
-
MDEV-13139 CREATE PACKAGE: package-wide declarations
-
- Stalled
-
-
MDEV-20030 sql_mode="oracle" does not support FOR ALL ... BULK COLLECT
-
- Open
-
-
MDEV-34323 Oracle compatibility project 3
-
- Open
-
-
MDEV-34560 Qualified package data types
-
- Open
-
-
MDEV-34569 Oracle compatibility project 4
-
- Open
-
- is blocked by
-
MDEV-34317 DECLARE TYPE type_name IS RECORD (..) with scalar members in stored routines
-
- Closed
-
- is part of
-
MDEV-35973 Oracle Compatibility Project 1 for 2025
-
- In Progress
-
- relates to
-
MDEV-32670 VARRAY for scalar data types
-
- Open
-
-
MDEV-34648 Support Composite Variable, Associative Arrays, in Procedure Parameter out
-
- Open
-
-
MDEV-35980 Oracle: Nested tables for scalar elements
-
- Open
-
Changing the priority to Critical as there is a pull request:
https://github.com/MariaDB/server/pull/3797