Home arrow Oracle arrow Page 5 - Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs

Working with PL/SQL TABLE and sub-programs - Oracle

This is part 14 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we concentrated on some tips for using procedures together with the introduction to functions. In this article we will look through several examples that cover using sub-programs in a very effective manner. I suggest you to go through part two in order to follow this article properly.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs
  2. Mixing PROCEDURE and FUNCTION
  3. Using RECORD with sub-programs
  4. Returning a RECORD from FUNCTION
  5. Working with PL/SQL TABLE and sub-programs
By: Jagadish Chatarji
Rating: starstarstarstarstar / 18
August 23, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

It is not simply a database table. As you may recall from part two, it is a PL/SQL TABLE. Now I would like to work with a PL/SQL TABLE together with sub-programs. Let us consider the following program:

declare

type t_emptbl is table of emp%rowtype;

v_emptbl t_emptbl;

procedure dispEmp is

 

begin

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;

BEGIN

select * bulk collect into v_emptbl from emp;

dispEmp;

END;

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 it can store any number of rows, just like a table, from the table ‘emp’ in memory, without having any relation to a physical database. As you will note, we included ‘emp%rowtype’. Make sure that it is only a data type. If you know the C language, you can consider it 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’. The above declarations can be used globally in the entire program, including sub-programs.

select * bulk collect into v_emptbl from emp;

This is the most important statement; it performs all 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 into a TABLE typed variable. From the above statement, it is quite clear that we are retrieving all 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 in 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.

procedure dispEmp is

 

begin

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 part is a procedure named ‘dispEmp’ containing a FOR loop which 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 using the notation TABLE(INDEX).FIELDNAME (which is implemented using 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: