Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query by Function

Status
Not open for further replies.

barab

Programmer
Nov 15, 2011
3
SA
Hi all,
Following :

CREATE TABLE DEPT
(
DEPT_ID NUMBER(5) NOT NULL,
DEPT_NAME VARCHAR2(100),
DEPT_PARENT NUMBER(5)
)

ALTER TABLE DEPT ADD (
CONSTRAINT P_DEPT
PRIMARY KEY
( DEPT_ID));

Insert into DEPT
(DEPT_ID, DEPT_NAME, DEPT_PARENT)
Values
(1, 'AA', NULL);
Insert into DEPT
(DEPT_ID, DEPT_NAME, DEPT_PARENT)
Values
(2, 'BB', 1);
Insert into DEPT
(DEPT_ID, DEPT_NAME, DEPT_PARENT)
Values
(3, 'CC', 2);
Insert into DEPT
(DEPT_ID, DEPT_NAME, DEPT_PARENT)
Values
(4, 'DD', 3);
Insert into DEPT
(DEPT_ID, DEPT_NAME, DEPT_PARENT)
Values
(5, 'FF', 3);
COMMIT;

CREATE TABLE DEPT_MANAGER
(
DEPT_ID NUMBER(5) NOT NULL,
EMPL_ID NUMBER(5)
)

ALTER TABLE DEPT_MANAGER ADD (
CONSTRAINT P_DEPT_MANAGER
PRIMARY KEY
( DEPT_ID,EMPL_ID));

Insert into DEPT_MANAGER
(DEPT_ID, EMPL_ID)
Values
(1, '10');
Insert into DEPT_MANAGER
(DEPT_ID, EMPL_ID)
Values
(2, '30');
Insert into DEPT_MANAGER
(DEPT_ID, EMPL_ID)
Values
(3, '40');
Insert into DEPT_MANAGER
(DEPT_ID, EMPL_ID)
Values
(4, '50');
Insert into DEPT_MANAGER
(DEPT_ID, EMPL_ID)
Values
(5, '70');
COMMIT;

SELECT * FROM DEPT
DEPT_ID DEPT_NAME DEPT_PARENT
------- --------- -----------
1 AA
2 BB 1
3 CC 2
4 DD 3
5 FF 3

SELECT * FROM DEPT_MANAGER

DEPT_ID EMPL_ID
------- -------
1 10
2 30
3 40
4 50
5 70

I want function pass empl_id retrieve dept_id and dept_name for empl_id
and all department under this department

get_dept_emp(10);

Expected Output

dept_id dept_name
------- ---------
1 AA
2 BB
3 CC
4 DD
5 FF

get_dept_emp(40);

Expected Output

dept_id dept_name
------- ---------
3 CC
4 DD
5 FF

Thanks in advance
 
You'd need a query like this:

Code:
select level, dept_id, dept_name
from 
(select d.dept_id, d.dept_name, d.dept_parent, dm.empl_id 
 from dept d, 
      dept_manager dm
where dm.dept_id = d.dept_id)
start with empl_id = 10
connect by prior dept_id = dept_parent

As for returning it via a function, there isn't a way to do this as if you were just typing the SQL in SQL*Plus. You'd have to return a cursor and then iterate through the cursor. Alternatively, you could use a pipeline function.

 
Thanks Dagon
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top