Lab 18.2 Varrays
As mentioned earlier, a varray is another collection type, and it stands for variable-size arrays. Similar to PL/SQL tables, each element of a varray is assigned a consecutive subscript starting at 1, as shown in Figure 18.2. Figure 18.2. Varray Figure 18.2 shows a varray consisting of five integer numbers . Each number is assigned a unique subscript that corresponds to its position in the varray. It is important to note that a varray has a maximum size. In other words, a subscript of a varray has a fixed lower bound equal to 1, and an upper bound that is extensible if such a need arises. In Figure 18.2, the upper bound of a varray is 5, but it can be extended to 6, 7, 8, and so on up to 10. Therefore, a varray can contain a number of elements, varying from zero (empty array) to its maximum size. You will recall that PL/SQL tables do not have a maximum size that must be specified explicitly. The general syntax for creating a varray is as follows (the reserved words and phrases surrounded by brackets are optional): TYPE type_name IS {VARRAY VARYING ARRAY} (size_limit) OF element_type [NOT NULL]; varray_name TYPE_NAME; First, a varray structure is defined using the TYPE statement, where TYPE_NAME is the name of the type that is used in the second step to declare an actual varray. Notice that there are two variations of the type, VARRAY and VARYING ARRAY. A SIZE_LIMIT is a positive integer literal that specifies the upper bound of a varray. Just like in the case of PL/SQL tables, there are restrictions that apply to an ELEMENT_TYPE of a varray. These restrictions are listed in Oracle help available online. Second, the actual varray is declared based on the type specified in the first step. Consider the following code fragment: DECLARE TYPE last_name_type IS VARRAY(10) OF student. last_name%TYPE; last_name_varray last_name_type; In this example, type last_name_type is declared as a varray of ten elements based on the column LAST_NAME of the STUDENT table. Next , the actual varray last_name_varray is declared based on the LAST_NAME_TYPE. Similar to nested tables, a varray is automatically NULL when it is declared and must be initialized before its individual elements can be referenced. So consider a modified version of the example used in the previous lab. Instead of using nested table, this version uses varray (changes are highlighted in bold). DECLARE CURSOR name_cur IS SELECT last_name FROM student WHERE rownum <= 10; TYPE last_name_type IS VARRAY(10) OF student. last_name%TYPE; last_name_varray last_name_type := last_name_type(); v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_varray.EXTEND; last_name_varray(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('v_counter'): ' last_name_varray(v_counter)); END LOOP; END; This example produces the following output: last_name(1): Crocitto last_name(2): Landry last_name(3): Enison last_name(4): Moskowitz last_name(5): Olvsade last_name(6): Mierzwa last_name(7): Sethi last_name(8): Walter last_name(9): Martin last_name(10): Noviello PL/SQL procedure successfully completed. Based on the preceding example, you may realize that collection methods seen in the previous lab can be used with varrays as well. Consider the following example, which illustrates the use of various collection methods when applied to a varray. DECLARE TYPE varray_type IS VARRAY(10) OF NUMBER; varray varray_type := varray_type(1, 2, 3, 4, 5, 6); BEGIN DBMS_OUTPUT.PUT_LINE ('varray.COUNT = 'varray.COUNT); DBMS_OUTPUT.PUT_LINE ('varray.LIMIT = 'varray.LIMIT); DBMS_OUTPUT.PUT_LINE ('varray.FIRST = 'varray.FIRST); DBMS_OUTPUT.PUT_LINE ('varray.LAST = 'varray.LAST); varray.EXTEND(2, 4); DBMS_OUTPUT.PUT_LINE ('varray.LAST = 'varray.LAST); DBMS_OUTPUT.PUT_LINE ('varray('varray.LAST') = 'varray(varray.LAST)); -- Trim last two elements varray.TRIM(2); DBMS_OUTPUT.PUT_LINE('varray.LAST = 'varray.LAST); END; Consider the output returned by the example: varray.COUNT = 6 varray.LIMIT = 10 varray.FIRST = 1 varray.LAST = 6 varray.LAST = 8 varray(8) = 4 varray.LAST = 6 PL/SQL procedure successfully completed. The first two lines of output varray.COUNT = 6 varray.LIMIT = 10 show the results of the COUNT and LIMIT methods, respectively. You will recall that the COUNT method returns the number of elements that a collection contains. The collection has been initialized to six elements, so the COUNT method returns a value of 6. The next line of output corresponds to another collection method, LIMIT. This method returns the maximum number of elements that a collection can contain and is usually used with varrays only because varrays have an upper bound specified at the time of declaration. The collection VARRAY has an upper bound of ten, so the LIMIT method returns a value of 10. When used with nested tables, the LIMIT method returns NULL because nested tables do not have a maximum size. The third and fourth lines of the output varray.FIRST = 1 varray.LAST = 6 show the results of the FIRST and LAST methods. The fifth and six lines of the output varray.LAST = 8 varray(8) = 4 show the results of LAST method and the value of the eighth element of the collection after the EXTEND method increased the size of the collection. Notice that the EXTEND method varray.EXTEND(2, 4); appends two copies on the fourth element to the collection. As a result, the seventh and eighth elements both contain a value of 4. Next, the last line of output varray.LAST = 6 shows the value of the last subscript after the last two elements were removed via the TRIM method.
 |
![Oracle PL[s]SQL by Example](/icons/blank_book.jpg "Oracle PL[s]SQL by Example"){kind=icon}