请教sql server中的函数到oralce的问题
请问下面的函数,移植到oracle中怎么写?
关键是返回参数是一个表,可以吗?
create function GetChildOrderInfo
(@OrderID decimal)
returns @var table (
orderID decimal ,
FatherOrderID decimal, ---节点的父节点
op int --------------标识节点的层次关系
)
as
--set nocount on
begin
declare @i int
set @i=1
if @orderid<>0
begin
insert @var
select orderid,fatherorderID,0
from tm_product_tree where orderid = @orderid
insert @var
select orderid,fatherorderID,1
from tm_product_tree where Fatherorderid = @orderid
--- select * from @var
while exists(select * from @var where op = 1 )
begin
set @i = @i + 1
update @var set op = @i where op = 1
insert @var
select a.orderid,a.FatherorderID,1
from tm_product_tree as a, @var as b where a.FatherorderID = b.orderid and b.op = @i
end
end
return
end