Pipelined Function Example
by Marcin Jankowski
02nd February 2011 AD
Version 1.1
Table of Contents
Overview
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.
Example
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.
02
03 Type Some_Strange_Name_R Is Record(
04 Sample_Name_1 Varchar2(10),
05 Sample_Name_2 Date
06 );
07
08 Type Some_Strange_Name_T Is Table Of Some_Strange_Name_R;
09
10 Function Get_Data(
11 P_Some_Parameter_I Varchar
12 ) Return Some_Strange_Name_T Pipelined;
13
14 End Pipelined_Example;
15 /
16
17 Create Or Replace Package Body Pipelined_Example As
18
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;
24 Begin
25
26 For K In 1..10 Loop
27
28 Local_Record.Sample_Name_1 := Trim(To_Char(Sysdate+K,'Day'));
29 Local_Record.Sample_Name_2 := Sysdate+K;
30
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;
35
36 Return;
37 Exception
38 When Some_Exception Then
39 -- Do something
40 Return;
41 End Get_Data;
42
43 End Pipelined_Example;
44 /
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
Line 01:
Creating package specification for "Pipelined_Example" package
Lines 03-06:
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.
Line 08:
Definition of the collection type. It is table of the previous record type.
Line 12:
Specification of the function. Please not that the function returns the collection type and has the keyword pipelined at the end.
Line 22:
Declaration of local variable. In this example it is the record type defined in the specification
Line 32:
This is one of important bits. Here the local record is pipelined.
Line 36:
At the logical end of function simple return is used. There is no need to return specific variable.
Line 38:
Dummy exception
Line 40:
It is important that in exception there is also return command.