SQL Server的函数移植到Oracle时，对内存表的操作问题，请高手帮忙

[sglogin]

一个[SQL Server的函数，涉及到对内存表的操作

-- =============================================
-- Description:	<递归取上级单位编码>
-- para: <UnitID>
-- =============================================
CREATE FUNCTION [dbo].[fGetUnitUpInfo]
(
	@strUnitID varchar(20)
)
returns @tabUnit table
(
	UnitID  int  NULL
	,UnitDes  nvarchar(20)  NULL
	,UpUnitID  int  NULL
)    
as
begin
	insert @tabUnit
		SELECT UnitID, UnitDes, UpUnitID FROM cauUnit
			WHERE (UnitID=(select UpUnitID from cauUnit WHERE 

UnitID=@strUnitID))
	while @@rowcount>0
	begin
		insert @tabUnit
			select UnitID, UnitDes, UpUnitID from cauUnit
				where UnitID in (SELECT UpUnitID FROM @tabUnit)
					and UnitID not in (SELECT UnitID FROM @tabUnit)
	end
	return
end

根据临时掌握的Oracle语法转为Oracle的函数，但是涉及内存表读取的问题解决不了：

--先声明一个对象，包含返回表的所有字段
CREATE TYPE R_GETUNITUPINFO AS OBJECT
(
UNITID INT NULL
,UNITDES NVARCHAR2(20) NULL
,UPUNITID INT NULL
);
--再声明这个对象的表类型，用来返回
CREATE TYPE T_GETUNITUPINFO AS TABLE OF R_GETUNITUPINFO;
--最后声明函数部分
-- =============================================
-- DESCRIPTION: <递归取上级单位编码>
-- PARA: <UNITID>
-- =============================================
CREATE OR REPLACE FUNCTION FGETUNITUPINFO
(
AR_STRUNITID VARCHAR
)
 RETURN T_GETUNITUPINFO PIPELINED AS ROWLINE R_GETUNITUPINFO;
BEGIN

  FOR ROW1 IN (
    SELECT UNITID,UNITDES,UPUNITID FROM CAUUNIT
      WHERE (UNITID=(SELECT UPUNITID FROM CAUUNIT WHERE UNITID=AR_STRUNITID))
  ) LOOP
    ROWLINE := R_GETUNITUPINFO(ROW1.UNITID, ROW1.UNITDES, ROW1.UPUNITID);
    PIPE ROW (ROWLINE);
  END LOOP;

  FOR ROW2 IN (
    SELECT UNITID,UNITDES,UPUNITID FROM CAUUNIT
      WHERE UNITID IN (SELECT UPUNITID FROM ROWLINE)
        AND UNITID NOT IN (SELECT UNITID FROM ROWLINE)
  ) LOOP
    ROWLINE := R_GETUNITUPINFO(ROW2.UNITID, ROW2.UNITDES, ROW2.UPUNITID);
    PIPE ROW (ROWLINE);
  END LOOP;

  RETURN;
END;

提示ROWLINE，视图或表不存在
请问各位高手这个问题能否解决呀？

[bw555]

oracle 树形结构比sqlsever简单多了，没必要使用循环 内存表使用的意义也不是很大 利用下面语句可以一次性将部门的所有上级部门查出来

SELECT UNITID,UNITDES,UPUNITID FROM CAUUNIT
start with UNITID=AR_STRUNITID
connect by prior UPUNITID=UNITID

[sglogin]

谢谢，很简单呀，那就没必要使用什么自定义函数了，结贴