Oracle中Trigger例子2
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；
