ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle中Trigger例子2 (转)

Oracle中Trigger例子2 (转)

原创 Linux操作系统 作者:jcszjswkzhou 时间:2019-06-29 13:15:07 0 删除 编辑
Oracle中Trigger例子2,sql,sql教程,Oracle基础

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;

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

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

注册时间:2007-08-29

  • 博文量
    3488
  • 访问量
    2621064