Pipelined Function Example
by Marcin Jankowski
02nd February 2011 AD
Table of Contents
Pipelined functions operate like a table. It means that they can be used as the source of data instead of the regular table or view.
Their main use is whenever regular table operations are required on set of data that is either dynamically created or heavily processed.
This is example of using pipelined function. This particular one is not extremely brilliant or useful but serves the demonstration purposes.
What it does is to list the Weekdays and dates in the next 10 days omitting the weekday passed as parameter.
03 Type Some_Strange_Name_R Is Record(
04 Sample_Name_1 Varchar2(10),
05 Sample_Name_2 Date
08 Type Some_Strange_Name_T Is Table Of Some_Strange_Name_R;
10 Function Get_Data(
11 P_Some_Parameter_I Varchar
12 ) Return Some_Strange_Name_T Pipelined;
14 End Pipelined_Example;
17 Create Or Replace Package Body Pipelined_Example As
19 Function Get_Data(
20 P_Some_Parameter_I Varchar
21 ) Return Some_Strange_Name_T Pipelined Is
22 Local_Record Some_Strange_Name_R;
23 Some_Exception Exception;
26 For K In 1..10 Loop
28 Local_Record.Sample_Name_1 := Trim(To_Char(Sysdate+K,'Day'));
29 Local_Record.Sample_Name_2 := Sysdate+K;
31 If ( Upper(Local_Record.Sample_Name_1) != Upper(P_Some_Parameter_I) ) Then
32 Pipe Row (Local_Record);
33 End If;
34 End Loop;
38 When Some_Exception Then
39 -- Do something
41 End Get_Data;
43 End Pipelined_Example;
The above function can be used now as the source of data.
Select * From Table(Pipelined_Example.Get_Data('Thursday')) Order By Sample_Name_1 Asc;Result:
Sample_Name_1 Sample_Name_2 ----------------------------- Friday 04-FEB-11 Friday 11-FEB-11 Monday 07-FEB-11 Saturday 12-FEB-11 Saturday 05-FEB-11 Sunday 06-FEB-11 Tuesday 08-FEB-11 Wednesday 09-FEB-11
Important lines in the example
Creating package specification for "Pipelined_Example" package
Definition of the type that will be returned as table. Think about it as structure of the table. The name of the type does not really matter.
Definition of the collection type. It is table of the previous record type.
Specification of the function. Please not that the function returns the collection type and has the keyword pipelined at the end.
Declaration of local variable. In this example it is the record type defined in the specification
This is one of important bits. Here the local record is pipelined.
At the logical end of function simple return is used. There is no need to return specific variable.
It is important that in exception there is also return command.