这样的调用可以达到你的目的不?create or replace procedure prc_Test_refcursor(v_refcur out PKG_RefCursor.Refcursor) is strsql varchar2(200); V_SYSDATE DATE; begin strsql:='select sysdate from DUAL'; open v_refcur for strsql; --CLOSE V_REFCUR; --DBMS_OUTPUT.put_line(TO_CHAR(V_SYSDATE,'YYYY-MM-DD')); end prc_Test_refcursor; create or replace procedure p_test_refcur is v_refcur PKG_RefCursor.Refcursor; begin prc_Test_refcursor(v_refcur); end p_test_refcur;
我是这样定义的: PACKAGE "PKG_TD_PAYMENT" IS TYPE m_refcur IS REF CURSOR; PROCEDURE select_Return_ID_Collection ( I_TAG_STR IN VARCHAR2, I_WORKSTATION_ID IN VARCHAR2, o_cs_name OUT m_refcur ); PROCEDURE select_STAGE_1_FEE_COUNT( I_TAG_STR IN VARCHAR2, I_WORKSTATION_ID IN VARCHAR2, I_PREVIOUS_WORKING_DATE IN VARCHAR2, o_cs_name OUT m_refcur ); END Pkg_Td_Payment; PACKAGE BODY "PKG_TD_PAYMENT" AS PROCEDURE select_Return_ID_Collection ( I_TAG_STR IN VARCHAR2, I_WORKSTATION_ID IN VARCHAR2, o_cs_name OUT m_refcur ) IS BEGIN 。 END select_Return_ID_Collection; PROCEDURE select_STAGE_1_FEE_COUNT( I_TAG_STR IN VARCHAR2, I_WORKSTATION_ID IN VARCHAR2, I_PREVIOUS_WORKING_DATE IN VARCHAR2, o_cs_name OUT m_refcur ) IS str VARCHAR2(2000); str_1 VARCHAR2(1000); BEGIN CASE I_TAG_STR WHEN 'office' THEN str_1 := 'AND pt.INPUT_OPERATOR_ID IN(' || PKG_TD_PAYMENT.select_Return_ID_Collection('office',I_WORKSTATION_ID,o_cs_name) ||')'; WHEN 'terminal' THEN str_1 := 'AND pt.INPUT_WORKSTATION_ID=' || PKG_TD_PAYMENT.select_Return_ID_Collection('terminal',I_WORKSTATION_ID,o_cs_name); WHEN 'group' THEN str_1 := 'AND pt.INPUT_OPERATOR_ID IN(' || PKG_TD_PAYMENT.select_Return_ID_Collection('group',I_WORKSTATION_ID,o_cs_name)||')';END CASE; ........ END select_STAGE_1_FEE_COUNT;
我那样试过不行。
strsql varchar2(200);
V_SYSDATE DATE;
begin
strsql:='select sysdate from DUAL';
open v_refcur for strsql;
--CLOSE V_REFCUR;
--DBMS_OUTPUT.put_line(TO_CHAR(V_SYSDATE,'YYYY-MM-DD'));
end prc_Test_refcursor;
create or replace procedure p_test_refcur is
v_refcur PKG_RefCursor.Refcursor;
begin
prc_Test_refcursor(v_refcur);
end p_test_refcur;
PACKAGE "PKG_TD_PAYMENT"
IS
TYPE m_refcur IS REF CURSOR;
PROCEDURE select_Return_ID_Collection (
I_TAG_STR IN VARCHAR2,
I_WORKSTATION_ID IN VARCHAR2,
o_cs_name OUT m_refcur
); PROCEDURE select_STAGE_1_FEE_COUNT(
I_TAG_STR IN VARCHAR2,
I_WORKSTATION_ID IN VARCHAR2,
I_PREVIOUS_WORKING_DATE IN VARCHAR2,
o_cs_name OUT m_refcur
);
END Pkg_Td_Payment;
PACKAGE BODY "PKG_TD_PAYMENT" AS
PROCEDURE select_Return_ID_Collection (
I_TAG_STR IN VARCHAR2,
I_WORKSTATION_ID IN VARCHAR2,
o_cs_name OUT m_refcur
)
IS
BEGIN
。
END select_Return_ID_Collection; PROCEDURE select_STAGE_1_FEE_COUNT(
I_TAG_STR IN VARCHAR2,
I_WORKSTATION_ID IN VARCHAR2,
I_PREVIOUS_WORKING_DATE IN VARCHAR2,
o_cs_name OUT m_refcur
)
IS
str VARCHAR2(2000);
str_1 VARCHAR2(1000);
BEGIN
CASE I_TAG_STR
WHEN 'office' THEN
str_1 := 'AND pt.INPUT_OPERATOR_ID IN('
|| PKG_TD_PAYMENT.select_Return_ID_Collection('office',I_WORKSTATION_ID,o_cs_name)
||')';
WHEN 'terminal' THEN
str_1 := 'AND pt.INPUT_WORKSTATION_ID='
|| PKG_TD_PAYMENT.select_Return_ID_Collection('terminal',I_WORKSTATION_ID,o_cs_name);
WHEN 'group' THEN
str_1 := 'AND pt.INPUT_OPERATOR_ID IN('
|| PKG_TD_PAYMENT.select_Return_ID_Collection('group',I_WORKSTATION_ID,o_cs_name)||')';END CASE;
........
END select_STAGE_1_FEE_COUNT;
|| PKG_TD_PAYMENT.select_Return_ID_Collection('terminal',I_WORKSTATION_ID,o_cs_name);
出
老是提示PKG_TD_PAYMENT.select_Return_ID_Collection再次范围内不存在。