oralce 游标

定义：

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;