Working with PL/SQL using NESTED TABLE with OBJECT TYPE - Oracle

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.