首页 > 数据库技术 > 详细

oracle自定义函数返回结果集

时间:2017-03-14 10:26:37      阅读:398      评论:0      收藏:0      [点我收藏+]

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

技术分享

呵呵。

oracle自定义函数返回结果集

原文:http://www.cnblogs.com/vinsonLu/p/6546926.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!