Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes - Returning PL/SQL TABLE from a FUNCTION to a PROCEDURE
(Page 2 of 5 )
In the previous section of this article, I used a PL/SQL TABLE as a parameter for the PROCEDURE. Now, I shall revise the above stated program in such a way that I will use both FUNCTION and PROCEDURE together, communicating with PL/SQL TABLEs. Let us consider the following example:
declare
type t_emptbl is table of emp%rowtype;
procedure dispEmp(p_emptbl t_emptbl) is
begin
for i in p_emptbl.first .. p_emptbl.last
loop
dbms_output.put_line(p_emptbl(i).ename ||
' earns ' || p_emptbl(i).sal);
end loop;
end;
function getEmployeeList return t_emptbl is
v_emptbl t_emptbl;
begin
select * bulk collect into v_emptbl from emp;
return v_emptbl;
end;
BEGIN
dispEmp(getEmployeeList);
END;
Let us examine the two sub-programs separately.
procedure dispEmp(p_emptbl t_emptbl) is
begin
for i in p_emptbl.first .. p_emptbl.last
loop
dbms_output.put_line(p_emptbl(i).ename || ' earns ' || p_emptbl(i).sal);
end loop;
end;
It just receives PL/SQL TABLE as parameter and displays all the information available in it.
function getEmployeeList return t_emptbl is
v_emptbl t_emptbl;
begin
select * bulk collect into v_emptbl from emp;
return v_emptbl;
end;
The above function ‘getEmployeeList’ executes the SELECT statement and the output is gathered into ‘v_emptbl’, which is returned back (or pushed out).
dispEmp(getEmployeeList);
The above is the most important statement in the program. It looks very simple, but a lot of logic is embedded in it. First of all, it executes the FUNCTION ‘getEmployeeList’. That FUNCTION returns a PL/SQL TABLE, which will be sent as a parameter to the procedure ‘dispEmp’ automatically. We need not actually store the PL/SQL TABLE returned by the FUNCTION.
Next: What are IN types of parameters? >>
More Oracle Articles
More By Jagadish Chatarji
