Plsql==> Collection

1. Union array
   

The syntax for creating union arrays is as follows (reserved words and phrases in square brackets are optional)

TYPE type_name is Table of element_type (not null) index by Element_type;

Type_name type_name;

Note that it takes two steps to declare a union array. First, use the type statement to declare the table structure, where type_name is the name of the type used in the 2nd step to declare an actual table. Element_type is a plsql data type, such as number,varchar2 or date, and other restrictions may exist.

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 contents of the Union array Last_name_tab are populated with the name information of the student table. Note that V_counter is used as a subscript to refer to individual table elements.

It is important to note that referencing a non-existent data row throws a No_data_found exception.

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 (one): ' | | last_name_tab (11));

The END;

17/

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 (+): Noviello

DECLARE

*

ERROR at line 1:

Ora-01403:no Data found

Ora-06512:at Line 15

2. Nested tables

The syntax for creating a nested table is as follows (the reserved word of square brackets is optional):

TYPE type_name is table of Element_type[not NULL]

Type_name type_name;

Note that this declaration is very type in the declaration of the Union array, except that there is no index by Binary_integer statement. As in the Union array, the restriction also applies to the Element_type of the nested table.

Note that you must first initialize the nested table before you can reference the elements in it.

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;

The END;

16/

DECLARE

*

ERROR at line 1:

Ora-06531:reference to uninitialized Collection

Ora-06512:at Line 11

This example causes an error because the nested table is automatically set to NULL when the nested table is declared. That is, the nested table has no elements, because the nested table itself is null. To reference a single element in a nested table, you must initialize it with a system-defined function named constructor. The constructor has the same name as the nested table type.

Note that in most cases, you cannot predict the value of a particular nested table. Therefore, the following statement produces an empty, but not null, nested table:

Last_name_tab:=last_name_type ();

Note that no arguments are passed to the constructor.

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;

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 (+): Noviello

PL/SQL procedure successfully completed.

In this version, the nested table is initialized at the time of declaration. This means that the nested table is empty, but not null. The statement for the cursor loop contains a collection method: Extend. This method allows you to increase the size of the collection. Note that the Extend method cannot be used with a federated array.

What is the difference between a null set and an empty collection? If a collection has not been initialized, referencing its individual elements results in 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 6

If the collection is initialized, but there is no data in the collection, referencing a single element causes another 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));

7 END;

8/

DECLARE

*

ERROR at line 1:

Ora-06533:subscript Beyond Count

Ora-06512:at Line 6

Method of collection:

In the previous example, the method extend for the collection has been used. The collection method is a built-in function that can be called with a dot tag as follows:

Collection_name.method_name

The following list resolves the collection's methods, and you can use the collection method to manipulate or get information about a particular collection:

If a particular element exists in the collection, exists returns TRUE. This method can be used to avoid subscript_outside_limit exceptions.

Count returns the number of elements in the collection.

Extend will expand the size of the collection

Delete Deletes all the elements in the collection, the elements of the specified range, or specific elements. Note that Plsql retains the placeholder for the deleted element.

First and last return the subscript of the primary and the final element in the collection. Note that if the first element of a nested table is deleted, the Fisrt method returns a value greater than 1. If you delete an intermediate element from a nested table, the return value of the last method is greater than the return value of the Count method.

Prior and next return the pre-order and subsequent subscripts for the specified set of subscripts.

Trim removes one from the end of the collection, or specifies the number of elements. Note that Plsql does not save placeholders for deleted elements.

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= ' | | nested_table.prior (2));

Dbms_output.put_line (' nested_table.next= ' | | nested_table.next (2));

Dbms_output.put_line (' index_by_table.prior= ' | |

Index_by_table.prior (2));

Dbms_output.put_line (' index_by_table.next= ' | | index_by_table.next (2));

--trim last elements

Nested_table.trim (2);

--trim last element;

Nested_table.trim;

Dbms_output.put_line (' nested_table.last= ' | | nested_table.last);

END;

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=

Nested_table.next=4

Index_by_table.prior=1

Index_by_table.next=3

Nested_table.last=7

Because the Exists method returns True, the first line of output is as follows:

Index_by_table (3) =3

Therefore, if the result of the IF statement evaluates to True

IF index_by_table.exists (3)

Then

Dbms_output.put_line (' index_by_table (3) = ' | | index_by_table (3));

END IF;

The second and third rows of output show that some elements are removed from the union array and the nested table, and the result of the method count is:

Nested_table.count=6

Index_by_table.count=9

Next, line fourth through seventh is the output of the first and last methods:

Nested_table.first=4

Nested_table.last=9

Index_by_table.first=1

Index_by_table.last=9

Note that the first method that is applied to the nested table returns 4 because the top three elements were deleted earlier. Next, line eighth through tenth is the output of the prior and next methods.

Nested_table.prior=

Nested_table.next=4

Index_by_table.prior=1

Index_by_table.next=3

Plsql==> Collection