定义:
CURSOR C_QGCSMO IS
SELECT T.FPROVINCE, COUNT(1) MOCOUNT
FROM SMSBILL.BILLINGTABLE@D3BILL T
WHERE T.FUD IN ('a', 'A')
AND T.FMASTER = '054'
AND T.FMSGTYPE = '62'
AND T.FDESTADDR LIKE '106660609181%'
AND T.FPROVINCE IN ('黑龙江', '福建')
AND T.FSUBMITTIME > V_SYSDATE - 1 / 24 / 2
GROUP BY T.FPROVINCE;
TYPE T_QGCSMO IS TABLE OF C_QGCSMO%ROWTYPE INDEX BY BINARY_INTEGER;
V_QGCSMODATA T_QGCSMO ;
使用方法一:
OPEN C_QGCSMO;
FETCH C_QGCSMO BULK COLLECT
INTO V_QGCSMODATA;
FOR J IN 1 .. V_QGCSMODATA.COUNT LOOP
BEGIN
IF V_QGCSMODATA(J).MOCOUNT > 0 THEN
V_MSG := V_MSG || V_QGCSMODATA(J).FPROVINCE;
END IF;
END;
END LOOP;
CLOSE C_QGCSMO;
使用方法二:
OPEN C_QGCSMO;
LOOP
FETCH C_QGCSMO INTO V_PRVNC,V_COUNT;
EXIT WHEN C_QGCSMO%NOTFOUND;
IF V_COUNT > 0 THEN
V_MSG := V_MSG || V_PRVNC;
END IF;
END LOOP;
CLOSE C_QGCSMO;