函数如何返回表值？

Question

在 SQL Server 2000 里，函数可以返回表值，由于具备表的形态，可以 select from,可以 join，看起来和表一样，想必高手都知道。
请问 oracle 的函数如何返回表值？下面的代码用来返回一棵树，如果能直接翻译成 oracle 的写法就更好了。 thx ～

Answer 1

create table  tbTree(
 id   int,
 name nvarchar(4),
 parent_id int 
)
goinsert into tbTree values(1,'中国',null);
insert into tbTree values(2,'江西',1);
insert into tbTree values(3,'广东',1);
insert into tbTree values(4,'南昌',2);
insert into tbTree values(5,'广州',3);
insert into tbTree values(6,'青云谱',4);
gocreate function  func_tree
(
@ID int
,@exceptId int  -- 避免的 id
)
returns @tmp table (id int,parent_id int,sort float, name nvarchar(20),level int)
begin
declare @max int --最大编号,作为相除的底
declare @base bigint
declare @level int
set @level = 0

select @max = max(id) from tbTree
set @max = @max + 1
set @base = 1

--第一级
if(@id is null)
begin
insert @tmp(id,parent_id,sort,name)
select id,parent_id,id,name from tbTree 
where parent_id is null and id <> @exceptId 
end
else
begin
insert @tmp(id,parent_id,sort,name)
select id,parent_id,id,name from tbTree 
where id = @id and id <> @exceptId
end
update @tmp set level = @level --子级
while exists(select top 1 1 from tbTree a,@tmp b 
where a.parent_id = b.ID and a.ID not in (select ID from @tmp)
and a.id <> @exceptId
    )
begin
set @level = @level + 1
set @base = @base * @max
insert into @tmp
select  a.id,a.parent_id, b.sort + cast(a.id as float) / @base,a.name, @level
from tbTree a,@tmp b
where a.parent_id = b.ID and a.ID not in (select ID from @tmp)
and a.id <> @exceptId
end

return
end
go
select * from func_tree(1,-1) order by sort
go
drop table tbTree
go
drop function func_tree
go

Answer 2

oracle中不能直接返回表值，用包和游标返回结果集吧。

Answer 3

怎么办？ 方便写一个 demo 吗？ 我的 oracle 水平为 0 ~

Answer 4

游标返回记录集的例子:(供参考)
create or replace package pkg_test
as
  type myCursor is ref cursor;
  function get(p_id number) return myCursor;
end pkg_test;create or replace package body pkg_test 
as
  function get(p_id number) return myCursor is
     rc myCursor;
     strsql varchar2(200);
  begin
     if p_id=0 then 
        open rc for select a.user_name from fnd_user a ;  
     else
        strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
        open rc for strsql using p_id;
     end if;
     return rc;  
     end get;
     
end pkg_test;
--调用
set serverout on 
declare 
  w_rc pkg_test.myCursor;
  w_name varchar2(100);
begin
  w_rc:=pkg_test.get(0);
  loop
  fetch w_rc into w_name;
        exit when w_rc%notfound;
  dbms_output.put_line(w_name);
  end loop;
end;
/

Answer 5

也是copy前辈的例子，你看看吧。
create or replace package pkg_test
as
  type myCursor is ref cursor;
  function get(p_id number) return myCursor;
end pkg_test;create or replace package body pkg_test 
as
  --输入ID 返回记录集的函数
  function get(p_id number) return myCursor is
     rc myCursor;
     strsql varchar2(200);
  begin
     if p_id=0 then 
        open rc for select a.user_name from fnd_user a ;  
     else
        strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
        open rc for strsql using p_id;
     end if;
     return rc;  
     end get;
end pkg_test;
--上面是一个返回结果集的函数,下面调用.
create or replace procedure pro_test as
    v_out pkg_test%myCursor;
    v_name varchar2(100);
begin
    v_out:=pkg_test.get(0);  --得到结果集
    loop
        fetch v_out into v_name;
        exit when v_out%notfound;
        --这里进行处理,想要处理哪一行或进行什么处理在这里进行
    end loop;
......
end;

Answer 6

谢谢～不能像 select * from pkg_test.get(0) 这样写？
因为我那个取树的函数是取骨架表的，细节资料分散在各自的表里，所以经常要用到 join 。照这样说，也许只能封装成存储过程吧？

Answer 7

问题已经解决.取树在 oracle 有现成的句法
select * from tbTree connect by prior id=parent_id
start with parent_id is null