Oracle存储过程中使用cursor

olayszuvsq

　　CREATE OR REPLACE PROCEDURE GLU_SP_RECALCULATE_RATE (P OUT VARCHAR2)AS
　　rateRow REV_SHARE_RATE%rowtype;
  cursor rateRows is
    SELECT
        DISTRIBUTOR_ID,
        RETAILER_ID,
        RATE,
        MODIFIED_DATE ,
        GENRE
    FROM REV_SHARE_RATE
    WHERE
        IS_RUN=0;
　　BEGIN
　　FOR rateRow in rateRows loop
　　--Qualcomm,Qualcomm(p)Qualcomm(s)
      IF rateRow.Distributor_Id =1 OR rateRow.Distributor_Id =35 OR rateRow.Distributor_Id =61 THEN
          UPDATE DAILYDAP SET USD_VALUE = (NUM_DOWNLOADS * DAP_AMOUNT* rateRow.Rate)/(NET_REV/USD_VALUE),-- v_usd_fx,
                 GBP_VAlUE = (NUM_DOWNLOADS * DAP_AMOUNT* rateRow.Rate)/(NET_REV/GBP_VAlUE),--v_gbp_fx,
                 NET_REV= NUM_DOWNLOADS * DAP_AMOUNT* rateRow.Rate
          WHERE DISTRIB_ID=rateRow.DISTRIBUTOR_ID AND RETAILER_ID=rateRow.Retailer_Id AND NET_REV > 0 AND REPORT_DATE >= rateRow.Modified_Date;
      --ATT
      ELSIF rateRow.DISTRIBUTOR_ID = 49 THEN
         update (select /*+ BYPASS_UJVC */ d.DISTRIB_ID,d.RETAILER_ID,rate.genre,d.USD_VALUE,d.DATA_RAW,d.NET_REV,d.GBP_VAlUE,d.REPORT_DATE from dailydap d
                 inner join att_raw_daily att on d.ref_id = att.id
                 inner join rev_share_rate rate on d.distrib_id = rate.DISTRIBUTOR_ID and d.retailer_id = rate.Retailer_Id and att.product_category = rate.genre
                )
         SET
            USD_VALUE = (DATA_RAW * rateRow.Rate)/ (NET_REV/USD_VALUE),--v_usd_fx,
            GBP_VAlUE = (DATA_RAW * rateRow.Rate)/(NET_REV/GBP_VAlUE),--v_gbp_fx,
            NET_REV= DATA_RAW * rateRow.Rate
         where
             DISTRIB_ID=rateRow.DISTRIBUTOR_ID AND RETAILER_ID=rateRow.Retailer_Id AND
             GENRE=rateRow.GENRE AND NET_REV > 0 AND REPORT_DATE >= rateRow.Modified_Date;
      --sprint,nextel...
      ELSE
          UPDATE DAILYDAP SET USD_VALUE = (DATA_RAW * rateRow.Rate)/ (NET_REV/USD_VALUE),--v_usd_fx,
                 GBP_VAlUE = (DATA_RAW * rateRow.Rate)/(NET_REV/GBP_VAlUE),--v_gbp_fx,
                 NET_REV= DATA_RAW * rateRow.Rate
          WHERE DISTRIB_ID=rateRow.DISTRIBUTOR_ID AND RETAILER_ID=rateRow.Retailer_Id AND NET_REV > 0 AND REPORT_DATE >= rateRow.Modified_Date;
      END IF;
　　--UPDATE REV_SHARE_RATE SET IS_RUN = 1
      UPDATE REV_SHARE_RATE SET IS_RUN=1 WHERE DISTRIBUTOR_ID=rateRow.DISTRIBUTOR_ID AND RETAILER_ID=rateRow.Retailer_Id;
　　END LOOP;
　　COMMIT;
  P:='OK';
EXCEPTION
   WHEN OTHERS THEN
   dbms_output.put_line(SQLERRM);
   ROLLBACK;
   P:= SQLERRM;
END;