Description\n-----------\n\nThe DECLARE TYPE declaration specifies user-defined data types that must be compatible with Oracle within stored procedures and anonymous blocks. It provides Oracle-compatible type declarations, such as record types, associative arrays, and REF CURSOR types, which allows for more flexible data handling and better compatibility with Oracle PL/SQL.\n\nAssociative arrays (INDEX BY tables) offer an in-memory key-value structure for quick data storage and effective lookups. REF CURSOR types allow for the creation of cursor variables that refer to query result sets and can be passed between program blocks.\n\nThe general syntax for defining associative array types is as follows:\n\n``sql\nDECLARE\n   TYPE type_name TABLE OF rec_type_name INDEX BY idx_type_name\n`\n\n type_name supports explicit and anchored data types (for instance, t1.col1%TYPE).\n rec_type_name supports scalar and record data types.\n The INDEX BY clause defines the key type using idx_type_name, which supports integer and string data types.\n\nAssociative Arrays\n\nIn Oracle, associative arrays (called index-by tables) are sparse collections of elements indexed by keys, which can be integers or strings.\n\nHere’s an example of how to declare an associative array in Oracle:\n\n`sql\nDECLARE\n  TYPE array_t IS TABLE OF VARCHAR2(64) INDEX BY PLS_INTEGER;\n  array array_t;\nBEGIN\n  array(1) := 'Hello';\n  array(2) := 'World';\n  DBMS_OUTPUT.PUT_LINE(array(1));\nEND;\n`\n\nMethods\n\nAssociative arrays support the following methods:\n\n FIRST — a function that returns the first key\n LAST — a function that returns the last key\n NEXT — a function that returns the key after the given one\n PRIOR — a function that returns the key before the given one\n COUNT — a function that returns the number of elements\n EXISTS — a function that returns TRUE if the key exists\n DELETE — a procedure that removes a specific key, or clears the array\n\nWhile the MariaDB implementation is largely aligned with Oracle’s implementation, there are a few differences:\n\n Only literals as keys in the constructor: When using constructors, keys must be literals — Oracle allows expressions.\n Collation control: Instead of NLS_SORT or NLS_COMP, MariaDB uses the SQL-standard COLLATE` clause.\n* No nested associative arrays: Arrays of arrays are not supported.\n\nThese differences are largely rooted in architectural constraints — MariaDB is aiming at staying as close to Oracle semantics as possible while maintaining performance and predictability.\n\nExamples\n--------\n\nDECLARE\n  TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);\n  salary_list salary;\n  name VARCHAR2(20);\nBEGIN\n  salary_list('Rajnisj') := 62000;\n  salary_list('James') := 78000;\n  name:= salary_list.FIRST;\n  WHILE name IS NOT NULL\n  LOOP\n    dbms_output.put_line(name || ' ' || TO_CHAR(salary_list(name)));\n    name:= salary_list.NEXT(name);\n  END LOOP;\nEND;\n/\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/declare-type