将函数中的sys_refcursor传递给过程中的sys-refcursor out参数

Question

我在从存储在变量crs_scenaries中的函数返回的sys_refcursor输出结果时遇到问题。然后我想把这个数据集合传递给输出参数pout_result。我得到错误PLS-00487：对变量"POUT_RESULT"的引用无效。你能告诉我如何解决这个问题吗？

非常感谢！

declare
pin_scenarioName          scenarios.scen_name%TYPE := 'zz_berlin_testen';
pin_scenarioRegion        inf_ausbaugebiete.ausg_name%TYPE DEFAULT NULL;
pin_scenarioCutOffDate    scenarios.scen_cut_off_date%TYPE DEFAULT NULL;
pin_scenarioStatus        scenario_status.scst_name%TYPE DEFAULT NULL;
pout_result               SYS_REFCURSOR;
pout_strerrorcode         VARCHAR2(1000);
pout_strerrormessage      VARCHAR2(1000);
BEGIN
pout_result := funk30.pbi$capi_export_pck.cfn_getscenarios(pin_scenarioName       =>   pin_scenarioName,
pin_scenarioRegion     =>   pin_scenarioRegion,
pin_scenarioCutOffDate =>   pin_scenarioCutOffDate,
pin_scenarioStatus     =>   pin_scenarioStatus,
pout_strerrorcode      =>   pout_strerrorcode,
pout_strerrormessage    =>  pout_strerrormessage);
dbms_output.put_line(pout_result.ID || ' ' ||pout_result.NAME || ' ' ||pout_result.CUT_OFF || ' ' ||pout_result.STATUS ||
' ' || pout_result.PRIORITY || ' ' ||pout_result.PARENT_SCENARIO|| ' ' ||pout_result.REGION|| ' ' || pout_result.REMARK);

END cpr_getscenarios;

Function:
FUNCTION mfn_getscenarios(pin_scenarioName          IN scenarios.scen_name%TYPE DEFAULT NULL,
pin_scenarioRegion        IN inf_ausbaugebiete.ausg_name%TYPE DEFAULT NULL,
pin_scenarioCutOffDate    IN scenarios.scen_cut_off_date%TYPE DEFAULT NULL,
pin_scenarioStatus        IN scenario_status.scst_name%TYPE DEFAULT NULL,
pout_strerrorcode         OUT VARCHAR2,
pout_strerrormessage      OUT VARCHAR2)
RETURN SYS_REFCURSOR IS
crs_scenarios SYS_REFCURSOR;
n_cnt_exists  NUMBER;
ex_scennotexists EXCEPTION;
strprocedurename VARCHAR2(128) := package_name || '.mfn_getScenarios';
BEGIN
BEGIN
SELECT 1 INTO n_cnt_exists FROM scenarios WHERE rownum = 1;
EXCEPTION
WHEN no_data_found THEN
RAISE ex_scennotexists;
END;
OPEN crs_scenarios FOR
SELECT scen.scen_id         id,
scen.scen_name         NAME,
scen.scen_cut_off_date cut_off,
scst.scst_name         STATUS,
scen.scen_priority     PRIORITY,
parent.scen_name       PARENT_SCENARIO,
ausg.ausg_name         REGION,
scen.scen_comment      REMARK
FROM scenarios scen,
scenarios parent,
scenario_status scst,
inf_ausbaugebiete ausg
WHERE scen.scen_scst_id = scst.scst_id
AND scen.scen_parent_scen_id = parent.scen_id(+)
AND scen.scen_ausg_id= ausg.ausg_id(+)
AND lower(scen.scen_name) like nvl(lower('%'||pin_scenarioName||'%'), lower(scen.scen_name))
AND NVL(ausg.ausg_name, 'xxxxx') = nvl(pin_scenarioRegion, NVL(ausg.ausg_name, 'xxxxx'))
AND scen.scen_cut_off_date = nvl(pin_scenarioCutOffDate, scen.scen_cut_off_date)
AND scst.scst_name = nvl(pin_scenarioStatus, scst.scst_name);
pout_strerrorcode    := '0';
pout_strerrormessage := '';
RETURN crs_scenarios;
EXCEPTION
WHEN ex_scennotexists THEN
pout_strerrorcode    := 'API-00239';
pout_strerrormessage := rtrim(api_err_pck.apierrormsg('API-00239', strprocedurename), ' @');
RETURN NULL;
WHEN OTHERS THEN
pout_strerrorcode    := '-1';
pout_strerrormessage := substr(SQLERRM, instr(SQLERRM, 'ORA') + 11, length(SQLERRM));
RETURN NULL;
END mfn_getscenarios;

Answer 1

Sys_refcursor只是一个SQL定义。如果你想运行它，你必须FETCH数据。所以pout_result变量本身没有数据。

--------功能----

create or replace FUNCTION mfn 
RETURN SYS_REFCURSOR IS
crs_scenarios SYS_REFCURSOR;
BEGIN

OPEN crs_scenarios FOR
SELECT dummy from dual;
RETURN crs_scenarios;
END ;

--执行

set serveroutput on
declare

pout_result   SYS_REFCURSOR;

type pout_result_tab is table of dual%rowtype; -- cursor datatype
pout_result_t pout_result_tab;
BEGIN
pout_result := mfn;
fetch pout_result bulk collect  into  pout_result_t;  -- bulk collect because I assume you have recordset, not one record
dbms_output.put_line(pout_result_t(1).dummy);

END ;
/

---结果

X

PL/SQL procedure successfully completed.