1.8. Collections A collection is a data structure that acts like a list or a single-dimensional array. Collections are, in fact, the closest you can get to traditional arrays in the PL/SQL language. You can use collections to manage lists of information in your programs. 1.8.1. Types of Collections Oracle supports three different types of collections. While these different types have much in common, they also each have their own particular characteristics.
Associative arrays These are single-dimensional, unbounded, sparse collections of homogeneous elements that are available only in PL/SQL. They were called PL/SQL tables in PL/SQL 2 and index-by tables in Oracle8 Database and Oracle8i Database (because, when you declare such a collection, you explicitly state that they are "indexed by" the row number). In Oracle9i Database, the name was changed to associative arrays. The motivation for the name change was that starting with that release, the INDEX BY syntax could be used to "associate" or index contents by VARCHAR2 or PLS_INTEGER.
Nested tables These are also single-dimensional, unbounded collections of homogeneous elements. They are initially dense but can become sparse through deletions. Nested tables can be defined in both PL/SQL and the database (for example, as a column in a table). Nested tables are multisets, which means that there is no inherent order to the elements in a nested table.
VARRAYs Like the other two collection types, variable-sized arrays (VARRAYs) are also single-dimensional collections of homogeneous elements. However, they are always bounded and never sparse. When you define a type of VARRAY, you must also specify the maximum number of elements it can contain. Like nested tables, they can be used in PL/SQL and in the database. Unlike nested tables, when you store and retrieve a VARRAY, its element order is preserved.
1.8.2. Working with Collections This section provides relatively simple examples of each of the different types of collections in this section, with explanations of the major characteristics. 1.8.2.1. Using an associative array In the following example, I declare an associative array type and then a collection based on that type. I populate it with four rows of data and then iterate through the collection, displaying the strings in the collection. A more thorough explanation appears after the code. 1 DECLARE 2 TYPE list_of_names_t IS TABLE OF person.first_name%TYPE 3 INDEX BY PLS_INTEGER; 4 happyfamily list_of_names_t; 5 l_row PLS_INTEGER; 6 BEGIN 7 happyfamily (2020202020) := 'Eli'; 8 happyfamily (-15070) := 'Steven'; 9 happyfamily (-90900) := 'Chris'; 10 happyfamily (88) := 'Veva'; 11 12 l_row := happyfamily.FIRST; 13 14 WHILE (l_row IS NOT NULL) 15 LOOP 16 DBMS_OUTPUT.put_line (happyfamily (l_row)); 17 l_row := happyfamily.NEXT (l_row); 18 END LOOP; 19* END; SQL> / Chris Steven Veva Eli
Line(s) | Description |
|---|
23 | Declare the associative array TYPE, with its distinctive INDEX BY clause . A collection based on this type contains a list of strings, each of which can be as long as the first_name column in the person table. | 4 | Declare the happyfamily collection from the list_of_names_t type. | 910 | Populate the collection with four names. Notice that I can use virtually any integer value that I like. The row numbers don't have to be sequential in an associative array; they can even be negative! | 12 | Call the FIRST method (a function that is "attached" to the collection) to get the first or lowest defined row number in the collection. | 1418 | Use a WHILE loop to iterate through the contents of the collection, displaying each row. Line 17 shows the use of the NEXT method to move from the current defined row to the next defined row "skipping over" any gaps. |
1.8.2.2. Using a nested table In the following example, I first declare a nested table type as a schema-level type. In my PL/SQL block, I declare three nested tables based on that type. I put the names of everyone in my family into the happyfamily nested table. I put the names of my children in the children nested table. I then use the Oracle Database 10g set operator, MULTISET EXCEPT, to extract just the parents from the happyfamily nested table; finally, I display the names of the parents. A more thorough explanation appears after the code. REM Section A SQL> CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100); 2 / Type created. REM Section B SQL> 1 DECLARE 2 happyfamily list_of_names_t := list_of_names_t ( ); 3 children list_of_names_t := list_of_names_t ( ); 4 parents list_of_names_t := list_of_names_t ( ); 5 BEGIN 6 happyfamily.EXTEND (4); 7 happyfamily (1) := 'Eli'; 8 happyfamily (2) := 'Steven'; 9 happyfamily (3) := 'Chris'; 10 happyfamily (4) := 'Veva'; 11 12 children.EXTEND; 13 children (1) := 'Chris'; 14 children.EXTEND; 15 children (2) := 'Eli'; 16 17 parents := happyfamily MULTISET EXCEPT children; 18 19 FOR l_row IN parents.FIRST .. parents.LAST 20 LOOP 21 DBMS_OUTPUT.put_line (parents (l_row)); 22 END LOOP; 23* END; SQL> / Steven Veva
Line(s) | Description |
|---|
Section A | The CREATE TYPE statement creates a nested table type in the database itself. By taking this approach, I can declare nested tables in any PL/SQL block that has SELECT authority on the type. I can also declare columns in relational tables of this type. | 24 | Declare three different nested tables based on the schema-level type. Notice that in each case I also call a constructor function to initialize the nested table. This function always has the same name as the type and is created for us by Oracle. You must initialize a nested table before it can be used. | 6 | Call the EXTEND method to "make room" in my nested table for the members of my family. Here, in contrast to associative arrays, I must explicitly ask for a row in a nested table before I can place a value in that row. | 710 | Populate the happyfamily collection with our names. | 1215 | Populate the children collection. In this case, I extend a single row at a time. | 17 | To obtain the parents in this family, I simply take the children out of the happyfamily. This is transparently easy to do in releases from Oracle Database 10g onward, where we have high-level set operators like MULTISET EXCEPT (very similar to the SQL MINUS). | 1922 | Because I know that my parents collection is densely filled from the MULTISET EXCEPT operation, I can use the numeric FOR loop to iterate through the contents of the collection. This construct will raise a NO_DATA_FOUND exception if used with a sparse collection. |
1.8.2.3. Using a VARRAY In the following example, I demonstrate the use of VARRAYs as columns in a relational table. First, I declare two different schema-level VARRAY types. I then create a relational table, family, that has two VARRAY columns. Finally, in my PL/SQL code, I populate two local collections and then use them in an INSERT into the family table. A more thorough explanation appears after the code. REM Section A SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100); 2 / Type created. SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100); 2 / Type created. REM Section B SQL> CREATE TABLE family ( 2 surname VARCHAR2(1000) 3 , parent_names first_names_t 4 , children_names child_names_t 5 ); Table created. REM Section C SQL> 1 DECLARE 2 parents first_names_t := first_names_t ( ); 3 children child_names_t := child_names_t ( ); 4 BEGIN 5 parents.EXTEND (2); 6 parents (1) := 'Samuel'; 7 parents (2) := 'Charina'; 8 -- 9 children.EXTEND; 10 children (1) := 'Feather'; 11 12 -- 13 INSERT INTO family 14 (surname, parent_names, children_names 15 ) 16 VALUES ('Assurty', parents, children 17 ); 18 END; SQL> / PL/SQL procedure successfully completed. SQL> SELECT * FROM family 2 / SURNAME PARENT_NAMES CHILDREN_NAMES -------------------------------------------- Assurty FIRST_NAMES_T('Samuel', 'Charina') CHILD_NAMES_T('Feather')
Line(s) | Description |
|---|
Section A | Use CREATE TYPE statements to declare two different VARRAY types. Notice that with a VARRAY, I must specify the maximum length of the collection. Thus, my declarations in essence dictate a form of social policy: you can have at most two parents and at most one child. | Section B | Create a relational table, with three columns: a VARCHAR2 column for the surname of the family and two VARRAY columns, one for the parents and another for the children. | Section C, lines 23 | Declare two local VARRAYs based on the schema-level type. As with nested tables (and unlike with associative arrays), I must call the constructor function of the same name as the TYPE to initialize the structures. | 50 | Extend and populate the collections with the names of parents and then the single child. If I try to extend to a second row, Oracle will raise the ORA-06532: Subscript outside of limit error. | 1317 | Insert a row into the family table, simply providing the VARRAYs in the list of values for the table. Oracle certainly makes it easy for us to insert collections into a relational table! |
1.8.3. Collection Methods (Built-ins) PL/SQL offers a number of built-in functions and procedures, known as collection methods , that let you obtain information about and modify the contents of collections. The following collection methods are available:
COUNT function Returns the current number of elements in a collection.
DELETE procedure Removes one or more elements from the collection. Reduces COUNT if the element is not already DELETEd. With VARRAYS, you can only delete the entire contents of the collection.
EXISTS function Returns TRUE or FALSE to indicate whether the specified element exists.
EXTEND procedure Increases the number of elements in a nested table or VARRAY. Increases COUNT.
FIRST, LAST functions Return the smallest (FIRST) and largest (LAST) subscripts in use.
LIMIT function Returns the maximum number of elements allowed in a VARRAY.
PRIOR, NEXT functions Return the subscript immediately before (PRIOR) or after (NEXT) a specified subscript. You should always use PRIOR and NEXT to traverse a collection, especially if you are working with sparse (or potentially sparse) collections.
TRIM procedure Removes collection elements from the end of the collection (highest defined subscript). Reduces COUNT if elements are not DELETEd.
These programs are referred to as methods because the syntax for using the collection built-ins is different from the normal syntax used to call procedures and functions. Collection methods employ a member method syntax that's common in object-oriented languages such as C++. The general syntax for calling these associative array built-ins is either of the following: An operation that takes no arguments: table_name.operation
An operation that takes a row index for an argument: table_name.operation(index_number [, index_number])
The following statement, for example, returns TRUE if the 15th row of the company_tab associative array is defined: company_tab.EXISTS(15)
The collection methods are not available from within SQL; they can be used only in PL/SQL programs. |
