Lab 18.1 PLSQL Tables


Lab 18.1 PL/SQL Tables

Lab Objectives

After this Lab, you will be able to:

Use Index-By 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

graphics/18fig01.gif

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.  
graphics/trick_icon.gif

It is important to note that referencing a nonexistent row raises the NO_DATA_FOUND exception as follows:

 
 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;  DBMS_OUTPUT.PUT_LINE ('last_name(11): 'last_name_tab(11));  END; 

This example produces the output shown below:

 
  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   DECLARE   *   ERROR at line 1:   ORA-01403: no data found   ORA-06512: at line 19  

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.  
graphics/trick_icon.gif

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:

 
 DECLARE    TYPE integer_type IS TABLE OF INTEGER;    integer_tab integer_type;    v_counter integer := 1; BEGIN    DBMS_OUTPUT.PUT_LINE (integer_tab(v_counter)); END;  DECLARE   *   ERROR at line 1:   ORA-06531: Reference to uninitialized collection   ORA-06512: at line 7  

If a collection has been initialized so that it is empty, referencing its individual elements causes a different error:

 
 DECLARE    TYPE integer_type IS TABLE OF INTEGER;    integer_tab integer_type := integer_type();    v_counter integer := 1; BEGIN    DBMS_OUTPUT.PUT_LINE (integer_tab(v_counter)); END;  DECLARE   *   ERROR at line 1:   ORA-06533: Subscript beyond count   ORA-06512: at line 7  

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.

graphics/trick_icon.gif

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 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.



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net