ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 游标--FOR循环实现

oracle 游标--FOR循环实现

原创 Linux操作系统 作者:golden_zhou 时间:2012-01-13 13:48:22 0 删除 编辑
CREATE OR REPLACE PROCEDURE TOPUP.sp_card_paystat (currentDate IN DATE)
AS
   v_paystat_id      NUMBER (19);                               --查询供应商ID
   v_count           NUMBER;                                  --查询充值卡数量
   v_otype           VARCHAR2 (50);                           --查询充值卡类型
   gatherBeginDate   DATE;                                      --统计开始日期
   gatherEndDate     DATE;                                      --统计结束日期
BEGIN
   v_paystat_id := 0;                                           --查询供应商ID
   v_count := 0;                                              --查询充值卡数量
   v_otype := '0';                                            --查询充值卡类型
   gatherBeginDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD');     --统计开始日期
   gatherEndDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD');       --统计结束日期

   --OTYPE的值:sz:代表移动:75  lt:代表联通:76 dx:代表电信: 77
   --统计开始日期
   --SELECT   TRUNC (currentDate - 25) INTO gatherBeginDate FROM DUAL;
   SELECT   TRUNC (currentDate - 1) INTO gatherBeginDate FROM DUAL;
   --统计结束日期
   --SELECT   TRUNC (currentDate - 24) INTO gatherEndDate FROM DUAL;
   SELECT   TRUNC (currentDate) INTO gatherEndDate FROM DUAL;

   --定义游标
   DECLARE
      CURSOR card_paystat
      IS
         SELECT   *
           FROM   (  SELECT   c.supply_id,
                              i.parvalue,
                              i.operator_id,
                              COUNT ( * ) AS total
                       FROM   tab_topup_mobile_cardpay c,
                              tab_topup_mobile_carditem i
                      WHERE       c.id = i.card_id
                              AND i.ttf = 1
                              AND i.status_id = 43
                              AND c.supply_id = 22
                              AND c.finisheddate >= gatherBeginDate
                              AND c.finisheddate <= gatherEndDate
                   GROUP BY   c.supply_id, i.parvalue, i.operator_id) ic;
      c_paystat   card_paystat%ROWTYPE;
   --operatorid ic.operator_id%type;
   --开始使用游标取数据
   BEGIN
      FOR c_paystat IN card_paystat
      LOOP
         SELECT   SEQ_CARD_PAYSTAT.NEXTVAL INTO v_paystat_id FROM DUAL;

         CASE
            WHEN c_paystat.operator_id = '75'
            THEN
               v_otype := 'sz';
            WHEN c_paystat.operator_id = '76'
            THEN
               v_otype := 'lt';
            WHEN c_paystat.operator_id = '77'
            THEN
               v_otype := 'dx';
         END CASE;
 
         INSERT INTO TAB_CARD_PAYSTAT (ID,
                                       STAT_START_DATE,
                                       STAT_END_DATE,
                                       CARD_LIMIT,
                                       SUPPLY_ID,
                                       CARD_TYPE,
                                       COUNT,
                                       VALID,
                                       CREATEDATE,
                                       LASTDATE)
           VALUES   (v_paystat_id,
                     gatherBeginDate,
                     gatherEndDate,
                     c_paystat.parvalue,
                     c_paystat.supply_id,
                     v_otype,
                     c_paystat.total,
                     1,
                     SYSDATE,
                     NULL);
         COMMIT;
      END LOOP;
   END;
--发生异常时返回错误码
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'AUTO CARD PAY GATHER ERROR,PLEASE OPERATION AGAIN,THANKS!'
      );
      ROLLBACK;
END;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25198367/viewspace-714884/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-09

  • 博文量
    238
  • 访问量
    293512