Database Interaction with PL/SQL, part 3 - Combining TABLE and RECORD
(Page 3 of 5 )
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.
Next: NESTED TABLES and PL/SQL >>
More Oracle Articles
More By Jagadish Chatarji
