Oracle Learning (13) Collection

The collection of Pl/sql languages is similar to arrays and is a necessary structure for managing multiple rows of data. A set is a list that may be ordered or unordered. The Pl/sql collection types are: union arrays, nested tables, and variable arrays three kinds.

1. Combined array

A federated array is similar to an array in the C language.

(1) The syntax format is as follows:

Type TypeName is  
table of arraytype index by Binary_integer;

Where TypeName represents the type name of the new type, Arrowtype represents the type of the union array to be defined.

Assuming that there is a table T_module (name varchar (20)), we can set up the following union array for its name field:

Type modulename is table of T_module.name%type index by Binary_integer;

(2) Element assignment

The sample code is as follows:

Declare
    type NameArray is  
    table of varchar (a) index by Binary_integer;/* Defines a federated array namearrow*/  
    na namearray; * Declares a variable of the Union array namearrow
    /Begin NA (1): = ' Hello ';/* give the federated array the value of  
    /NA (2): = ' world ';/* Assign value to the Federated array  
    /NA (5): = ' Oracle '; /* Give the federated array the value of * *  
    dbms_output.put_line (NA (1) | | na (2));  
End

Attention:

A. The elements in a federated array are not arranged in a particular order, and the subscript of the elements is also unordered, so Namearrow ( -100): = ' Hello ' is also possible.

The B.index data type is Binary_integer type, so the index range is -214483647~+214483647.

C. An error occurs when you call an element that does not exist.

2. Nested table

The declaration of a nested table and the declaration of a federated array are very similar. The syntax format is as follows:

Type TypeName is table of arraytype [NOT NULL]

The only difference between the declaration of a nested table and the federated array is that there is no index by Binary_integer clause.

(1) Initialization of nested tables

The initialization of the nested table and the initialization of the federated array are different. After the union array is declared, then declare a variable for the Union array, and if you do not assign a value to the variable at this time, then the associated array of the variable is empty, and you can add elements to the federated array in subsequent statements, and if you do not initialize the nested table variable when you declare it, The nested table is automatically initialized to null and is read-only. If you continue to add elements to the nested table in subsequent code, you will get an error, but you can modify the value of the element that already exists.

The following is an example of an initialization of a nested table:

Declare
    type NameArray is table of varchar (a);  
    Na namearray: = NameArray (' Zhangsan ', ' LiSi ', ' Wangwu '); /* Initialize the nested table  
    /begin for
        Nameindex in 1..3 loop  
            dbms_output.put_line (Na (nameindex))  
        at the time of Declaration; End Loop;  
    End