Oracle PLSQL Tutorial (Part 12)

This series of introductory Oracle PLSQL tutorials aims to show you how you can use PL/SQL to improve the performance and scalability of your Oracle systems whilst simultaneously reducing downtime. We continue our introduction to the use of PL/SQL collection types by looking at PLSQL associative arrays (known as index-by tables or PLSQL tables in previous versions of Oracle).

Oracle PLSQL Collection Types

Just to recap briefly, (see here for an introduction to Oracle PLSQL collection types) the 3 different types of PL/SQL collections are:-

nested tables - see part 10
varrays - see part 11
PL/SQL associative arrays (previously known as index-by or PLSQLtables) - covered in this tutorial.

PLSQL Associative Arrays

Oracle PL/SQL associative arrays were known as index-by tables or PLSQL tables in previous versions of Oracle and this gives us a clue as to their purpose and functionality - they have an index. In earlier versions of Oracle PLSQL tables could only be indexed by BINARY INTEGERs, in Oracle 9i Release 2 and above they can be indexed (associated) with BINARY INTEGER or VARCHAR2 constants/variables. Apart from this, PLSQL associative arrays are in many respects the same as PLSQLnested tables:

they are one-dimensional arrays;
they are unbounded;
they are homogeneous (every element must be of the same type).

The other difference between associative arrays and PLSQLnested tables is that nested tables are initially dense whereas associative arrays are sparse.

Let's look at a couple of examples. First we have to declare the table type:
TYPE phone_no_tab IS TABLE OF VARCHAR2 (20)INDEX BY BINARY_INTEGER;

Next we declare a variable of that type: phone_nos phone_no_tab;

This creates an array (table) that is available within our PL/SQL session. Inserting and retrieving data is done in the same way as with nested tables but there is no need to initialise the array or extend it. Instead we just access the cells we want, whether we want to insert data or retrieve data from the array, as in the following examples:

SQL> declare
2     TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
3     phone_nos phone_no_tab;
4 begin
5     phone_nos(1) := '+44 (0) 117 942 2508';
6     dbms_output.put_line('phone_no(1) is '||phone_nos(1));
7 end;
8 /
phone_no(1) is +44 (0) 117 942 2508

As you can see from the above example with nested tables in PLSQL, there's no need to initialise our array or to EXTEND it to define extra cells - the elements (cells) of the array are created automatically by Oracle, line 3 acts as as the declaration and constructor of the array. However if we access a cell that doesn't have any data in it, we will generate a NO_DATA_FOUND exception as in the following example:

SQL> DECLARE
2     TYPE phone_no_tab IS IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
3     phone_nos phone_no_tab;
4 BEGIN
5     phone_nos (21) := '0117 942 2508';
6     DBMS_OUTPUT.PUT_LINE('phone_no(1) is '||phone_nos(1));
7 END;
8 /
declare
*

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6

That's demonstrated the use of associative arrays when indexed by BINARY INTEGERS, but our example is not very practical. It is much more useful to be able to access a table of phone numbers by type (home,office,mobile) rather than by position. Let's have a look at associating elements of the array with character strings (VARCHAR2 datatype).

SQL> declare
2     TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(30);
3     phone_nos phone_no_tab;
4 begin
5     phone_nos('office') := '+44 (0) 117 942 2508';
6     DBMS_output.put_line('phone_no(office) is '||phone_nos('office'));
7 end;
8 /
phone_no(office) is +44 (0) 117 942 2508

We'll finish off this tutorial by having a look at other methods we can use on PLSQL associative arrays.

COUNT: This function returns the number of elements (cells) in the collection

DELETE: This procedure with no parameters deletes all the elements in the collection, as with nested tables, though, we can specify a start and end point to say which element(s) is (are) to be deleted. After deleting an element or the whole collection, any subsequent attempt to read that element in the collection generates "ORA-01403: no data found". However you can write to any element without any errors as this re-creates the element

EXISTS(n) : used to determine if the specified element has been created and not deleted, returns TRUE if the element exists, FALSE if not. The index variable can be either a number of type BINARY_INTEGER or a character string of type VARCHAR2

FIRST : returns the subscript of the first element in the PLSQL associative array
LAST : returns the subscript of the last element in the PL/SQL associative array

PRIOR(n): returns the subscript of the previous element in the PL/SQL associative array or NULL if if no more elements exist
NEXT(n) : returns the subscript of the next element in the PLSQL associative array or NULL if no more elements exist.

TRIM : Not appropriate for associative arrays - generates a compilation error
EXTEND : Not appropriate for associative arrays - generates a compilation error

----

The next tutorial (part 13) introduces Oracle PLSQL multilevel collections.

Oracle PL/SQL tutorial Part 11 introduced pl/sql varrays

---------------------------------------

Looking for more Oracle tips and tricks ? For clear, concise, relevant technical information subscribe to our ezine.

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.