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,