我有一个存储过程是这样用的:
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
”
这是为什么?
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
”
这是为什么?
CREATE OR REPLACE PACKAGE a
IS
FUNCTION f(t varchar2) return VARCHAR2
IS
BEGIN
.........
RETURN t;
END f; PROCEDURE p(val VARCAHR2)
IS
str VARCHAR2(1000);
BEGIN
str :='f('||val||')';
END p;
END a;
你看看创建package的例子就应该清楚了
这段代码应该是create package body的,不应该出现在create package块的
CREATE OR REPLACE PACKAGE a
IS
FUNCTION f(t varchar2) return VARCHAR2;
PROCEDURE p(val VARCAHR2);
END a;CREATE OR REPLACE PACKAGE BODY a
AS
FUNCTION f(t varchar2) return VARCHAR2
IS
BEGIN
.........
RETURN t;
END f; PROCEDURE p(val VARCAHR2)
IS
str VARCHAR2(1000);
BEGIN
str :='f('||val||')'; //执行时这里提示没有此Function.
END p;
END a;