Oracle PLSQL minimize multiple IF Conditions or prepare dynamically

Question

I have a stored procedure as shown below preparing dynamic where clause. There are multiple if conditions reason being if any IN parameter is null, that field shouldn't end up in where clause.

This stored procedure is working fine. However, I'm trying to,

1. Loop through the IN parameters (and not OUT parameters) and prepare the IF conditions inside the loop instead of writing one by one. In any case, the number of arguments to stored procedure are fixed.

2. At the same time, use bind variables for better performance

CREATE OR REPLACE TEST_PROC(VAR1 IN VARCHAR2, VAR2 IN DATE, VAR3 IN DATE, VAR4 IN NUMBER, VAR5 IN NUMBER, VAR6 IN VARCHAR2, VAR7 IN VARCHAR2, VAR8 IN VARCHAR2, VAR9 IN VARCHAR2, VAR10 IN VARCHAR2, P_OUT1 OUT SYS_REFCURSOR, P_OUT2 OUT VARCHAR2 )

AS

V_SQL CLOB;

BEGIN

V_SQL := 'SELECT COL1, COL2, COL3, COL4, COL5 FROM TABLE1 WHERE 1 = 1 ';

/* EQUALS CONDITION */

IF VAR1 IS NOT NULL THEN
   V_SQL := V_SQL || 'AND COL1 = :VAR1';
ELSE
   V_SQL := V_SQL || 'AND ((1 = 1) OR :VAR1 IS NULL)';
END IF;

/* LESS THAN CONDITION */

IF VAR2 IS NOT NULL THEN
   V_SQL := V_SQL || 'AND COL1 < :VAR2';
ELSE
   V_SQL := V_SQL || 'AND ((1 = 1) OR :VAR2 IS NULL)';
END IF;
.
.
.
.
IF VAR10 IS NOT NULL THEN
   V_SQL := V_SQL || 'AND COL10 = :VAR10';
ELSE
   V_SQL := V_SQL || 'AND ((1 = 1) OR :VAR10 IS NULL)';
END IF;

OPEN P_OUT1 FOR V_SQL USING VAR1, VAR2, VAR3, VAR4, VAR5, VAR6, VAR7, VAR8, VAR9, VAR10;

EXCEPTION
   WHEN OTHERS THEN
      P_OUT2 := SQLERRM;

END;
/

As you see I have multiple if conditions. Is there a way to rewrite the same in a loop? While doing so, I still need to have bind variables for better performance.

If there is a different better approach than this in terms of performance, please suggest.

Answer 1

Let's look at the first condition:

IF VAR1 IS NOT NULL THEN
   V_SQL := V_SQL || 'AND COL1 = :VAR1';
ELSE
   V_SQL := V_SQL || 'AND ((1 = 1) OR :VAR1 IS NULL)';
END IF;

If VAR fails this check, then it is null. Then you check this again inside the query. So you may just put the entire condition inside the query:

where 1 = 1
  and (col1 = :var1 or :var1 is null)
  and (colX <predicate> :varY or :varY is null)

After this you do not need any dynamic SQL and bind variables, you may use a plain query:

...
  open p_out1 for
    select <cols>
    from table1
    where 1 = 1
      and (col1 = var1 or var1 is null)
      and (colX <predicate> varY or varY is null)
      ...
    ;