Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Mar 12, 2003

Pipelined Table Function

By DatabaseJournal.com Staff



>>Script Language and Platform: Oracle PL/SQL
The purpose of this code is to provide an example of the new pipelined and parallel PL/SQL table functions available in Oracle9i Database.

Author: Claudiu Ariton


Script Code: create table phones
(cust_id number(12) primary key,
phone1 varchar2(50),
phone2 varchar2(50),
phone3 varchar2(50),
phone4 varchar2(50),
phone5 varchar2(50)
)
/

-- insert data into table
begin

 for i in 1..1000000 loop
  insert into phones values (i,i+1,i+2,i+3,i+4,i+5);

  if mod(i,1000)=0 then
    commit;
  end if;

 end loop;

 commit;

end;
/
 
create or replace package TypePkg is
 type PhoneRecord is RECORD 
(cust_id number(12),
 phone1 varchar2(50),
 phone2 varchar2(50),
 phone3 varchar2(50),
 phone4 varchar2(50),
 phone5 varchar2(50));
 type PhoneCursorType is REF CURSOR return PhoneRecord;  
end;
/


create or replace type PhoneResult as object 
( cust_id  number(12),
phone varchar2(50) );
/

create or replace type PhoneResultTab as table of PhoneResult;    
/

create or replace function GetPhones (inrecs IN TypePkg.PhoneCursorType)

return PhoneResultTab pipelined 
order inrecs by ( cust_id ) 
parallel_enable ( partition inrecs by Hash ( cust_id ) ) 
as
 
 currec PhoneResult := PhoneResult(null, null); 
 initrec TypePkg.PhoneRecord;

begin

loop
 fetch inrecs into initrec.cust_id, initrec.phone1, initrec.phone2,
initrec.phone3, initrec.phone4, initrec.phone5;         
 exit when inrecs%NOTFOUND;  

 currec.cust_id:=initrec.cust_id;

 if initrec.phone1 is not null then
  currec.phone:=initrec.phone1;
  pipe row(currec);
 end if;

 if initrec.phone2 is not null then
  currec.phone:=initrec.phone2;
  pipe row(currec);
 end if;

 if initrec.phone3 is not null then
  currec.phone:=initrec.phone3;
  pipe row(currec);
 end if;

 if initrec.phone4 is not null then
  currec.phone:=initrec.phone4;
  pipe row(currec);
 end if;

 if initrec.phone5 is not null then
  currec.phone:=initrec.phone5;
  pipe row(currec);
 end if;

end loop;

close inrecs;
return;    

end;
/

***************************************************************** 
Test: 

select * from 
TABLE(CAST(GetPhones(cursor(select
cust_id,phone1,phone2,phone3,phone4,phone5 from phones)) AS
PhoneResultTab)); 
/ 

select /*+ USE_NL(vt) */ cust_id,phone from
TABLE(CAST(GetPhones(cursor(select
cust_id,phone1,phone2,phone3,phone4,phone5 from phones)) AS
PhoneResultTab)) vt
where exists
(select /*+ USE_NL(p) */ cust_id,phone1 from phones p where
p.cust_id=vt.cust_id and p.PHONE1=vt.phone)
/


Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued


Back to Database Journal Home



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM