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

A Powerful FOR loop in PL/SQL - 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

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.



 
 
>>> 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: