过程如下:
CREATE OR REPLACE TYPE RS_TYPE as object(GATENAME varchar2(50),
IMONTH NUMBER,
ISUM NUMBER,
FEE NUMBER,
SUM_FEE NUMBER);
----------------------------------------------------------------------
CREATE OR REPLACE TYPE RS_TAB_TYPE as TABLE OF RS_TYPE;
----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SPGATE_DOWM_FLOW_MONTH(IN_Y IN NUMBER,
IN_MONTH IN NUMBER,
IN_SPISUNCM IN NUMBER,
IN_GATENAME IN VARCHAR2,
MY_TAB OUT rs_tab_type) AS
type rc is ref cursor;
MY_CURSOR rc;
CURSOR C_1 IS
SELECT DISTINCT IYMD
FROM MT_DATAREPORT@SMSSVR_220_LINK.COM MT, XT_GATE_QUEUE@SMSACC_220_LINK.COM XGQ
WHERE MT.Y = IN_Y
AND MT.IMONTH = IN_MONTH
AND XGQ.SPISUNCM = IN_SPISUNCM
AND XGQ.GATENAME = IN_GATENAME
AND MT.SPGATE = XGQ.SPGATE;--定义游标根据输入数据查询日期
RS_RECORD_TEMP RS_TYPE;
ICOUNT_TEMP NUMBER;--中间变量
FEE_TEMP NUMBER;
SUM_ICOUNT_TEMP NUMBER;
SUM_FEE_TEMP NUMBER;
DAY_TEMP NUMBER;
BEGIN
FOR I IN 1 .. 31 LOOP
SUM_ICOUNT_TEMP := 0;--中间变量初始化
SUM_FEE_TEMP := 0;
OPEN C_1;
LOOP
FETCH C_1
INTO DAY_TEMP;
EXIT WHEN C_1%NOTFOUND;
IF (SUBSTR(TO_CHAR(DAY_TEMP), 7, 2) = I) THEN
SELECT SUM(ICOUNT), XGQ.FEE
INTO ICOUNT_TEMP, FEE_TEMP
FROM MT_DATAREPORT@SMSSVR_220_LINK.COM MT, XT_GATE_QUEUE@SMSACC_220_LINK.COM XGQ
WHERE MT.Y = IN_Y
AND MT.IMONTH = IN_MONTH
AND XGQ.SPISUNCM = IN_SPISUNCM
AND XGQ.GATENAME = IN_GATENAME
AND MT.SPGATE = XGQ.SPGATE
GROUP BY XGQ.FEE;--根据输入年份、月份、运营商、通道名称和发送日期查询发送总数和费率
SUM_ICOUNT_TEMP := SUM_ICOUNT_TEMP + ICOUNT_TEMP;
END IF;
END LOOP;
SUM_FEE_TEMP := SUM_ICOUNT_TEMP * FEE_TEMP;--计算短信发送的费用
CLOSE C_1;
OPEN MY_CURSOR FOR
SELECT IN_GATENAME GATENAME,
I IMONTH,
SUM_ICOUNT_TEMP ISUM,
FEE_TEMP FEE,
SUM_FEE_TEMP SUM_FEE
FROM DUAL;--打开游标查询所有结果
FETCH MY_CURSOR
INTO RS_RECORD_TEMP;
MY_TAB(I) := RS_RECORD_TEMP;--将结果赋值到临时表
--DBMS_OUTPUT.PUT_LINE(i||MY_TAB(I).ISUM);
END LOOP;
END SPGATE_DOWM_FLOW_MONTH;
测试后报ora-06504:PL/SQL:结果集变量或查询的返回类型不匹配求各位高手解答.....
CREATE OR REPLACE TYPE RS_TYPE as object(GATENAME varchar2(50),
IMONTH NUMBER,
ISUM NUMBER,
FEE NUMBER,
SUM_FEE NUMBER);
----------------------------------------------------------------------
CREATE OR REPLACE TYPE RS_TAB_TYPE as TABLE OF RS_TYPE;
----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SPGATE_DOWM_FLOW_MONTH(IN_Y IN NUMBER,
IN_MONTH IN NUMBER,
IN_SPISUNCM IN NUMBER,
IN_GATENAME IN VARCHAR2,
MY_TAB OUT rs_tab_type) AS
type rc is ref cursor;
MY_CURSOR rc;
CURSOR C_1 IS
SELECT DISTINCT IYMD
FROM MT_DATAREPORT@SMSSVR_220_LINK.COM MT, XT_GATE_QUEUE@SMSACC_220_LINK.COM XGQ
WHERE MT.Y = IN_Y
AND MT.IMONTH = IN_MONTH
AND XGQ.SPISUNCM = IN_SPISUNCM
AND XGQ.GATENAME = IN_GATENAME
AND MT.SPGATE = XGQ.SPGATE;--定义游标根据输入数据查询日期
RS_RECORD_TEMP RS_TYPE;
ICOUNT_TEMP NUMBER;--中间变量
FEE_TEMP NUMBER;
SUM_ICOUNT_TEMP NUMBER;
SUM_FEE_TEMP NUMBER;
DAY_TEMP NUMBER;
BEGIN
FOR I IN 1 .. 31 LOOP
SUM_ICOUNT_TEMP := 0;--中间变量初始化
SUM_FEE_TEMP := 0;
OPEN C_1;
LOOP
FETCH C_1
INTO DAY_TEMP;
EXIT WHEN C_1%NOTFOUND;
IF (SUBSTR(TO_CHAR(DAY_TEMP), 7, 2) = I) THEN
SELECT SUM(ICOUNT), XGQ.FEE
INTO ICOUNT_TEMP, FEE_TEMP
FROM MT_DATAREPORT@SMSSVR_220_LINK.COM MT, XT_GATE_QUEUE@SMSACC_220_LINK.COM XGQ
WHERE MT.Y = IN_Y
AND MT.IMONTH = IN_MONTH
AND XGQ.SPISUNCM = IN_SPISUNCM
AND XGQ.GATENAME = IN_GATENAME
AND MT.SPGATE = XGQ.SPGATE
GROUP BY XGQ.FEE;--根据输入年份、月份、运营商、通道名称和发送日期查询发送总数和费率
SUM_ICOUNT_TEMP := SUM_ICOUNT_TEMP + ICOUNT_TEMP;
END IF;
END LOOP;
SUM_FEE_TEMP := SUM_ICOUNT_TEMP * FEE_TEMP;--计算短信发送的费用
CLOSE C_1;
OPEN MY_CURSOR FOR
SELECT IN_GATENAME GATENAME,
I IMONTH,
SUM_ICOUNT_TEMP ISUM,
FEE_TEMP FEE,
SUM_FEE_TEMP SUM_FEE
FROM DUAL;--打开游标查询所有结果
FETCH MY_CURSOR
INTO RS_RECORD_TEMP;
MY_TAB(I) := RS_RECORD_TEMP;--将结果赋值到临时表
--DBMS_OUTPUT.PUT_LINE(i||MY_TAB(I).ISUM);
END LOOP;
END SPGATE_DOWM_FLOW_MONTH;
测试后报ora-06504:PL/SQL:结果集变量或查询的返回类型不匹配求各位高手解答.....
CREATE OR REPLACE TYPE RS_TYPE as object(GATENAME varchar2(50),
IMONTH NUMBER(16,2),
ISUM NUMBER(16,2),
FEE NUMBER(16,2),
SUM_FEE NUMBER(16,2))