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

Combining TABLE and RECORD - Oracle

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

In my previous article, I explained PL/SQL records. Now we shall combine RECORDs with TABLEs to achieve effective results in a simple way. Let us consider the following example.

 

declare
   
type t_empRec is record
   
(
       
ename emp.ename%type,
       
sal emp.sal%type,
       
deptno emp.deptno%type
   
);
   
type t_emptbl is table of t_emprec;
    v_emptbl    t_emptbl;
begin
   
select ename,sal,deptno 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 || ',' ||
         v_emptbl(i).sal || ',' || v_emptbl(i).deptno);
   
end loop;
end;

The above program retrieves 'ename', 'sal' and 'deptno' columns from 'emp' table and displays all of those details. In my previous article, I displayed the same but used %ROWTYPE. Now in this program, I am combining the definitions of RECORD and TABLE to store only the data we need (but not the entire row). The most important statement in the above program is the following:

type t_emptbl is table of t_emprec;

That statement defines a PL/SQL table named 't_emptbl'. But the content (rows) within that table should match with the structure defined in the following declaration:

type t_empRec is record
(
   
ename emp.ename%type,
   
sal emp.sal%type,
   
deptno emp.deptno%type
);

So, indirectly 't_emptbl' can have any number of rows with only the fields 'ename', 'sal' and 'deptno'. This is a wonderful technique to define PL/SQL tables with our own fields. The rest of the program is just similar to the example I gave in my previous article.



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