In the previous article in this series, I showed you how to work with a PL/SQL record, which is a composite datatype composed of one or more fields. In this article, I will explore another composite datatype, the collection. An Oracle PL/SQL collection is a single-dimensional array; it consists of one or more elements accessible through an index value.
Collections are used in some of the most important performance optimization features of PL/SQL, such as
This article introduces you to collections and gives you a solid foundation in both collection syntax and features.Collection Concepts and Terminology
Before exploring collections, it is helpful to have a common collections vocabulary that includes the following terms.
Index value. The location of the data in a collection. Index values are usually integers but for one type of collection can also be strings.
Element. The data stored at a specific index value in a collection. Elements in a collection are always of the same type (all of them are strings, dates, or records). PL/SQL collections are homogeneous.
Sparse. A collection is sparse if there is at least one index value between the lowest and highest defined index values that is not defined. For example, a sparse collection has an element assigned to index value 1 and another to index value 10 but nothing in between. The opposite of a sparse collection is a dense one.
Method. A collection method is a procedure or function that either provides information about the collection or changes the contents of the collection. Methods are attached to the collection variable with dot notation (object-oriented syntax), as in my_collection.FIRST.Types of Collections
Collections were first introduced in Oracle7 Server and have been enhanced in several ways through the years and across Oracle Database versions. There are now three types of collections to choose from, each with its own set of characteristics and each best suited to a different circumstance.
Associative array. The first type of collection available in PL/SQL, this was originally called a “PL/SQL table” and can be used only in PL/SQL blocks. Associative arrays can be sparse or dense and can be indexed by integer or string.
Nested table. Added in Oracle8 Database, the nested table can be used in PL/SQL blocks, in SQL statements, and as the datatype of columns in tables. Nested tables can be sparse but are almost always dense. They can be indexed only by integer. You can use the MULTISET operator to perform set operations and to perform equality comparisons on nested tables.
Varray. Added in Oracle8 Database, the varray (variable-size array) can be used in PL/SQL blocks, in SQL statements, and as the datatype of columns in tables. Varrays are always dense and indexed by integer. When a varray type is defined, you must specify the maximum number of elements allowed in a collection declared with that type.
You will rarely encounter a need for a varray (How many times do you know in advance the maximum number of elements you will define in your collection?). The associative array is the most commonly used collection type, but nested tables have some powerful, unique features (such as MULTISET operators) that can simplify the code you need to write to use your collection.Nested Table Example
Let’s take a look at the simple example in Listing 1, which introduces the many aspects of collections explored later in the article.
Code Listing 1: Nested table example
1 DECLARE 2 TYPE list_of_names_t IS TABLE OF VARCHAR2 (100); 3 4 happyfamily list_of_names_t := list_of_names_t (); 5 children list_of_names_t := list_of_names_t (); 6 parents list_of_names_t := list_of_names_t (); 7 BEGIN 8 happyfamily.EXTEND (4); 9 happyfamily (1) := ‘Veva’; 10 happyfamily (2) := ‘Chris’; 11 happyfamily (3) := ‘Eli’; 12 happyfamily (4) := ‘Steven’; 13 14 children.EXTEND; 15 children (children.LAST) := ‘Chris’; 16 children.EXTEND; 17 children (children.LAST) := ‘Eli’; 18 19 parents := happyfamily MULTISET EXCEPT children; 20 21 FOR l_row IN 1 .. parents.COUNT 22 LOOP 23 DBMS_OUTPUT.put_line (parents (l_row)); 24 END LOOP; 25 END;
|2||I declare a new nested table type. Each element in a collection declared with this type is a string whose maximum length is 100.|
|4–6||I declare three nested tables—happyfamily, children, and parents—based on my new collection type. Note that I also assign a default value to each variable by calling a constructor function that has the same name as the type.|
|8||I “make room” in my happyfamily collection for four elements by calling the EXTEND method.|
|9–12||I assign the names of the members of my immediate family (my wife, Veva; my two sons, Chris and Eli; and myself). Note the use of typical single-dimension array syntax to identify an element in the array: array_name (index_value).|
|14–17||Now I populate the children nested table with just the names of my sons. Rather than do a “bulk” extend as on line 8, I extend one index value at a time. Then I assign the name to the just-added index value by calling the LAST method, which returns the highest defined index value in the collection. Unless you know how many elements you need in advance, this approach of extending one row and then assigning a value to the new highest index value is the way to go.|
|19||Both of my children are adults and have moved out of the ancestral home. So who’s left in this place with too many bedrooms? Start with the happyfamily and subtract (with the MULTISET EXCEPT operator) the children. Assign the result of this set operation to the parents collection. It should be just Veva and Steven.|
|21–24||The result of a MULTISET operation is always either empty or densely filled and starts with index value 1. So I will iterate through all the elements in the collection, from 1 to the COUNT (the number of elements defined in the collection) and display the element found at each index value.|
When I run the block in Listing 1, I see the following output:
Listing 1 also includes references to the lines in the code block and descriptions of how those lines contribute to the nested table example.Declare Collection Types and Variables
Before you can declare and use a collection variable, you need to define the type on which it is based. Oracle Database predefines several collection types in supplied packages such as DBMS_SQL and DBMS_UTILITY. So if you need, for example, to declare an associative array of strings whose maximum length is 32767, you could write the following:
In most cases, however, you will declare your own application-specific collection types. Here are examples of declaring each of the different types of collections:
TYPE numbers_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE numbers_aat IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
TYPE numbers_nt IS TABLE OF NUMBER;
TYPE numbers_vat IS VARRAY(10) OF NUMBER;
Note: I use the suffixes _aat, _nt, and _vat, for associative array type, nested table type, and varray type, respectively.
You might be wondering why the syntax for defining a collection type does not use the word collection. The answer is that the IS TABLE OF syntax was first introduced in Oracle7 Server, when there was just one type of collection, the PL/SQL table.
From these examples, you can draw the following conclusions about collection types:
Once you’ve declared a collection type, you can use it to declare a collection variable as you would declare any other kind of variable:
DECLARE TYPE numbers_nt IS TABLE OF NUMBER; l_numbers numbers_nt;Initializing Collections
When you work with nested tables and varrays, you must initialize the collection variable before you can use it. You do this by calling the constructor function for that type. This function is created automatically by Oracle Database when you declare the type. The constructor function constructs an instance of the type associated with the function. You can call this function with no arguments, or you can pass it one or more expressions of the same type as the elements of the collection, and they will be inserted into your collection.
Here is an example of initializing a nested table of numbers with three elements (1, 2, and 3):
DECLARE TYPE numbers_nt IS TABLE OF NUMBER; l_numbers numbers_nt; BEGIN l_numbers := numbers_nt (1, 2, 3); END;
If you neglect to initialize your collection, Oracle Database will raise an error when you try to use that collection:
SQL> DECLARE 2 TYPE numbers_nt IS TABLE OF NUMBER; 3 l_numbers numbers_nt; 4 BEGIN 5 l_numbers.EXTEND; 6 l_numbers(1) := 1; 7 END; 8 / DECLARE * ERROR at line 1: ORA-06531: Reference to uninitialized collection ORA-06512: at line 5
You do not need to initialize an associative array before assigning values to it.Populating Collections
You can assign values to elements in a collection in a variety of ways:
The previous section included an example that used a constructor function. Following are examples of the other approaches:
DECLARE TYPE numbers_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER; l_numbers numbers_aat; BEGIN l_numbers (100) := 12345; END;
DECLARE TYPE numbers_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER; l_numbers1 numbers_aat; l_numbers2 numbers_aat; BEGIN l_numbers1 (100) := 12345; l_numbers2 := l_numbers1; END;
Pass a collection as an IN OUT argument, and remove all the elements from that collection:
DECLARE TYPE numbers_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER; l_numbers numbers_aat; PROCEDURE empty_collection ( numbers_io IN OUT numbers_aat) IS BEGIN numbers_io.delete; END; BEGIN l_numbers (100) := 123; empty_collection (l_numbers); END;
Fill a collection directly from a query with BULK COLLECT (covered in more detail in the next article in this series):
DECLARE TYPE numbers_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER; l_numbers numbers_aat; BEGIN SELECT employee_id BULK COLLECT INTO l_numbers FROM employees ORDER BY last_name; END;
A very common collection operation is to iterate through all of a collection’s elements. Reasons to perform a “full collection scan” include displaying information in the collection, executing a data manipulation language (DML) statement with data in the element, and searching for specific data.
The kind of code you write to iterate through a collection is determined by the type of collection with which you are working and how it was populated. Generally, you will choose between a numeric FOR loop and a WHILE loop.
Use a numeric FOR loop when
The following block, for example, raises a NO_DATA_FOUND exception:
DECLARE TYPE numbers_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER; l_numbers numbers_aat; BEGIN DBMS_OUTPUT.PUT_LINE (l_numbers (100)); END;
When, however, you know for certain that your collection is—and will always be—densely filled, the FOR loop offers the simplest code for getting the job done. The procedure in Listing 2, for example, displays all the strings found in a collection whose type is defined in the DBMS_UTILITY package.
Code Listing 2: Display all strings in a collection
CREATE OR REPLACE PROCEDURE show_contents ( names_in IN DBMS_UTILITY.maxname_array) IS BEGIN FOR indx IN names_in.FIRST .. names_in.LAST LOOP DBMS_OUTPUT.put_line (names_in (indx)); END LOOP; END; /
This procedure calls two methods: FIRST and LAST. FIRST returns the lowest defined index value in the collection, and LAST returns the highest defined index value in the collection.
The following block will display three artists’ names; note that the index values do not need to start at 1.
DECLARE l_names DBMS_UTILITY.maxname_array; BEGIN l_names (100) := ‘Picasso’; l_names (101) := ‘O’’Keefe’; l_names (102) := ‘Dali’;= show_contents (l_names); END; /
If your collection may be sparse or you want to terminate the loop conditionally, a WHILE loop will be the best fit. The procedure in Listing 3 shows this approach.
Code Listing 3: Use WHILE to iterate through a collection
CREATE OR REPLACE PROCEDURE show_contents ( names_in IN DBMS_UTILITY.maxname_array) IS l_index PLS_INTEGER := names_in.FIRST; BEGIN WHILE (l_index IS NOT NULL) LOOP DBMS_OUTPUT.put_line (names_in (l_index)); l_index := names_in.NEXT (l_index); END LOOP; END; /
In this procedure, my iterator (l_index) is initially set to the lowest defined index value. If the collection is empty, both FIRST and LAST will return NULL. The WHILE loop terminates when l_index is NULL. I then display the name at the current index value and call the NEXT method to get the next defined index value higher than l_index. This function returns NULL when there is no higher index value.
I call this procedure in the following block, with a collection that is not sequentially filled. It will display the three names without raising NO_DATA_FOUND:
DECLARE l_names DBMS_UTILITY.maxname_array; BEGIN l_names (-150) := 'Picasso'; l_names (0) := 'O''Keefe'; l_names (307) := 'Dali'; show_contents (l_names); END; /
I can also scan the contents of a collection in reverse, starting with LAST and using the PRIOR method, as shown in Listing 4.
Code Listing 4: Scan a collection in reverse
CREATE OR REPLACE PROCEDURE show_contents ( names_in IN DBMS_UTILITY.maxname_array) IS l_index PLS_INTEGER := names_in.LAST; BEGIN WHILE (l_index IS NOT NULL) LOOP DBMS_OUTPUT.put_line (names_in (l_index)); l_index := names_in.PRIOR (l_index); END LOOP; END; /Deleting Collection Elements
PL/SQL offers a DELETE method, which you can use to remove all, one, or some elements from a collection. Here are some examples:
l_names.DELETE (100, 200);
You can also use the TRIM method with varrays and nested tables to remove elements from the end of the collection. You can trim one or many elements:
l_names.TRIM; l_names.TRIM (3);Get Comfy with Collections
It is impossible to take full advantage of PL/SQL, including some of its powerful features, if you do not use collections. This article has provided a solid foundation for working with collections, but there are still several advanced features to explore, including string-indexed and nested collections, which will be covered in a future article.
The next article in this PL/SQL 101 series will explore how to use collections with PL/SQL’s most important performance-related PL/SQL features: FORALL and BULK COLLECT.
Each PL/SQL 101 article offers a quiz to test your knowledge of the information provided in it. The quiz appears below and also at PL/SQL Challenge (plsqlchallenge.com), a Website that offers online quizzes on the PL/SQL language as well as SQL and Oracle Application Express.
Which of the following blocks will display these three lines after execution:
Strawberry Raspberry Blackberry
DECLARE l_names DBMS_UTILITY.maxname_array; BEGIN l_names (1) := 'Strawberry'; l_names (10) := 'Blackberry'; l_names (2) := 'Raspberry'; FOR indx IN 1 .. l_names.COUNT LOOP DBMS_OUTPUT.put_line (l_names (indx)); END LOOP; END; /
DECLARE l_names DBMS_UTILITY.maxname_array; BEGIN l_names (1) := 'Strawberry'; l_names (10) := 'Blackberry'; l_names (2) := 'Raspberry'; indx := l_names.FIRST; WHILE (indx IS NOT NULL) LOOP DBMS_OUTPUT.put_line (l_names (indx)); indx := l_names.NEXT (indx); END LOOP; END; /
DECLARE l_names DBMS_UTILITY.maxname_array; BEGIN l_names (1) := 'Strawberry'; l_names (10) := 'Blackberry'; l_names (2) := 'Raspberry'; DECLARE indx PLS_INTEGER := l_names.FIRST; BEGIN WHILE (indx IS NOT NULL) LOOP DBMS_OUTPUT.put_line (l_names (indx)); indx := l_names.NEXT (indx); END LOOP; END; END; /
DECLARE l_names DBMS_UTILITY.maxname_array; BEGIN l_names (1) := 'Strawberry'; l_names (10) := 'Blackberry'; l_names (2) := 'Raspberry'; FOR indx IN l_names.FIRST .. l_names.LAST LOOP DBMS_OUTPUT.put_line (l_names (indx)); END LOOP; END; /
CURSOR parts_cur IS SELECT * FROM plch_parts; l_part parts_cur%ROWTYPE;
DOWNLOAD Oracle Database 11g
Photography by Aaron Burson, Unsplash