case when 在存储过程中的运用

---此 存储过程 表面上看吓一下跳，其实很简单，其框架无非就是一个 if -- then -XX--else -YY- end if,在XX和YY处增加了查询而已

CREATE OR REPLACE PROCEDURE USP_SUB_LU_FAC_S#LU_APP_ID#EFF
/*
Copyright(c) 1987-2008 xxx.
Remark : First Creation
*/
(
lu_app_id_in IN TBL_LU_FAC.lu_app_id%TYPE,
result_cursor OUT types.cursor_type
)
AS
v_max_custom_fld_9 TBL_LU_FAC.custom_fld_9%TYPE;
v_lmt_available TBL_LU_FAC.amt_appr%TYPE;
v_mt_fac_cd TBL_LU_FAC.mt_fac_cd%TYPE;
v_last_disb NUMBER;
v_last_disb_mt_time_cd VARCHAR2(20);
BEGIN
SELECT MAX(TO_NUMBER(TBL_LU_FAC.custom_fld_9))
INTO v_max_custom_fld_9
FROM TBL_LU_FAC
WHERE TBL_LU_FAC.lu_app_id = lu_app_id_in;

SELECT TBL_LU_FAC.MT_FAC_CD INTO v_mt_fac_cd
FROM TBL_LU_FAC WHERE TBL_LU_FAC.lu_app_id = lu_app_id_in
AND ROWNUM =1;
BEGIN
SELECT pp.last_disb_tenure,pp.last_disb_mt_time_cd
INTO v_last_disb,v_last_disb_mt_time_cd
FROM ebmaint.tbl_mt_prod_policy pp,
ebmaint.TBL_MT_FAC mf
WHERE mf.mt_prod_policy_cd = pp.cd
AND mf.cd = v_mt_fac_cd;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
IF v_mt_fac_cd LIKE 'P%' THEN
OPEN result_cursor FOR
SELECT
TBL_LU_FAC.ID,
TBL_LU_FAC.LU_APP_ID,
TBL_LU_FAC.FAC_ID,
TBL_LU_FAC.CIF_ID,
TBL_LU_FAC.CONTRACT_NO,
TBL_LU_FAC.FAC_ALCT_CIF_ID,
TBL_LU_FAC.ACCT_NO,
TBL_LU_FAC.LIMIT_NO,
TBL_LU_FAC.CP_MT_FAC_TYP_CD,
TBL_LU_FAC.MT_FAC_CD,
TBL_LU_FAC.LU_MT_PUR_CD,
TBL_LU_FAC.MT_BR_CD,
TBL_LU_FAC.MT_CUR_CD,
TBL_LU_FAC.EXCHG_RATE,
TBL_FAC.MT_CUR_CD "createCurrencyCode",
USF_GET_MT_CUR_DSCP(TBL_FAC.MT_CUR_CD) "createCurrencyDescription",
TBL_FAC.EXCHG_RATE "createExchangeRate",
TBL_LU_FAC.INFLATION_CLEARANCE_RATE,
TBL_LU_FAC.AMT_APPLIED,
TBL_LU_FAC.AMT_SHIFTED,
TBL_LU_FAC.AMT_APPR,
TBL_LU_FAC.TENURE_MT_TIME_CD,
TBL_LU_FAC.TENURE_APPLIED,
TBL_LU_FAC.TENURE_APPR,
TBL_LU_FAC.GRACE_PRD,
TBL_LU_FAC.CONTRACT_NO,
TBL_LU_FAC.DT_CONTRACT_SIGNED,
TBL_LU_FAC.DT_REVIEW,
TBL_LU_FAC.NEXT_REVIEW_PRD,
TBL_LU_FAC.NEXT_REVIEW_PRD_MT_TIME_CD,
TBL_LU_FAC.DT_NEXT_REVIEW,
TBL_LU_FAC.MT_REPYMT_TYP_CD,
TBL_LU_FAC.REPYMT_ACCT_NO,
TBL_LU_FAC.MT_PYMT_TERM_CD,
TBL_LU_FAC.OTHER_PYMT_TERM_DSCP,
TBL_LU_FAC.IS_TEMP,
TBL_LU_FAC.TEMP_PRD_MT_TIME_CD,
TBL_LU_FAC.TEMP_PRD,
TBL_LU_FAC.ROLLOVER_LMT,
TBL_LU_FAC.ROLLOVER_TERM_MT_TIME_CD,
TBL_LU_FAC.ROLLOVER_TERM,
TBL_LU_FAC.FX_LMT,
TBL_LU_FAC.PRE_STTLMT_LMT,
TBL_LU_FAC.IS_EFFECT_NOT_CLEARED,
TBL_LU_FAC.IS_AGAINST_FDR,
TBL_LU_FAC.MT_GRTEE_TYP_CD,
TBL_LU_FAC.BENEFICIARY_NM,
TBL_LU_FAC.DT_MATURITY,
TBL_LU_FAC.Dy_Contract,
TBL_LU_FAC.Zy_Contract,
TBL_LU_FAC.Bz_Contract,
CASE
WHEN tbl_lu_fac.dt_maturity IS NOT NULL AND v_last_disb_mt_time_cd = 'Y' THEN ADD_MONTHS(tbl_lu_fac.dt_maturity, 12 * v_last_disb)
WHEN tbl_lu_fac.dt_maturity IS NOT NULL AND v_last_disb_mt_time_cd = 'M' THEN ADD_MONTHS(tbl_lu_fac.dt_maturity, v_last_disb)
WHEN tbl_lu_fac.dt_maturity IS NOT NULL AND v_last_disb_mt_time_cd = 'D' THEN tbl_lu_fac.dt_maturity + v_last_disb
ELSE
NULL
END dt_last_disb_allow,
TBL_LU_FAC.REMARK,
TBL_LU_FAC.IS_DOWNLOAD,
TBL_LU_FAC.DT_DOWNLOAD,
TBL_LU_FAC.DT_TRXN_SRC,
TBL_LU_FAC.TRXN_ID,
TBL_LU_FAC.CREATED_BY,
TBL_LU_FAC.UPDATED_BY,
TBL_LU_FAC.DT_CREATED,
TBL_LU_FAC.DT_UPDATED,
TBL_LU_FAC.DEPOSIT_RATIO,
TBL_LU_FAC.PREV_DT_MATURITY,
TBL_LU_FAC.VERSION,
TBL_CS_MT_FAC_TYP.DSCP "cp_mt_fac_typ_dscp",
TBL_MT_FAC.DSCP "mt_fac_dscp",
TBL_MT_FAC.IS_CUR_CHANGEABLE,
TBL_LU_MT_PUR.DSCP "lu_mt_pur_dscp",
TBL_MT_BR.DSCP "mt_br_dscp",
TBL_MT_CUR.BUY_RATE "mt_cur_rate",
TBL_MT_CUR.DSCP "mt_cur_dscp",
TBL_MT_REPYMT_TYP.DSCP "mt_repymt_typ_dscp",
TBL_MT_PYMT_TERM.DSCP "mt_pymt_term_dscp",
TBL_MT_REPYMT_SCHD.DSCP "mt_repymt_schd_dscp",
TBL_MT_GRTEE_TYP.DSCP "mt_grtee_typ_dscp",
V_LMT_AVAILABLE "lmt_available",
V_MAX_CUSTOM_FLD_9 "max_custom_fld_9",
TBL_LU_FAC.CO_OP_LMT_CD,
EBMCIF.USF_GET_CO_OP_LMT_DSCP(TBL_LU_FAC.CO_OP_LMT_CD) "co_op_lmt_dscp", -- 运用函数来转码
TBL_LU_FAC.CO_OP_LMT_CIF_ID,
TBL_CIF.NM "co_op_lmt_cif_nm",
TBL_LU_FAC.CL_CONTRACT_NO,
TBL_LU_FAC.CL_PRICE_PER_UNIT,
TBL_LU_FAC.CL_CONTRACT_AMT,
TBL_LU_FAC.CL_PROPERTY_FEE,
TBL_LU_FAC.CL_DOWNPYMT,
TBL_LU_FAC.CL_REMARK,
TBL_LU_FAC.IS_SELF_SRV_LOAN,
TBL_LU_FAC.MT_EL_SCHOOL_CD,
EBMAINT.USF_GET_MT_EL_SCHOOL_DSCP(TBL_LU_FAC.MT_EL_SCHOOL_CD) "mt_el_school_dscp",
TBL_LU_FAC.EL_MAJOR,
TBL_LU_FAC.EL_NO_OF_YEAR,
TBL_LU_FAC.EL_TOTAL_FEE,
TBL_LU_FAC.MT_REPYMT_SCHD_CD,
TBL_MT_FAC.MT_FAC_CAT_CD,
TBL_LU_FAC.TRANS_FEE_DBT,
TBL_LU_FAC.TRANS_FEE_CDT,
TBL_LU_FAC.DV_RATIO,
TBL_LU_FAC.MT_FAC_PRC_ADJ_TYPE_CD,
ADJ.DSCP "adj_type_description",
TBL_LU_FAC.MT_FAC_TERM_TYPE_CD,
TBL_LU_FAC.MT_FIRST_REPYMT_FLAG,
TBL_LU_FAC.MT_LAST_REPYMT_FLAG,
TBL_LU_FAC.MT_AGREEMENT_FLAG,
TBL_LU_FAC.MT_RE_CAL_FLAG
FROM TBL_LU_FAC
INNER JOIN TBL_MT_CUR ON TBL_MT_CUR.CD = TBL_LU_FAC.MT_CUR_CD
INNER JOIN TBL_MT_BR ON TBL_MT_BR.CD = TBL_LU_FAC.MT_BR_CD
INNER JOIN TBL_MT_FAC ON TBL_MT_FAC.CD = TBL_LU_FAC.MT_FAC_CD
INNER JOIN TBL_FAC ON TBL_FAC.ID = TBL_LU_FAC.FAC_ID
LEFT JOIN TBL_CS_MT_FAC_TYP ON TBL_CS_MT_FAC_TYP.CD = TBL_LU_FAC.CP_MT_FAC_TYP_CD
LEFT OUTER JOIN TBL_MT_GRTEE_TYP ON TBL_MT_GRTEE_TYP.CD = TBL_LU_FAC.MT_GRTEE_TYP_CD
LEFT OUTER JOIN TBL_MT_PYMT_TERM ON TBL_MT_PYMT_TERM.CD = TBL_LU_FAC.MT_PYMT_TERM_CD
LEFT OUTER JOIN TBL_MT_REPYMT_TYP ON TBL_MT_REPYMT_TYP.CD = TBL_LU_FAC.MT_REPYMT_TYP_CD
LEFT OUTER JOIN TBL_MT_REPYMT_SCHD ON TBL_MT_REPYMT_SCHD.CD = TBL_LU_FAC.MT_REPYMT_SCHD_CD
LEFT OUTER JOIN TBL_LU_MT_PUR ON TBL_LU_MT_PUR.CD = TBL_LU_FAC.LU_MT_PUR_CD
LEFT JOIN TBL_CIF ON TBL_CIF.ID = TBL_LU_FAC.CO_OP_LMT_CIF_ID
LEFT JOIN ebmaint.TBL_MT_FAC_PRC_ADJ_TYPE ADJ ON ADJ.CD = TBL_LU_FAC.MT_FAC_PRC_ADJ_TYPE_CD
WHERE TBL_LU_FAC.lu_app_id = lu_app_id_in
AND TBL_LU_FAC.MAIN_LINE_LU_FAC_ID IS NOT NULL
ORDER BY TBL_LU_FAC.main_line_lu_fac_id DESC;

ELSE
OPEN result_cursor FOR
SELECT
TBL_LU_FAC.ID,
TBL_LU_FAC.LU_APP_ID,
TBL_LU_FAC.FAC_ID,
TBL_LU_FAC.CIF_ID,
TBL_LU_FAC.FAC_ALCT_CIF_ID,
TBL_LU_FAC.ACCT_NO,
TBL_LU_FAC.LIMIT_NO,
TBL_LU_FAC.CP_MT_FAC_TYP_CD,
TBL_LU_FAC.MT_FAC_CD,
TBL_LU_FAC.LU_MT_PUR_CD,
TBL_LU_FAC.MT_BR_CD,
TBL_LU_FAC.MT_CUR_CD,
TBL_LU_FAC.EXCHG_RATE,
TBL_FAC.MT_CUR_CD "createCurrencyCode",
USF_GET_MT_CUR_DSCP(TBL_FAC.MT_CUR_CD) "createCurrencyDescription",
TBL_FAC.EXCHG_RATE "createExchangeRate",
TBL_LU_FAC.INFLATION_CLEARANCE_RATE,
TBL_LU_FAC.AMT_APPLIED,
TBL_LU_FAC.AMT_SHIFTED,
TBL_LU_FAC.AMT_APPR,
TBL_LU_FAC.TENURE_MT_TIME_CD,
TBL_LU_FAC.TENURE_APPLIED,
TBL_LU_FAC.TENURE_APPR,
TBL_LU_FAC.GRACE_PRD,
TBL_LU_FAC.CONTRACT_NO,
TBL_LU_FAC.DT_CONTRACT_SIGNED,
TBL_LU_FAC.DT_REVIEW,
TBL_LU_FAC.NEXT_REVIEW_PRD,
TBL_LU_FAC.NEXT_REVIEW_PRD_MT_TIME_CD,
TBL_LU_FAC.DT_NEXT_REVIEW,
TBL_LU_FAC.MT_REPYMT_TYP_CD,
TBL_LU_FAC.REPYMT_ACCT_NO,
TBL_LU_FAC.MT_PYMT_TERM_CD,
TBL_LU_FAC.OTHER_PYMT_TERM_DSCP,
TBL_LU_FAC.IS_TEMP,
TBL_LU_FAC.TEMP_PRD_MT_TIME_CD,
TBL_LU_FAC.TEMP_PRD,
TBL_LU_FAC.ROLLOVER_LMT,
TBL_LU_FAC.ROLLOVER_TERM_MT_TIME_CD,
TBL_LU_FAC.ROLLOVER_TERM,
TBL_LU_FAC.FX_LMT,
TBL_LU_FAC.PRE_STTLMT_LMT,
TBL_LU_FAC.IS_EFFECT_NOT_CLEARED,
TBL_LU_FAC.IS_AGAINST_FDR,
TBL_LU_FAC.MT_GRTEE_TYP_CD,
TBL_LU_FAC.BENEFICIARY_NM,
TBL_LU_FAC.DT_MATURITY,
CASE
WHEN tbl_lu_fac.dt_maturity IS NOT NULL AND v_last_disb_mt_time_cd = 'Y' THEN
ADD_MONTHS(tbl_lu_fac.dt_maturity, 12 * v_last_disb)
WHEN tbl_lu_fac.dt_maturity IS NOT NULL AND v_last_disb_mt_time_cd = 'M' THEN
ADD_MONTHS(tbl_lu_fac.dt_maturity, v_last_disb)
WHEN tbl_lu_fac.dt_maturity IS NOT NULL AND v_last_disb_mt_time_cd = 'D' THEN
tbl_lu_fac.dt_maturity + v_last_disb
ELSE
NULL
END dt_last_disb_allow,
TBL_LU_FAC.CUSTOM_FLD_1,
TBL_LU_FAC.CUSTOM_FLD_2,
TBL_LU_FAC.CUSTOM_FLD_3,
TBL_LU_FAC.CUSTOM_FLD_4,
TBL_LU_FAC.CUSTOM_FLD_5,
TBL_LU_FAC.CUSTOM_FLD_6,
TBL_LU_FAC.CUSTOM_FLD_7,
TBL_LU_FAC.CUSTOM_FLD_8,
TBL_LU_FAC.CUSTOM_FLD_9,
TBL_LU_FAC.CUSTOM_FLD_10,
TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_1,
TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_2,
TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_3,
TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_4,
TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_5,
TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_6,
TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_7,
TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_8,
TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_9,
TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_10,
TBL_LU_FAC.REMARK,
TBL_LU_FAC.IS_DOWNLOAD,
TBL_LU_FAC.DT_DOWNLOAD,
TBL_LU_FAC.DT_TRXN_SRC,
TBL_LU_FAC.TRXN_ID,
TBL_LU_FAC.CREATED_BY,
TBL_LU_FAC.UPDATED_BY,
TBL_LU_FAC.DT_CREATED,
TBL_LU_FAC.DT_UPDATED,
TBL_LU_FAC.DEPOSIT_RATIO,
TBL_LU_FAC.PREV_DT_MATURITY,
TBL_LU_FAC.VERSION,
TBL_LU_FAC.MAIN_LINE_LU_FAC_ID,
TBL_CP_MT_FAC_TYP.DSCP "cp_mt_fac_typ_dscp",
TBL_MT_FAC.DSCP "mt_fac_dscp",
TBL_MT_FAC.IS_CUR_CHANGEABLE,
TBL_LU_MT_PUR.DSCP "lu_mt_pur_dscp",
TBL_MT_BR.DSCP "mt_br_dscp",
TBL_MT_CUR.BUY_RATE "mt_cur_rate",
TBL_MT_CUR.DSCP "mt_cur_dscp",
TBL_MT_REPYMT_TYP.DSCP "mt_repymt_typ_dscp",
TBL_MT_PYMT_TERM.DSCP "mt_pymt_term_dscp",
TBL_MT_REPYMT_SCHD.DSCP "mt_repymt_schd_dscp",
TBL_MT_GRTEE_TYP.DSCP "mt_grtee_typ_dscp",
DSCP1.DSCP "custom_fld_mt_resrc_dscp_1",
DSCP2.DSCP "custom_fld_mt_resrc_dscp_2",
DSCP3.DSCP "custom_fld_mt_resrc_dscp_3",
DSCP4.DSCP "custom_fld_mt_resrc_dscp_4",
DSCP5.DSCP "custom_fld_mt_resrc_dscp_5",
V_LMT_AVAILABLE "lmt_available",
V_MAX_CUSTOM_FLD_9 "max_custom_fld_9",
TBL_LU_FAC.IS_SELF_SRV_LOAN,
TBL_LU_FAC.MT_REPYMT_SCHD_CD,
TBL_MT_FAC.MT_FAC_CAT_CD,
TBL_LU_FAC.TRANS_FEE_DBT,
TBL_LU_FAC.TRANS_FEE_CDT,
TBL_LU_FAC.DV_RATIO,
TBL_LU_FAC.MT_FAC_PRC_ADJ_TYPE_CD,
ADJ.DSCP "adj_type_description",
TBL_LU_FAC.MT_FAC_TERM_TYPE_CD,
TBL_LU_FAC.MT_FIRST_REPYMT_FLAG,--add by zhengke 20071111 for bancs
TBL_LU_FAC.MT_LAST_REPYMT_FLAG,--add by zhengke 20071111 for bancs
TBL_LU_FAC.MT_AGREEMENT_FLAG,--add by zhengke 20071111 for bancs
TBL_LU_FAC.MT_RE_CAL_FLAG--add by zhengke 20071111 for bancs
FROM TBL_LU_FAC
INNER JOIN TBL_MT_CUR ON TBL_MT_CUR.CD = TBL_LU_FAC.MT_CUR_CD
INNER JOIN TBL_MT_BR ON TBL_MT_BR.CD = TBL_LU_FAC.MT_BR_CD
INNER JOIN TBL_MT_FAC ON TBL_MT_FAC.CD = TBL_LU_FAC.MT_FAC_CD
INNER JOIN TBL_FAC ON TBL_FAC.ID = TBL_LU_FAC.FAC_ID
LEFT JOIN TBL_CP_MT_FAC_TYP ON TBL_CP_MT_FAC_TYP.CD = TBL_LU_FAC.CP_MT_FAC_TYP_CD
LEFT JOIN TBL_CS_MT_FAC_TYP ON TBL_CS_MT_FAC_TYP.CD = TBL_LU_FAC.CP_MT_FAC_TYP_CD
LEFT OUTER JOIN TBL_MT_RESRC DSCP1 ON DSCP1.CD = TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_1
LEFT OUTER JOIN TBL_MT_RESRC DSCP2 ON DSCP2.CD = TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_2
LEFT OUTER JOIN TBL_MT_RESRC DSCP3 ON DSCP3.CD = TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_3
LEFT OUTER JOIN TBL_MT_RESRC DSCP4 ON DSCP4.CD = TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_4
LEFT OUTER JOIN TBL_MT_RESRC DSCP5 ON DSCP5.CD = TBL_LU_FAC.CUSTOM_FLD_MT_RESRC_CD_5
LEFT OUTER JOIN TBL_MT_GRTEE_TYP ON TBL_MT_GRTEE_TYP.CD = TBL_LU_FAC.MT_GRTEE_TYP_CD
LEFT OUTER JOIN TBL_MT_PYMT_TERM ON TBL_MT_PYMT_TERM.CD = TBL_LU_FAC.MT_PYMT_TERM_CD
LEFT OUTER JOIN TBL_MT_REPYMT_TYP ON TBL_MT_REPYMT_TYP.CD = TBL_LU_FAC.MT_REPYMT_TYP_CD
LEFT OUTER JOIN TBL_MT_REPYMT_SCHD ON TBL_MT_REPYMT_SCHD.CD = TBL_LU_FAC.MT_REPYMT_SCHD_CD
LEFT OUTER JOIN TBL_LU_MT_PUR ON TBL_LU_MT_PUR.CD = TBL_LU_FAC.LU_MT_PUR_CD
LEFT JOIN ebmaint.TBL_MT_FAC_PRC_ADJ_TYPE ADJ ON ADJ.CD = TBL_LU_FAC.MT_FAC_PRC_ADJ_TYPE_CD
WHERE TBL_LU_FAC.lu_app_id = lu_app_id_in
AND TBL_LU_FAC.MAIN_LINE_LU_FAC_ID IS NOT NULL
ORDER BY TBL_LU_FAC.main_line_lu_fac_id DESC;

END IF;

END;