问题描述

将以下的sqlserver的函数写成oracle的函数怎么改？

ALTER function [dbo].getSubDepartment)
returns @tabinfo table(deptid NUMERIC(20),deptname varchar(100),parentid numeric(20),level int)
as
begin
declare @cid int
set @cid = 1

insert @tabinfo

select deptid,deptname,parentid,@cid from DEPARTMENT where deptid = @deptid

while @@rowcount>0
begin
set @cid = @cid + 1

    insert @tabinfo
    select distinct ta.deptid,ta.deptname,ta.parentid,@cid
    from DEPARTMENT as ta join @tabinfo as tb
    on ta.parentid = tb.deptid
    where tb.level = @cid-1
end
return

end