How to manually initialize a collection of RECORDs in PL/SQL?
There is no "constructor" syntax for RECORDs, so you have to populate them like this:
declare
type a is record(a1 number, a2 number);
type b is table of a;
arr b := b();
begin
arr.extend(2);
arr(1).a1 := 1;
arr(1).a2 := 2;
arr(2).a1 := 3;
arr(2).a2 := 4;
end;
This works without objects, but you have to declare a constructor function for type 'a' values.
declare
type a is record(a1 number, a2 number);
type b is table of a;
arr b;
--Constructor for type a
function a_(a1 number, a2 number) return a is
r_a a;
begin
r_a.a1 := a1;
r_a.a2 := a2;
return(r_a);
end;
begin
arr := b(a_(1, 2), a_(3, 4), a_(5, 6), a_(7, 8));
for i in arr.first .. arr.last loop
dbms_output.put_line(arr(i).a1||', '||arr(i).a2);
end loop;
end;
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