I recently had an issue where I needed to retrieve a list of values from a PLSQL function and include the returned list in a SQL “where in” clause.
Eg. “Select * from TB_Debtors where ID in FN_GetOverDueDebtors();”
Obviously the function FN_GetOverDueDebtors could only return one value and therefore this SQL could only ever return one record (assuming that ID is the primary key).
The use of a function in this example may seem to be an over kill as you could simply write something like “where ID in (Select ID from TB_OverDueDebtors)”
However in cases where we would need some more complicated checking and filtering, then a function would be useful.
Introducing Pipelined PLSQL Functions:
Pipelined functions were introduced in Oracle 9i and allows a function to become a “virtual table”. Firstly the collection type being returned needs to be defined on the database.
Create or Replace Type IDS as Table of Number;
This creates a data collection type in the DB that can contain numeric values.
Now the function definition:
CURSOR cGetDebtors IS SELECT ID, Name, LastPaidDate From TB_Debtors
Where LastPaidDate > sysdate - 10;
Fetch cGetDebtors Into tID, tName, tLastPaidDate;
Exit When cGetDebtors %NotFound;
-- Some extra checking
If tID >= 500 then
If sysdate – tLastPaidDate> 20 then
-- For ids >= 500 we are allowing 20 days to pay
-- For ids < 500 we are allowing 10 days to pay
“Select * from TB_Debtors where ID in FN_GetOverDueDebtors();” will now treat FN_GetOverDueDebtors as a table and return a row for each ID returned by the function.
It can be called directly by wrapping a function with the TABLE operator.
“Select * from TABLE(FN_GetOverDueDebtors());”
I have also found pipelined functions useful in a scenario where I needed to return 10 records even if >10 records matched the where clause. The function simply piped out rows representing the non-existing records with values of “N/A”.
- Back to Blogs