Home arrow Oracle arrow Page 3 - Database Interaction with PL/SQL: Nested Tables

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

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.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: Nested Tables
  2. Working with DML statements using NESTED TABLE with OBJECT TYPE
  3. Working with PL/SQL using NESTED TABLE with OBJECT TYPE
  4. A Powerful FOR loop in PL/SQL
By: Jagadish Chatarji
Rating: starstarstarstarstar / 66
June 21, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: