Database Interaction with PL/SQL: Nested Tables

This series has been focusing on database interactions with Oracle PL/SQL. Having just covered OBJECT TYPE in Oracle and both SQL and PL/SQL ways of working with OBJECTs in the last article, we will now learn about using NESTED TABLE with OBJECT TYPES from both SQL and PL/SQL point of views. We will also discuss FOR loops with an inline SELECT statement. This article is the fifth in this series.

Please note that all the examples in this series have been tested only with Oracle 10g.  I didn’t really test them with all the previous versions of Oracle.  I suggest you to refer the documentation of respective version you are using, if any of the programs failed to execute.

NESTED TABLE with OBJECT TYPE

Part-3 of my series covered NESTED TABLE a bit and part-4 introduced the concept of OBJECT TYPE.  Now, we shall combine those two in this article.  Let us prepare scripts from scratch to discuss them in detail.  Consider the following:

CREATE TYPE t_experience AS OBJECT
(
    CompanyName    varchar2(20),
    Position       varchar2(20),
    NoOfYears      number(2)
);
/

The above script just creates an OBJECT TYPE (which is almost similar to the one present in part-4). 

CREATE TYPE t_experience_tbl AS TABLE OF t_experience;

If we observe the above carefully, we are still creating another TYPE (not a concrete table), but it is of TABLE TYPE within database.  For our convenience, I named it as ‘t_experience_tbl’.

CREATE TABLE employees
(
    Name            varchar2(20),
   
Experiences     t_experience_tbl
)
NESTED TABLE Experiences STORE AS Experiences_tab;

The above statement creates a new table ‘employees’ (make sure to drop an old one if it exists) with only two fields,  ‘name’ and ‘experiences’.  The field ‘Experiences’ is created based on the TABLE TYPE ‘t_experience_tbl’.  This means that every employee can now store his experience list in the same row.  Indirectly, experience list itself is a table of information (CompanyName, Position, NoOfYears).  This table is being stored as a part of single row in the table ‘employees’, which is what the NESTED TABLE is.

Even though the NESTED TABLE is logically part of the ‘employees’ table, it is stored externally (with a different table name ‘experiences_tab’) from the main table.  The following statement inserts a row into that table.

insert into employees values
(
    ‘jag’,
    t_experience_tbl
    (
        t_experience(‘abc company’,’Software Engineer’,3),
        t_experience(‘xyz company’,’System Analyst’,2),
        t_experience(‘mnp company’,’Research fellow’,4)
     )
);

Better not confuse ourselves with the example above.  ‘t_experience_tbl’ is a TABLE TYPE based on ‘t_experience’ OBJECT TYPE.  So, ‘t_experience_tbl’ can have any number of OBJECTs of TYPE ‘t_experience’.  The same concept is practiced above.  And you can insert any number of experiences for a single row.

{mospagebreak title=Working with DML statements using NESTED TABLE with OBJECT TYPE}

The following SELECT statement gives the respective details of NESTED TABLE available in a single row of parent table:

select a.CompanyName,a.NoOfYears from
table(select experiences from employees where name=’jag’) a

You can also replace the word ‘table’ with another keyword ‘the’ as follows:

select a.CompanyName,a.NoOfYears from
the(select experiences from employees where name=’jag’) a

Any of the above two examples performs two operations.  First, it reads an ‘employees’ row with the given name.  Then, it reads the elements stored in that row’s nested table.  For better readability, I used ‘table’ instead of ‘the’.  You can even replace the field names with ‘*’ as follows:

select a.* from
table(select experiences from employees where name=’jag’) a

By now, we already know how to retrieve information from NESTED TABLEs.  Now we need to know how to insert rows to the NESTED TABLE (not to the main table ‘employees’)?  This means I wanted to add another ‘experience’ row to the same employee ‘jag’.  The following statement does it.

INSERT INTO
TABLE(SELECT experiences FROM employees WHERE name = ‘jag’)
VALUES
(
   
t_experience(‘efg company’,’Professor’,2)
);

If you compare the above example with previous SELECT statements, you should be able to understand and there is nothing new from any angle.  Actually it is not necessary to use ‘t_experience’ in the above statement, you can also insert it as follows:

INSERT INTO
TABLE(SELECT experiences FROM employees WHERE name = ‘jag’)
VALUES
(
    
‘efg company’,’Professor’,2
);

But for better readability, I suggest you to use the TYPE name along with values.  You can even modify the existing rows using UPDATE statement as follows:

UPDATE
TABLE(SELECT experiences FROM employees WHERE name = ‘jag’)
set NoOfYears=5
where CompanyName=’abc company';

You can even use DELETE statement on NESTED TABLE as follows:

DELETE FROM
TABLE(SELECT experiences FROM employees WHERE name = ‘jag’)
where CompanyName=’abc company';

If you don’t use any OBJECT type in the NESTED TABLE (like a NESTED TABLE of varchar2, like in part-3), you can use a keyword ‘column_value’ for UPDATE or DELETE statements.  It is necessary because the NESTED TABLE does not have any specific columns to address.

{mospagebreak title=Working with PL/SQL using NESTED TABLE with OBJECT TYPE}

PL/SQL nested tables represent sets of values. You can think of them as one-dimensional arrays with no upper bound. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables.  Within the database, nested tables are column types that hold sets of values. Oracle stores the rows of a nested table in no particular order. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.

The PL/SQL would be almost similar to the example presented in part-3 of my article, but with some simple enhancements.  Consider the following example:

declare
    v_experiences    t_experience_tbl;
    v_name           varchar2(20) := ‘&name';
begin
    select experiences into v_experiences
    from employees where name=v_name;
    dbms_output.put_line(‘Experience list of ‘ || v_name);
    dbms_output.put_line(‘———————–‘);
    for i in v_experiences.first .. v_experiences.last
    loop
        dbms_output.put(v_experiences(i).Position);
        dbms_output.put_line (‘,’ || v_experiences(i).NoOfYears);
     end loop;
end;

I don’t think there exists anything new from the above program.  You are just creating a TABLE based variable ‘v_experiences’, storing all the values into that variable using the SELECT statement and displaying them using a FOR loop.  It seems quite comfortable to work with single row of information (as above).  Let us consider another issue.  How to display all employees with all their experiences?  The following example shows the solution:

declare
    v_experiences    t_experience_tbl;
    TYPE t_name is TABLE OF varchar2(20);
    v_name           t_name;

begin
    select name bulk collect into v_name from employees;
    for j in v_name.first .. v_name.last
    loop
        select experiences into v_experiences
        from employees where name=v_name(j);
        dbms_output.put_line(‘———————–‘);
        dbms_output.put_line(‘Experience list of ‘ || v_name);
        dbms_output.put_line(‘———————–‘);
        for i in v_experiences.first .. v_experiences.last
        loop
            dbms_output.put(v_experiences(i).Position);
            dbms_output.put_line (‘,’ || v_experiences(i).NoOfYears);
        end loop;
    end loop;
end;

In the above program, I declared a new TABLE type ‘t_name’ and a variable ‘v_name’ based on it to hold all employee names as a bulk.  I populated all the employee names into that variable using the first SELECT statement.  I am using two FOR loops.  One loop for the employee names and the other for experience list of employee retrieved by the outer FOR loop.  It is something like the parent-child display.

The above program is NOT the only solution.  We can develop in several other ways based on the appropriate situation.

Can we INSERT, UPDATE or DELETE from NESTED TABLES using PL/SQL?  Why not?  We can do it in several ways.  We can use RECORD types, TABLE types or even individual variables to do all such tasks.   The syntax will be almost similar to the examples given in the previous section (SQL statements).  But to be bit enhanced with PL/SQL based variables in whatever way we like.  I leave it to you to try and investigate the maximum number of ways to achieve them.

{mospagebreak title=A Powerful FOR loop in PL/SQL}

Till now to work with more than one row in PL/SQL, we are using a BULK COLLECT together with TABLE type of variable.  But there is another powerful way of doing the same without using any of the above two.  Let us consider a very simple example as following:

begin
    for i in (select ename,sal from emp)
    loop
        dbms_output.put_line(i.ename || ‘,’ || i.sal);
    end loop;
end;

A wonder? Yes!  You can directly use a FOR loop with a SELECT inside it without using any complicated structures.  It is also called as a FOR loop with inline SELECT statement.  This is really a very powerful technique of doing things very fast, without even really thinking about any declarations or any such nonsense.  You can also simplify the above complicated program a bit as following:

declare
    v_experiences    t_experience_tbl;
begin
    for j in (select name from employees)
    loop
        select experiences into v_experiences
        from employees where name=j.name;
        dbms_output.put_line(‘———————–‘);
        dbms_output.put_line(‘Experience list of ‘ || j.name);
        dbms_output.put_line(‘———————–‘);
        for i in v_experiences.first .. v_experiences.last
 
       loop
            dbms_output.put(v_experiences(i).Position);
            dbms_output.put_line (‘,’ || v_experiences(i).NoOfYears);
        end loop;
    end loop;
end;

The above program removed several statements of declarations and even a SELECT also.  Not only that, this also looks very simplified in terms of readability.

We shall discuss in my up-coming articles about the pros and cons of each approach, but not yet.  The reason we must wait is that I need to cover another powerful topic called as CURSOR (which also does the same as above and bit more too).  After completing the explanations of all approaches, then it would be best to discuss the pros and cons of each and every approach in comparison with other approaches.  I hope you do agree with me.

[gp-comments width="770" linklove="off" ]

chat