Combined arrays are previously called PLSQL tables. Union arrays cannot be used in tables. They can only be used as structures for programming. Union Arrays can only be accessed in PLSQL
A union array is previously called a PL/SQL table. Union arrays cannot be used in tables. They can only be used as structures for programming. Union Arrays can only be accessed in PL/SQL
A union array is previously called a PL/SQL table. Union arrays cannot be used in tables. They can only be used as structures for programming. Union Arrays can only be accessed in PL/SQL.
It is very important to note some key issues brought about by the Union array. We need to take some special methods to introduce their usage. These problems include:
No initialization or constructor syntax is required for federated arrays. Before assigning values to them, you do not need to allocate storage space for them, and you do not need to use the extend method of the set api.
In ORACLE 10 GB and earlier than 10 Gb, you can use a digital index to join arrays. In addition, you can use a unique variable-length string as the index of the Union array in ORACLE 10 Gb.
Any integer can be used as the index of the Union array, which means that the index of the Union array can be any positive number, negative number, or 0.
You can explicitly convert the equivalent return values of % ROWTYPE, record type, and object type to the struct of the Union array.
Federated arrays are the key to using the FORALL statement or bulk collect clause, which allows batch conversions from databases to program design units.
When using global settings, such as NLS_COMP or NLS_SORT, to use strings as the Union array index, we need to perform special processing.
1. Define the Union array and program struct used as PL/SQL
In PL/SQL, there are two syntaxes for defining Union Arrays:
Create or replace type type_name
As table of element_type [not null]
Index by [PLS_INTEGER | BINARY_INTEGER | VARCHAR2 (size)];
You can use positive, negative, or 0 values as the index of the Union array. The PLS_INTEGER and BINARY_INTEGER types in ORACLE 10 Gb are unrestricted data types, which are mapped to the call specifications of C/C ++, C #, and JAVA.
A variable-length string can contain a maximum of 4000 characters.
The syntax for defining the Union array is as follows:
Create or replace type type_name
As table of element_type [not null]
Index by key_type;
The key_type allows us to use the VARCHAR2, STRING, or LONG type. When VARCHAR2 and STRING are used, the size must be defined. When the LONG type is used, you do not need to define the size because it is defined by defining VARCHAR (32760.
No initialization or constructor syntax is required for federated arrays. This is essentially different from the other two set types (VARRAYS and nested tables.
If you construct a union array like below, a PLS-00222 exception is thrown.
The Code is as follows:
-- Define an associative array of strings.
TYPE card_table is table of VARCHAR2 (5 CHAR)
Index by BINARY_INTEGER;
-- And attempt to construct an associative array.
Cards CARD_TABLE: = card_table ('A', 'B', 'C ');
BEGIN
NULL;
END;
In the previous introduction, we know that the object constructor can be used as a function. Other set types, such as VARRAYS and nested tables, are the object types explicitly defining constructors. The Union array is only a struct, not an object type. Therefore, it cannot create constructors explicitly or call constructors.
2. Joint array Initialization
As mentioned above, we can use a number or a unique variable-length string as an index to construct a union array. For example, if the index is an integer, it can be a positive integer, a negative integer, or a 0 value. The unique variable-length STRING can be of the VARCHAR2, STRING, or LONG data type.
1) use numbers as the combined array index
The following example presents a process of assigning values to elements in the Union array indexed by numbers. This example demonstrates the process of transferring the content of VARRAY to the Union array.
The Code is as follows:
-- Define a varray of twelve strings.
TYPE months_varray is varray (12) of string (9 CHAR );
-- Define an associative array of strings.
TYPE calendar_table is table of VARCHAR2 (9 CHAR)
Index by BINARY_INTEGER;
-- And construct a varray.
Month MONTHS_VARRAY: =
Months_varray ('january ', 'february', 'march'
, '10000l', 'may', 'june'
, 'July', 'August ', 'September'
, 'October ', 'november', 'december ');
-- An associative array variable.
Calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar. COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE ('assignment loop :');
DBMS_OUTPUT.PUT_LINE ('----------------');
-- Loop through all the varray elements.
FOR I IN month. FIRST... month. LAST LOOP
-- Initialize a null associative array element.
Calendar (I): = '';
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE (
'Index ['| I |'] is ['| calendar (I) |'] ');
-- Assign the numeric index valued varray element
-- To an equal index valued associative array element.
Calendar (I): = month (I );
End loop;
-- Print a title
DBMS_OUTPUT.PUT (CHR (10 ));
DBMS_OUTPUT.PUT_LINE ('post-assignment loop :');
DBMS_OUTPUT.PUT_LINE ('---------------------');
-- Loop through all the associative array elements.
FOR I IN calendar. FIRST... calendar. LAST LOOP
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE (
'Index ['| I |'] is ['| calendar (I) |'] ');
End loop;
End if;
END;
/
In the first FOR-LOOP, assign a null value to the calendar variable of the combined array type with an index value equal to the month index of the VARRAY type. This is the only way to allocate space for the Union array.
2) use a unique string as the Union array index
For example:
The Code is as follows:
-- Define a varray of twelve variable length strings.
TYPE months_varray is varray (12) of string (9 CHAR );
-- Define an associative array of variable length strings.
TYPE calendar_table is table of VARCHAR2 (9 CHAR)
Index by VARCHAR2 (9 CHAR );
-- And construct a varray.
Month MONTHS_VARRAY: =
Months_varray ('january ', 'february', 'march'
, '10000l', 'may', 'june'
, 'July', 'August ', 'September'
, 'October ', 'november', 'december ');
-- An associative array variable.
Calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar. COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE ('assignment loop :');
DBMS_OUTPUT.PUT_LINE ('----------------');
-- Loop through all the varray elements.
FOR I IN month. FIRST... month. LAST LOOP
-- Assign the numeric index valued varray element
-- To an equal index valued associative array element.
Calendar (month (I): = ''; -- I;
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE (
'Index ['| month (I) |'] is ['| I |'] ');
End loop;
-- Print a title
DBMS_OUTPUT.PUT (CHR (10 ));
DBMS_OUTPUT.PUT_LINE ('post-assignment loop :');
DBMS_OUTPUT.PUT_LINE ('---------------------');
-- Loop through all the associative array elements.
FOR I IN calendar. FIRST... calendar. LAST LOOP
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE (
'Index ['| I |'] is ['| calendar (I) |'] ');
End loop;
End if;
END;
An error occurs when running the above Code. ORA-06502: PL/SQL: numeric or value error: character to number convertion error. There is no problem with initialization in the first FOR-LOOP. However, in the second FOR-LOOP, the program tries to pass a non-numeric value to the counter variable. In the above program, the counter variable is I. The data type of the counter variable is defined as PLS_INTEGER. Therefore, you cannot assign the index value of the entire variable-length string to an integer variable because the variable-length string is not an integer. As a result, the type conversion error ORA-06502 is naturally raised. This example causes an error because when initializing the Union array members, the counter variable is converted to the VARCHAR2 type. when reading the Union array, convert the Counter type to the INTEGER type.
This actually raises a new question for us. For non-numeric index values, we need to clearly know the index start value and the index increment method. The FIRST and NEXT methods of the Set API provide this tool.
For example:
The Code is as follows:
-- Define variables to traverse an associative array that
-- Uses variable length strings for index values.
Current VARCHAR2 (9 CHAR );
Element INTEGER;
-- Define a varray of twelve variable length strings.
TYPE months_varray is varray (12) of string (9 CHAR );
-- Define an associative array of variable length strings.
TYPE calendar_table is table of VARCHAR2 (9 CHAR)
Index by VARCHAR2 (9 CHAR );
-- And construct a varray.
Month MONTHS_VARRAY: =
Months_varray ('january ', 'february', 'march'
, '10000l', 'may', 'june'
, 'July', 'August ', 'September'
, 'October ', 'november', 'december ');
-- An associative array variable.
Calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar. COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE ('assignment loop :');
DBMS_OUTPUT.PUT_LINE ('----------------');
-- Loop through all the varray elements.
FOR I IN month. FIRST... month. LAST LOOP
-- Assign the numeric index valued varray element
-- To an equal index valued associative array element.
Calendar (month (I): = TO_CHAR (I );
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE (
'Index ['| month (I) |'] is ['| I |'] ');
End loop;
-- Print a title
DBMS_OUTPUT.PUT (CHR (10 ));
DBMS_OUTPUT.PUT_LINE ('post-assignment loop :');
DBMS_OUTPUT.PUT_LINE ('---------------------');
-- Loop through all the associative array elements.
FOR I IN 1 .. calendar. COUNT LOOP
-- Check if the first element in the loop.
IF I = 1 THEN
-- Assign the first character index to a variable.
Current: = calendar. FIRST;
-- Use the derived index to find the next index.
Element: = calendar (current );
ELSE
-- Check if next index value exists.
IF calendar. NEXT (current) IS NOT NULL THEN
-- Assign the character index to a variable.
Current: = calendar. NEXT (current );
-- Use the derived index to find the next index.
Element: = calendar (current );
ELSE
-- Exit loop since last index value is read.
EXIT;
End if;
End if;
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE (
'Index ['| current |'] is ['| element |'] ');
End loop;
End if;
END;
3. Combined array with bulk collect and FORALL
With bulk collect and FORALL, we opened the door to eliminating row-level processing. You can use bulk collect to obtain the record set stored in the Union array or nested table. You can use FORALL to send DML statements in batches. FORALL can insert, update, and delete data. These methods reduce the number of times the Context Environment is switched back and forth between the PL/SQL engine and the SQL engine. Without these methods, there will be too many parsing or value processes.
You should remember that row-level processing actually uses % ROWTYPE and % TYPE. The former can be directly mapped to the record type. Bulk collect can assign values to a set of % ROWTYPE or % TYPE values as a Union array or a set of nested tables. FORALL provides a way to transfer the contents of a federated array or nested table to a database object.
The Union array and nested table set types can be used with bulk collect and FORALL. When using a nested table, you need to construct the nested table into a set of null elements. Bulk collect explicitly allocates the storage space of the nested table. You do not need to construct the Union array. You only need to assign values to one batch. Similarly, the Union array and nested table can both be used as the source structure of the SQL command FORALL.
Example:
The Code is as follows:
-- Create a table for the example.
Create table bulk_numbers
(Number_id NUMBER NOT NULL
, CONSTRAINT number_id_pk primary key (number_id ));
-- Define an associative array of integers.
TYPE number_table is table of bulk_numbers.number_id % TYPE
Index by BINARY_INTEGER;
-- Define a variable of the associative array type.
Number_list NUMBER_TABLE;
BEGIN
-- Loop from 1 to a million and increment associative array.
FOR I IN 1 .. 10000 LOOP
-- Assign number value.
Number_list (I): = I;
End loop;
-- Loop through all to do a bulk insert.
FORALL I IN 1 .. number_list.COUNT
INSERT
INTO bulk_numbers
VALUES (number_list (I ));
-- Commit records.
COMMIT;
END;
-- Use a bulk collect to retrieve a table into
-- Associative array.
-- Define an associative array of integers.
TYPE number_table is table of bulk_numbers.number_id % TYPE
Index by BINARY_INTEGER;
-- Define a variable of the associative array type.
Number_list NUMBER_TABLE;
BEGIN
-- Check if calendar has no elements.
SELECT number_id
BULK COLLECT
INTO number_list
From bulk_numbers;
-- Print a title
DBMS_OUTPUT.PUT_LINE ('bulk Collected :');
DBMS_OUTPUT.PUT_LINE ('---------------');
-- Loop through to print elements.
-- Print only the first two records and the last two records
FOR I IN number_list.FIRST .. number_list.LAST LOOP
-- Print only the first and last two.
IF I <= 2 OR I> = 9999 THEN
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE ('Number ['| number_list (I) |'] ');
End if;
End loop;
END;
Order by is used in the bulk collect clause to ensure that the results are listed in ascending ORDER of numbers. If you do not sort the elements, you will find that they are obtained in random order, rather than in numerical order.
