Home » SQL & PL/SQL » SQL & PL/SQL » Oracle equivalent of SQL Server table-valued functions (Oracle 10g Enterprise (10.2.0.1.0))
Oracle equivalent of SQL Server table-valued functions [message #378100] Sun, 28 December 2008 21:10 Go to next message
SydneyDotNetter
Messages: 4
Registered: December 2008
Location: Sydney, Australia
Junior Member
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


------------------------------------------------------------
Re: Oracle equivalent of SQL Server table-valued functions [message #378104 is a reply to message #378100] Sun, 28 December 2008 22:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I am not sure if the point is to reproduce a table-valued function or to reproduce a hierarchical query, so I have provided both below. If all you are trying to reproduce is a hierarchical query, then the second method, without any function or types, is the simplest in Oracle. I have provided examples of full and partial hierarchies for both. I also added level columns to show the hierarchy.

-- test tables and data:
SCOTT@orcl_11g> SELECT * FROM department
  2  /

        ID NAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SCOTT@orcl_11g> SELECT * FROM people
  2  /

        ID NAME         PARENTID DEPARTMENTID
---------- ---------- ---------- ------------
      7369 SMITH            7902           20
      7499 ALLEN            7698           30
      7521 WARD             7698           30
      7566 JONES            7839           20
      7654 MARTIN           7698           30
      7698 BLAKE            7839           30
      7782 CLARK            7839           10
      7788 SCOTT            7566           20
      7839 KING                            10
      7844 TURNER           7698           30
      7876 ADAMS            7788           20
      7900 JAMES            7698           30
      7902 FORD             7566           20
      7934 MILLER           7782           10

14 rows selected.


-- counterpart of table-valued function (the hard way):
SCOTT@orcl_11g> CREATE OR REPLACE TYPE results_typ AS OBJECT
  2    (id	      INT,
  3  	name	      VARCHAR2(15),
  4  	parentid      INT,
  5  	departmentid  INT,
  6  	lvl	      INT);
  7  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE results_tab AS TABLE OF results_typ;
  2  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION getChildItems
  2    (p_startingID INT)
  3    RETURN results_tab
  4  AS
  5    v_results results_tab := results_tab();
  6  BEGIN
  7    FOR rc IN
  8  	 (SELECT  id, name, parentid, departmentid, LEVEL AS lvl
  9  	  FROM	  people
 10  	  START   WITH id = p_startingID
 11  	  CONNECT BY PRIOR id = parentid)
 12    LOOP
 13  	 v_results.EXTEND;
 14  	 v_results (v_results.COUNT) :=
 15  	   resultS_typ (rc.id, rc.name, rc.parentid, rc.departmentid, rc.lvl);
 16    END LOOP;
 17    RETURN v_results;
 18  END getChildItems;
 19  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT d.name, p.name, p.id, p.parentid, p.lvl
  2  FROM   department d
  3  INNER  JOIN TABLE (getChildItems (7839)) p ON d.id = p.departmentid
  4  /

NAME           NAME                    ID   PARENTID        LVL
-------------- --------------- ---------- ---------- ----------
ACCOUNTING     KING                  7839                     1
RESEARCH       JONES                 7566       7839          2
RESEARCH       SCOTT                 7788       7566          3
RESEARCH       ADAMS                 7876       7788          4
RESEARCH       FORD                  7902       7566          3
RESEARCH       SMITH                 7369       7902          4
SALES          BLAKE                 7698       7839          2
SALES          ALLEN                 7499       7698          3
SALES          WARD                  7521       7698          3
SALES          MARTIN                7654       7698          3
SALES          TURNER                7844       7698          3
SALES          JAMES                 7900       7698          3
ACCOUNTING     CLARK                 7782       7839          2
ACCOUNTING     MILLER                7934       7782          3

14 rows selected.

SCOTT@orcl_11g> SELECT d.name, p.name, p.id, p.parentid, p.lvl
  2  FROM   department d
  3  INNER  JOIN TABLE (getChildItems (7566)) p ON d.id = p.departmentid
  4  /

NAME           NAME                    ID   PARENTID        LVL
-------------- --------------- ---------- ---------- ----------
RESEARCH       JONES                 7566       7839          1
RESEARCH       SCOTT                 7788       7566          2
RESEARCH       ADAMS                 7876       7788          3
RESEARCH       FORD                  7902       7566          2
RESEARCH       SMITH                 7369       7902          3


-- Oracle hierarchical query (the easy way):
SCOTT@orcl_11g> SELECT	d.name, p.name, p.id, p.parentid, LEVEL AS lvl
  2  FROM    department d
  3  INNER   JOIN people p ON d.id = p.departmentid
  4  START   WITH p.id = 7839
  5  CONNECT BY PRIOR p.id = p.parentid
  6  /

NAME           NAME               ID   PARENTID        LVL
-------------- ---------- ---------- ---------- ----------
ACCOUNTING     KING             7839                     1
RESEARCH       JONES            7566       7839          2
RESEARCH       SCOTT            7788       7566          3
RESEARCH       ADAMS            7876       7788          4
RESEARCH       FORD             7902       7566          3
RESEARCH       SMITH            7369       7902          4
SALES          BLAKE            7698       7839          2
SALES          ALLEN            7499       7698          3
SALES          WARD             7521       7698          3
SALES          MARTIN           7654       7698          3
SALES          TURNER           7844       7698          3
SALES          JAMES            7900       7698          3
ACCOUNTING     CLARK            7782       7839          2
ACCOUNTING     MILLER           7934       7782          3

14 rows selected.

SCOTT@orcl_11g> SELECT	d.name, p.name, p.id, p.parentid, LEVEL AS lvl
  2  FROM    department d
  3  INNER   JOIN people p ON d.id = p.departmentid
  4  START   WITH p.id = 7566
  5  CONNECT BY PRIOR p.id = p.parentid
  6  /

NAME           NAME               ID   PARENTID        LVL
-------------- ---------- ---------- ---------- ----------
RESEARCH       JONES            7566       7839          1
RESEARCH       SCOTT            7788       7566          2
RESEARCH       ADAMS            7876       7788          3
RESEARCH       FORD             7902       7566          2
RESEARCH       SMITH            7369       7902          3

SCOTT@orcl_11g> 

Re: Oracle equivalent of SQL Server table-valued functions [message #378107 is a reply to message #378100] Sun, 28 December 2008 23:24 Go to previous messageGo to next message
SydneyDotNetter
Messages: 4
Registered: December 2008
Location: Sydney, Australia
Junior Member
Dear Barbara,

What you have provided above is absolute gold Smile
This has answered perfectly what i was looking for. I had been able to do recursion in Oracle just fine with start...connect by syntax, but returning it as a table from a function and joining on that from outside the function had me stumped.
I've used the code below (and made adjustments to suit the exact schema here) and it has worked PEFECTLY...and first time!

Thank you so much for your time and help in posting this solution, i greatly appreciate it and it will help immensely. I've seen other SQL Server developers post similar queries elsewhere and none are anywhere as clear as this...or even work for that matter.

Cheers,

Tony. Cool

[Updated on: Sun, 28 December 2008 23:24]

Report message to a moderator

Re: Oracle equivalent of SQL Server table-valued functions [message #378120 is a reply to message #378107] Mon, 29 December 2008 01:05 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, before posting any code or SQL statement, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Removing multiple tables in one go [merged]
Next Topic: problem in nested block
Goto Forum:
  


Current Time: Sat Dec 10 20:34:55 CST 2016

Total time taken to generate the page: 0.05592 seconds