下面是用mssql写的，要用oracle写怎么办

[csyangzh]

CREATE FUNCTION sf_FindAFlowID(@FlowID varchar(50))
RETURNS @TmpTbl table(PFlowID varchar(50))
AS
BEGIN
DECLARE @TmpID varchar(50);
DECLARE @ParentID varchar(50);
SET @ParentID=@FlowID;
WHILE (@parentid<>'')
BEGIN
SET @TmpID=@ParentID;
SELECT @ParentID=parentid FROM T_Workflow_FlowMain WHERE Flowid=@TmpID
END;
insert into @TmpTbl(PFlowID) values(@TmpID);
RETURN
END

[qiyousyc]

CREATE FUNCTION sf_FindAFlowID(FlowID varchar2(50))
RETURNS TmpTbl varchar2(50)
AS
DECLARE
TmpID varchar2(50);
ParentID varchar2(50);
BEGIN
ParentID:=FlowID;
WHILE (parentid<>'')
BEGIN
TmpID:=ParentID;
SELECT parentid into ParentID FROM T_Workflow_FlowMain WHERE Flowid=TmpID;
END;
insert into TmpTbl(PFlowID) values(TmpID);
RETURN ;
END

[beckhambobo]

明白楼主意思，想利用函数返回记录集，显示结构是树型结构吧
CREATE OR REPLACE
package pkg_test as
type myrctype is ref cursor;
end pkg_test;
/
CREATE FUNCTION sf_FindAFlowID(p_FlowID in varchar2)
RETURN pkg_test.myrctype
AS
v_rc pkg_test.myrctype;
str varchar2(50);
BEGIN
str:='select ParentID from T_Workflow_FlowMain start with Flowid='||p_FlowID||' connect by prior ParentID = Flowid';
open v_rc for str;
return v_rc;
END;
/

declare
v_rc pkg_test.myrctype;
v_ParentID varchar2(50);
begin
v_rc:=sf_FindAFlowID(...);
loop
fetch v_rc into v_ParentID;
exit when v_rc%notfound;
dbms_output.put_line(v_ParentID);
end loop;
end;
/

[csyangzh]

我怎么无法在pl/sql developer测试通过

[beckhambobo]

哪里出错了，用pl/sql developer单步测试

[csyangzh]

在 open v_rc for str 包无效的列名

[csyangzh]

我不理解你写的代码，请解释一下好吗？我在程序里掉用要怎么处理

[beckhambobo]

包头创建了吗，按照上面创建顺序不会出错.

一、
用包头创建一个游标变量
二、
函数返回记录集
三、
显示结果集

[csyangzh]

start with Flowid='||p_FlowID||' connect by prior ParentID = Flowid'
这句话我看不懂

在程序里怎么用

[beckhambobo]

请参考:
http://expert.csdn.net/Expert/topic/1551/1551178.xml?temp=7.024783E-02