| [ Team LiB ] |
Lab 18.1 PL/SQL Tables
Use Nested 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 Tables
The 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.
It is important to note that referencing a nonexistent row raises the NO_DATA_FOUND exception as follows:
This example produces the output shown below:
Notice that the DBMS_OUTPUT.PUT_LINE statement shown in bold letters raises the NO_DATA_FOUND exception because it references the eleventh row of the table, even though the table contains only ten rows. |
Nested Tables
The 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.
It is important to note the difference between NULL collection and empty collection. If a collection has not been initialized, referencing its individual elements causes the following error:
If a collection has been initialized so that it is empty, referencing its individual elements causes a different error:
|
Collection Methods
In 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:
EXISTS�
Returns TRUE if a specified element exists in a collection. This method can be used to avoid raising SUBSCRIPT_OUTSIDE_ LIMIT exceptions.
COUNT�
Returns the total number of elements in a collection.
EXTEND�
Increases the size of a collection.
DELETE�
Deletes either all elements, elements in the specified range, or a particular element from a collection. Note that PL/SQL keeps placeholders of the deleted elements.
FIRST and LAST�
Return subscripts of the first and last elements of a collection. Note that if first elements of a nested table are deleted, the FIRST method returns a value greater than one. If elements have been deleted from the middle of a nested table, the LAST method returns a value greater than the COUNT method.
PRIOR and NEXT�
Return subscripts that precede and succeed a specified collection subscript.
TRIM�
Removes either one or a specified number of elements from the end of a collection. Note that PL/SQL does not keep placeholders for the trimmed elements.
Note that DELETE and TRIM methods cannot be used with index-by tables. |
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 10th 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.
| [ Team LiB ] |
No comments:
Post a Comment