具有多个OR - IN's的PL/Sql IF循环

Question

我有一个pl/sql循环，如下所示：

IF A IN (1,2,3,4,5) OR B IN (1,2,3,4,5) OR C IN (1,2,3,4,5) 
THEN
   --Do Something--
ELSIF A IN (6,7) AND B IN (6,7) AND C IN (6,7) 
THEN
   --Do Something Else--
ELSE
   --Do Nothing--
END IF;

有更好的方法来写上面的循环吗？

以上示例仅供参考。我最初的程序在IF循环中有更多的条件。

请帮我优化一下。我没有主意了。

谢谢。

Answer 1

创建一个集合 (嵌套表)类型来保存值和测试条件，然后可以使用`多功能‘操作来比较它们：

• (a MULTISET INTERSECT DISTINCT b) IS NOT EMPTY相当于OR语句；
• (a MULTISET EXCEPT DISTINCT b) IS EMPTY相当于AND语句

为了便于测试，我将代码包装在一个函数中：

SQL Fiddle

Oracle 11g R2架构设置

CREATE OR REPLACE TYPE Number_Table_Type AS TABLE OF NUMBER
/

CREATE OR REPLACE FUNCTION Test_Multiple_Values (
  A IN NUMBER,
  B IN NUMBER,
  C IN NUMBER,
  D IN NUMBER,
  E IN NUMBER,
  F IN NUMBER,
  G IN NUMBER,
  H IN NUMBER
) RETURN NUMBER
AS
  inputs Number_Table_Type := Number_Table_Type( A, B, C, D, E, F, G, H );
BEGIN
  IF ( inputs MULTISET INTERSECT DISTINCT Number_Table_Type( 1, 2, 3, 4, 5 ) ) IS NOT EMPTY THEN
     RETURN 1;
  ELSIF ( inputs MULTISET EXCEPT DISTINCT Number_Table_Type( 6, 7 ) ) IS EMPTY THEN
     RETURN 2;
  ELSE
     RETURN 3;
  END IF;
END;
/

查询1

WITH inputs AS (
  SELECT 1 AS A, 2 AS B, 3 AS C, 4 AS D, 5 AS E, 6 AS F, 7 AS G, 8 AS H FROM DUAL
  UNION ALL
  SELECT 6, 7, 6, 7, 6, 7, 6, 7 FROM DUAL
  UNION ALL
  SELECT 8, 9, 10, 11, 12, 13, 14, 15 FROM DUAL
)
SELECT A, B, C, D, E, F, G, H, Test_Multiple_Values( A, B, C, D, E, F, G, H ) FROM inputs

结果

| A | B |  C |  D |  E |  F |  G |  H | TEST_MULTIPLE_VALUES(A,B,C,D,E,F,G,H) |
|---|---|----|----|----|----|----|----|---------------------------------------|
| 1 | 2 |  3 |  4 |  5 |  6 |  7 |  8 |                                     1 |
| 6 | 7 |  6 |  7 |  6 |  7 |  6 |  7 |                                     2 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 |                                     3 |

Answer 2

尝试CASE语句。它将比IF语句更具可读性。在这里查查更多

Answer 3

CASE
WHEN (A IN (1,2,3,4,5) OR B IN (1,2,3,4,5) OR C IN (1,2,3,4,5)) THEN 
--do something
WHEN (A IN (6,7) AND B IN (6,7) AND C IN (6,7)) THEN 
--do something
ELSE 
--do nothing
END CASE;