带有大小写的oracle过程取决于参数 [英] oracle procedure with case It depends from parameter

问题描述

我想进行如下操作:

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)

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

I want to add an extra filter it depends from parameter:

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

when vgroupid = -1 then I need all records an when vgroupid > -1 then I need only the records in vgroupid

有什么主意吗?

推荐答案

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)-所有记录

for example: if vgroupid = -1, then last condition will be (((GROUPID = -1) AND (-1 > -1)) OR (-1 = -1)) or ((forever_false AND forever_false) OR (forever_true)) or (-1 = -1) - all records

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

instead, if vgroupid = 123 last condition will be (((GROUPID = 123) AND (123 > -1)) OR (123 = -1)) or (((GROUPID = 123) and forever_true) OR (forever_false)) or (GROUPID = 123) - only 123 GROUPID

这篇关于带有大小写的oracle过程取决于参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！