Lab 18.1 PL/SQL Tables
A PL/SQL table is similar to one-column database table. The rows of a PL/SQL table are not stored in any predefined order, yet when they are retrieved in a variable each row is assigned a consecutive subscript starting at 1, as shown in the in Figure 18.1. Figure 18.1. PL/SQL Table
Figure 18.1 shows a PL/SQL table consisting of integer numbers . Each number is assigned a unique subscript that corresponds to its position in the table. For example, number 3 has subscript 5 assigned to it because it is stored in the fifth row of the PL/SQL table. There are two types of PL/SQL tables: index-by tables and nested tables . They have the same structure, and their rows are accessed in the same way via subscript notation as shown in Figure 18.1. The main difference between these two types is that nested tables can be stored in a database column, and the index-by tables cannot. Index-By TablesThe general syntax for creating an index-by table is as follows (the reserved words and phrases surrounded by brackets are optional): TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTEGER; table_name TYPE_NAME; Notice that there are two steps in the declaration of an index-by table. First, a table 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 table. An ELEMENT_TYPE is any PL/SQL datatype, such as NUMBER, VARCHAR2, or DATE, with some restrictions. The majority of restricted datatypes are beyond the scope of this book and are not mentioned in this chapter. However, you can find the complete list in Oracle help available online. Second, the actual table is declared based on the type specified in the previous step. Consider the following code fragment. FOR EXAMPLE DECLARE TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER; last_name_tab last_name_type; In this example, type last_name_type is declared based on the column LAST_NAME of the STUDENT table. Next, the actual index-by table last_name_tab is declared as LAST_NAME_TYPE. As mentioned earlier, the individual elements of a PL/SQL table are referenced via subscript notation as follows: table_name (subscript) This technique is demonstrated in the following example. FOR EXAMPLE DECLARE CURSOR name_cur IS SELECT last_name FROM student WHERE rownum <= 10; TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER; last_name_tab last_name_type; v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_tab(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('v_counter'): ' last_name_tab(v_counter)); END LOOP; END; In this example, the index-by table last_name_tab is populated with last names from the STUDENT table. Notice that the variable v_counter is used as a subscript to reference individual table elements. 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.
Nested TablesThe general syntax for creating a nested table is as follows (the reserved words and phrases surrounded by brackets are optional): TYPE type_name IS TABLE OF element_type [NOT NULL]; table_name TYPE_NAME; Notice that this declaration is very similar to the declaration of an index-by table except that there is no INDEX BY BINARY_INTEGER clause. Just like in the case of an index-by table, there are restrictions that apply to an ELEMENT_TYPE of a nested table. These restrictions are listed in Oracle help available online. It is important to note that a nested table must be initialized before its individual elements can be referenced. Consider the modified version of the example used earlier in this lab. Notice that the last_name_type is defined as a nested table (there is no INDEX BY clause). FOR EXAMPLE DECLARE CURSOR name_cur IS SELECT last_name FROM student WHERE rownum <= 10; TYPE last_name_type IS TABLE OF student.last_name%TYPE; last_name_tab last_name_type; v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_tab(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('v_counter'): ' last_name_tab(v_counter)); END LOOP; END; This example causes the following error: DECLARE * ERROR at line 1: ORA-06531: Reference to uninitialized collection ORA-06512: at line 14 The example causes an error because a nested table is automatically NULL when it is declared. In other words, there are no individual elements yet because the nested table itself is NULL. In order to reference the individual elements of the nested table, it must be initialized with the help of a system-defined function called constructor . The constructor has the same name as the nested table type. For example, last_name_tab := last_name_type('Rosenzweig', 'Silvestrova'); This statement initializes the last_name_tab table to two elements. Note that most of the time, it is not known in advance what values should constitute a particular nested table. So, the following statement produces an empty but non-null nested table. last_name_tab := last_name_type(); Notice that there are no arguments passed to a constructor. Consider a modified version of the example shown previously. FOR EXAMPLE DECLARE CURSOR name_cur IS SELECT last_name FROM student WHERE rownum <= 10; TYPE last_name_type IS TABLE OF student.last_name%TYPE; last_name_tab 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_tab.EXTEND; last_name_tab(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('v_counter'): ' last_name_tab(v_counter)); END LOOP; END; In this version, the nested table is initialized at the time of the declaration. This means that it is empty, but non-null. In the cursor loop, there is a statement with one of the collection methods , EXTEND. This method allows you to increase the size of the collection. Note that the EXTEND method cannot be used with index-by tables . You will see detailed explanation of various collection methods later in this chapter. Next, the nested table is assigned values just like the index-by table in the original version of the example. When run, the script 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.
Collection MethodsIn the previous examples, you have seen one of the collection methods, EXTEND. A collection method is a built-in function that is called using a dot notation as follows: collection_name.method_date The following list explains collection methods that allow you to manipulate or gain information about a particular collection:
Consider the following example, which illustrates the use of various collection methods. FOR EXAMPLE DECLARE TYPE index_by_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; index_by_table index_by_type; TYPE nested_type IS TABLE OF NUMBER; nested_table nested_type := nested_type(1, 2, 3, 4, 5, 6, 7, 8, 9, 10); BEGIN -- Populate index by table FOR i IN 1..10 LOOP index_by_table(i) := i; END LOOP; IF index_by_table.EXISTS(3) THEN DBMS_OUTPUT.PUT_LINE ('index_by_table(3) = 'index_by_table(3)); END IF; -- delete 10 th element from a collection nested_table.DELETE(10); -- delete elements 1 through 3 from a collection nested_table.DELETE(1,3); index_by_table.DELETE(10); DBMS_OUTPUT.PUT_LINE ('nested_table.COUNT = 'nested_table.COUNT); DBMS_OUTPUT.PUT_LINE ('index_by_table.COUNT = 'index_by_table.COUNT); DBMS_OUTPUT.PUT_LINE ('nested_table.FIRST = 'nested_table.FIRST); DBMS_OUTPUT.PUT_LINE ('nested_table.LAST = 'nested_table.LAST); DBMS_OUTPUT.PUT_LINE ('index_by_table.FIRST = 'index_by_table.FIRST); DBMS_OUTPUT.PUT_LINE ('index_by_table.LAST = 'index_by_table.LAST); DBMS_OUTPUT.PUT_LINE ('nested_table.PRIOR(2) = 'nested_table. PRIOR(2)); DBMS_OUTPUT.PUT_LINE ('nested_table.NEXT(2) = 'nested_table.NEXT(2)); DBMS_OUTPUT.PUT_LINE ('index_by_table.PRIOR(2) = 'index_by_table.PRIOR(2)); DBMS_OUTPUT.PUT_LINE ('index_by_table.NEXT(2) = 'index_by_table.NEXT(2)); -- Trim last two elements nested_table.TRIM(2); -- Trim last element nested_table.TRIM; DBMS_OUTPUT.PUT_LINE('nested_table.LAST = 'nested_table.LAST); END; Consider the output returned by the example: index_by_table(3) = 3 nested_table.COUNT = 6 index_by_table.COUNT = 9 nested_table.FIRST = 4 nested_table.LAST = 9 index_by_table.FIRST = 1 index_by_table.LAST = 9 nested_table.PRIOR(2) = nested_table.NEXT(2) = 4 index_by_table.PRIOR(2) = 1 index_by_table.NEXT(2) = 3 nested_table.LAST = 7 PL/SQL procedure successfully completed. The first line of the output index_by_table(3) = 3 is produced because the EXISTS method returns TRUE, and as a result, the IF statement IF index_by_table.EXISTS(3) THEN DBMS_OUTPUT.PUT_LINE ('index_by_table(3) = ' index_by_table(3)); END IF; evaluates to TRUE as well. The second and third lines of the output nested_table.COUNT = 6 index_by_table.COUNT = 9 show the results of method COUNT after some elements were deleted from the index-by and nested tables. Next, lines four through seven of the output nested_table.FIRST = 4 nested_table.LAST = 9 index_by_table.FIRST = 1 index_by_table.LAST = 9 show the results of FIRST and LAST methods. Notice that the FIRST method applied to the nested table returns 4 because the first three elements were deleted earlier. Next, lines eight through eleven of the output nested_table.PRIOR(2) = nested_table.NEXT(2) = 4 index_by_table.PRIOR(2) = 1 index_by_table.NEXT(2) = 3 show the results of PRIOR and NEXT methods. Notice that the PRIOR method applied to the nested table returns NULL because the first element was deleted earlier. Finally, the last line of the output nested_table.LAST = 7 shows the value of the last subscript after the last three elements were removed. As mentioned earlier, once the DELETE method is issued, the PL/SQL keeps placeholders of the deleted elements. Therefore, the first call of the TRIM method removed ninth and tenth elements from the nested table, and the second call of the TRIM method removed eighth element of the nested table. As a result, the LAST method returned value 7 as the last subscript of the nested table. |