Oracle PLSQL最小化多个IF条件或动态准备

Question

我有一个存储过程，如下所示，准备动态where子句。如果IN参数为null，则存在多个if条件，该字段不应在where子句中结束。

这个存储过程运行良好。不过，我想，

1. 循环通过IN参数(而不是OUT参数)，并在循环中准备IF条件，而不是一个接一个地编写。无论如何，存储过程的参数数是固定的。

1. 同时使用绑定变量以获得更好的性能

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;
/

如你所见，我有多个条件。有办法在循环中重写相同的内容吗？在这样做的同时，我仍然需要绑定变量以获得更好的性能。

如果在性能方面有不同的更好的方法，请提出建议。

Answer 1

让我们看一下第一个条件：

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;

如果VAR失败了，那么它就是null。然后在查询中再次检查这一点。因此，您可以将整个条件放在查询中：

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

在此之后，您不需要任何动态SQL和绑定变量，您可以使用普通查询：

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