Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL, part 2

Accessing more than one row in PL/SQL (TYPE with TABLE) - Oracle

This article picks up where part one left off. You will learn more about how to use %ROWTYPE and %TYPE, and be introduced to TYPE, RECORD, and TABLE declarations in PL/SQL.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, part 2
  2. UPDATE with RETURNING clause
  3. TYPE with RECORD declaration
  4. Accessing more than one row in PL/SQL (TYPE with TABLE)
By: Jagadish Chatarji
Rating: starstarstarstarstar / 20
May 31, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In all of the previous sample programs, we discussed retrieving and manipulating only a single row. How about retrieving more than one (or a set of rows based on a SELECT statement)? Before going into too many explanations, let us go through the following beautiful example:

declare

type t_emptbl is table of emp%rowtype;

v_emptbl t_emptbl;

begin

select * bulk collect into v_emptbl from emp;

for i in v_emptbl.first .. v_emptbl.last

loop

dbms_output.put_line(v_emptbl(i).ename || ' earns ' || v_emptbl(i).sal);

end loop;

end;

The above program displays all of the employee names with their respective salaries. Here I introduced a new declaration, TYPE..IS TABLE. Let me explain the above program part by part.

type t_emptbl is table of emp%rowtype;

The above statement defines a new data type, "t_emptbl" as an in-memory table representation. This means that it can store any number of rows (just like a table) from the table "emp" (as we included "emp%rowtype") in memory (without having any relation to the physical database). Make sure that it is only a data type. If you know the C language, you can think of it as a structured array.

v_emptbl t_emptbl;

The above statement declares a variable "v_emptbl" which is authorized to store information based on the table type defined by "t_emptbl."

select * bulk collect into v_emptbl from emp;

This is the most important statement, which does all of the operations. If you remove BULK COLLECT, it would raise an error (because you cannot work with more than one row at a time). The BULK COLLECT lets you fetch any number of rows based on the SELECT statement issued and store all of them in a TABLE typed variable. From the above statement, it is quite clear that we are retrieving all of the rows from the table "emp" and collecting them into the TABLE typed variable "v_emptbl."

Now the next issue is to fetch all those rows of values and display them back on the screen. In general, each and every row of the TABLE typed variable is indexed, starting from one (just like an array). We retrieve each and every row with that index, using a FOR loop.

for i in v_emptbl.first .. v_emptbl.last

loop

dbms_output.put_line(v_emptbl(i).ename || ' earns ' || v_emptbl(i).sal);

end loop;

The above part is a FOR loop that starts from the first index of the TABLE typed variable "v_emptbl" and ends at the last index of the same. Here "first" and "last" are keywords which can be used with any TABLE typed variables. We retrieve each and every field of information by using the notation TABLE(INDEX).FIELDNAME (which is done in the DBMS_OUTPUT statement).



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