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

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