Query by Function

[barab]

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

 

[Dagon]

You'd need a query like this:

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.

 

[barab]

Thanks Dagon

 

[sem]

barab,

is SYS_CONNECT_BY_PATH what you're looking for?

Regards, Dima

http://seminihin.narod.ru