Oracle equivalent of SQL Server table-valued functions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Oracle equivalent of SQL Server table-valued functions

  1. #1
    Join Date
    Dec 2008
    Posts
    0

    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


    ------------------------------------------------------------

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Talking Hierachical Query

    In Oracle you would need to use a Hierarchical query.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width