oracle自定义函数返回结果集

首先要弄两个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_table
as
    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 || '<span class="btn btn-defaut select-btn select-site-wrap" title="' || c2.name ||  '">' || c2.name ||  '</span>';
      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进行传参：

呵呵。

转载于:https://www.cnblogs.com/vinsonLu/p/6546926.html