Oracle equivalent of SQL Server table-valued functions
Hi,
The company i have started working for supports both SQL Server and Oracle for the product we release to clients.
Being from a SQL Server background, i am basically new to Oracle. Where i am stumped at the moment is the equivalent of
table-valued functions in Oracle. In SQL Server we have functions which using recursion, pass back a table of hierachal data (e.g.
a hierachical list of people and their managers or reports). Then, joins etc are done with the table set returned from the function to manipulate other data.
In Oracle, i'm having trouble finding an equivalent of this, and given the tight timescales i don't have much of a time window
to research...so i would appreciate any advice here from those in the Oracle know-how!
A simplified example of what we are doing is below, and contains exactly the same logic as we have in our SQL Server database.
This is basically getting a list of people underneath someone, and the departments they work in.
Any Oracle equivalent code/suggestions would be much appreciated.
People table (id int,name varchar,parentid int, departmentid int)
Department (id, name)
---recursive function to get a list of people underneath someone (starting with their ID) -----
create function getChildItems(@startingID int)
returns @results table(id int, name varchar(50), parentid int, departmentid int)
as
begin
with recurseChildren(id,name,parentid,departmentid)
as
(
select id, name, parentid,departmentid
from people
where id=@startingID
union all
select p2.id, p2.name,p2.parentid, p2.departmentid
from people P2
inner join recurseChildren RC on P2.parentid=RC.id
)
INSERT INTO @results(id, name, parentid,departmentid) select * from recurseChildren
return
end
---other code which references the function and joins ----
select d.name, p.name, p.id, p.parentid
from department d
inner join getChildItems(2) p on d.id=p.departmentid
------------------------------------------------------------
