Let
me give you a typical scenario which we encounter everday.
Say
we have a table called employee and the fields are (or rather the most
significant fields for our discussion) are
ename
and dno .
SQL>
select substr(ename,1,25),dno from employee;
SUBSTR(ENAME,1,25)
DNO
-------------------------
----------
JACK
10
JILL
10
MASON
20
MARK
20
you
are being asked to write a small pl/sql code to return the enames of a
particular department, the normal way we would
do
is to write a procedure like
CREATE
OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(500)
/
CREATE
or replace procedure p1(dno_p IN number,ename_p OUT vcarray)
IS
CURSOR
c1 is select ename from employee where dno=dno_p;
i
number:=1;
BEGIN
ename_p
:= vcarray();
for
c2 in c1
loop
ename_p.extend;
ename_p(ename_p.count)
:=c2.ename ;
i:=i+1;
end
loop;
END;
/
To
call the above procedure , we are forced to write a pl/sql block like
SQL>
declare
2 ename vcarray ;
3 begin
4 p1(10,ename);
5 for i in 1..ename.COUNT
6 LOOP
7 dbms_output.put_line(ename(i));
8 end loop;
9 end;
10
/
JACK
JILL
PL/SQL
procedure successfully completed.
So
in the above example we are forced to write a pl/sql block to read from the
procedure.
The
below example uses pipelined functions which will make it possibel for the same
job
to be done via a function (eventhough we think of function as only returning
a
single value) and the icing on the cake is the function can be referenced as a
simple select
from
sqlplus prompt itself....
CREATE
OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(500)
/
CREATE
OR REPLACE FUNCTION get_employees (dno_p IN number)
RETURN
vcarray
PIPELINED
AS
CURSOR
c1 is select ename from employee where dno=dno_p;
BEGIN
FOR
c2 in c1
pipe row (c2.ename);
END
LOOP ;
RETURN;
END;
/
SQL>
select * from TABLE (get_employees(10) );
COLUMN_VALUE
----------------------------------------------------------------------------------------------------
JACK
JILL
