oracle程序与案例它取决于参数

嗨,我想做一个如下的程序：

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中的记录

任何的想法？

解决方法

CURSOR c
IS
  SELECT COMPANYID,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))或(123 = -1))或(((GROUPID = 123)和forever_true)或(forever_false))或(GROUPID = 123) – 只有123 GROUPID

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!