Processing of optional Oracle Parameters

This small problem is also common today. Record it.

Query data within an input range: Select * From Table1 where fld1> = A and between <= B

Both A and B are optional input items, which have the following combinations:

1: A has a value, B has no value, that is, select * From Table1 where fld1> =

2: A has a value, B has a value, that is, select * From Table1 where fld1> = A and then <= B

3: A has no value, B has no value, that is, select * From Table1

4: A has no value, B has a value, that is, select * From Table1 where partition <= B

The basic syntax is as follows (Oracle). The fld1 field is a numeric function.

Select * From Table1

Where

Decode ('& 1', '', 1, fld1)> = to_number (decode (' & 1','', 1, '& 1 '))

And

Decode ('& 2', '', 1, fld1) <= to_number (decode (' & 2','', 1, '& 2 '))

The basic idea is:

If the input value is null, the system takes the initiative to give 1 and set the field to 1.
If the value is null, this condition is converted to 1> = 1, and the value is constant to true, so that the condition is invalid (that is, it is valid ).

Similarly, for an optional parameter

Select * From Table1

Where

Decode ('& 1', '', 1, fld1)> = to_number (decode (' & 1','', 1, '& 1 '))