Oracle PL/SQL Tutorial Part 9 - PL/SQL Collections

This series of tutorials has been created as a supplement to the Oracle PL/SQL Language Reference to help you see quickly how you can use the language features in your applications. We've covered data types, language elements, cursors, etc. in previous tutorials, Now let’s look at advanced data types - PL/SQL collections.

PL/SQL Collections

In part 3 of this Oracle PL/SQL we introduced the basic data types, however , in order to solve real-world problems, all languages need structured data types such as records, sets, arrays etc. Other than records, these structured data types are known as collections in PL/SQL and are available in 3 different forms: • nested tables • varrays • associative arrays Let's look at the definition and use of each of these in more detail.

PLSQL Nested Tables

Nested tables are one-dimensional arrays which can be declared in the database as a column in another table and in PL/SQL 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 num_tab IS TABLE OF NUMBER ; my_tab num_tab;

VARRAYs

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 PL/SQL. 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 create the data structures required for our example, the declarations would be:- TYPE phone_no_tab IS VARRAY(3) OF NUMBER ; TYPE address_tab IS VARRAY(3) OF VARCHAR2(500); TYPE email_addr_tab IS VARRAY(3) OF VARCHAR2(100); TYPE name_tab IS TABLE OF VARCHAR2(100); addresses    address_tab; phone_nos    phone_no_tab; names        name_tab; email_addresses email_addr_tab; 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 IS TABLE OF contact_rec; contacts contacts_tab;

PL/SQL Associative Arrays

The final collection type is an associative array - previously known as a PL/SQL tables or an index-by table. They are known as associative arrays because they are "associated with"  (i.e. indexed by) PLSQL integers or character strings (VARCHAR2, VARCHAR, STRING or LONG data types). 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 - a 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_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER; my_tab number_tab; The next tutorial looks more deeply into using these collection data types. As good as these tutorials are, they can only take you so far with learning PL/SQL, so if you want to become proficient quickly the best way is to take one of our classroom-based Oracle training courses. PL/SQL tutorial Part 8                                                                     PL/SQL tutorial Part 10  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor- led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you make your Oracle systems faster and more reliable and save you hours searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Need help with Oracle? Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2014. All rights reserved.