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
You can also use collections to work with lists of data in your program that are not stored in database tables.
This article introduces you to collections and gives you a solid foundation in both collection syntax and features.
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.
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(Index by tables) : 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.
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.
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:
Declare an associative array of numbers, indexed by integer:
TYPE numbers_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
Declare an associative array of numbers, indexed by string:
TYPE numbers_aat IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
Declare a nested table of numbers:
TYPE numbers_nt IS TABLE OF NUMBER;
Declare a varray of numbers:
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;
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.
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:
Assign a number to a single index value. Note that with an associative array, it is not necessary to use EXTEND or start with index value 1.
DECLARE TYPE numbers_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER; l_numbers numbers_aat; BEGIN l_numbers (100) := 12345; END;
Assign one collection to another. As long as both collections are declared with the same type, you can perform collection-level assignments.
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
Conversely, use a WHILE loop when
You should use a numeric FOR loop with dense collections to avoid a NO_DATA_FOUND exception. Oracle Database will also raise this exception, however, if you try to "read" an element in a collection at an undefined index value.
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; /
PL/SQL offers a DELETE method, which you can use to remove all, one, or some elements from a collection. Here are some examples:
Remove the first element in a collection; to remove one element, pass the index value to DELETE. This form of DELETE can be used only with an associative array or a nested table.
Remove all the elements between the specified low and high index values. This form of DELETE can be used only with an associative array or a nested table.
l_names.DELETE (100, 200);
If you specify an undefined index value, Oracle Database will not raise an error.
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);
PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose -
|S.No||Method Name & Purpose|
Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
Returns the number of elements that a collection currently contains.
Checks the maximum size of a collection.
Returns the first (smallest) index numbers in a collection that uses the integer subscripts.
Returns the last (largest) index numbers in a collection that uses the integer subscripts.
Returns the index number that precedes index n in a collection.
Returns the index number that succeeds index n.
Appends one null element to a collection.
Appends n null elements to a collection.
Appends n copies of the ith element to a collection.
Removes one element from the end of a collection.
Removes n elements from the end of a collection.
Removes all elements from a collection, setting COUNT to 0.
Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.
The following table provides the collection exceptions and when they are raised -
|Collection Exception||Raised in Situations|
|COLLECTION_IS_NULL||You try to operate on an atomically null collection.|
|NO_DATA_FOUND||A subscript designates an element that was deleted, or a nonexistent element of an associative array.|
|SUBSCRIPT_BEYOND_COUNT||A subscript exceeds the number of elements in a collection.|
|SUBSCRIPT_OUTSIDE_LIMIT||A subscript is outside the allowed range.|
|VALUE_ERROR||A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.|
Admin on Nov 15, 2019 at 12:05 am
If you like dEexams.com and would like to contribute, you can write your article here or mail your article to firstname.lastname@example.org . See your article appearing on the dEexams.com main page and help others to learn.