请教!Oracle存储过程如何返回多个结果集!
现在有一个任务,就是需要把一个SQL server中的存储过程改写成Oracle下的。
遇到的问题是:
在SQL Server的存储过程中,在while循环下,有select语句。于是返回多个结果集
但在Oracle的存储过程中,返回结果集得用游标,如果把游标放到while循环下,会被一次次替换,最后只剩最后一张表了。
所以我想问各位前辈, 怎样才能在Oracle存储过程中返回多个结果集
SQL Server中部分代码:
while (@PointerPrev < LEN(@ID))
Begin
Set @PointerCurr=CharIndex(',',@ID,@PointerPrev)
if(@PointerCurr>0)
Begin
set @TId=cast(SUBSTRING(@ID,@PointerPrev,@PointerCurr-@PointerPrev) as int)
EXEC [dbo].[GetParentsById] --这句是调用另一个存储过程,返回一张表。
@id = @TID
SET @PointerPrev = @PointerCurr+1
End
else
Break
End
Oracle部分代码:
while(pointerprev<length(ids)) loop
pointercurr:=instr(ids,',',pointerprev);
if(pointercurr>0) then
TID:=cast(substr(ids,pointerprev,pointercurr-pointerprev) as int);
open cur_sls for --在while循环中用游标
with t as
(
select distinct t.*,level as lev from CG2 t
start with id=TID
connect by prior pid=id
order by level desc
)select t.*,CG_Resource.showFileName, dbo.CG_Resource.uniqueName AS serviceName, dbo.CG_Resource.dataUrl AS serviceUrl,
dbo.CG_Resource.fishnetUrl, dbo.CG_Resource.shpDataPath AS dataPath, dbo.CG_Resource.ip, dbo.CG_Resource.dataSize, dbo.CG_Resource.fields AS fieldNames,
dbo.CG_Resource.recordCount AS dataCount, dbo.CG_Resource.dataType
FROM t LEFT OUTER JOIN CG_Resource ON t.dataid =CG_Resource.id
WHERE (t.name IS NOT NULL);
pointerprev:=pointercurr+1;
else
exit;
end if;
end loop;