HomeOracle 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.
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.