oracle 程序与 case 取决于参数

Question

嗨，我想做一个如下的程序:

CREATE OR REPLACE PROCEDURE SOL.INSERT_LD_NEXTPROCESS (vgroupid NUMBER)
IS
VPERIODID     VARCHAR2 (10);
vPROCSESSID   NUMBER;

CURSOR c
IS
  SELECT COMPANYID,
         GROUPID,
         PERIODID,
         FN_PPROCESSCURRENT
FROM LIQUIDATIONSDETAILS
   WHERE     PROCESSID = FN_PPROCESSPREVIOUS
         AND (UNCOLLECTED > 0 OR INVOICE = 0)

我想添加一个额外的过滤器，它取决于参数:

CASE WHEN vgroupid > -1 then 
        AND GROUPID = vgroupid
ELSE
        NULL
END
...

所以那里的原因就像

WHERE PROCESSID = FN_PPROCESSPREVIOUS
AND (UNCOLLECTED > 0 OR INVOICE = 0) AND GROUPID = vgroupid

当 vgroupid = -1 时，我需要所有记录，当 vgroupid > -1 时，我只需要 vgroupid 中的记录

任何的想法？

Answer 1

CURSOR c
IS
  SELECT COMPANYID,
         GROUPID,
         PERIODID,
         FN_PPROCESSCURRENT
FROM LIQUIDATIONSDETAILS
   WHERE     PROCESSID = FN_PPROCESSPREVIOUS
         AND (UNCOLLECTED > 0 OR INVOICE = 0)
         AND (((GROUPID = vgroupid) AND (vgroupid > -1)) OR (vgroupid = -1))

例如:
如果 vgroupid = -1 ，那么最后一个条件将是 (((GROUPID = -1) AND (-1 > -1)) OR (-1 = -1))或 ((forever_false AND forever_false) OR (forever_true))或 (-1 = -1) - 所有记录

相反，如果 vgroupid = 123最后一个条件是 (((GROUPID = 123) AND (123 > -1)) OR (123 = -1))或 (((GROUPID = 123) and forever_true) OR (forever_false))或 (GROUPID = 123) - 只有 123 个 GROUPID