Oracle中Trigger例子2

  PROCEDURE dcgn_dccm_crc (

      in_period       IN       VARCHAR2,

      in_soc          IN       VARCHAR2,

      in_actv_date    IN       VARCHAR2,

      o_soc_rcc_amt   OUT      NUMBER,

      o_ind           OUT      CHAR

   )

   IS

      -- Working Variables

      nbase_amt             NUMBER (19, 4)  DEFAULT 0;

      namt                  NUMBER (19, 4)  DEFAULT 0;

      nmax_rate             NUMBER (19, 4)  DEFAULT 0;

      nmin_rate             NUMBER (19, 4)  DEFAULT 0;

      ntmp_amt              NUMBER (19, 4)  DEFAULT 0;

      bissocfound           BOOLEAN         DEFAULT FALSE;

      vservice_feature      VARCHAR2 (10);                             --:1:2

      vsoc                  CHAR (9);

      deffective_date       DATE;

      vfeature_code         CHAR (6);

      crc_charge_lvl_code   CHAR (1);

      crc_usg_depend_code   CHAR (1);

      crc_rate_qty_type     CHAR (1);

      vsoc_v                CHAR (9);

      veffective_date_v     VARCHAR2 (14);

      vfeature_code_v       CHAR (6);

      bamt_cal_ind          BOOLEAN;

      namt_ws               NUMBER (19, 4);

      bsoc_ind              BOOLEAN;

      twhen                 TIMESTAMP ( 3 );

      ierr_code             INTEGER;

      vMsg              VARCHAR2 (255);

   BEGIN

      BEGIN

         SELECT rate_ftr.soc, rate_ftr.effective_date,

                rate_ftr.feature_code, rate_ftr.rc_charge_lvl_code,

                rate_ftr.rc_usg_depend_code, rate_ftr.rc_rate_qty_type

           INTO vsoc, deffective_date,

                vfeature_code, crc_charge_lvl_code,

                crc_usg_depend_code, crc_rate_qty_type

           FROM rated_feature rate_ftr, feature ftr

          WHERE rate_ftr.soc = in_soc

            AND rate_ftr.effective_date <= TO_DATE (in_actv_date, ''''YYYYMMDD'''')

            AND NVL (TO_CHAR (rate_ftr.expiration_date, ''''YYYYMMDD''''),

                     ''''47001231''''

                    ) >= in_actv_date

            AND rate_ftr.rc_info_ind = ''''Y''''

            AND rate_ftr.feature_code = ftr.feature_code

            AND ftr.feature_group = ''''SF''''

            AND ftr.period = rate_ftr.period

            AND ftr.period = in_period;

      EXCEPTION

         WHEN NO_DATA_FOUND

         THEN

                   END；

END；

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18