首先要弄两个type，不知道什么鬼：

1.

create or replace type obj_table as object(  id varchar2(38),  name varchar2(4000),  count varchar2(10))

2.

create or replace type t_table is table of obj_table

函数体：

create or replace function getStationListTb(s number)return t_tableas    rs t_table:= t_table();    str varchar2(4000);    i number := 0;begin    for c in (select DepartmentId from sys_department) loop    str := '';    i := 0;    for c2 in (select t2.name from sys_departmentstation t1               left join bsd_station t2 on t1.stationid=t2.stationid where t1.DepartmentId=c.departmentid) loop      str := str || '' || c2.name ||  '';      i := i + 1;    end loop;    str := substr(str,0,length(str)-1);    rs.extend;    rs(rs.count) := obj_table(c.departmentid,str,to_char(i));  end loop;return rs;end getStationListTb;

好奇怪的，这个函数我不定义参数它就出错，稍微哪里写错一点东西，就报错，又没提示哪里错，感觉好难写的；这次难得写成功了，赶紧记录下来，下次直接过来copy。

调用：

select * from table(getStationListTb(1))

那么，这个函数出来就是一张表了，可以把它看成表去使用：

SELECT t1.*,t2.name StationList，t2.count StationCount FROM SYS_Department t1      left join (select * from table(getStationListTb(1))) t2 on t1.DepartmentId=t2.id      WHERE 1=1

先前还写了个按照ID去获取每行数据的:

create or replace function getStationList(DepartmentId  VARCHAR2) return VARCHAR2    as      --定义变量      resultStr VARCHAR2(8000);    begin      for c in (select stationid                  from sys_departmentstation                  where DepartmentId=DepartmentId) loop           resultStr := resultStr || c.stationid || ',';       end loop;      return substr(resultStr,0,length(resultStr)-1);    end getStationList;

想这样直接根据ID关联数据：

SELECT t1.*,getStationList(t1.DepartmentId) StationList FROM SYS_Department t1

结果出来呵呵的，完全不像sql那样会根据每行的DepartmentId去传参执行getStationList（DepartmentId VARCHAR2）函数，而是一直使用第一行的DepartmentId进行传参：

呵呵。