如何在Oracle存储过程中调用含隐式结果集的存储过程并获取游标

我有一个Oracle (21c)存储过程TESTPROC，它通过DBMS_SQL.RETURN_RESULT将游标作为隐式结果集返回。现需编写另一个存储过程USETESTPROC，调用TESTPROC且不通过OUT参数获取该游标，现有代码如下：

CREATE OR REPLACE NONEDITIONABLE PROCEDURE TESTPROC 
(
  V_USERACCOUNTID IN VARCHAR2 
) AS 
  V_CURSOR SYS_REFCURSOR;
BEGIN
  OPEN V_CURSOR FOR
  SELECT * FROM USERACCOUNT WHERE ID = V_USERACCOUNTID;
  
  DBMS_SQL.RETURN_RESULT(V_CURSOR);
END TESTPROC;


CREATE OR REPLACE NONEDITIONABLE PROCEDURE USETESTPROC 
(
  V_USERACCOUNTID IN VARCHAR2 
) AS 
  V_CURSOR SYS_REFCURSOR;
BEGIN
  TESTPROC(V_USERACCOUNTID); 
  -- 如何将此处的隐式结果集存入V_CURSOR？
  
  DBMS_SQL.RETURN_RESULT(V_CURSOR);
END TESTPROC;

请问该如何实现？

在Oracle 21c中，可通过DBMS_SQL.GET_NEXT_RESULT函数捕获DBMS_SQL.RETURN_RESULT返回的隐式结果集，该函数会直接返回一个SYS_REFCURSOR对象，可直接赋值给你的变量。

修改后的USETESTPROC代码如下：

CREATE OR REPLACE NONEDITIONABLE PROCEDURE USETESTPROC 
(
  V_USERACCOUNTID IN VARCHAR2 
) AS 
  V_CURSOR SYS_REFCURSOR;
BEGIN
  TESTPROC(V_USERACCOUNTID); 
  -- 捕获隐式结果集存入V_CURSOR
  V_CURSOR := DBMS_SQL.GET_NEXT_RESULT;
  
  DBMS_SQL.RETURN_RESULT(V_CURSOR);
END USETESTPROC;

关键说明 #

• DBMS_SQL.GET_NEXT_RESULT会按顺序获取当前会话中未处理的隐式结果集，每次调用获取一个；若存在多个隐式结果集，可循环调用该函数直到触发NO_DATA_FOUND异常。
• 此方案无需修改原TESTPROC的定义，完全满足“不通过OUT参数获取游标”的需求。

内容的提问来源于stack exchange，提问作者Failwyn