Qualified Expressions Overview

Qualified expressions improve program clarity and developer productivity by providing the ability to declare and define a complex value in a compact form where the value is needed.

A qualified expression combines expression elements to create values of almost any type. They are most useful for records, associative arrays, nested tables, and variable arrays .

Qualified expressions use an explicit type indication to provide the type of the qualified item. This explicit indication is known as a typemark.

Qualified expressions have this structure:

qualified_expression ::= empty_qualified_expression 
                      | simple_qualified_expression 
                      | aggregate_qualified_expression
typemark ::= type_name
type_name ::= identifier 
              | type_name . identifier
empty_qualified_expression ::= typemark ( )
simple_qualified_expression ::= typemark ( expr )
aggregate_qualified_expression ::= typemark ( aggregate )

aggregate ::= [ positional_choice_list ] [ explicit_choice_list ] [ others_choice ]

positional_choice_list ::= ( expr )+
                          | sequence_iterator_choice

sequence_iterator_choice ::= FOR iterator SEQUENCE => expr

explicit_choice_list ::= named_choice_list 
                       | indexed_choice_list
                       | iterator_choice
                       | index_iterator_choice

named_choice_list ::= identifier => expr [,]+

indexed_choice_list ::= expr => expr [,] +

iterator_choice ::= FOR iterator => expr

index_iterator_choice ::= FOR iterator INDEX expr => expr

others_choice ::= OTHERS => expr

See "qualified_expression ::=" for more information about the syntax and semantics.

Empty Qualified Expressions

An empty qualified expression has the form typemark ( ). For example, the expression T ( ) where T is a typemark, provides a new value as defined by the declaration of type T. In PL/SQL, all types define an initialization for their values, sometimes, it is simply NULL. When the typemark includes constraints, the value of the qualified expression is required to honor those constraints, or an exception is raised.

Simple Qualified Expressions

A simple qualified expression has the form typemark ( expr) where expr is an expression that produces a single value, not necessarily a scalar value.

Aggregate Qualified Expressions

An aggregate qualified expression has the form typemark ( aggregate). For example, given T is a typemark of a compound type, it looks like T(C1, C2, ..., Cn) where each of the C’s is a choice that describes some elements of type T.

A positional choice contains only an initializing expression expr. If an aggregate contains positional choices, they must appear before any other choices. Positional choices may only be used with structured types and lower bounded vector types.

A named choice has the form N1 | N2 | ... | Nn => expr where there may be only one name and where the names Ni are field names from the structured type T. Named choices may only be used with structured types.

An indexed choice has the form I => expr where index I is a numeric or varchar2 expression. Indexed choices may only be used with vector types.

An iterator choice has the form F..L =>expr where there where F and L are each numeric expressions. The bounds follow the same rules as used for the bounds of a for loop. Iterator choices may only be used with vector types and they may not be used with unbounded vector types that have a varchar2 index type.

Indexed and iterator choices may be intermixed freely, including by alternation as in I1 | F2..L2 | .. | In => expr.

An others choice has the form OTHERS => expr and must appear last if it appears at all.. An others choice may only be used with structured types and bounded vector types.

Positional choices must precede explicit choices which must precede the others choice if it appears.

An alternation index or iterator choice has the form I1 | F2..L2 | ... | In => expr and has the same effect as the collection of single index and iterator choices I1 => expr, F2..L2 => expr, ..., In => expr.

This example shows different methods to assign values to a record with the same results.


DECLARE
  TYPE t_rec IS RECORD (
    id   NUMBER,
    val1 VARCHAR2(10),
    val2 VARCHAR2(10),
    val3 VARCHAR2(10)  );

  l_rec t_rec;
BEGIN
  -- Method 1: Direct assignment to record fields (not using aggregate).
  l_rec.id   := 1;
  l_rec.val1 := 'ONE';
  l_rec.val2 := 'TWO';
  l_rec.val3 := 'THREE';

  -- Method 2 : Using aggregate qualified expression positional association
  l_rec := t_rec(1, 'ONE', 'TWO', 'THREE');
 
  -- Method 3 : Using aggregate qualified expression named association
  l_rec := t_rec(id   => 1, val1 => 'ONE', val2 => 'TWO', val3 => 'THREE');
END;
/

Iterator Choice Association

The iterator choice association uses the iterand as an index.

For each iterand value, the expression is evaluated and added to the collection using the iterand value as the index.

For each value of iterand generated by the iteration controls:

Evaluate the expression producing an expression value.
If appropriate for the collection type, extend the collection to the index specified by the iterand.
Add the expression value to the collection at the index specified by the iterand value.

Example 6-8 Iterator Choice Association in Qualified Expressions

This example creates a vector of the first N fibonacci numbers.


result := vec_t (FOR i IN 1..n => fib(i));

This example creates a vector of the first N even numbers.


result := vec_t (FOR i IN 1..n => 2*i);

Index Iterator Choice Association

The index iterator choice association provides an index expression along with the value expression.

For each iterand value, the index expression and value expression are evaluated. Then the expanded value is added to the collection using the expanded index.

For each value of iterand generated by the iteration controls:

Evaluate the expression producing an expression value.
Evaluate the index expression producing an index value.
If appropriate for the collection type, extend the collection to the index specified by the index value.
Add the expression value to the collection at the index specified by the index value.

Example 6-9 Index Iterator Choice Association in Qualified Expressions

This example creates a copy of vec with values incremented by N.


result := vec_t (FOR I,j IN PAIRS OF vec INDEX I => j+n);

This example creates a vector of the first N even numbers.


result := vec_t (FOR i IN 2..n BY 2 INDEX i/2 => i);

Sequence Iterator Choice Association

The sequence iterator choice association allows a sequence of values to be added to the end of a collection. In each case, the expressions specified may reference the iterands.

For each iterand value, the value expression is evaluated and added to the end of the collection.

For each value of iterand generated by the iteration controls:

Evaluate the expression producing an expression value.
If appropriate for the collection type, extend the collection by one.
Add the expression value to the collection at its end.

Example 6-10 Sequence Iterator Choice Association in Qualified Expressions

This example concatenates vectors v1 and reversed v2 together.


result := vec_t (FOR v IN VALUES OF v1, 
                          REVERSE VALUES OF v2
                   SEQUENCE => v);

This example creates a vector of the prime numbers less than or equal to N.


result := vec_t (FOR i IN 1..n WHEN is_prime(i)
                   SEQUENCE => i);

Example 6-11 Assigning Values to Associative Array Type Variables Using Qualified Expressions

This example uses a function to display the values of a table of BOOLEAN.

Live SQL:

You can view and run this example on Oracle Live SQL at "18c Assigning Values to Associative Array Type Variables Using Qualified Expressions"

CREATE FUNCTION print_bool (v IN BOOLEAN)
  RETURN VARCHAR2
IS
  v_rtn VARCHAR2(10);
BEGIN
  CASE v
  WHEN TRUE THEN
    v_rtn := 'TRUE';
  WHEN FALSE THEN
    v_rtn := 'FALSE';
  ELSE
    v_rtn := 'NULL';
  END CASE;
  RETURN v_rtn;
END print_bool;
/

The variable v_aa1 is initialized using index key-value pairs.


DECLARE
  TYPE t_aa IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
  v_aa1 t_aa := t_aa(1=>FALSE,
                     2=>TRUE,
                     3=>NULL);
BEGIN
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(1)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(2)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(3)));
END;
/

Result:

FALSE
TRUE
NULL

Example 6-12 Assigning values to a RECORD Type Variables using Qualified Expressions

This example shows a record of values assigned using a qualified expression. The value for rec.a is assigned using the position notation, the value for rec.c uses the named association and rec.b is assigned a value of 2 since it is not defined by the position and named association, it falls in the other notation.

DECLARE 
  TYPE r IS RECORD(a PLS_INTEGER, b PLS_INTEGER, c NUMBER);  
  rec r;
BEGIN  
  rec := r(1, c => 3.0, OTHERS => 2);  
-- rec contains [ 1, 2, 3.0 ]
END;
/

Example 6-13 Assigning Values to a VARRAY Type using Qualified Expressions

In this example, the variable array vec contains [ 1, 3, 2, 3 ] .

DECLARE 
  TYPE v IS VARRAY(4) OF NUMBER;  
  vec v;
BEGIN 
  vec := v(1, 3 => 2, OTHERS => 3);  
END;
/