两个经典的Oracle触发器实例,Oracle中Trigger例子2 （转）

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/，如需转载，请注明出处，否则将追究法律责任。