Home arrow Oracle arrow Database Interaction with PL/SQL, part 3

Database Interaction with PL/SQL, part 3

Jagadish Chatarji has been writing about database interactions with Oracle PL/SQL. The last part started on TYPE, RECORD, and TABLE declarations of PL/SQL. This one now goes further into TABLE, RECORD, and using them together. It will also introduce NESTED TABLES.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, part 3
  2. Using TABLE Without Interacting With Database
  3. Combining TABLE and RECORD
  4. NESTED TABLES and PL/SQL
  5. Using DML Commands on NESTED TABLES Within PL/SQL
By: Jagadish Chatarji
Rating: starstarstarstarstar / 18
June 07, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Please note that all the examples in this series have been tested only with Oracle 10g, not with all the previous versions of Oracle.  I suggest you to refer the documentation of respective version you are using if any of these programs fails to execute.

Accessing More Than One Row in PL/SQL (Using WHILE)

In our previous article, we have seen how to display more than one row using a FOR loop. Now, we will see the same program being used with WHILE loop. Let us go through the following example first.

declare
    type t_emptbl is table of emp%rowtype;
    v_emptbl    t_emptbl;
    i           integer;
begin
   
select * bulk collect into v_emptbl from emp;
   
i := v_emptbl.first;
    while i is not null
   
loop
       
dbms_output.put_line(v_emptbl(i).ename || ' earns '
          || v_emptbl(i).sal);
       
i := v_emptbl.next(i);
    end loop;
end;

In the above program, all the declarations and other parts are identical to that of the previous article, except that we used WHILE loop here. You may be wondering if the FOR loop is simpler than the WHILE loop. And of course, I admit it is. But by using this example, I wanted to introduce the collection method NEXT which can be used with a TABLE typed variable (whereas it may not be necessary when using a FOR loop).

In the above program, we use a variable 'i' as a counter. We initialize it using 'v_emptbl.first' and we increment it using 'v_emptbl.next(i)'. If no more entries are available (beyond last index), it returns NULL into the variable 'i', which is being checked as a condition of the WHILE loop.

We can also display the result in reverse using collection method PRIOR (in combination with collection method LAST as initial value of index) as shown below.

declare
    type t_emptbl is table of emp%rowtype;
   
v_emptbl    t_emptbl;
   
i           integer;
begin
   
select * bulk collect into v_emptbl from emp;
   
i := v_emptbl.last;
   
while i is not null
   
loop
       
dbms_output.put_line(v_emptbl(i).ename || ' earns '
          || v_emptbl(i).sal);
       
i := v_emptbl.prior(i);
   
end loop;
end;



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