首先要弄两个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进行传参:

呵呵。

posted on 2017-03-14 09:51  vinsonLu  阅读(14826)  评论(0编辑  收藏  举报