◉ Syntax
The basic syntax for a qualified expression is as follows
typemark(aggregate)
The typemark is the type name. The aggregate is the data associated with this instance of the type. The data can specified using positional or the named association syntax. That all sounds a bit complicated, but it's similar to using a constructor for a object and will be obvious once you see some examples.
◉ Qualified Expressions with Record Types
Records with large numbers of columns can be a little clumsy to work with. Qualified expressions can simplify code in some circumstances.
The following example shows three ways to populate a record in Oracle 18c. The first method, available in previous releases, involves a direct assignment to each column in the record variable. The second method uses a qualified expression where the aggregate uses positional notation. The third example uses a qualified expression where the aggregate uses the named association syntax.
DECLARE
TYPE t_rec IS RECORD (
id NUMBER,
val1 VARCHAR2(10),
val2 VARCHAR2(10),
val3 VARCHAR2(10),
val4 VARCHAR2(10),
val5 VARCHAR2(10),
val6 VARCHAR2(10),
val7 VARCHAR2(10),
val8 VARCHAR2(10),
val9 VARCHAR2(10)
);
l_rec t_rec;
BEGIN
-- Pre-18c - Direct assignment to record columns.
l_rec.id := 1;
l_rec.val1 := 'ONE';
l_rec.val2 := 'TWO';
l_rec.val3 := 'THREE';
l_rec.val4 := 'FOUR';
l_rec.val5 := 'FIVE';
l_rec.val6 := 'SIX';
l_rec.val7 := 'SEVEN';
l_rec.val8 := 'EIGHT';
l_rec.val9 := 'NINE';
-- 18c - Qualified expression using position notation.
l_rec := t_rec(1, 'ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX', 'SEVEN', 'EIGHT', 'NINE');
-- 18c - Qualified expression using named association.
l_rec := t_rec(id => 1,
val1 => 'ONE',
val2 => 'TWO',
val3 => 'THREE',
val4 => 'FOUR',
val5 => 'FIVE',
val6 => 'SIX',
val7 => 'SEVEN',
val8 => 'EIGHT',
val9 => 'NINE');
END;
/
The first and last examples show clearly which columns gets which values, but they take a bit more space. The qualified expression using position notation is more compact, but relies on you knowing the order of the columns. In this case it's easy as the type if declared directly above. It would be less obvious if the type were defines in a package specification.
Things look a little different if we are only dealing with a subset of the columns. In the following example the qualified expression using named association looks neater, but still similar to the direct assignment to the record columns.
DECLARE
TYPE t_rec IS RECORD (
id NUMBER,
val1 VARCHAR2(10),
val2 VARCHAR2(10),
val3 VARCHAR2(10),
val4 VARCHAR2(10),
val5 VARCHAR2(10),
val6 VARCHAR2(10),
val7 VARCHAR2(10),
val8 VARCHAR2(10),
val9 VARCHAR2(10)
);
l_rec t_rec;
BEGIN
-- Pre-18c - Direct assignment to record columns.
l_rec.id := 1;
l_rec.val1 := 'ONE';
l_rec.val9 := 'NINE';
-- 18c - Qualified expression using position notation.
l_rec := t_rec(1, 'ONE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'NINE');
-- 18c - Qualified expression using named association.
l_rec := t_rec(id => 1, val1 => 'ONE', val9 => 'NINE');
END;
/
The difference becomes more apparent when the same variable is used for multiple sparse records, each referencing different columns in the record. In the following example the same record variable is used twice for each method. In the first pass the val1 and val9 columns are set. In the second pass the val2 and val8 columns are set. After each assignment the values of the val1 and val9 columns are displayed. The qualified expressions represent a new instance of the record, so all the unused columns are blanked explicitly or implicitly. Without the qualified expression it is up to the developer to blank the previous values manually.
SET SERVEROUTPUT ON
DECLARE
TYPE t_rec IS RECORD (
id NUMBER,
val1 VARCHAR2(10),
val2 VARCHAR2(10),
val3 VARCHAR2(10),
val4 VARCHAR2(10),
val5 VARCHAR2(10),
val6 VARCHAR2(10),
val7 VARCHAR2(10),
val8 VARCHAR2(10),
val9 VARCHAR2(10)
);
l_rec t_rec;
BEGIN
-- Pre-18c - Direct assignment to record columns.
l_rec.id := 1;
l_rec.val1 := 'ONE';
l_rec.val9 := 'NINE';
DBMS_OUTPUT.put_line('(1) Record1 val1 = ' || l_rec.val1 || ' val9 = ' || l_rec.val9);
l_rec.id := 2;
l_rec.val2 := 'TWO';
l_rec.val8 := 'EIGHT';
DBMS_OUTPUT.put_line('(1) Record2 val1 = ' || l_rec.val1 || ' val9 = ' || l_rec.val9);
-- 18c - Qualified expression using position notation.
l_rec := t_rec(1, 'ONE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'NINE');
DBMS_OUTPUT.put_line('(2) Record1 val1 = ' || l_rec.val1 || ' val9 = ' || l_rec.val9);
l_rec := t_rec(1, NULL, 'TWO', NULL, NULL, NULL, NULL, NULL, 'EIGHT', NULL);
DBMS_OUTPUT.put_line('(2) Record2 val1 = ' || l_rec.val1 || ' val9 = ' || l_rec.val9);
-- 18c - Qualified expression using named association.
l_rec := t_rec(id => 1, val1 => 'ONE', val9 => 'NINE');
DBMS_OUTPUT.put_line('(3) Record1 val1 = ' || l_rec.val1 || ' val9 = ' || l_rec.val9);
l_rec := t_rec(id => 1, val2 => 'TWO', val8 => 'EIGHT');
DBMS_OUTPUT.put_line('(3) Record2 val1 = ' || l_rec.val1 || ' val9 = ' || l_rec.val9);
END;
/
(1) Record1 val1 = ONE val9 = NINE
(1) Record2 val1 = ONE val9 = NINE
(2) Record1 val1 = ONE val9 = NINE
(2) Record2 val1 = val9 =
(3) Record1 val1 = ONE val9 = NINE
(3) Record2 val1 = val9 =
PL/SQL procedure successfully completed.
SQL>
We can even use a qualified expression in the definition of a default value. In the following example a procedure accepts a record type as a parameter, which has a default value specified using a qualified expression.
DECLARE
TYPE t_rec IS RECORD (
id NUMBER,
val1 VARCHAR2(10),
val2 VARCHAR2(10)
);
PROCEDURE dummy (p_rec IN t_rec DEFAULT t_rec(id => 1, val1 => 'ONE')) AS
BEGIN
NULL;
END;
BEGIN
NULL;
END;
/
◉ Qualified Expressions with Associative Arrays
When dealing with associative arrays we have the option of assigning values to the individual elements of the associative array, or creating a new associative array using a qualified expression. The following example uses a PLS_INTEGER as the index of the associative array.
DECLARE
TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
l_tab t_tab;
BEGIN
-- Pre-18c - Direct assignment to elements of the collection.
l_tab(1) := 'ONE';
l_tab(2) := 'TWO';
l_tab(3) := 'THREE';
-- 18c - Qualified expression using named association.
l_tab := t_tab(1 => 'ONE',
2 => 'TWO',
3 => 'THREE');
END;
/
This example uses a VARCHAR2 as the index of the associative array.
DECLARE
TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
l_tab t_tab;
BEGIN
-- Pre-18c - Direct assignment to record columns.
l_tab('IND1') := 'ONE';
l_tab('IND2') := 'TWO';
l_tab('IND3') := 'THREE';
-- 18c - Qualified expression using named association.
l_tab := t_tab('IND1' => 'ONE',
'IND2' => 'TWO',
'IND3' => 'THREE');
END;
/
Remember, the qualified expression creates a new instance of the associative array, so any previously defined elements are removed. In this example we create an associative array with three elements, then immediately assign a two element associative array. If we try to reference the element with index 2 we get a NO_DATA_FOUND exception.
SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
l_tab t_tab;
BEGIN
-- 18c - Qualified expression using named association.
l_tab := t_tab(1 => 'ONE',
2 => 'TWO',
3 => 'THREE');
l_tab := t_tab(1 => 'ONE',
3 => 'THREE');
DBMS_OUTPUT.put_line('2=' || l_tab(2));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('I knew this would cause a NDF error!');
END;
/
I knew this would cause a NDF error!
PL/SQL procedure successfully completed.
SQL>
In the following example a procedure accepts an associative array as a parameter, which has a default value specified using a qualified expression.
DECLARE
TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
PROCEDURE dummy (p_tab IN t_tab DEFAULT t_tab(1 => 'ONE', 2 => 'TWO',3 => 'THREE')) AS
BEGIN
NULL;
END;
BEGIN
NULL;
END;
/
Posts
0 comments:
Post a Comment