The federated Array was formerly called the Pl/sql table. Federated arrays cannot be used in tables and can only be used as a structural body for programming. Federated arrays can only be accessed in Pl/sql.
It is important to note that some of the key issues associated with the federated array are significant. These issues allow us to introduce their usage in a number of special ways. These issues include:
Federated arrays do not require initialization or constructor syntax. Until they are assigned, there is no need to allocate storage space specifically for them, and there is no need to use the Extend method of the collection API.
In Oracle 10G, as well as in previous versions of Oracle 10G, you can use a combined array of numeric indices. In addition, in Oracle 10G, a variable-length string with uniqueness can also be used as the index of a federated array.
You can use any integer as the index of a federated array, which means that the index of the federated array can be any positive, negative, or 0.
You can explicitly convert the return value of an equivalent%rowtype, record type, and object type to a struct of a federated array.
A federated array is the key to using a forall statement or a bulk collect clause, while the latter allows batch conversions from the database to the programming unit.
In a database that uses globalization settings, such as Nls_comp or nls_sort initialization parameters, a string is used as a federated array index, requiring special processing.
1. Defines a federated array and a program structure for use as a pl/sql
There are two types of syntax for defining a federated array in the Pl/sql language:
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 indexes for federated arrays. Pls_integer in ORACLE 10G what Binary_integer types are unrestricted data types, both of which are mapped to call specifications in C + +, C #, and Java.
The maximum length of a variable long string is 4,000 characters.
Another syntax for defining a federated array is:
CREATE OR REPLACE TYPE type_name
As TABLE of Element_type [not NULL]
INDEX by Key_type;
The Key_type allows us to use VARCHAR2, string, or long types. When you use VARCHAR2 and string, you need to define the size. When you use a long type, you do not need to define the size because it is defined by defining varchar (32760).
Federated arrays do not need to be initialized or constructor syntax. This is where the two other types of collections (Varrays and nested tables) are fundamentally different.
If you construct a federated array as follows, a PLS-00222 exception is thrown.
Copy Code code 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 constructor of an object can be used entirely as a function. Other collection types, such as varrays and nested tables, are object types that explicitly define the constructor. The federated array is just a struct, not an object type. Therefore, it cannot explicitly create constructors or call constructors.
2. Initialization of federated arrays
As we've already said, we can construct a federated array of numbers or variable-length strings with unique characters as indexes. Numeric indices, such as integers, can be positive integers, negative integers, and 0 values. Variable-length strings for uniqueness can be VARCHAR2, string, or Long data types.
1) with numbers as the combined array index
The following example shows a procedure to assign a value to an element in a federated array that is numerically indexed, and the example demonstrates the process of transferring varray content to a federated array.
Copy Code code as follows:
--Define a varray of twelve strings.
TYPE Months_varray is Varray (a) of STRING (9 CHAR);
--Define an associative array of strings.
TYPE calendar_table is Table of VARCHAR2 (9 CHAR)
INDEX by Binary_integer;
--Construct a varray.
Month Months_varray: =
Months_varray (' January ', ' February ', ' March ')
, ' April ', ' 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. A.. Month. Last LOOP
--Initialize a null associative array element.
Calendar (i): = ';
--the Print an indexed element from the associative array.
Dbms_output. Put_Line (
' Index [' | | i| | '] is [' | | Calendar (i) | | '];
--Assign The numeric index valued varray element
--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. A.. Calendar. Last LOOP
--the 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 loop, assign a null value to the calendar variable of the union array type, using an index value that is equal to the month index of the Varray type. This is the only way to allocate space for a federated array.
2) as a combined array index with a unique string
As shown in the following example:
Copy Code code as follows:
--Define a varray of twelve variable length strings.
TYPE Months_varray is Varray (a) 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);
--Construct a varray.
Month Months_varray: =
Months_varray (' January ', ' February ', ' March ')
, ' April ', ' 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. A.. Month. Last LOOP
--Assign The numeric index valued varray element
--an equal index valued associative array element.
Calendar (month (i)): = '; I.;
--the 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. A.. Calendar. Last LOOP
--the Print an indexed element from the associative array.
Dbms_output. Put_Line (
' Index [' | | i| | '] is [' | | Calendar (i) | | '];
End LOOP;
End IF;
End;
Running the above code will cause an error. Ora-06502:pl/sql:numeric or value error:character to number convertion error. Initialization in the first for-loop is not a problem. But in the second For-loop loop, the program tries to pass a Non-numeric value to the counter variable. In the above program, this counter variable is i. The data type of the counter variable is defined as the Pls_integer type. As a result, you cannot assign an integer variable to the index value of the entire variable length string-because the variable length string is not an integer. In this way, a type conversion error ORA-06502 is naturally caused. The example raises an error because when the union array member is initialized, the counter variable is converted to the VARCHAR2 type, and when the Union array is read, the counter type is also converted to an integer type.
This actually raises a new question for us. Non-numeric index values require us to know exactly how the index starts and how the index is incremented. This tool is provided by the in-next method of the collection API.
As shown in the following example:
Copy Code code as follows:
--Define variables to traverse a 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 (a) 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);
--Construct a varray.
Month Months_varray: =
Months_varray (' January ', ' February ', ' March ')
, ' April ', ' 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. A.. Month. Last LOOP
--Assign The numeric index valued varray element
--an equal index valued associative array element.
Calendar (month (i)): = To_char (i);
--the 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 in the loop.
IF i = 1 THEN
--Assign The character index to a variable.
Current: = Calendar. A;
--use the derived index to find the next index.
element: = Calendar (current);
ELSE
--Check If Next index value exists.
IF Calendar. NEXT (current) are 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;
--the Print an indexed element from the associative array.
Dbms_output. Put_Line (
' Index [' | | current| | '] is [' | | element| | '];
End LOOP;
End IF;
End;
3. Using associative arrays with bulk collect and ForAll
Using bulk collect and forall stomach we opened the door to eliminate row-level processing. You can use bulk collect to get a recordset stored in a federated array or nested table. 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 would be too many parsing or value-taking processes.
You should remember that row-level processing actually uses%rowtype and%type. The former can be mapped directly to the record type. BULK collect can assign a collection of values of%rowtype or%type types as a collection of federated arrays or nested tables. ForAll provides a way to transfer content from a federated array or a nested table to a database object.
federated arrays and nested table collection types can be used in conjunction with bulk collect and forall. When you use nested tables, you need to construct a nested table as a collection of empty elements. BULK collect explicitly allocates storage space for nested tables. There is no need to construct a federated array, as long as a batch assignment is available. Similarly, federated arrays and nested tables can be the source structure of the SQL command forall.
The following example shows:
Copy Code code 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 todo 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
--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 and last two records
For I in Number_list. A.. Number_list. Last LOOP
--Print only the two.
IF I <= 2 OR i >= 9999 THEN
--the Print an indexed element from the associative array.
Dbms_output. Put_Line (' number [' | | Number_list (i) | | | '];
End IF;
End LOOP;
End;
The order by is used in the bulk collect clause to ensure that the resulting results are sorted in ascending numbers. If elements are not sorted, they are obtained in a random order, rather than in their numerical order.
