用decode取代where条件的动态sql
select * from xxx where decode(:str,'','1',null,'1',columnname) = decode(:str,'','1',null,'1',:str)
类似,对于group by 等也可以
下面是偶摘的程序中一个pro*c中的语句:
EXEC SQL INSERT INTO setl_result(SETL_SCHM_ID,CYCLE_ID,SETL_OBJ_ID,OPP_OBJ_ID,PFL,FEE)
SELECT :iSETL_SCHM_ID,
:strCYCLE_ID,
decode(:strSETL_OBJ_ID,'$',a.reg_setl_obj_id,:strSETL_OBJ_ID),
decode(:strOPP_OBJ_ID,'$',a.accs_setl_obj_id,:strOPP_OBJ_ID),
sum(a.pfl),
sum(a.fee)
FROM setl_result_detail a
WHERE a.cycle_id = :strCYCLE_ID
AND a.SETL_SCHM_ID = :iSETL_SCHM_ID
GROUP BY decode(:strSETL_OBJ_ID,'$',a.reg_setl_obj_id,:strSETL_OBJ_ID),
decode(:strOPP_OBJ_ID,'$',a.accs_setl_obj_id,:strOPP_OBJ_ID);
select * from xxx where decode(:str,'','1',null,'1',columnname) = decode(:str,'','1',null,'1',:str)
类似,对于group by 等也可以
下面是偶摘的程序中一个pro*c中的语句:
EXEC SQL INSERT INTO setl_result(SETL_SCHM_ID,CYCLE_ID,SETL_OBJ_ID,OPP_OBJ_ID,PFL,FEE)
SELECT :iSETL_SCHM_ID,
:strCYCLE_ID,
decode(:strSETL_OBJ_ID,'$',a.reg_setl_obj_id,:strSETL_OBJ_ID),
decode(:strOPP_OBJ_ID,'$',a.accs_setl_obj_id,:strOPP_OBJ_ID),
sum(a.pfl),
sum(a.fee)
FROM setl_result_detail a
WHERE a.cycle_id = :strCYCLE_ID
AND a.SETL_SCHM_ID = :iSETL_SCHM_ID
GROUP BY decode(:strSETL_OBJ_ID,'$',a.reg_setl_obj_id,:strSETL_OBJ_ID),
decode(:strOPP_OBJ_ID,'$',a.accs_setl_obj_id,:strOPP_OBJ_ID);