Oracle Function: Case and Decode

Oracle Function: Case and Decode

CASE expression perform if-then-else logic in SQL without having to use PL/SQL.
CASE works in a similar manner to DECODE().
CASE is ANSI-compliant.
There are two types of CASE expressions:
Simple case expressions use expressions to determine the returned value.
Searched case expressions use conditions to determine the returned value.
Simple CASE expressions use expressions to determine the returned value and have the following syntax:
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
END
where
search_expression is the expression to be evaluated.
expression1, expression2, ..., expressionN are the expressions to be evaluated against search_expression.
result1, result2, ..., resultN are the returned results (one for each possible expression). If expression1 evaluates to search_expression, result1 is returned, and so on.
default_result is the default result returned when no matching expression is found.

1 CREATE TABLE t1 (
2 pname VARCHAR2(10),
3 cases NUMBER(3));
4 
5 INSERT INTO t1 VALUES ('',2);
6 INSERT INTO t1 VALUES ('Dan',3);
7 INSERT INTO t1 VALUES ('Jack',2);
8 INSERT INTO t1 VALUES ('Helen',1);
9 COMMIT;

case 1:

1 SELECT CASE
2          WHEN D.PNAME IS NULL THEN
3           'DATA IS NULL'
4          ELSE
5           D.PNAME
6        END,
7        D.CASES
8   FROM T1 D

case 2:

1   SELECT CASE D.PNAME
2            WHEN D.PNAME THEN
3             D.PNAME
4            ELSE
5             'DATA IS NULL'
6          END
7     FROM T1 D

case 3:

1 SELECT CASE
2          WHEN 1 > 2 THEN
3           '1 大于 2'
4          ELSE
5           '1 小于 2'
6        END
7   FROM DUAL

在DECODE函数中，NULL值可以很明确的被处理：

1 SELECT DECODE(T.PNAME, NULL, 'NO DATA', T.PNAME), 
2 T.CASES FROM T1 T

case 4:(条件1和条件2是等价的)

1 SELECT CASE
2          WHEN EXISTS (SELECT * FROM DUAL) THEN
3           '存在数据EXISTS'
4          WHEN NOT EXISTS (SELECT * FROM DUAL) THEN
5           '不存在数据NOT EXISTS'
6          ELSE
7           'NULL(EXISTS条件语句中没有查询到结果集)'
8        END
9   FROM DUAL

限制条件后(没有结果集，直接跳到else执行(在case中需要进行空结果集的处理--需要在最后接上ELSE))

1 SELECT CASE
2          WHEN EXISTS (SELECT * FROM DUAL WHERE 1 = 2) THEN
3           '存在数据EXISTS'
4          ELSE
5           'NULL(EXISTS条件语句中没有查询到结果集)'
6        END
7   FROM DUAL

Case5：(也可以添加限制条件:WHERE 1=2)

1 SELECT CASE
2          WHEN 'X' IN (SELECT * FROM DUAL) THEN
3           '存在数据IN'
4          ELSE
5           '不存在数据'
6        END
7   FROM DUAL

PL/SQL中的CASE使用:

 1 DECLARE
 2   V_A NUMBER DEFAULT 1;
 3 BEGIN
 4   CASE V_A
 5     WHEN 1 THEN
 6       DBMS_OUTPUT.PUT_LINE('1!');
 7     WHEN 2 THEN
 8       DBMS_OUTPUT.PUT_LINE('2!');
 9     WHEN 3 THEN
10       DBMS_OUTPUT.PUT_LINE('3!');
11   END CASE;
12 END;

当将V_A的初始值改为4：没有与之匹配的值，则提示错误信息:

处理该问题需要在后面添加上else语句，用于捕获不匹配的结果：

 1 DECLARE
 2   V_A NUMBER DEFAULT 4;
 3 BEGIN
 4   CASE V_A
 5     WHEN 1 THEN
 6       DBMS_OUTPUT.PUT_LINE('1!');
 7     ELSE 
 8       DBMS_OUTPUT.PUT_LINE('没有匹配的结果!');
 9   END CASE;
10 END;

在进行CASE语句的过程中，需要注意，避免使用NULL值作为条件，虽然在语法上是对的，但是他得不到想要的结果

When creating selector CASE statements, you cannot have NULL in the list of possible values.

Although the following code is correct from the syntax point of view, it doesn't work:
例如：

 1 DECLARE
 2   V_A NUMBER DEFAULT NULL;
 3 BEGIN
 4   CASE V_A
 5     WHEN NULL THEN
 6       DBMS_OUTPUT.PUT_LINE('1!');
 7     WHEN 2 THEN
 8       DBMS_OUTPUT.PUT_LINE('2!');
 9     ELSE
10       DBMS_OUTPUT.PUT_LINE('没有匹配的!');
11   END CASE;
12 END;

理论结果应该是1，结果是：

可以使用case条件语句调用存储过程和函数。
DECODE的用法：

DECODE(value, search_value, result, default_value) compares value with search_value. If the values are equal, DECODE() returns result, otherwise default_value is returned. DECODE() allows you to perform if-then-else logic in SQL without having to use PL/SQL.

1、

1 SELECT DECODE(VALUE, < IF THIS VALUE >, < RETURN THIS VALUE >)
2 FROM DUAL;

2、

1 SELECT DECODE (value,<if this value>,<return this value>,
2                      < if this value>,<return this value>,
3                       ....)
4 FROM DUAL;

3、

1 SELECT DECODE (value,<if this value>,<return this value>,
2                      <if this value>,<return this value>,
3                      ....
4                      <otherwise this value>)
5 FROM DUAL;

CASE:

1  SELECT DECODE(1, 1, 'A', 2, 'B', 3, 'C', 4, 'D', 'Default') FROM DUAL

在DECODE函数中，NULL值可以很明确的被处理：

1 SELECT DECODE(T.PNAME, NULL, 'NO DATA', T.PNAME), 
2 T.CASES FROM T1 T