Oracle中Trigger例子2

发布: 2007-7-02 11:08 | 作者: admin | 来源: | 查看: 10次 | 进入软件测试论坛讨论

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；