Filling a collection or record used to take one assignment per element or field. A qualified expression builds the whole value in one expression: the type name followed by the values, by position, by name, or by index. For associative arrays it can even compute the elements with an iterator.
Code for This Guide
The main examples are in the examples/collections folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
type_name(value, ...) -- positional type_name(field => value, ...) -- record fields by name type_name(index => value, ...) -- associative array elements by index type_name(for i in ... [when ...] index i => value) -- computed elements
Associative Arrays in One Expression
Fares by cabin, seats at chosen indexes, and the even rows computed by an iterator with WHEN:
Example:
declare
type t_fares is table of number index by varchar2(8);
type t_seats is table of varchar2(4) index by pls_integer;
v_fares t_fares := t_fares('BUSINESS' => 2290, 'ECONOMY' => 641);
v_seats t_seats := t_seats(1 => '1A', 2 => '1C', 10 => '12F');
v_even t_seats := t_seats(for i in 1 .. 6 when mod(i, 2) = 0 index i => i || 'A');
begin
dbms_output.put_line('Business fare ' || v_fares('BUSINESS'));
dbms_output.put_line('Seats ' || v_seats.count || ', first ' || v_seats(v_seats.first)
|| ', last ' || v_seats(v_seats.last));
dbms_output.put_line('Even rows: ' || v_even(2) || ' ' || v_even(4) || ' ' || v_even(6));
end;
/Output:
Business fare 2290 Seats 3, first 1A, last 12F Even rows: 2A 4A 6A PL/SQL procedure successfully completed.
The iterator for i in 1 .. 6 when mod(i, 2) = 0 index i => i || 'A' builds the elements 2A, 4A, and 6A at indexes 2, 4, and 6.
Records and Collections of Records
A record type's name is a constructor too, with values by name or position, and qualified expressions nest.
Example:
declare
type t_fare is record (route_id number, cabin varchar2(8), fare number);
type t_fares is table of t_fare index by pls_integer;
v_one t_fare := t_fare(route_id => 1, cabin => 'ECONOMY', fare => 520);
v_list t_fares := t_fares(1 => t_fare(1, 'ECONOMY', 520),
2 => t_fare(1, 'BUSINESS', 2290));
begin
dbms_output.put_line(v_one.cabin || ' ' || v_one.fare);
for i, f in pairs of v_list loop
dbms_output.put_line(i || ': ' || f.cabin || ' ' || f.fare);
end loop;
end;
/Output:
ECONOMY 520 1: ECONOMY 520 2: BUSINESS 2290 PL/SQL procedure successfully completed.
One declaration builds a single fare record by name, and another an associative array of two records by index.
Things to Know
- Qualified expressions can be used anywhere a value of the type is expected: in declarations, assignments, and as arguments.
- Named notation for records makes code robust against changes in field order.
- For nested tables and varrays, the classic constructor type_name(v1, v2, ...) is the positional form.
Related Guides
Conclusion
Qualified expressions build records and collections in a single expression, with values by position, by name, or by index, and with iterators that compute associative array elements. Use them to initialize data structures clearly in one place.
