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
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