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  |
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  |
 |
Barbara Boehmer
Messages: 9106
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 |
SydneyDotNetter
Messages: 4
Registered: December 2008
Location: Sydney, Australia
|
Junior Member |
|
|
Dear Barbara,
What you have provided above is absolute gold 
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. 
[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 |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
Goto Forum:
Current Time: Mon Nov 17 13:05:42 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
