Oracle:使用宽松的where条件的PLSQL查询-优雅的实现方式

Question

请看下面的查询。我怎样才能用一种更具可读性的方式来写它呢？我想减少IF的使用。

  vSQL := ' SELECT loc.RMV_REMI_VIRTUALE_COD, loc.LOC_DATA_VER_FIN FROM ENI_SAG_TSF_LOCALITA_DEF loc WHERE 1=1 ';
  IF P_LOC_LOCALITA_COD IS NOT NULL THEN
    vSQL := vSQL||' AND LOC.LOC_LOCALITA_COD = '''||P_LOC_LOCALITA_COD||''' ';
  END IF;
  IF P_ISTAT_CITTA IS NOT NULL THEN
    vSQL := vSQL||' AND loc.COMB_ISTAT_COD = '''||P_ISTAT_CITTA||''' ';
  END IF;
  IF P_PLAY_PLAYER_COD IS NOT NULL THEN
    vSQL := vSQL||' AND LOC.PLAY_PLAYER_COD = '''||P_PLAY_PLAYER_COD||''' ';
  END IF;
  IF P_LOC_DATA IS NOT NULL THEN
    vSQL := vSQL||' AND TO_TIMESTAMP ('''||P_LOC_DATA||''' , ''DD/MM/YYYY HH24:MI:SS.FF3'') BETWEEN LOC.LOC_DATA_VER_INI AND LOC.LOC_DATA_VER_FIN ';
  END IF; 

Answer 1

读了你的答案后，我发现了这个公式。你觉得那个怎么样？

SELECT loc.RMV_REMI_VIRTUALE_COD, loc.LOC_DATA_VER_FIN
  FROM ENI_SAG_TSF_LOCALITA_DEF loc
 WHERE 1 = 1
       AND (LOC.LOC_LOCALITA_COD = :P_LOC_LOCALITA_COD OR :P_LOC_LOCALITA_COD IS NULL)
       AND (loc.COMB_ISTAT_COD = :P_ISTAT_CITTA OR :P_ISTAT_CITTA IS NULL)
       AND (LOC.PLAY_PLAYER_COD = :P_PLAY_PLAYER_COD OR LOC.PLAY_PLAYER_COD IS NULL)
       AND (TO_TIMESTAMP (:P_LOC_DATA, 'DD/MM/YYYY HH24:MI:SS.FF3') BETWEEN LOC.LOC_DATA_VER_INI AND LOC.LOC_DATA_VER_FIN OR :P_LOC_DATA IS NULL)

Answer 2

嗯，我总是发现使用绑定变量而不是值连接的代码更容易阅读(而且这是更好的实践)。您没有说明SQL将如何运行，但假设有一个引用游标，您可以这样做：

vSQL := ' SELECT loc.RMV_REMI_VIRTUALE_COD, loc.LOC_DATA_VER_FIN FROM ENI_SAG_TSF_LOCALITA_DEF loc WHERE 1=1 ';
  IF P_LOC_LOCALITA_COD IS NOT NULL THEN
    vSQL := vSQL||' AND LOC.LOC_LOCALITA_COD = :P_LOC_LOCALITA_COD ';
  ELSE
    vSQL := vSQL||' AND (1=1 OR :P_LOC_LOCALITA_COD IS NULL)';
  END IF;
  IF P_ISTAT_CITTA IS NOT NULL THEN
    vSQL := vSQL||' AND loc.COMB_ISTAT_COD = :P_ISTAT_CITTA ';
  ELSE
    vSQL := vSQL||' AND (1=1 OR :P_ISTAT_CITTA IS NULL)';
  END IF;
  IF P_PLAY_PLAYER_COD IS NOT NULL THEN
    vSQL := vSQL||' AND LOC.PLAY_PLAYER_COD = :P_PLAY_PLAYER_COD ';
  ELSE
    vSQL := vSQL||' AND (1=1 OR :P_PLAY_PLAYER_COD IS NULL)';
  END IF;
  IF P_LOC_DATA IS NOT NULL THEN
    vSQL := vSQL||' AND TO_TIMESTAMP (:P_LOC_DATA, ''DD/MM/YYYY HH24:MI:SS.FF3'') BETWEEN LOC.LOC_DATA_VER_INI AND LOC.LOC_DATA_VER_FIN ';
  ELSE
    vSQL := vSQL||' AND (1=1 OR :P_LOC_DATA IS NULL)';
  END IF; 

  OPEN refcur FOR vSQL USING P_LOC_LOCALITA_COD, P_ISTAT_CITTA, P_PLAY_PLAYER_COD, P_LOC_DATA;

我添加ELSE子句是因为对于原生动态SQL，语句中绑定变量的数量必须是固定的。如果使用DBMS_SQL包，则不需要这样做。

至于避免IFs，您可以这样做：

vSQL := ' SELECT loc.RMV_REMI_VIRTUALE_COD, loc.LOC_DATA_VER_FIN FROM ENI_SAG_TSF_LOCALITA_DEF loc WHERE 1=1 ';
  || CASE WHEN P_LOC_LOCALITA_COD IS NOT NULL THEN
              ' AND LOC.LOC_LOCALITA_COD = :P_LOC_LOCALITA_COD '
          ELSE
              ' AND (1=1 OR :P_LOC_LOCALITA_COD IS NULL)'
          END
  || CASE WHEN P_ISTAT_CITTA IS NOT NULL THEN
              ' AND loc.COMB_ISTAT_COD = :P_ISTAT_CITTA '
          ELSE
              ' AND (1=1 OR :P_ISTAT_CITTA IS NULL)'
          END
... etc.

显然，您现在有了CASEs，但至少您丢失了所有的vSQL := vSQL ||位。

如果您添加了许多类似的条件，则可以将逻辑封装在如下函数中：

vSQL := ' SELECT loc.RMV_REMI_VIRTUALE_COD, loc.LOC_DATA_VER_FIN FROM ENI_SAG_TSF_LOCALITA_DEF loc WHERE 1=1 ';
  || and_condition ('LOC.LOC_LOCALITA_COD', 'BV1', P_LOC_LOCALITA_COD)
  || and_condition ('loc.COMB_ISTAT_COD', 'BV2', P_ISTAT_CITTA)
  ... etc.

(当然，这不适用于介于两者之间的情况)。

Answer 3

“可读性更好”有点主观，但是如果你不喜欢IF THEN代码块，有几个选择：

 vSQL := ' SELECT loc.RMV_REMI_VIRTUALE_COD, loc.LOC_DATA_VER_FIN FROM ENI_SAG_TSF_LOCALITA_DEF loc WHERE 1=1 ';

 vSQL := vSQL||
         NVL2(P_LOC_LOCALITA_COD, 
             ' AND LOC.LOC_LOCALITA_COD = '''||P_LOC_LOCALITA_COD||''' ',
              NULL);

 vSQL := vSQL||
         NVL2(P_ISTAT_CITTA,
              ' AND loc.COMB_ISTAT_COD = '''||P_ISTAT_CITTA||''' ',
              NULL);

 vSQL := vSQL||
         NVL2(P_PLAY_PLAYER_COD, 
              ' AND LOC.PLAY_PLAYER_COD = '''||P_PLAY_PLAYER_COD||''' ',
              NULL);

 vSQL := vSQL||
         NVL2(P_LOC_DATA, 
              ' AND TO_TIMESTAMP ('''||P_LOC_DATA||''' , ''DD/MM/YYYY HH24:MI:SS.FF3'') BETWEEN LOC.LOC_DATA_VER_INI AND LOC.LOC_DATA_VER_FIN ',
              NULL);

或

  vSQL := ' SELECT loc.RMV_REMI_VIRTUALE_COD, loc.LOC_DATA_VER_FIN FROM ENI_SAG_TSF_LOCALITA_DEF loc WHERE 1=1 '
        ||NVL2(P_LOC_LOCALITA_COD, 
               ' AND LOC.LOC_LOCALITA_COD = '''||P_LOC_LOCALITA_COD||''' ',
               NULL)
        ||NVL2(P_ISTAT_CITTA,
               ' AND loc.COMB_ISTAT_COD = '''||P_ISTAT_CITTA||''' ',
               NULL)
        ||NVL2(P_PLAY_PLAYER_COD,
               ' AND LOC.PLAY_PLAYER_COD = '''||P_PLAY_PLAYER_COD||''' ',
               NULL)
        ||NVL2(P_LOC_DATA,
               ' AND TO_TIMESTAMP ('''||P_LOC_DATA||''' , ''DD/MM/YYYY HH24:MI:SS.FF3'') BETWEEN LOC.LOC_DATA_VER_INI AND LOC.LOC_DATA_VER_FIN ',
               NULL);

希望这能帮上忙。