存储过程中的问题,高手请进!
我有一个存储过程是这样用的:
CREATE OR REPLACE PACKAGE BODY "PKG_TD_PAYMENT"
IS
TYPE m_refcur IS REF CURSOR;
TYPE results_rt IS RECORD(id VARCHAR2(100));
TYPE results_tt IS TABLE OF results_rt INDEX BY BINARY_INTEGER;
//这个是被调用的方法
FUNCTION select_Return_ID_Collection (
I_TAG_STR IN VARCHAR2,
I_WORKSTATION_ID IN VARCHAR2
)RETURN results_tt;
//这个存储过程是主调用过程
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;
程序主体是这样:
CREATE OR REPLACE PACKAGE BODY "PKG_TD_PAYMENT" AS
FUNCTION select_Return_ID_Collection (
I_TAG_STR IN VARCHAR2,
I_WORKSTATION_ID IN VARCHAR2
) RETURN results_tt
IS
cur m_refcur;
result results_tt;
rec results_rt;
BEGIN
CASE I_TAG_STR
WHEN 'office' THEN
OPEN cur
FOR
SELECT a.OPERATOR_ID
FROM RECON_REPORT_GROUP_CODE a,WORKSTATION_GROUP_CODE b
WHERE a.DEPT_DIVISION_CODE = b.DEPT_DIVISION_CODE
AND b.WORKSTATION_ID = I_WORKSTATION_ID;
....//此处省略
END CASE;
LOOP
FETCH cur INTO rec;
EXIT WHEN cur%NOTFOUND;
result(cur%ROWCOUNT):=rec;
END LOOP;
CLOSE cur;
RETURN result;
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||'))';
。。。。此处省略.
END CASE;
str :='SELECT ptd.FEE_TYPE_CODE,'
||' NVL(SUM(DECODE(ptd.FEE_TYPE_CODE ,''R'',1,0)),0) AS R,'
...//此处省略
||' FROM PAYMENT_TXN pt, PAYMENT_TXN_DETAILS ptd'
||' WHERE pt.BUSINESS_TXN_KEY_REF = ptd.BUSINESS_TXN_KEY_REF'
||' AND TO_CHAR(pt.BUSINESS_TXN_DATE) = '||I_PREVIOUS_WORKING_DATE
||' '
|| str_1
||' Group by ptd. FEE_TYPE_CODE'
||' Order by ptd. FEE_TYPE_CODE';
OPEN o_cs_name
FOR str;
END select_STAGE_1_FEE_COUNT;
END PKG_TD_PAYMENT;
在我执行存储过程select_STAGE_1_FEE_COUNT时总是提示,
“在此范围内不存在函数
PKG_TD_PAYMENT.select_Return_ID_Collection
”
这是为什么?