There is now a task, it is need to changed a stored procedure in the SQL server into Oracle,
The problem is:
In the process of SQL Server storage, under the while loop, a select statement, then return multiple result sets
But in the Oracle stored procedure, return a result set with cursor, if put the cursor in the while loop, will be replaced, once again before the end of the last table,
So I want to ask everybody, how can return multiple result sets in the Oracle stored procedure

Part of the code from the SQL Server:
While (@ PointerPrev & lt; LEN (@ ID))
The Begin
The Set @ PointerCurr=CharIndex (', '@ ID, @ PointerPrev)
If (@ PointerCurr> 0)
The Begin
Set @ TId=cast (SUBSTRING (@ ID, @ PointerPrev, @ PointerCurr - @ PointerPrev) as int)
the EXEC [dbo] [GetParentsById] - this is to call a stored procedure, return to a table,
@ id=@ dar
The SET @ PointerPrev=@ PointerCurr + 1
End
The else
Break
End


Oracle code:
While (pointerprevPointercurr:=instr (', 'ids, pointerprev);
If (pointercurr> 0) then
TID=cast (substr (ids, pointerprev pointercurr - pointerprev) as int);
open cur_sls for - with the cursor in the while loop
With t as
(
Select distinct t *, level as Lev from CG2 t
Start with id=dar
Connect by the prior pid=id
The 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
The FROM t LEFT OUTER JOIN CG_Resource ON t.d ataid=CG_Resource. Id
The WHERE (t.n ame IS NOT NULL).
Pointerprev:=pointercurr + 1;
The else
The exit;
end if;
End loop;

CodePudding user response:

Result set is used in Oracle cursor, if you want to return multiple result sets, must use multiple output parameters, cursor type is usually not recommended to do so,
Since access to the Oracle, work and study are not seen in one such instance,

CodePudding user response:

You said return multiple result sets, in fact is the use of the SQL statement to achieve,
Do not need to adopt in the process of storage by circle output,

CodePudding user response:

Thank you, because I am a intern, a task is to make someone else's rewriting stored in SQL server process, he is thinking over there like that, I'll think of some way to implement, in particular, I want to know, oracle stored procedure can realize my above requirement, it seems, is not easy is

CodePudding user response:

Another idea is to say, can output parameters of the stored procedure is a cursor type array, and then put the results in an array, each cycle
But if no cursor type array "in oracle?

CodePudding user response:

Take a return a list of three examples of the result set.
The implementation code is roughly as follows:

 
The create or replace p_test 
(
Cur1 out sys_refcursor, 
Cur2 out sys_refcursor, 
Cur3 out sys_refcursor 
) 
Is 
The begin 
Select a t1. * into cur1 from t1; 
The select t2. * into cur2 from t2. 
The select t3. * into cur3 from t3. 
The end; 

CodePudding user response:

Such code performance is bad, readability and robustness is very poor, return a list of more than a process, itself is a kind of very good solution, under any circumstances are not recommended to do this,