create or replace function GetEmp(
inDeptID varchar2
)
return varchar2
is
declare
s varchar2(4000):='';
begin
for t_emp in (select EmpName from Emp where Dept_ID=inDeptID) loop
s := s || ', ' || EmpName;
end loop;
if lengthb(s)>0 then
return substrb(s,3);
else
return '';
end if;
end;
/
inDeptID varchar2
)
return varchar2
is
declare
s varchar2(4000):='';
begin
for t_emp in (select EmpName from Emp where Dept_ID=inDeptID) loop
s := s || ', ' || EmpName;
end loop;
if lengthb(s)>0 then
return substrb(s,3);
else
return '';
end if;
end;
/
CREATE TYPE number_list_t AS TABLE OF NUMBER;
2。
CREATE
Function CONCAT_LIST
( lst IN number_list_t, separator varchar2)
RETURN VARCHAR2 IS
ret varchar2(1000);
BEGIN
FOR j IN 1..lst.LAST LOOP
ret := ret || separator || lst(j);
END LOOP; RETURN ret;
END;
3。
SELECT MGR, CONCAT_LIST(EMPLS, ', ') EMPLOYEES from (
SELECT
MGR,
CAST(MULTISET(SELECT empno FROM EMP e WHERE e.mgr=m.MGR )
AS number_list_t) EMPLS
FROM EMP m
where m.mgr is not null
group by MGR
);