This is part 9 of our introductory Oracle PLSQL tutorial designed to show you how to use PL/SQL to improve the performance, scalability and availability of your Oracle systems. This time we'll take a quick look at the use of collections in PL/SQL.
We hinted briefly in part 2 of this Oracle pl/sql tutorial that programmers can define their own composite data types in PL/SQL. These composite data types are known as collections and are available in 3 different forms:
Let's look at the definition and use of each of these in more detail.
Nested tables are one-dimensional arrays which can be declared in the database as a column in another table and in PLSQL as stand-alone tables, however they should not be confused with the PL/SQL index-by tables that were available in PL/SQL 8 - those are now known as associative arrays as we'll see later.
Theoretically nested tables have no upper or lower limits and are therefore constrained only by the amount of memory available. When created, nested tables are densely populated - i.e. each row in the table has a value - but rows can be (logically) deleted so nested tables can become sparsely populated. Each row of the nested table must be of the same type.
Declaring a nested table in PL/SQL is done in 2 stages - first declare a type and then declare a variable of that type as follows:
TYPE <type_name> IS TABLE OF <existing_type> ;
<variable_name> <type_name>;
For example, suppose we want to create a table of numbers, the declarations would then be
TYPE number_table_type IS TABLE OF NUMBER ;
my_tab number_table_type;
VARRAYs are variable length, one dimensional, densely populated arrays (dense in the sense that each cell must exist, although it may be empty). Again each element must be of the same type and like nested tables they can be defined in the database or in PLSQL. The declaration defines the size of the array which is fixed - you cannot add new cells to or delete cells from a VARRAY once you've declared it. These are designed for storing a small number of attributes.
For example let's suppose we had a very primitive contact management system which stored names, 3 addresses (business,home and head office), 3 email addresses (business, home and alternative) and 3 phone numbers (business, home and mobile).
In this case we know in advance the maximum number of each type of attribute we want to store so VARRAYs are ideal for this. If we didn't know how many would be stored (for example we wanted to keep a history of addresses, names and telephone numbers) then we would use nested tables. For the sake of this example we'll pretend that PL/SQL automatically stores everything in a database for us.
Like nested tables we first declare a type and then declare a variable of that type as follows:
TYPE <type_name> IS VARRAY <size> OF <existing_type> ;
<variable_name> <type_name>;
To take our example, the declarations would be:-
TYPE phone_no_tab_type IS VARRAY(3)
OF NUMBER ;
TYPE address_tab_type IS VARRAY(3)
OF VARCHAR2(500);
TYPE email_addr_tab_type IS VARRAY(3)
OF VARCHAR2(100);
TYPE name_tab_type IS TABLE OF VARCHAR2(100);
addresses
address_tab_type;
phone_nos phone_no_tab_type;
names
name_tab_type;
email_addresses email_addr_tab_type;
Note that at the moment, each of the types of data is stored separately and therefore has to be worked with separately. To put this all together we need to create another type as a record then create a nested table to hold the records as follows:-
TYPE contact_rec IS RECORD (
name VARCHAR2(100);
addrs address_tab_type;
phone_nums phone_no_tab_type;
email_addrs email_addr_tab_type;
);
TYPE contacts_tab_type IS TABLE OF contact_rec;
contacts contacts_tab_type;
The final collection type is an associative array - known as PL/SQL table in PL/SQL 2 and index-by table in PL/SQL 8. They are known as associative arrays because they are "associated" (i.e. indexed by) with PLSQL integers (BINARY_INTEGER) or character strings (VARCHAR2). Like nested tables, associative arrays are unbounded, single dimensional and each cell must be of the same type. However, unlike nested tables, associative arrays are sparse - each cell does not have to exist.
To make this clearer let's have a look at the syntax and an example. As with nested tables we first declare a type and then declare a variable of that type as follows:
TYPE <type_name> IS TABLE OF <existing_type> ;
<variable_name> <type_name>;
For example, suppose we want to create a table of numbers, the declarations would then be
TYPE number_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
my_tab number_table_type;
The next Oracle plsql tutorial looks more deeply into using these tools, so don't go away!
Take advantage of one of our classroom-based Oracle training courses to extend, enhance and consolidate your knowledge of PL/SQL. See the Oracle training page for details.
PLSQL tutorial Part 8 Oracle PLSQL tutorial part 10
---------------------------------------
Looking for more Oracle tips and tricks? For clear, concise and relevant technical information subscribe to our ezine and steal a march over your rivals.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.