← Tutti gli articoli
PIPELINED functions will operate like a table.
27 December 2010 ·
PLSQL · Article ·
143 visite
Pipelined functions are useful if there is a need for a data source other than a table in a select statement.
A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions. Prior to Oracle Database 9, large transformations required either significant memory overhead, or storing the data in intermediate tables between each stage of the transformation. The loading process caused immense performance degradations in both cases.
create or replace type o_Company as object (
idsocieta NUMBER(11,0),
denominazione varchar2(99)
)
/
create type t_Company as table of o_Company
/
create or replace function f_CompanyNames return t_Company
pipelined
as
a_Company o_Company := o_Company(null,null);
cursor cursor_Company is select * from Societa;
rec_company cursor_Company%ROWTYPE;
begin
open cursor_Company;
loop
fetch cursor_Company into rec_company;
exit when cursor_Company%NOTFOUND;
select denominazione, idsocieta
into a_Company.denominazione, a_Company.idsocieta
from societa
where idsocieta=rec_company.idsocieta;
pipe row (a_Company);
end loop;
close cursor_Company;
return;
end;
/
select * from table(cast(f_date_varchar2(40) as t_date_varchar2));