How to manually initialize a collection of RECORDs in PL/SQL?

user_272864 0
Viewed 39004

guys. Here's a simple sample two-dimensional array in PL/SQL, which is working perfectly.

declare
  type a is table of number;
  type b is table of a;

  arr b := b(a(1, 2), a(3, 4));
begin
  for i in arr.first .. arr.last loop
    for j in arr(i).first .. arr(i).last loop
      dbms_output.put_line(arr(i) (j));
    end loop;
  end loop;
end;

What I need to do, is to create something similar for a table of RECORDS. Like this: 

 type a is record(a1 number, a2 number);
 type b is table of a;

The question is, can I manually initialize this kind of array, or it is supposed to be filled by bulk collects or similar? The same syntax as above doesn't seem to work, and I wasn't able to find any initialization sample in manuals.

oracle9i multidimensional-array plsql oracle arrays
3 Answers

Since release 18c Qualified Expressions provides an alternative way to define the values of complex data types. Quote:

Starting with Oracle Database Release 18c, any PL/SQL value can be provided by an expression (for example for a record or for an associative array) like a constructor provides an abstract datatype value. In PL/SQL, we use the terms "qualified expression" and "aggregate" rather than the SQL term "type constructor", but the functionality is the same.

Here's an working example:

declare 
    type a is record (a1 number, a2 number);
    type b is table of a index by varchar2 (16);
    arr b := b ('key1' => a (1, 2), 'key2' => a (3, 4)); 
begin 
    declare key varchar2 (16) := arr.first; begin 
    <<foreach>> loop
        dbms_output.put_line (arr(key).a1||','||arr (key).a2);
        key := arr.next (key);
        exit foreach when key is null;
    end loop; end;
end;
/
PL/SQL procedure successfully completed.

1,2
3,4
user_65710200
Related