MDEV-34319: DECLARE TYPE .. TABLE OF .. INDEX BY in stored routines

[iqbal-rsec]

trafficstars

• [x] The Jira issue number for this PR is: MDEV-34319

Description

This patch adds support for associative arrays in stored procedures for sql_mode=ORACLE. The syntax follows Oracle's PL/SQL syntax for associative arrays - TYPE assoc_array_t IS TABLE OF VARCHAR2(100) INDEX BY INTEGER; or TYPE assoc_array_t IS TABLE OF record_t INDEX BY VARCHAR2(100); where record_t is a record type.

The following functions were added for associative arrays:

• COUNT - Retrieve the number of elements within the arra
• EXISTS - Check whether given key exists in the array
• FIRST - Retrieve the first key in the array
• LAST - Retrieve the last key in the array
• PRIOR - Retrieve the key before the given key
• NEXT - Retrieve the key after the given key
• DELETE - Remove the element with the given key or remove all elements if no key is given

The arrays/elements can be initialized with the following methods:

• Constructor i.e. array:= assoc_array_t('key1'=>1, 'key2'=>2, 'key3'=>3)
• Assignment i.e. array(key):= record_t(1, 2)
• SELECT INTO i.e. SELECT x INTO array(key)

TODOs:

• Nested tables are not supported yet. i.e. TYPE assoc_array_t IS TABLE OF other_assoc_array_t INDEX BY INTEGER;
• Associative arrays comparisons are not supported yet.

Release Notes

N/A, but new kb page should be created for associative arrays.

How can this PR be tested?

mysql-test/mtr sp-assoc-array

Basing the PR against the correct MariaDB version

• [x] This is a new feature or a refactoring, and the PR is based against the main branch.
• [ ] This is a bug fix, and the PR is based against the earliest maintained branch in which the bug can be reproduced.

PR quality check

• [x] I checked the CODING_STANDARDS.md file and my PR conforms to this where appropriate.
• [x] For any trivial modifications to the PR, I am ok with the reviewer making the changes themselves.