oracle 动态sql

用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);