CREATE TABLE TestReportParameter

(

VarcharField VARCHAR2(20),

NumberField NUMBER(10,0),

DateField DATE

);

INSERT INTO TestReportParameter VALUES('a',1,SYSDATE-1);

INSERT INTO TestReportParameter VALUES('b',2,SYSDATE);

INSERT INTO TestReportParameter VALUES('c',3,SYSDATE+2);

INSERT INTO TestReportParameter VALUES('d',4,SYSDATE-2);

COMMIT;

处理的秘密在于对缺省值和输入值之间做一个判断，保持任何一种值的存在为TRUE即可。

对于单值数据的处理比较简单，参看下面脚本

SELECT *

FROM TestReportParameter

WHERE (' ' = &VarcharField OR VarcharField = &VarcharField)

AND (-1 = &NumberField OR NumberField = &NumberField)

SELECT *

FROM TestReportParameter

WHERE (

' '=DECODE(&VarcharField,' ',' ',&VarcharField)

OR

VarcharField IN (&VarcharField)

)

AND (

-1=DECODE(&NumberField,-1,-1,&NumberField)

OR

NumberField IN (&NumberField)

)