|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
In
versions prior to 9i, ETL method adopted from within PLSQL involves
creation of intermediate tables that get created with lookup
information for source data and these intermediate tables are then
used to populate the destination tables. This type of method requires
additional space and additional processing time due to creation of the
intermediate tables and querying from them. These overheads can be
overcome starting 9i with the use of piplelined table functions. One
other advantage to using piplelined table functions is the ability of
these functions to execute in parallel which can potentially improve
the performance of these functions and the overall data processing.
This article outlines an
example on the usage of the piplelined table function. Comments are
included within the example code to give more details on the various
attributes of the piplelined table function. The data transformation
is done on the results from the dba_objects view.
(1) Create the necessary
types:
create or replace type object_record as object
(object_id number,
object_name varchar2(128),
owner varchar2(30),
created date);
/
create or replace type object_recordtable as table of object_record;
/
(2) Create the record types required to be used from within the function:
create or replace package test_pipe_pkg is
type v_objrecordtype is record (object_id number,object_name varchar2(128),owner varchar2(30),
created date);
type v_refcursor is REF CURSOR return v_objrecordtype;
end;
/
(3) Create the function:
create or replace function
testfunction (in_ref_cursor test_pipe_pkg.v_refcursor)
return object_recordtable
--
This tells the PL/SQL parser and
execution engine that this function is to be pipelined
pipelined
parallel_enable (partition in_ref_cursor by any)
is
output_rec object_record := object_record(null,null,null,null);
input_rec in_ref_cursor%ROWTYPE;
begin
loop
fetch in_ref_cursor into input_rec;
output_rec.object_id,output_rec.object_name,output_rec.owner,output_rec.created;
exit when in_ref_cursor%notfound;
output_rec.object_id := input_rec.object_id;
output_rec.object_name := input_rec.object_name;
output_rec.owner := input_rec.owner;
output_rec.created := input_rec.created + 1;
--
The 'pipe row' statement returns
a single row of data to the caller. The PL/SQL runtime uses
-- co-routines to allow the
producer (the table function) and consumer(the caller) to execute
-- concurrently; PLSQL takes care
of caching an appropriate number of rows as they are returned
-- from the function to the
caller.
pipe row(output_rec);
end loop;
close in_ref_cursor;
-- The 'return' keyword doesn't
require a return value because, unlike traditional functions, the data
is
-- returned via another
mechanism. The sole purpose of the 'return' keyword is to denote that
there is
-- no more data available.
return;
end testfunction;
/
(4) Call the function, supplying
the query to it:
select * from TABLE(testfunction(cursor(select object_id,object_name,owner,created
from dba_objects)));
|
