函数如何返回表值？

[inshua]

在 SQL Server 2000 里，函数可以返回表值，由于具备表的形态，可以 select from,可以 join，看起来和表一样，想必高手都知道。
请问 oracle 的函数如何返回表值？

下面的代码用来返回一棵树，如果能直接翻译成 oracle 的写法就更好了。 thx ～

[guangli_zhang]

学习

[inshua]

create table tbTree(
id int,
name nvarchar(4),
parent_id int
)
go

insert 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);
go

create 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

[waterfirer]

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

[inshua]

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

[qiaozhiwei]

游标返回记录集的例子:(供参考)
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;
/

[waterfirer]

也是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;

[inshua]

谢谢～

不能像 select * from pkg_test.get(0) 这样写？
因为我那个取树的函数是取骨架表的，细节资料分散在各自的表里，所以经常要用到 join 。

照这样说，也许只能封装成存储过程吧？

[inshua]

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